Data Type Mismatch...Syntax?

  • Thread starter Thread starter Monty
  • Start date Start date
M

Monty

I have an SQL statement that returns a 'data type mismatch' in the
criteria of a DoCmd.RunSQL statement:

DoCmd.RunSQL ("DELETE FROM Hardware WHERE Hardware.[record key] = ' "
Forms![Hardware Form]![hardware list].Column(0) " ' ")

(I've included spaces in the single and double quotes for clarity in
this post)

[record key] has an AutoNumber property setting, and [hardware list]
list is a combo box that has an SQL select statement as it's control
source. Column(0) is the value of [record key] from the Hardware
table.

I presume the error messages refers to the data type returned in the
WHERE clause. If I'm correct in that assumption, what data type should
it be? Looking at the Hardware table [record key] properties I see
it's a Long Integer, but my attempts to do a temporary data type
conversion with CLng result in the same error.

What am I missing? Hope there's enough information here.

Dale
 
Monty said:
I have an SQL statement that returns a 'data type mismatch' in the
criteria of a DoCmd.RunSQL statement:

DoCmd.RunSQL ("DELETE FROM Hardware WHERE Hardware.[record key] = ' "
Forms![Hardware Form]![hardware list].Column(0) " ' ")

(I've included spaces in the single and double quotes for clarity in
this post)

[record key] has an AutoNumber property setting, and [hardware list]
list is a combo box that has an SQL select statement as it's control
source. Column(0) is the value of [record key] from the Hardware
table.

I presume the error messages refers to the data type returned in the
WHERE clause. If I'm correct in that assumption, what data type
should it be? Looking at the Hardware table [record key] properties
I see it's a Long Integer, but my attempts to do a temporary data
type conversion with CLng result in the same error.

What am I missing? Hope there's enough information here.

Dale
Click to expand...

Single quotes are necessary when the field in question is text. When
it is number (you say autonumber), no delimiters are needed.

....WHERE Hardware.[record key] = " & _
Forms![Hardware Form]![hardware list].Column(0)
 
Monty said:
I have an SQL statement that returns a 'data type mismatch' in the
criteria of a DoCmd.RunSQL statement:

DoCmd.RunSQL ("DELETE FROM Hardware WHERE Hardware.[record key] = ' "
Forms![Hardware Form]![hardware list].Column(0) " ' ")

(I've included spaces in the single and double quotes for clarity in
this post)
Click to expand...

Did you also drop out the ampersands (&) that should be used to
concatenate the value from the form control into the SQL statement?

If your field [record key] is an autonumber or long integer, you should
not put quotes around the value. Try this:

DoCmd.RunSQL _
"DELETE FROM Hardware " & _
"WHERE Hardware.[record key] = " & _
Forms![Hardware Form]![hardware list].Column(0)
 
Dirk said:
Monty said:
I have an SQL statement that returns a 'data type mismatch' in the
criteria of a DoCmd.RunSQL statement:

DoCmd.RunSQL ("DELETE FROM Hardware WHERE Hardware.[record key] = ' "
Forms![Hardware Form]![hardware list].Column(0) " ' ")

(I've included spaces in the single and double quotes for clarity in
this post)
Click to expand...

Did you also drop out the ampersands (&) that should be used to
concatenate the value from the form control into the SQL statement?

If your field [record key] is an autonumber or long integer, you should
not put quotes around the value. Try this:

DoCmd.RunSQL _
"DELETE FROM Hardware " & _
"WHERE Hardware.[record key] = " & _
Forms![Hardware Form]![hardware list].Column(0)


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...

Ah, text versus numbers...how often have I fallen for that? And, to
answer your question, the ampersands are there in the original
statement, I just forgot to include them here.

That works, thanks :)
 
Back
Top