Data type mismatch

  • Thread starter Thread starter injanib via AccessMonster.com
  • Start date Start date
I

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??
 
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??
Click to expand...
 
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??
Click to expand...

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]
 
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.
Click to expand...
[quoted text clipped - 10 lines]
Any ideas??
Click to expand...

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]
Click to expand...
 
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]
Click to expand...
Click to expand...
 
Back
Top