VBA Sql date issues

[Larryg]

Hello;

I need some help. What am I doing wrong?

strSQL2 = "SELECT * " & _
"FROM tbl_WorkOrderTickets_Converted " & _
"WHERE([dDateOrderImported]) Like #" & Me.tb_dDate & "# & "" * "" &" _


And IsNull([tbl_WorkOrderTickets_Converted].[ProcessingCompletedDate]) ==> error 2465 Access can't find the field"1" referenced in your expression.

I know the fields ( [ProcessingCompletedDate] and [dDateOrderImported]) are in the table, and I know they are spelled correctly because I did a copy and paste of the field names. Table field is formatted General Date for both, and the form control (Me.tb_dDate) is formatted Short Date, hence the addition of the wildcard representing the time portion of the date/time stored in the General Date format.

Simplified...
strSQL2 = "SELECT * " & _
"FROM tbl_WorkOrderTickets_Converted " & _
"WHERE([dDateTicketImported]) Like #" & Me.tb_dDate & "# & "" * """ ==> returns no records when clearly there are records that meet the date criteria, however produces no errors.

Thanks...

[davegri]

Convert the dates to a long integer and the time part disappears....

Clng(Int(dDateOrderImpirted)) = Clng(Int(me.tb_dDate)) AND IsNull(....

[Micron]

Like is a string comparison operator. I do not think you can use it for date or numeric data types (unless maybe you coerce them to strings first).
For dates, use >= or <= or BETWEEN. However, BETWEEN causes the date to be cut off at midnight of the second date value, thus a record time stamped at 1:00 A.M. would not be returned where the date field value contains the time portion of the date.

[Larryg]

Convert the dates to a long integer and the time part disappears....

Clng(Int(dDateOrderImpirted)) = Clng(Int(me.tb_dDate)) AND IsNull(....

dvegri...
strSQL2 = "SELECT * " & _
"FROM tbl_WorkOrderTickets_Converted " & _
"WHERE(Clng(dDateOrderImported)) = Clng(Int(Me.tb_dDate)) And IsNull[ProcessingCompletedDate]" ==> produces Syntax error (missing operator) in query expression...

and...
strSQL2 = "SELECT * " & _
"FROM tbl_WorkOrderTickets_Converted " & _
"WHERE(Clng(dDateOrderImported)) = Clng(Int(Me.tb_dDate)) And IsNull([ProcessingCompletedDate])" ==> produces error of Too few parameters. Expected 1.

However, this works when running from the query window:
SELECT tbl_WorkOrderTickets_Converted.*, tbl__WorkOrderTickets_Converted.dDateOrderImported , tbl_WorkOrderTickets_Converted.ProcessingCompleted Date
FROM tbl_WorkOrderTickets_Converted
WHERE (((tbl_WorkOrderTickets_Converted.dDateOrderImport ed) Like [forms]![frm_AM_LoadOtherTickets]![tb_dDate] & "*") AND ((tbl_WorkOrderTickets_Converted.ProcessingComplet edDate) Is Null)); ==> provided desired results.

However when I convert the above into VBA sql and run it, it...
strSQL2 = "SELECT tbl_WorkOrderTickets_Converted.*, tbl_WorkOrderTickets_Converted.dDateOrderImported, tbl_WorkOrderTickets_Converted.ProcessingCompleted Date " & _
"FROM tbl_WorkOrderTickets_Converted " & _
"WHERE (((tbl_WorkOrderTickets_Converted.dDateOrderImport ed) Like [forms]![frm_AM_LoadOtherTickets]![tb_dDate] & ""*"") AND ((tbl_WorkOrderTickets_Converted.ProcessingComplet edDate) Is Null));" ==> produces the Too Few Parameters error.

This is driving me up a wall....

[davegri]

The integer thing won't work unless it is applied to the imbedded Date() and Now() reserved words. That's not the case for your example.
I think Micron is right - you can't use "like" in date/time that way.
But I do believe that the datevalue function will work:

strSQL2 = "SELECT * " & _
"FROM tbl_WorkOrderTickets_Converted " & _
"WHERE datevalue(dDateOrderImported) = '" & DateValue(Me.tb_dDate) & "'" & " And [ProcessingCompletedDate] IS NULL"
Debug.Print strSQL2

[Larryg]

The integer thing won't work unless it is applied to the imbedded Date() and Now() reserved words. That's not the case for your example.
I think Micron is right - you can't use "like" in date/time that way.
But I do believe that the datevalue function will work:

strSQL2 = "SELECT * " & _
"FROM tbl_WorkOrderTickets_Converted " & _
"WHERE datevalue(dDateOrderImported) = '" & DateValue(Me.tb_dDate) & "'" & " And [ProcessingCompletedDate] IS NULL"
Debug.Print strSQL2

davegri... you're a genius ! It worked ! Thank you to Micron also. You guys are a great team !

Thank you so much!

[davegri]

Glad to get that cleaned up. Good luck with the rest of the project!

[Micron]

However when I convert the above into VBA sql and run it, it...
FYI: IIRC, vba constructed sql cannot make use of the Access Expression Service, which can evaluate parameters such as references to form controls or variables that pass criteria. In other words, a stored query can evaluate Forms!frmMain.txtCust as criteria if you run that quer (even if you use vba to run that stored query). However, constructing sql and running it from the vba side requires you to create parameters as objects and set the parameter property to the criteria value.
Check out DAO.Parameter (although I believe it's possible to just Dim paramDept as Parameter, I always try to reference the specific library unless I need late binding).