Please help!!! Data Type Mismatch in Criteria Expression.

G

Guest

I have a form called Company (Built off the Company table which contains a
list of all the vendors that we deal with). This includes a subform that
lists our Contacts at the Company. The sub form is based off a Contact table.

The subform lists Contact Name, Email and Phone number. The email and phone
number fields are disabled. The intent is that the user will select the
Contact Name which will automatically populate the email and phone number for
the selected Contact.

I have tried to accomplish this using the following code against the
AfterUpdate event of the Contact Name field. However, on running it I get a
"The Data Type Mismatch in Criteria Expression." error. Aargh!!! Any
suggestions?

Private Sub Contact_Name_AfterUpdate()

Dim rst As New ADODB.Recordset
Dim SQLstmt As String

SQLstmt = "SELECT * FROM [Contact] WHERE [ID] = """ & Me.Contact_Name & """"
rst.Open SQLstmt, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
With Me
.Email= rst!Email
.Phone_Number = rst!Phone_Number


End With
rst.Close
Set rst = Nothing

End Sub
 
G

Guest

A field name like [ID] generally suggests a Numeric field however you
comparing it to the value of "Contact_Name" from your form. This seems
out-of-whack to me and you didn't provide any explanation. You might just
need to remove a bunch of quotes:
SQLstmt = "SELECT * FROM [Contact] WHERE [ID] =" & Me.Contact_Name
 
G

Guest

A couple of assumptions:

That [ID] in [contact] is a string value

First it appears you are using two sets of double quotes instead of encasing
a single quote inside of double quotes on both sides of the string. I would
suggest the following:
....WHERE [ID] = "'" & Me.Contact_Name.Value & "'"

Note that there is a single quote (') inside the double quotes. Expanded it
would look like " and then ' and then " on both sides of the string value.

Also, while not required it is good practice to decare the ".Value" of the
textbox.

Back to my assumption: If the value [ID] is not a string (say an integer)
you would want to remove the quotes all together:
....WHERE [ID] = " & Me.Contact_Name.Value

Note the single quote is expressing that the value is a string and if the
[ID] is not a string this will cause a data msimatch

Let me know if this helps.


Randy
 
G

Guest

Duane,

To add some clarity. The Contact_Name field is a foreign key link to the
Contact table. Hence I am comparing the value of the "Contact_Name" field to
the "ID" field of the Contact table.

Mike

Duane Hookom said:
A field name like [ID] generally suggests a Numeric field however you
comparing it to the value of "Contact_Name" from your form. This seems
out-of-whack to me and you didn't provide any explanation. You might just
need to remove a bunch of quotes:
SQLstmt = "SELECT * FROM [Contact] WHERE [ID] =" & Me.Contact_Name

--
Duane Hookom
Microsoft Access MVP


MHESSA said:
I have a form called Company (Built off the Company table which contains a
list of all the vendors that we deal with). This includes a subform that
lists our Contacts at the Company. The sub form is based off a Contact table.

The subform lists Contact Name, Email and Phone number. The email and phone
number fields are disabled. The intent is that the user will select the
Contact Name which will automatically populate the email and phone number for
the selected Contact.

I have tried to accomplish this using the following code against the
AfterUpdate event of the Contact Name field. However, on running it I get a
"The Data Type Mismatch in Criteria Expression." error. Aargh!!! Any
suggestions?

Private Sub Contact_Name_AfterUpdate()

Dim rst As New ADODB.Recordset
Dim SQLstmt As String

SQLstmt = "SELECT * FROM [Contact] WHERE [ID] = """ & Me.Contact_Name & """"
rst.Open SQLstmt, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
With Me
.Email= rst!Email
.Phone_Number = rst!Phone_Number


End With
rst.Close
Set rst = Nothing

End Sub
Click to expand...
Click to expand...
 
G

Guest

Randy,

The value [ID] is an integer and your recommendation worked!!! I am really
grateful to you. Great way to end the day!!!

Best Regards,
Mike

Randy Wayne said:
A couple of assumptions:

That [ID] in [contact] is a string value

First it appears you are using two sets of double quotes instead of encasing
a single quote inside of double quotes on both sides of the string. I would
suggest the following:
...WHERE [ID] = "'" & Me.Contact_Name.Value & "'"

Note that there is a single quote (') inside the double quotes. Expanded it
would look like " and then ' and then " on both sides of the string value.

Also, while not required it is good practice to decare the ".Value" of the
textbox.

Back to my assumption: If the value [ID] is not a string (say an integer)
you would want to remove the quotes all together:
...WHERE [ID] = " & Me.Contact_Name.Value

Note the single quote is expressing that the value is a string and if the
[ID] is not a string this will cause a data msimatch

Let me know if this helps.


Randy


MHESSA said:
I have a form called Company (Built off the Company table which contains a
list of all the vendors that we deal with). This includes a subform that
lists our Contacts at the Company. The sub form is based off a Contact table.

The subform lists Contact Name, Email and Phone number. The email and phone
number fields are disabled. The intent is that the user will select the
Contact Name which will automatically populate the email and phone number for
the selected Contact.

I have tried to accomplish this using the following code against the
AfterUpdate event of the Contact Name field. However, on running it I get a
"The Data Type Mismatch in Criteria Expression." error. Aargh!!! Any
suggestions?

Private Sub Contact_Name_AfterUpdate()

Dim rst As New ADODB.Recordset
Dim SQLstmt As String

SQLstmt = "SELECT * FROM [Contact] WHERE [ID] = """ & Me.Contact_Name & """"
rst.Open SQLstmt, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
With Me
.Email= rst!Email
.Phone_Number = rst!Phone_Number


End With
rst.Close
Set rst = Nothing

End Sub
Click to expand...
Click to expand...
 
G

Guest

Your welcome. Lots of good folks, way smarter than me, have helped me over
the years.

Glad to help!!

--
Thanks,

Randy


MHESSA said:
Randy,

The value [ID] is an integer and your recommendation worked!!! I am really
grateful to you. Great way to end the day!!!

Best Regards,
Mike

Randy Wayne said:
A couple of assumptions:

That [ID] in [contact] is a string value

First it appears you are using two sets of double quotes instead of encasing
a single quote inside of double quotes on both sides of the string. I would
suggest the following:
...WHERE [ID] = "'" & Me.Contact_Name.Value & "'"

Note that there is a single quote (') inside the double quotes. Expanded it
would look like " and then ' and then " on both sides of the string value.

Also, while not required it is good practice to decare the ".Value" of the
textbox.

Back to my assumption: If the value [ID] is not a string (say an integer)
you would want to remove the quotes all together:
...WHERE [ID] = " & Me.Contact_Name.Value

Note the single quote is expressing that the value is a string and if the
[ID] is not a string this will cause a data msimatch

Let me know if this helps.


Randy


MHESSA said:
I have a form called Company (Built off the Company table which contains a
list of all the vendors that we deal with). This includes a subform that
lists our Contacts at the Company. The sub form is based off a Contact table.

The subform lists Contact Name, Email and Phone number. The email and phone
number fields are disabled. The intent is that the user will select the
Contact Name which will automatically populate the email and phone number for
the selected Contact.

I have tried to accomplish this using the following code against the
AfterUpdate event of the Contact Name field. However, on running it I get a
"The Data Type Mismatch in Criteria Expression." error. Aargh!!! Any
suggestions?

Private Sub Contact_Name_AfterUpdate()

Dim rst As New ADODB.Recordset
Dim SQLstmt As String

SQLstmt = "SELECT * FROM [Contact] WHERE [ID] = """ & Me.Contact_Name & """"
rst.Open SQLstmt, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
With Me
.Email= rst!Email
.Phone_Number = rst!Phone_Number


End With
rst.Close
Set rst = Nothing

End Sub
Click to expand...
Click to expand...
Click to expand...
 
J

John Vinson

SQLstmt = "SELECT * FROM [Contact] WHERE [ID] = """ & Me.Contact_Name & """"
rst.Open SQLstmt, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
With Me
.Email= rst!Email
.Phone_Number = rst!Phone_Number


End With
rst.Close
Set rst = Nothing
Click to expand...

I suspect you are yet another victim of the misdesigned "Lookup
Wizard". Is Contact_Name a Lookup field? If so, the actual value in
the table is a Long Integer number but what you *see* is a name.

In the Contact table, what is the datatype of ID? If it's a Number or
Autonumber, then instead of using Contact_Name as a Text field, you
need to be using the numeric contact ID.

John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with Type Mismatch Error 2
Data Type mismatch in criteria expression - WHY?WHY?WHy? 4
Data Type Mismatch in Criteria Expression 4
'Data type mismatch in criteria expression' Error 7
Data type mismatch in criteria expression 4
use of * caracter in rst.Open strSQL 2
Data type mismatch in criteria expression 2
Data type mismatch in criteria expression 1

Top