Datatype mismatch in Criteria Expression error in Query

[abhay_547]

Hi All,

I have database in which I have data table called commentary table now I created a userform to input/modify data in the same table the form has 1 Combobox which contains a list of products populated from the product column of the datatable same way there is dtpicker which is updated with the "Data as of date" column of data table and a commentary box which is textbox .i.e. text15 which gets populated with the commentary column data from the data table. Now this form will be used like this .i.e. everyday product guys will select their respective product in combobox on form and then remove the last entered commentary from commentary box .i.e. text15 and update new commentary and select new date in the dtpicker and then click on a command button .i.e. submit commentary which is inserted on the form. Now I want this command button to function in such a way that it should update the commentary and the date selected by the product guys against his respective product in the datatable. I am using the below sql statement for the same but it's showing following error. Please help. Enclosed is my database file for your reference.

Private Sub Command17_Click() 
DoCmd.SetWarnings WarningsOff
 
 
Dim sSQL As String
 
sSQL = "UPDATE [Commentary Table] SET " 
sSQL = sSQL & "[Commentary] = '" & Me.Text15 & "'," 
sSQL = sSQL & "[Data As Of Date] = #" & Me.DTPicker1 & "# " 
sSQL = sSQL & "WHERE (((ID)= '" & Me.Combo2 & "'))"
 
DoCmd.RunSQL sSQL 
DoCmd.SetWarnings WarningsOn
 
 
End Sub

Error Msg: Run-time error '3464': Data type mismatch in criteria expression

Thanks a lot for your help in advance

 

[pbaldy]

You don't want the single quotes around a numeric data type. Try

sSQL = sSQL & "WHERE ID= " & Me.Combo2

 

[abhay_547]

Hi pbaldy,

Thanks a lot for your quick reply, I have incorporated the change suggested by you in my sql statement but still I am facing the below error msg.

Error Msg: Run time error '3075': syntax error (missing operator) in query expression 'ID = FI Cash'

When I debug the above error it highlights the below line.

DoCmd.RunSQL sSQL

Apart from the above error I need help on one more thing .i.e. in my database table .i.e. commentary table I have column called "Expected Data As of Date" now this column has those dates which the data provider is supposed to select in "Data As of Date" .i.e. DTPicker while submitting the commentary I want to put some code in the click event of the command17 which will look for the "Expected Data As of Date" for the product selected in the combobox and then try to match it with the date which is selected by the user in the DTpicker and if it doesn't match then it will show a msgbox that the date selected by user is incorrect and he need to select the correct date. I tried to do the same by using dlookup .i.e. If Me.Dtpicker1.value <> dlookup("[Expected data as of date]"..... then show msgbox at the end of the clickevent code but unfortunately it didn't work. Can you please help me with the same as well. I have commented out the code lines in below command button clickevent.

Private Sub Command17_Click() 
DoCmd.SetWarnings False
 
 
Dim sSQL As String
 
sSQL = "UPDATE [Commentary Table] SET " 
sSQL = sSQL & "[Commentary] = '" & Me.Text15 & "'," 
sSQL = sSQL & "[Data As Of Date] = #" & Me.DTPicker1 & "# " 
sSQL = sSQL & "WHERE ID= " & Me.Combo2
 
DoCmd.RunSQL sSQL 
DoCmd.SetWarnings True
 
 
'If Me.DTPicker1.Value <> DLookup("[Expected Data As of Date]", "[Commentary Table]", "[Product] = '" & Me.Combo2 & "'") Then 'MsgBox "The Commentary Date selected doesn't meet the expected Timeline. Please Select the Correct date" 
'Else
 'MsgBox "Commentary Submitted Successfully" 
'End If 
End Sub

Thanks a lot for your help in advance

 

[pbaldy]

As I mentioned, that was appropriate for a numeric data type, which yours isn't. Try:

sSQL = sSQL & "WHERE ID= '" & Me.Combo2 & "'"

 

[abhay_547]

Hi pbaldy,

Thanks a lot for your quick reply, Post incorporating the change suggested by you now I am facing a different error.

Error Msg: Run-time error '3464' Datatype mismatch in criteria expression

When I debug it highlights the same line .i.e.

DoCmd.RunSQL sSQL

But this time I have observed a different thing that is if I select a product which has space in it's name in the combo2 box then it shows the above error for e.g. when I selected FI Cash and tried to modify the commentary in Text15 box and click on submit commentary button then it showed the above mentioned error msg but when I select a product which doesn't have space in it's name for e.g. DCM then it shows a different error msg which is as below:

Error Msg when I select a different product .i.e. DCM which doesn't have space in it's name : Run time error '3075': syntax error (missing operator) in query expression ''Let's hope that the Sunday News will now call for Stedman to conduct the investigation that Totaro refused to do, and which Luna and those who grieve for him and seek justice deserve'.
When I debug it again highlights the same line .i.e.

DoCmd.RunSQL sSQL

Now in the above error msg you see the entire commentary entered in text15 box for DCM product .i.e. Let's hope....

Thanks a lot for your help in advance.

 

[pbaldy]

Is the ID field numeric or text? Are those values appropriate for it?

Secondly, the problem with the next bit is the apostrophe in "Let's". It closes off the string you were building. If you are going to allow them, try this:

sSQL = sSQL & "[Commentary] = " & Chr(34) & Me.Text15 & Chr(34) & ","

 

[abhay_547]

Hi pbaldy,

Thanks a lot once again for your reply, I have incorporated the change and below is the error which I am facing.

Error msg: Compile error: can't find project or library

When debug it highlights the Chr text the following line : sSQL = sSQL & "[Commentary] = " & Chr(34) & Me.Text15 & Chr(34) & ","

Apart from this I have checked the design mode the commentary data table and I can see that the ID field is data type is Autonumber which means numeric.

Just for your info I am testing this in access 2010. But at my workplace I have access 2003. Ideally it should be working in both versions.

Thanks a lot for your help in advance

 

[pbaldy]

I think this will all work in either version. The Chr() function is common, so it sounds like this:

http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

For the ID field, it sounds like you're comparing apples and oranges, in other words a text value and a numeric field. I suspect the wrong column of the combo is bound. You can change that or use the .Column property.

 

[abhay_547]

Hi pbaldy,

Thanks a lot for your reply, It's working fine now. Can you pls help me with below issue as well.

I need help on one more thing .i.e. in my database table .i.e. commentary table I have column called "Expected Data As of Date" now this column has those dates which the data provider is supposed to select in "Data As of Date" .i.e. DTPicker while submitting the commentary I want to put some code in the click event of the command17 which will look for the "Expected Data As of Date" for the product selected in the combobox and then try to match it with the date which is selected by the user in the DTpicker and if it doesn't match then it will show a msgbox that the date selected by user is incorrect and he need to select the correct date. I tried to do the same by using dlookup .i.e. If Me.Dtpicker1.value <> dlookup("[Expected data as of date]"..... then show msgbox at the end of the clickevent code but unfortunately it didn't work. Can you please help me with the same as well. I have commented out the code lines in below command button clickevent.

I need help on the code lines highlighted in blue.

Private Sub Command17_Click()
[B][COLOR=blue]If Me.DTPicker1.Value <> DLookup("[Expected Data As of Date]", "[Commentary Table]", "[Product] = '" & Me.Combo2 & "'") Then
MsgBox "The Commentary Date selected doesn't meet the expected Timeline. Please Select the Correct date"
Else
MsgBox "Commentary Submitted Successfully"
End If
[/COLOR][/B]DoCmd.SetWarnings False
Dim sSQL As String
sSQL = "UPDATE [Commentary Table] SET "
sSQL = sSQL & "[Commentary] = " & Chr(34) & Me.Text15 & Chr(34) & ","
sSQL = sSQL & "[Data As Of Date] = #" & Me.DTPicker1 & "# "
sSQL = sSQL & "WHERE Product= '" & Me.Combo2 & "'"
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True
End Sub

Thanks a lot for your help in advance.

 

[pbaldy]

"Working fine" I understand. "Didn't work" is so nebulous I don't even know where to start looking, and I'm too lazy to look everywhere.

Does it error? If so what's the error message? Wrong result? Etc?