Matching date fields 2

[paulminne]

Hi,

I am trying to run an SQL query to select all of the records from a query with JobDates matching a date entered into a text box on a form. The text box contents is automatically populated by date().

However it doesnt seem to find any matching records, even when I am sure there are records with the same JobDate.

The code:
**********************************
Dim strInput As Date
Dim db As Database
Dim rs As ADODB.Recordset
Dim strSQL As String

stDocName = "JobsOverdue"

strInput = txtDate.Value

Set rs = New ADODB.Recordset

rs.ActiveConnection = CurrentProject.Connection

stLinkCriteria = "[JobDate]= #" & strInput & "#"

strSQL = "SELECT * FROM JobsBooked2 WHERE " & stLinkCriteria

Set db = CurrentDb

'Open the recordset
rs.Open strSQL

If (rs.EOF) Then
MsgBox "There are currently no oustanding bookings as of " & strInput & "", vbExclamation
Else
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
End If

Set rs = Nothing
Set db = Nothing

**************************************

Thanks for any help,

- Paul

PS. There are no errors, just not finding any records...

 

[Darrylles]

Hi,

Not sure, but have you tried defining strInput as String?

stLinkcriteria contents are being formatted as date (as they should be), though they are already of type date.

Just an idea - may be well off-mark, but it's a simple/quick test.

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[paulminne]

Hi,

I have tried changing this from string to date and back again with no luck. I have also run some error checking and have determined that it is a problem with matching it to the date() function...for some reason it wont match with the fields in the format I have stored them in the database.

Quite stupid,

thanks for your help,

- Paul

 

[cmmrfrds]

A datetime data type has time in the field so it is best on a search to format the field to ignore the time portion of the date unless you need the time in your search.


the string should resolve to look like this.

Where Format([JobDate],"Short Date&quot = #11/15/02#

the problem is that the Format function uses quotes around the part that determines the format and you will need to control for this when building the string.

strSQL = "SELECT * FROM JobsBooked2 WHERE " & _
"Format([JobDate]," & chr(34) "Short Date" & _
chr(34) & &quot = #" & "11/15/02" & "#"

debug.print strSQL '- check the string out.

 

[Darrylles]

Hi again,

OK, then use the Format function in conjunction with the Date function to get a 'standard' date.

I'm sure the Date() function returns this:

ddmmyyyy:hhmmss (maybe yyyymmdd)

So you are comparing: e.g.

02/02/2003 with 02/02/2003/23:15:04

It can never be equal.

The solution depends on what is or is not formatted.

You can format at database field level - to store just the date. You can format at field level using the field properties, you can format it in code using Format().

Sorry - too many permutations for me to GIVE you the right answer, but format and the Format() function is the key.

Use your error-checking as you already have done, in parallel with these formatting options - using debug and msgboxes, you'll see if they 'match'.

Hope this helps you,

Darrylle




"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[Darrylles]

Hi,

Sorry cmm - you got in between my reading Pauls response and writing mine.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk