Data type mismatch in criteria expression

  • Thread starter Thread starter benjaminkmartin
  • Start date Start date
B

benjaminkmartin

I'm getting an error that I don't know how to deal with.

I think the error has something to do with the coding in my
AfterUpdate event of combo28 which is as follows...

Private Sub Combo28_AfterUpdate()
Combo36.RowSource = "SELECT [Location ID].[Location Name], [Location
ID].[Location Code] FROM [Location ID] where [Location ID].[Vehicle
ID] = " & Combo28.Column(1) & ";"
Combo36.Requery
End Sub

When I click on combo36 I get the error message, "Data type mismatch
in criteria expression." I thought maybe the problem was that the
Vehicle ID of one table was numeric while the other was text. But
they are both set as text as I would like them to be. There's even an
established relationship between the Vehicle ID's of each table (the
two tables are "Vehicle ID" and "Location ID"). So I have no idea why
the Vehicle ID from combo28.column(1) (this combobox pulls it's
choices from table "Vehicle ID) does not match up with the Vehicle
ID's from the table "Location ID"

Any thoughts? (And any bit of information is helpful!)

Thanks so much!
 
You know that the combobox is 0 based, correct.  So column 1 is actually the
second column.  column 0 is probably the ID.
Just a guess.

Damon




I'm getting an error that I don't know how to deal with.
Click to expand...
I think the error has something to do with the coding in my
AfterUpdate event of combo28 which is as follows...
Click to expand...
Private Sub Combo28_AfterUpdate()
 Combo36.RowSource = "SELECT [Location ID].[Location Name], [Location
ID].[Location Code] FROM [Location ID] where [Location ID].[Vehicle
ID] = " & Combo28.Column(1) & ";"
 Combo36.Requery
End Sub
Click to expand...
When I click on combo36 I get the error message, "Data type mismatch
in criteria expression."  I thought maybe the problem was that the
Vehicle ID of one table was numeric while the other was text.  But
they are both set as text as I would like them to be.  There's even an
established relationship between the Vehicle ID's of each table (the
two tables are "Vehicle ID" and "Location ID").  So I have no idea why
the Vehicle ID from combo28.column(1) (this combobox pulls it's
choices from table "Vehicle ID) does not match up with the Vehicle
ID's from the table "Location ID"
Click to expand...
Any thoughts?  (And any bit of information is helpful!)
Click to expand...
Thanks so much!- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

No, its the right column, I checked that like 10 times...
 
Add the message box line.

Private Sub Combo28_AfterUpdate()
MsgBox Combo28.Column(1)
Combo36.RowSource = "SELECT [Location ID].[Location Name], [Location
ID].[Location Code] FROM [Location ID] where [Location ID].[Vehicle
ID] = " & Combo28.Column(1) & ";"
Combo36.Requery
End Sub

What's showing in the message box?
 
Add the message box line.

Private Sub Combo28_AfterUpdate()
  MsgBox Combo28.Column(1)
  Combo36.RowSource = "SELECT [Location ID].[Location Name], [Location
ID].[Location Code] FROM [Location ID] where [Location ID].[Vehicle
ID] = " & Combo28.Column(1) & ";"
  Combo36.Requery
End Sub

What's showing in the message box?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)




I'm getting an error that I don't know how to deal with.
Click to expand...
I think the error has something to do with the coding in my
AfterUpdate event of combo28 which is as follows...
Click to expand...
Private Sub Combo28_AfterUpdate()
 Combo36.RowSource = "SELECT [Location ID].[Location Name], [Location
ID].[Location Code] FROM [Location ID] where [Location ID].[Vehicle
ID] = " & Combo28.Column(1) & ";"
 Combo36.Requery
End Sub
Click to expand...
When I click on combo36 I get the error message, "Data type mismatch
in criteria expression."  I thought maybe the problem was that the
Vehicle ID of one table was numeric while the other was text.  But
they are both set as text as I would like them to be.  There's even an
established relationship between the Vehicle ID's of each table (the
two tables are "Vehicle ID" and "Location ID").  So I have no idea why
the Vehicle ID from combo28.column(1) (this combobox pulls it's
choices from table "Vehicle ID) does not match up with the Vehicle
ID's from the table "Location ID"
Click to expand...
Any thoughts?  (And any bit of information is helpful!)
Click to expand...
Thanks so much!- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

The message box line isn't what I needed, but thanks anyway.
I changed both Location ID's to the number data type. And it works
that way. But not when are both are simply texts. Is there something
in the code that requires it be set as data type number?
 
I don't understand what you mean by "simply texts". The ID must either be
numeric for all fields or not.

If it's numeric for all fields, you can define it in the table as a numeric
data type. It it's not numeric for all fields, you have no choice but to
define it in the table as the text data type.

Once you've defined the data type, that dictates what your SQL needs to be.

If it's numeric, your SQL is fine.

If it's text, you need quotes around the value:

Combo36.RowSource = "SELECT [Location Name], [Location Code] " & _
"FROM [Location ID] " & _
"WHERE [Vehicle ID] = '" & Combo28.Column(1) & "';"

Exagerated for clarity, that last line is

"WHERE [Vehicle ID] = ' " & Combo28.Column(1) & " ' ; "

(although the semi-colon is optional)

If the Vehicle ID field has been defined as text, it doesn't matter whether
you're looking for 123 or abc: you must have quotes around it.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


- Show quoted text -

The message box line isn't what I needed, but thanks anyway.
I changed both Location ID's to the number data type. And it works
that way. But not when are both are simply texts. Is there something
in the code that requires it be set as data type number?
 
Back
Top