Monday Blues

[rookery]

Hi there,

can anyone tell me why the following code works if the "InitialDate" field is a text field but not when it is set to a Date/Time type:

Private Sub Command0_Click()
Dim dbs As Database
Dim myrs As Recordset
Set dbs = CurrentDb
Set myrs = dbs.OpenRecordset("SELECT * FROM Customers WHERE InitialDate = '04/01/2000'&quot
Debug.Print myrs!CustId

Please dont say that I should put "#" where the "'" are at the moment (around the date) as it doesn't work. Anyone's brain not fried from the weekend??

 

[jebry]

Hi!

Try this:

Private Sub Command0_Click()
Dim dbs As Database
Dim myrs As Recordset
Set dbs = CurrentDb
Set myrs = dbs.OpenRecordset("SELECT * FROM Customers WHERE InitialDate = " & CDate("04/01/2000&quot
Debug.Print myrs!CustId

hth
Jeff Bridgham
bridgham@purdue.edu

 

[jebry]

Hi!

Try this:

Private Sub Command0_Click()
Dim dbs As Database
Dim myrs As Recordset
Set dbs = CurrentDb
Set myrs = dbs.OpenRecordset("SELECT * FROM Customers WHERE InitialDate = " & CDate("04/01/2000&quot
Debug.Print myrs!CustId

Please note, I haven't tested this.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[rookery]

Hi Jeff

Thanks for your reply but I'm afraid no joy. I just keep getting a "No Current Record" error.

I find it hard to believe that no-one else has ever come across this problem. Am I just asking moronic questions or something that dont deserve answering!!

 

[jebry]

Hi!

The no current record error means that the Select statement is returning no records so when you get to the debug statement there is no record to print. Check to be sure that you should be finding records with that criteria. For instance, if your date/time field is actually storing the date and time, then you will not be getting a direct match because of the time factor. You may need to change it to a Between .. And statement and include the time in the CDate statements.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[rookery]

Jeff

I managed to solve it eventually by using the DateValue function.

I hadn't thought of the Time factor until you mentioned it but even when I changed the Format property in the InitialDate filed to ShortDate, it still fell over on a "The Number You Entered isnt Valid For This Field" error. I used the code below:

Private Sub Command0_Click()
Dim dbs As Database
Dim myrs As Recordset
Set dbs = CurrentDb
Set myrs = dbs.OpenRecordset("SELECT * FROM Customers WHERE InitialDate = " & #4/1/2000# & "&quot
Me![Text1] = myrs!CustId

I suppose at least I've found a roundabout way of doing it but it would be nice to pinpoint my mistakes if you can see them. Thanks for your responses.