Data type mismatch

[injanib via AccessMonster.com]

Hello,

This has been working fine untill today. It is a form with a button
"TodaysReport" and an unbound text box called "txtDate".
the default value of the unbound text field is set to =Date().
The button is supposed to filter a report called "Report" for the date value
in the text box. Which will be all the records entered today. The report
contains a field called "ReceivedOn". This field is updated with line
[ReceivedOn]=Date in the after update event of another bound control.

The code behind the button is simply as such.

Private Sub TodaysReport_Click()
Dim strWhere As String

strWhere = "DateValue(ReceivedOn)=#" & Me.txtDate & "#"
DoCmd.OpenReport "Report", acNormal, , strWhere
End Sub

As I mentionded, this has been working just fine untill today. I get the Run-
Time error 3646 " Data type mismatch in criteria expression" and it occurs at
the line DoCmd. OpenReport

Any ideas??

 

[injanib via AccessMonster.com]

by the way, the "ReceivedOn" is a Date/Time data type. the input mask is set
to short date. The txtDate field is also in the short date format.

Hello,

This has been working fine untill today. It is a form with a button
"TodaysReport" and an unbound text box called "txtDate".
the default value of the unbound text field is set to =Date().
The button is supposed to filter a report called "Report" for the date value
in the text box. Which will be all the records entered today. The report
contains a field called "ReceivedOn". This field is updated with line
[ReceivedOn]=Date in the after update event of another bound control.

The code behind the button is simply as such.

Private Sub TodaysReport_Click()
Dim strWhere As String

strWhere = "DateValue(ReceivedOn)=#" & Me.txtDate & "#"
DoCmd.OpenReport "Report", acNormal, , strWhere
End Sub

As I mentionded, this has been working just fine untill today. I get the Run-
Time error 3646 " Data type mismatch in criteria expression" and it occurs at
the line DoCmd. OpenReport

Any ideas??

 

[John W. Vinson]

The code behind the button is simply as such.

Private Sub TodaysReport_Click()
Dim strWhere As String

strWhere = "DateValue(ReceivedOn)=#" & Me.txtDate & "#"
DoCmd.OpenReport "Report", acNormal, , strWhere
End Sub

As I mentionded, this has been working just fine untill today. I get the Run-
Time error 3646 " Data type mismatch in criteria expression" and it occurs at
the line DoCmd. OpenReport

Any ideas??

If txtDate is blank - or something which cannot be interpreted as a valid date
- you might get this error. Of course it could also be some sort of corruption
- does compacting the database make any difference?

John W. Vinson [MVP]

 

[injanib via AccessMonster.com]

Thanks for replying.
First of all the txtDate is a valid date field. Like I said it is set to
=Date() by default and displays today's date.

I am not sure what you mean by compacting the database.

The code behind the button is simply as such.

[quoted text clipped - 10 lines]

Any ideas??

If txtDate is blank - or something which cannot be interpreted as a valid date
- you might get this error. Of course it could also be some sort of corruption
- does compacting the database make any difference?

John W. Vinson [MVP]

 

[injanib via AccessMonster.com]

I fixed it John.

I changed the code for the date format and it took care of it. It is weird
because it was working fine with the old format and just stopped working
suddenly.

this is what I used

const conDateFormat = "\#mm\/dd\/yyyy\#"
strWhere=strWhere & "([ReceivedOn]=" & Format(Me.txtDate, conDateFormat) & ")
"

Thanks for your reply anyways.

Thanks for replying.
First of all the txtDate is a valid date field. Like I said it is set to
=Date() by default and displays today's date.

I am not sure what you mean by compacting the database.
[quoted text clipped - 7 lines]

John W. Vinson [MVP]