Recordset where date = ......

[Lonx]

Hey guys,

I'm trying to pull information out of a database where the date is equal to (whateva). But i keep ending up with no records even when i know that the data exists.

The field in the database is set to the date format "2005/05/17".
and newdate in the code is the same format "2005/05/17"
I just don't understand....

Code...

Public Sub Change_Date(newdate As Date)

Dim dateRS As Recordset
Set dateRS = DB.OpenRecordset("SELECT FixtureDate FROM Fixtures Where fixturedate = " & newdate & "")

If dateRS.RecordCount <> 0 Then
Fdate = dateRS.Fields("fixturedate").Value
End If

End Sub


Thank you

 

[Giraffe69]

different db platforms like their dates in different formats, eg MS access SQL likes #25/12/2005#, oracle sql likes '25dec2005'.

the way to write the date in the sql is independent of how the column may be formatted when the table's displayed.

hope this helps

 

[Giraffe69]

oops, MS access likes #mm/dd/yyyy# so Christmas would be #12/25/2005#...


what db provider are you using?

 

[Lonx]

Access, but i just realised something, I changed the code so that it updates a record using "newdate" and when i looked at the value it changed, It changed it to "1900/01/22" I still can't find the link between the value of newdate (2005/05/17) and the value inputted (1900/01/22)

Thanks

 

[RoyVidar]

He - he

in the immediate pane try:

[tt]? format(2005/05/17,"dd/mm/yyyy")[/tt]

You'll need to format the date into the string, also ensuring a non ambiguous format, for instance:

[tt]...Where fixturedate = #" & format(newdate,"yyyy-mm-dd") & "#")[/tt]

Roy-Vidar

 

[Lonx]

newdate = (Calendar1.Month & "-" & Calendar1.Day & "-" & Calendar1.Year)

But when i put a watch on newdate the format is "yyyy/mm/dd"

Is there a way i can change the date format in vb, Thanks.

 

[bjd4jc]

Are you storing your date field in access as Date/Time? or as Text?