Type Mismatch

[Guest]

I'm using the following open recordset to check my SQL database for
duplicates on two keys: Composite_name and month. I'm getting the error
"Type Mismatch" The "month" field in the SQL database is set as a date
field and set as a date field in Access as well. I've tried to declare the
"month" field as different types of variables.

Set rst = db.OpenRecordset("SELECT Month and Composite_Name FROM
dbo_TblMonthlyData " & _
"WHERE Composite_Name = " & QUOTE & strCompositeName & QUOTE And QUOTE &
strmonth & QUOTE, dbOpenDynaset)

 

[John Vinson]

I'm using the following open recordset to check my SQL database for
duplicates on two keys: Composite_name and month. I'm getting the error
"Type Mismatch" The "month" field in the SQL database is set as a date
field and set as a date field in Access as well. I've tried to declare the
"month" field as different types of variables.

Set rst = db.OpenRecordset("SELECT Month and Composite_Name FROM
dbo_TblMonthlyData " & _
"WHERE Composite_Name = " & QUOTE & strCompositeName & QUOTE And QUOTE &
strmonth & QUOTE, dbOpenDynaset)

Assuming that QUOTE is the " character, it's the wrong delimiter for a
date/time Access field: the proper delimiter for dates is #. Also, the
search criterion needs to be a date/time value that is unambiguously a
single date, not a month or a month name; what are typical values of
strmonth?

More critically, you're misusing the word "and". It's not an English
language conjunction; it's a logical operator. "SELECT Month and
Composite_Name" is invalid; if you want to select those two fields,
use a comma in place of the word and. Similarly, if you want to search
the value of the field [month] you need to mention the name of the
field in your query.

Please explain what's stored in the [month] field - which, by the bye,
should be renamed since Month is a reserved word - and what's in
strMonth.


John W. Vinson[MVP]