By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,611 Members | 1,466 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,611 IT Pros & Developers. It's quick & easy.

Data type mismatch in criteria expression

P: 53

I know others have the same problem here, I tried (a lot! ) different solutions but still can't get around the problem.. :s

Here's the code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cb_swedit_sopi_AfterUpdate()
  2. Dim qry_upd_sopi As Variant
  4. On Error GoTo Err_sopi_AfterUpdate
  6.     MsgBox (Me.RN_EQU)
  7.     MsgBox (Me.cb_swedit_sopi.Column(0))
  9.     Me.txt_swedit_sopi_nr = Me.cb_swedit_sopi.Column(2)
  11.     qry_upd_sopi = "UPDATE [Equipment and Software] SET [Equipment and Software].SOP_install = '& Me.cb_swedit_sopi.Column(0) &' WHERE [Equipment and Software].RN_EQU = ' & Me.RN_EQU.Value &';"
  12.     DoCmd.RunSQL qry_upd_sopi
  14. Err_sopi_AfterUpdate:
  15.     MsgBox Err.Description
  17. End Sub
cb_ is a ComboBox

Do I need " DoCmd.GoToRecord , , acGoTo " ?

Besides this, the error is data type mismatch in criteria expression
(Where did he say??)

So I guess there is a missmatch between the type of value I get from the ComboBox
and the type of value of the SOP_install field (number).
The error is still there even if I add ".ToNumber" or not to cb_blabla.Column(0)

RN_EQU: primary key and autonumber (long integer)
SOP_install: number (long integer) in both tables..

The Message Boxes display the correct numbers. Either with .Value or not.

Can anyone tell me what is going wrong here?
If more info needed, don't hesitate to ask!
Jul 20 '09 #1
Share this Question
Share on Google+
14 Replies

Expert 100+
P: 635

May be this is the solution (your query is just a literal string) the quotation marks delimiters cause the variable/control values to be returned as part of the query string.

Expand|Select|Wrap|Line Numbers
  1. qry_upd_sopi = "UPDATE [Equipment and Software] SET [Equipment and Software].SOP_install = '" & Me.cb_swedit_sopi.Column(0) & "' WHERE [Equipment and Software].RN_EQU = '" & Me.RN_EQU.Value & "';"

Jul 20 '09 #2

P: 53

when I put double quotes, it still doesn't change a thing.

But when I add " .ToNumber " to cb_xx.Column(0)
It now says : "Object required".

I don't know what it means, so you can help me again :)
But I'll try also to look on my side..
Jul 20 '09 #3

Expert 5K+
P: 8,680
Expand|Select|Wrap|Line Numbers
  1. Dim qry_upd_sopi As String
  3. If IsNull(Me![RN_EQU]) Then Exit Sub
  5. DoCmd.SetWarnings False
  7. qry_upd_sopi = "UPDATE [Equipment and Software] SET [Equipment and Software].SOP_install = '" & _
  8.                 Me.cb_swedit_sopi.Column(0) & "' WHERE [Equipment and Software].RN_EQU = '" & _
  9.                 Me.RN_EQU.Value & "';"
  11. DoCmd.RunSQL qry_upd_sopi
  13. DoCmd.SetWarnings True
Jul 20 '09 #4

P: 53

thanks for the help, but still have a "data type mismatch" mith the code above.

If I put .ToNumber or .ToLongInteger I get object required again..

additional info:
-the cb's control source is empty -> unbound
- row source type is a query:
SELECT SOP.SOP_id, SOP.SOP_name, SOP.SOP_nr FROM SOP WHERE SOP_domain = "soft" AND (SOP_type = "I" OR SOP_type = "MI")

I created an application before and worked with buttons to insert and delete.
But here I use the AfterUpdate property to UPDATE. I see that updates in access cause a lot of trouble to people. Never had any problem before with a real SQL base and php for example.. So this is really confusing me.

Did I forget any other commands or something? I will lose my hair with this :)
Jul 20 '09 #5

Expert 5K+
P: 8,680
Assuming everything else is correct, namely: Table Field Names are correct, Form Field is correct, Column Reference is valid, etc., there can be 1 of 4 possible SQL Statements depending on the Data Types of the 2 Fields ([SOP_install] and [RN_EQU]). They are:
Expand|Select|Wrap|Line Numbers
  1. '[SOP_install] and [RN_EQU] are STRINGS
  2. qry_upd_sopi = "UPDATE [Equipment and Software] SET [Equipment and Software].SOP_install = '" & _
  3.                 Me.cb_swedit_sopi.Column(0) & "' WHERE [Equipment and Software].RN_EQU = '" & _
  4.                 Me.RN_EQU.Value & "';"
  6. '[SOP_install] and [RN_EQU] are NUMERIC
  7. qry_upd_sopi = "UPDATE [Equipment and Software] SET [Equipment and Software].SOP_install = " & _
  8.                 Me.cb_swedit_sopi.Column(0) & " WHERE [Equipment and Software].RN_EQU = " & _
  9.                 Me.RN_EQU.Value & ";"
  11. '[SOP_install] is a STRING and [RN_EQU] is NUMERIC
  12. qry_upd_sopi = "UPDATE [Equipment and Software] SET [Equipment and Software].SOP_install = '" & _
  13.                 Me.cb_swedit_sopi.Column(0) & "' WHERE [Equipment and Software].RN_EQU = " & _
  14.                 Me.RN_EQU.Value & ";"
  16. '[SOP_install] is NUMERIC and [RN_EQU] is A STRING
  17. qry_upd_sopi = "UPDATE [Equipment and Software] SET [Equipment and Software].SOP_install = " & _
  18.                 Me.cb_swedit_sopi.Column(0) & " WHERE [Equipment and Software].RN_EQU = '" & _
  19.                 Me.RN_EQU.Value & "';"
Jul 20 '09 #6

Expert 100+
P: 1,287
Since you specified that the table fields are Long Integer, you would use ADezii's 2nd query, at line 7 of the previous post.
If you start a string with a quotation, you need to close it with another quotation before you can append the value of a variable. You can't mix " and ' as in the original post.
Jul 20 '09 #7

P: 53
Wouhouou!! It works! Thanks to you all.

And saying it was so simple... and that I tried with simple quotes, a mix of simple and double. But double only was the only thing that I didn't tried! Shame on me o_0; !!!

So it's working, my selection is recorded in the table. But now I have two other questions..

When I reopen the form, the cb's starting value is empty, what's normal.
In order to load the stored value, do I need to put:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     sql blabla select...  
  3. End Sub
Or is it something else to display the stored value in the cb?

The other question:
When I've selected my value in the cb, a messagebox is coming up, but it is empty (no text). Should there be something like "1 record updated"? If so,
how do I make it display in the msgbox.
If not, what could it be?
Jul 21 '09 #8

Expert 100+
P: 1,287
If you want to get a stored value out of a table, you can use the DLookup function. It's not quite clear to me the way this form functions, or why you wouldn't just use a form bound to a recordset.
You have two MsgBox statements in your code, so you're going to have to be more specific about the other question.
Jul 21 '09 #9

P: 53
Well, the access application is one mess (not mine ^^). The database is not ACID at all! So I' m improving it a bit, but it's not that easy.

This is probably a form created with the wizard. Every field is linked to a table.field trough record source. So Access creates the record once the form is closed. Since my cboxes are not 'record sourced' they do not function like the rest of the form. (and this creates other problems..) And I can't make them 'record sourced' since this would not display the right information or put the wrong information in the table. I tried with record source, but I'm unable to make it work liek I want it to..

The disadvantage (or advantage for some) are the 'automatic' jobs of Access.
Like: there is a form to add new software (autonumber ID). The field for this ID has a control source. You fill in the fields of the different tabs in the form, close the form and everything is saved in the new record (like an insert into..)

nb: procedure = SOP

TABLE (RN_EQU(strange name for software id...), other stuff, SOP_use (foreign key), SOP_maint (fk), etc..)

The problem I have here is with my comboboxes (for selecting SOP). I can't set a recordsource since I don't want to show an id number in the field. So I use vb (at least, with vb, you know how it works).

On the first tab, selecting the type (here "software"), triggers the creation of a new record in the table, the ID exists, but the record isn't created yet.
So when I choose a SOP with cb that triggers an update, the update doesn't work because the new record is in memory only. So now I'm searching how to arrange this problem... (maybe create a new post for this one.. form_onClose)

The field to fill in (in software table) with cb (insert or update) is an id which is a foreign key to another table. The information displayed in cb is the info from the other table SOP(id, name, and nr (not id)). This is done trough row source type: query and a SELECT query from the SOP table with some WHERE clauses.

It's kind a mess to explain because it is a mess in fact..
Here's the code that works for cb afterupdate and with which (is this English?) I have a msgbox displayed with no text..

Expand|Select|Wrap|Line Numbers
  2. Private Sub cb_swnew_sopu_AfterUpdate()
  3. Dim qry_upd_sopu As String
  4. On Error GoTo Err_sopu_AfterUpdate
  6.     If IsNull(Me![RN_EQU]) Then Exit Sub
  7.     DoCmd.SetWarnings False
  9.     Me.txt_swnew_sopu_nr = Me.cb_swnew_sopu.Column(2)
  11.     qry_upd_sopu = "UPDATE [Equipment and Software] SET [Equipment and Software].SOP_use = " & _
  12.     Me.cb_swnew_sopu.Column(0) & " WHERE [Equipment and Software].RN_EQU = " & _
  13.     Me.RN_EQU.Value & ";"
  14.     DoCmd.RunSQL qry_upd_sopu
  15.     DoCmd.SetWarnings True
  17. Err_sopu_AfterUpdate:
  18.     MsgBox Err.Description
  20. End Sub
So I have no idea what Access is trying to say and why.. (1 record updated, perhaps?)
(I'll take a look at dlookup)
Jul 21 '09 #10

Expert 100+
P: 1,287
Hah, your error code is being executed every time throught the function.
Jul 21 '09 #11

Expert 100+
P: 1,287
Expand|Select|Wrap|Line Numbers
  1. Function ...
  2. On Error GoTo ErrorHandler
  4.     ...
  5.     ...
  7. ExitCode:
  8.     Exit Function
  10. ErrorHandler:
  11.     ...
  12.     Resume ExitCode
  13. End Function
Jul 21 '09 #12

P: 53

thanks, it works (when changing Function to Sub of course).
But how does it come that it is executed every time?
I picked up the 'old' error handling code somewhere else, used it in an other access prog and never had an issue like this..

What concerns Dlookup, I think I'll rather use something like
Expand|Select|Wrap|Line Numbers
  1. form_onLoad:
  2.  cb = select x, y, z from SOP where (match current software record and sop_id) 
This is not correct syntax, but I guess you get the idea?
Or is this a bad idea?
Jul 21 '09 #13

Expert 100+
P: 1,287
The error handling code you had was executed because you were missing the Exit. The label on the code doesn't prevent it from being executed if you allow it to get there.
Are you talking about the source for the combo or the value for the current record? If you want to set it to the value for the current record, you need to get the value for the bound field and set it equal to that.
Jul 21 '09 #14

P: 53
Well, my cb is like this:
control source: empty
Row Source:
WHERE SOP_id = 1 OR ((SOP_domain IN ("soft","all")) AND (SOP_type IN ("U", "UM")))
Bound Column: 1

And I only display SOP_name and SOP_nr

So I want the value for the current record to be displayed..
I don't understand this really much.. :s
you need to get the value for the bound field and set it equal to that.
Control source is still a bit confusing to me (after reading explanation on M$ site..)
Jul 21 '09 #15

Post your reply

Sign in to post your reply or Sign up for a free account.