Where Clause Error In The DoCmd.OpenReport

[cheer]

Error prompt at below code

DoCmd.OpenReport "Report_Machine_Utilisation_Time", acViewPreview, , "OpnStartTime between #" & strStartDate & "# And #" & strEndDate & "#"

a) Error message: data type mismatch in criteria expression
b) Both strStartDate and strEndDate is value from TextBox
c) Example of strStartDate or strEndDate value is "11/13/2009 08:00:00"
d) Try before
DoCmd.OpenReport "Report_Machine_Utilisation_Time", acViewPreview, , "OpnStartTime between #" & cdate(strStartDate) & "# And #" & cdate(strEndDate) & "#"
but the same error message prompt
e) OpnStartTime is date field
f) I am using MS Access 2000
g) Tested sample value #11/10/2009# And #11/11/2009# at the query used in the report. The query works.

Any further advice why the above OpenReport code doesn't work ?

 

[georgedwilkinson]

I noticed nobody is answering so I'll jump in. BTW, I'm terrible with dates, so anyone else feel free to take over.

I THINK, that if you convert your date to a date with cdate(), you don't need the enclosing "#" marks.

It sounds like you're pretty thorough, but did you make sure there were no spaces or other special characters in your date string? Maybe a Debug.Print to verify that what you think is there is actually there?

 

[cheer]

I noticed nobody is answering so I'll jump in. BTW, I'm terrible with dates, so anyone else feel free to take over.

I THINK, that if you convert your date to a date with cdate(), you don't need the enclosing "#" marks.

It sounds like you're pretty thorough, but did you make sure there were no spaces or other special characters in your date string? Maybe a Debug.Print to verify that what you think is there is actually there?

Tested the SQL, it doesn't work. Syntax error, missing operator. Code written as below

DoCmd.OpenReport "Report_Machine_Utilisation_Time", acViewPreview, , "OpnStartTime between " & cdate(strStartDate) & " And " & cdate(strEndDate) & ""

 

[Galaxiom]

Try:

"OpnStartTime between " & Format(strStartDate,"\#mm/dd/yyyy hh:nn:ss\#") & etc

 

[cheer]

Try:

"OpnStartTime between " & Format(strStartDate,"\#mm/dd/yyyy hh:nn:ss\#") & etc

Code rephrase as below

DoCmd.OpenReport "Report_Machine_Utilisation_Time", acViewPreview, , "OpnStartTime between " & Format(strStartDate, "\#mm/dd/yyyy hh:mm:ss\#") & " And " & Format(strEndDate, "\#mm/dd/yyyy hh:mm:ss\#") & ""

Error message: data type mismatch in criteria expression

 

[boblarson]

If strStartDate and strEndDate are text boxes on the form, then you should be able to do:

DoCmd.OpenReport "Report_Machine_Utilisation_Time", acViewPreview, , "[OpnStartTime] Between #" & Me.strStartDate & "# And #" & Me.strEndDate & "#"

 

[cheer]

If strStartDate and strEndDate are text boxes on the form, then you should be able to do:

DoCmd.OpenReport "Report_Machine_Utilisation_Time", acViewPreview, , "[OpnStartTime] Between #" & Me.strStartDate & "# And #" & Me.strEndDate & "#"

Have tried, same error. Thanks
strStartDate and strEndDate is the value from textbox, not the control textbox name.
Maybe is better for me to include the whole code here.

Dim strStartDate as String
Dim strEndDate as String

strStartDate = txtStartDate.Value & " 08:00:00"
strEndDate = txtEndDate.Value & " 07:59:59"

DoCmd.OpenReport "Report_Machine_Utilisation_Time", acViewPreview, , "[OpnStartTime] between " & Format(strStartDate, "\#mm/dd/yyyy hh:mm:ss\#") & " And " & Format(strEndDate, "\#mm/dd/yyyy hh:mm:ss\#") & "" '<==I have tested all above suggestions

When i run debug.print , both strStartDate and strEndDate carry values.

 

[Galaxiom]

I am fairly sure George is not correct about being able to drop the #s but I think either what I posted or Cheer's original aught to work. And Bob's too with the control names.

Maybe there is something wrong with the syntax of strStartDate. It must be a valid date format and it sure looks like it is good in the sample posted.

BTW. The behaviour of Format() is interesting. Where the value is ambiguous as in a text field (eg 08/09/2009) it appears that it will default to the local date style but will assume the alternative if it is not valid in the local (eg 08/25/2009 in dd/mm/yyyy default).

Perhaps there is something odd about the OpnStartTime field?

Incidentally this one is also interesting in the same vein and I am looking forward to a Power Poster providing an explanation.
http://www.access-programmers.co.uk/forums/showthread.php?p=905251#post905251

 

[Galaxiom]

I recon there is an invalid datetime record in the OpnStartTime field.
A Null might even do it. Assuming Nulls are allowed in your table use Nz([OpnStartTime]) = etc in your Where clause. (If that is allowed?)

 

[cheer]

I am fairly sure George is not correct about being able to drop the #s but I think either what I posted or Cheer's original aught to work. And Bob's too with the control names.

Maybe there is something wrong with the syntax of strStartDate. It must be a valid date format and it sure looks like it is good in the sample posted.

BTW. The behaviour of Format() is interesting. Where the value is ambiguous as in a text field (eg 08/09/2009) it appears that it will default to the local date style but will assume the alternative if it is not valid in the local (eg 08/25/2009 in dd/mm/yyyy default).

Perhaps there is something odd about the OpnStartTime field?

Incidentally this one is also interesting in the same vein and I am looking forward to a Power Poster providing an explanation.
http://www.access-programmers.co.uk/forums/showthread.php?p=905251#post905251

1) When I try run the query, it works. Query.jpg show the example

2) When I run through the code, error prompt as above. Shown in VBA.jpg

 

[Galaxiom]

Picture paints a thousand words.
The AM in the DateTime is ok for the query but not allowed for SQL. Must be 24 hour time for SQL.
Your sample posted earlier was not accurate.

 

[cheer]

Picture paints a thousand words.
The AM in the DateTime is ok for the query but not allowed for SQL. Must be 24 hour time for SQL.
Your sample posted earlier was not accurate.

What is the conclusion?

 

[Galaxiom]

Your Immediate window shows your variable formatted as:
#mm/dd/yyyy hh:nn:ss AM#

While this works for criteria in the Access queries the Where clause in the OpenReport is SQL style and cannot include AM/PM but must use 24 hour time.

 

[cheer]

Your Immediate window shows your variable formatted as:
#mm/dd/yyyy hh:nn:ss AM#

While this works for criteria in the Access queries the Where clause in the OpenReport is SQL style and cannot include AM/PM but must use 24 hour time.

Now in 24hour time, problem remain.

 

[Galaxiom]

Have you checked that there are no Nulls or otherwise invalid entries in the [OpnStartTime] field?
I'm really running out of ideas.

 

[cheer]

Have you checked that there are no Nulls or otherwise invalid entries in the [OpnStartTime] field?
I'm really running out of ideas.

Appreciate your help above.

If I would like to filter out the Nulls and any invalid entries, how can I amend the code below ?

DoCmd.OpenReport "Report_Machine_Utilisation_Time", acViewPreview, "OpnStartTime Is Not Null" , "OpnStartTime between " & Format(strStartDate, "\#mm/dd/yyyy hh:mm:ss\#") & " And " & Format(strEndDate, "\#mm/dd/yyyy hh:mm:ss\#") & ""

1) I have shown above the Not Null condition, is this the right way to write ?

2) How to instruct the OpnStartTime field to accept the datetime format value ONLY. I find the table contains some invalid entries, however, the data do not fall in the query range above. Just wish to try out any effort to eliminate this to see whether this can be one of the root causes. Any help ?

 

[Galaxiom]

The field should only accept a Date/Time value if its DataType is set as Date/Time in the Design view of the table. If it is still on the default Text format this would certainly give you the problem. But then your query should not have worked either.

 

[cheer]

The field should only accept a Date/Time value if its DataType is set as Date/Time in the Design view of the table. If it is still on the default Text format this would certainly give you the problem. But then your query should not have worked either.

The field is the date/time type. However, I got detect few invalid entries which do not fall in the above query range. For example : 24/12/463 (incorrect value in year).

My questions now are as below

a) Any method I can filter out those invalid entries through the docmd.openreport or other VBA command ?

b) If there is a invalid entries, why the query work fines with the date range I attached earlier but not to the report via the code I have shown in earlier post. The report is constructed on the query. I have shown you earlier in attachment (refer to Query.jpg)

 

[Galaxiom]

24/12/463 is not actually an invalid date as such. But as it falls outside the range it would be filtered out of your query. I think there must be some invalid dates with extra spaces, wrong slash or something like that.

 

[cheer]

24/12/463 is not actually an invalid date as such. But as it falls outside the range it would be filtered out of your query. I think there must be some invalid dates with extra spaces, wrong slash or something like that.

How to filter this through query ?