Error 13 type mismatch

[Walter]

I have the following code and I keep getting a type mismatch error. I've
tried every possiblity that I can think of to no avail. I haven't been able
to find anything that seems to relate to my situation.

Dim strOldPosition As String
Dim strNewPosition As String
Dim intOldPosition As Integer
Dim intNewPosition As Integer
Dim intOldTire As Integer
Dim intNewTire As Integer

strOldPosition = Me.ActiveControl.Caption
strNewPosition = Nz(Forms!frmUpdateTireLocation!cboNewPosition, 0)
intOldPosition = _
"Select tblTirePosition.TirePositionID " & _
"From tblTirePosition " & _
"Where (((tblTirePosition.TirePosition) = """ & strOldPosition &
"""" & "));"

The code breaks at the query defining intOldPosition.
tblTirePosition.TirePosition is a text field. What am I missing?

 

[Tom van Stiphout]

On Wed, 24 Dec 2008 06:13:00 -0800, Walter

In the last line you essentially write:
i = "abc"
If the variable i is an integer, its value can only be a whole 16-bit
number, not a string.
It appears that what you want is to do a database lookup for some
specific integer value, and assign that to your integer variable:
i = <some_database_lookup>
Fortunately there is a solution for that: the DLookup function. Look
it up in the help file.

-Tom.
Microsoft Access MVP

 

[Walter]

How do you instruct Access to run the SQL statement to return the integer
value?
I've tried DoCmd.RunSQL(mySQLstatement) but get an "Expected Function or
variable" error with the".RunSQL" highlighted.

 

[Douglas J. Steele]

You can't use RunSQL with a Select query: it's intended for Action queries
only (Update, Insert Into, Delete)

Rather than use SQL, try using the DLookup function:

intOldPosition = _
DLookup("TirePositionID", _
"tblTirePosition", _
"TirePosition = """ & strOldPosition &"""")

 

[Walter]

I've used DLookup numerous times but I have a situation now where it seems
not to work. I have a series of queries to select the correct record. The
criteria, which comes from a control on the form, needs to be applied to the
first query since one of the subsequent queries selects the max for a field.
When using the DLookup function I'm getting the error "..doesn't contain the
automation object". If I include the criteria in the final query, I
eliminate the error but the result is not correct. How do I remedy this?

 

[Douglas J. Steele]

Sorry, you're not giving enough details.

 

[Walter]

I've figured out how to include the criteria field in the final query and
still be able to return the correct record. Thanks for the reply which got
me to take a fresh look and enabled me to find the solution!