Query on Datetime field 1

[Rules2liveby]

A Table has a field called [Callback] as Datetime (8) which is on SQL Server 2000
I would like to call a form and filter based on this field from an unbound text box in the current form called txtCallbackdate

A watch on stLinkCriteria = "[Callback] = 24/05/2004" (which I would expect)

I have used the following VB String:

stLinkCriteria = "[Callback] = " & CDate(Me.txtCallbackdate)
stDocName = "frmMarketingList"
DoCmd.OpenForm stDocName, , , stLinkCriteria

My problem is that the resulting form returns no rows... I have checked that a manual SQL query filtering in the same way does return several rows...

Please help...

Patrick

 

[cmmrfrds]

Try putting the date in US format of 'mm/dd/yyyy' and put single quotes around the date since it is a literal.

stLinkCriteria = "[Callback] = "
& chr(39) & CDate(Me.txtCallbackdate) & chr(39)

If you need to account for time in the date field which there are a number of ways to do. One way is to use a range for the day. i.e.
callback >= '05/24/2004' and callback <= '05/24/2004 23:59:59'
As you probably know, dates are stored internally as decimal numbers. Where the integer in the days since 1/1/1900 and the decimal part is a fraction of the day.
So, casting the date to an integer and then back to a date will strip off the time or use a formatting function to strip off the time.

SQL Server Convert Function.
Convert(varchar(10),callback,101) = '05/24/2004'
note'- 101 is the style which indicates integer part only. Also, sql server uses ' for all literals.

Access Format Function.
Format(callback,"Short Date") = #05/24/2004#
note'- access uses # to denote date literals.

Access cast the date to long and back to date.
cdate(clng(callback)) = #05/24/2004#

 

[Rules2liveby]

Bingo. That's cracked it - thanks.