Data Type Mismatch in query

[WineSnob]

This code works
varWhere = varWhere & "[Month] Like """ & Me.txtstartmonth & """ "

When I try to add a between
varWhere = varWhere & "[Month] Between """ & Me.txtstartmonth & """ AND """ & Me.txtendmonth & """ "

I get a "data type mismatch in criteria expression" error.
[month] is a number
txtstartmonth is a number
txtendmonth is a number

If I go directly to the query and use Between 1 and 5 the query runs fine.
Here is the Debug.Print varWhere
[Year] LIKE "2011" AND [Month] Between "1" AND "5"

What am I missing?

 

[spikepl]

Using reserved words as field names (as Year or Month) will bite you sooner or later - google access reserved words

As to your varWhere - if Month is a number then it should be compared to a number, not a string as in your varWhere

 

[boblarson]

Let's make the quotes easier to read:

varWhere = varWhere & "[Month] Between """ & Me.txtstartmonth & """ AND """ & Me.txtendmonth & """ "

Becomes

varWhere = varWhere & "[Month] Between " & Chr(34) & Me.txtstartmonth & Chr(34) & " AND " & Chr(34) & Me.txtendmonth & Chr(34)

Which might help if Month was text. But you say Month is a number (is it a number datatype?), so it wouldn't want quotes:

varWhere = varWhere & "[Month] Between " & Me.txtstartmonth & " AND " & Me.txtendmonth

 

[WineSnob]

Thanks AGAIN Bob,
I always have a problem with "" and ' and "'". Maybe someday I'll get it. I liked the Chr(34). It did make it easier to understand.

 

[boblarson]

Thanks AGAIN Bob,
I always have a problem with "" and ' and "'". Maybe someday I'll get it. I liked the Chr(34). It did make it easier to understand.

Yeah, I can't stand trying to make out them any other way. So, I have moved to using the Chr(34) method almost always.