Data type mismatch in criteira expression - HUH?

[Gina Whipp]

Hello All,

I have used this code over and over again but never on date fields. Now I
am trying to use on date fields (Start Date, End Date) and I keep getting
'Data type mismatch in criteria expression'. Now I have used m/yyyy fields
with no problems, why is the entire date giving me a problem?

Dim strSql As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 2
If Me("Filter" & intCounter) <> "" Then
strSql = strSql & "[" & Me("Filter" & intCounter).Tag & "] " & " =
" & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSql <> "" Then
'Strip Last " And "
strSql = Left(strSql, (Len(strSql) - 4))
'Set the Filter property
Reports![rptCheckingLog].Filter = strSql
Reports![rptCheckingLog].FilterOn = True
Else
Reports![rptCheckingLog].FilterOn = False
End If

Thanks,
Gina

 

[Marshall Barton]

I have used this code over and over again but never on date fields. Now I
am trying to use on date fields (Start Date, End Date) and I keep getting
'Data type mismatch in criteria expression'. Now I have used m/yyyy fields
with no problems, why is the entire date giving me a problem?

Dim strSql As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 2
If Me("Filter" & intCounter) <> "" Then
strSql = strSql & "[" & Me("Filter" & intCounter).Tag & "] " & " =
" & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

You must use the appropriate delimiter to identify the type
of the value you are concatenating into criteria expression
and the type of the value must match the type of the field
it is compared to.

numbers - no delimiter
text - " or '
dates = #

whitout the delimiter, Access has no idea what to do with
the characters that appear in the criteria. For example, if
you use [field] = 2/13/06, Access is going to think that
it's an expression and do two divisions, which is
meaningless for a date.

What all that means is that your code must be aware of the
type of the field so it can supply the appropriate
delimiter. In the case of a date field:

strSql = strSql & "[" & datefield & "] Between #" & _
Me("start date") & "# And #" Me("enddate") & "#"


Actually, that is not sufficient if you or any of your users
Windows settings has anything other than USA as the date
format. To be totally safe, you can use:

strSql = strSql & "[" & datefield & "] Between " _
& Format(Me"start date"), "\#m\/d\/yyyy\#) & _
" And " & Format(Me"end date"), "\#m\/d\/yyyy\#)

 

[Gina Whipp]

I think I was not clear though I now appreciate that that I need to #'s;
just not sure where. While I am querying Start Date & End Date those are
not the names of the fields on the form. It grabs the values of the
Filter1, Filter2, etc... and applies accordingly. Generic names for fields
on forms so I reuse the form to filter and array of reports.

I tried:

strSql & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & "#"
& Me("Filter" & intCounter) & "#" & Chr(34) & " And "

and I get the same error message

I have used this code over and over again but never on date fields. Now I
am trying to use on date fields (Start Date, End Date) and I keep getting
'Data type mismatch in criteria expression'. Now I have used m/yyyy
fields
with no problems, why is the entire date giving me a problem?

Dim strSql As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 2
If Me("Filter" & intCounter) <> "" Then
strSql = strSql & "[" & Me("Filter" & intCounter).Tag & "] " & "
=
" & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

You must use the appropriate delimiter to identify the type
of the value you are concatenating into criteria expression
and the type of the value must match the type of the field
it is compared to.

numbers - no delimiter
text - " or '
dates = #

whitout the delimiter, Access has no idea what to do with
the characters that appear in the criteria. For example, if
you use [field] = 2/13/06, Access is going to think that
it's an expression and do two divisions, which is
meaningless for a date.

What all that means is that your code must be aware of the
type of the field so it can supply the appropriate
delimiter. In the case of a date field:

strSql = strSql & "[" & datefield & "] Between #" & _
Me("start date") & "# And #" Me("enddate") & "#"


Actually, that is not sufficient if you or any of your users
Windows settings has anything other than USA as the date
format. To be totally safe, you can use:

strSql = strSql & "[" & datefield & "] Between " _
& Format(Me"start date"), "\#m\/d\/yyyy\#) & _
" And " & Format(Me"end date"), "\#m\/d\/yyyy\#)

 

[Klatuu]

The Chr(34) translates to "
To get # it is Chr(35)

Any time you need to find the ASCII value of a character, in the immediate
window:

?Asc("$") where $ is the character you want the ASCII value for

I think I was not clear though I now appreciate that that I need to #'s;
just not sure where. While I am querying Start Date & End Date those are
not the names of the fields on the form. It grabs the values of the
Filter1, Filter2, etc... and applies accordingly. Generic names for fields
on forms so I reuse the form to filter and array of reports.

I tried:

strSql & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & "#"
& Me("Filter" & intCounter) & "#" & Chr(34) & " And "

and I get the same error message

I have used this code over and over again but never on date fields. Now I
am trying to use on date fields (Start Date, End Date) and I keep getting
'Data type mismatch in criteria expression'. Now I have used m/yyyy
fields
with no problems, why is the entire date giving me a problem?

Dim strSql As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 2
If Me("Filter" & intCounter) <> "" Then
strSql = strSql & "[" & Me("Filter" & intCounter).Tag & "] " & "
=
" & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

You must use the appropriate delimiter to identify the type
of the value you are concatenating into criteria expression
and the type of the value must match the type of the field
it is compared to.

numbers - no delimiter
text - " or '
dates = #

whitout the delimiter, Access has no idea what to do with
the characters that appear in the criteria. For example, if
you use [field] = 2/13/06, Access is going to think that
it's an expression and do two divisions, which is
meaningless for a date.

What all that means is that your code must be aware of the
type of the field so it can supply the appropriate
delimiter. In the case of a date field:

strSql = strSql & "[" & datefield & "] Between #" & _
Me("start date") & "# And #" Me("enddate") & "#"


Actually, that is not sufficient if you or any of your users
Windows settings has anything other than USA as the date
format. To be totally safe, you can use:

strSql = strSql & "[" & datefield & "] Between " _
& Format(Me"start date"), "\#m\/d\/yyyy\#) & _
" And " & Format(Me"end date"), "\#m\/d\/yyyy\#)

 

[Gina Whipp]

Thanks you, thank you and a big THANK YOU. That was it! Here I was trying
to add the "#" without even thinking about the Chr()

The Chr(34) translates to "
To get # it is Chr(35)

Any time you need to find the ASCII value of a character, in the immediate
window:

?Asc("$") where $ is the character you want the ASCII value for

I think I was not clear though I now appreciate that that I need to #'s;
just not sure where. While I am querying Start Date & End Date those are
not the names of the fields on the form. It grabs the values of the
Filter1, Filter2, etc... and applies accordingly. Generic names for
fields
on forms so I reuse the form to filter and array of reports.

I tried:

strSql & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) &
"#"
& Me("Filter" & intCounter) & "#" & Chr(34) & " And "

and I get the same error message

Gina Whipp wrote:
I have used this code over and over again but never on date fields.
Now I
am trying to use on date fields (Start Date, End Date) and I keep
getting
'Data type mismatch in criteria expression'. Now I have used m/yyyy
fields
with no problems, why is the entire date giving me a problem?

Dim strSql As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 2
If Me("Filter" & intCounter) <> "" Then
strSql = strSql & "[" & Me("Filter" & intCounter).Tag & "] " &
"
=
" & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next


You must use the appropriate delimiter to identify the type
of the value you are concatenating into criteria expression
and the type of the value must match the type of the field
it is compared to.

numbers - no delimiter
text - " or '
dates = #

whitout the delimiter, Access has no idea what to do with
the characters that appear in the criteria. For example, if
you use [field] = 2/13/06, Access is going to think that
it's an expression and do two divisions, which is
meaningless for a date.

What all that means is that your code must be aware of the
type of the field so it can supply the appropriate
delimiter. In the case of a date field:

strSql = strSql & "[" & datefield & "] Between #" & _
Me("start date") & "# And #" Me("enddate") & "#"


Actually, that is not sufficient if you or any of your users
Windows settings has anything other than USA as the date
format. To be totally safe, you can use:

strSql = strSql & "[" & datefield & "] Between " _
& Format(Me"start date"), "\#m\/d\/yyyy\#) & _
" And " & Format(Me"end date"), "\#m\/d\/yyyy\#)