Code problem

[Nero]

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??

 

[Tom Morales]

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

 

[Nero]

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

 

[Dave Hawley]

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.

 

[Nero]

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

 

[Dave Hawley]

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))

 

[Nero]

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.

 

[Dave Hawley]

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.

 

[Nero]

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.

 

[Tom Morales]

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