Code problem
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Code problem

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Shropshire UK
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi,
    I have a command button with code behind it which retrieves data from an MS Access database and returns it to a worksheet using MS Query.The query retrieves data that is relevent to a specific date. If I edit my code and add the date that I want to use the query works fine.
    What I am trying to do is by means of an input box the user to enter the date they wish to use in the query.
    The code will then use the date entered into the variable to run the query. The code keeps crashing when it uses the variable.
    When I revert to entering the date directly into the code it works fine again.
    Could anyone tell me what I am doing wrong??

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Richland, Washington
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nero,
    I'm really shooting in the dark, here. Here's one thought: Could you be mixing types of variables? ie, is date in your query statement part of a string, while you are trying to input a Date variable? If that's the case, declaring your input box variable as a string may solve your problem.
    Tom

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Shropshire UK
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for your reply Tom.
    I Have tried declaring it as a date and as a string but it still keeps crashing.

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    You may have to use the DateSerial function to get Excel to recognise your date:

    Dim MyDate as Date

    MyDate =Range("A1")

    MyDate = dateserial(Year(MyDate),Month(MyDate),Year(MyDate))


    It would also help if you could paste the bit of code where you are parsing your date.



  5. #5
    New Member
    Join Date
    Mar 2002
    Location
    Shropshire UK
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,
    Here is the code that works fine.
    What I want to do is replace the date (2002-02-27)with the variable.
    Sorry it looks very confusing when I pasted it here.
    ___________________________________________

    Dim MyDate As Date

    Sheets("McMon Data").Select
    MyDate = InputBox("Enter the date for the day of the data you are trying to retrieve", "Message")
    Range("A2").Select
    With Selection.QueryTable
    .Connection = Array(Array( _
    "ODBC;DSN=MS Access Database;DBQ=M:DatabaseMachMon.mdb;DefaultDir=M:Database;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTime" _
    ), Array("out=5;"))
    .CommandText = Array( _
    "SELECT ProdDetails.MC, ProdDetails.Shift_ID, ProdDetails.Shift_Pat, ProdDetails.Date, ProdDetails.T5, ProdDetails.T6, ProdDetails.T7, ProdDetails.T8, ProdDetails.T15, ProdDetails.T16, ProdDetails.T17" & Chr(13) & "" _
    , _
    "" & Chr(10) & "FROM `M:DatabaseMachMon`.ProdDetails ProdDetails" & Chr(13) & "" & Chr(10) & "WHERE (ProdDetails.Date={ts'2002-02-27 00:00:00'}) AND (ProdDetails.Shift_Pat=1) AND (ProdDetails.Shift_ID In ('A','B'))" & Chr(13) & "" & Chr(10) & "ORDER BY ProdDetails.Shift_ID, ProdDetails.Shift_Pat, ProdDetails.MC")
    .BackgroundQuery = True
    End With

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not too sure but try:

    MyDate = _
    Application.InputBox("Enter the date for the day of the data you are trying to retrieve", "Message", , , , , , 1)
    If Not IsDate(MyDate) Then Exit Sub
    MyDate = DateSerial(Year(MyDate), Month(MyDate), Day(MyDate))

  7. #7
    New Member
    Join Date
    Mar 2002
    Location
    Shropshire UK
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,
    I have tried your suggetion.
    The code does not crash now but is exiting after the date has been entered via the exit sub.
    Does this mean the date format is wrong?

    Thanks for your help.

  8. #8
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, I would say so!

    Let's try this instead

    MyDate = _
    Application.InputBox("Enter the date for the day of the data you are trying to retrieve. Format should be: yy/mm/dd", "Message", "yy/mm/dd", , , , , 1)
    MyDate = DateSerial(Year(MyDate), Month(MyDate), Day(MyDate))
    If Year(MyDate) = 1900 Or MyDate = "12:00:00 AM" Then Exit Sub



    I have purposely used the Application.InputBox with Type 1 as this will only accept a number.



  9. #9
    New Member
    Join Date
    Mar 2002
    Location
    Shropshire UK
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,
    It is still the same as before.
    The code does not exit where I thought.The debug goes through all of the code but does not run the query.

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Richland, Washington
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Nero,
    Okay, here's another stab. Insert code such as:
    Dim dayte As Date, mydate As String
    Dim datinput As String
    mydayte = InputBox("Enter your date, pulease.")
    mydate = Format(mydayte, "YYYY-MM-DD HH:MM:SS")
    datinput = "{ts'" & mydate & "'}"

    'and then, modify your line of query string as follows..
    "WHERE (ProdDetails.Date=" & datinput & ") AND


    Tom

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com