Solved Access update query selects checkboxes (1 Viewer)

mreniff

Member
Local time
Today, 16:26
Joined
Nov 30, 2012
Messages
45
Current error message: Run Time Error 3464: Data Type Mismatch in criteria expression. My questions are below Module1 code

A value is entered in the Textbox1 (on a subform) and a button run this code and fails due to Run Time Error 3464: Data Type Mismatch in criteria expression

Private Sub cmdUpdate_Click()
Dim strSQL As String

strSQL = "UPDATE Students SET Students.UpdateWorkshop = 'Yes' "
strSQL = strSQL & "WHERE Students.[ID] IN "
strSQL = strSQL & "(SELECT TOP " & CLng(Me.Textbox1) & " [ID] FROM Students As S " ' select # or records based textbox1 value
strSQL = strSQL & "WHERE (NZ(S.UpdateWorkshop,'No')='No') AND (S.Major In ('BADM', 'PBA')) " ' UpdateWorkshop must No or Null, Major IN BADM or PBA
strSQL = strSQL & "AND (S.Session='" & [Forms]![WorkshopsSession]![SessionCombo] & "'));"

fExecuteQuery strSQL, dbFailOnError ' data type mismatch fExecuteQuery is Module1. See code below
' reset textbox to blank
Me.Textbox1 = Null
End Sub

==============Module1=================================

Function fExecuteQuery(strQuery As String, _
Optional intOptions As DAO.RecordsetOptionEnum = dbFailOnError, _
Optional blnReturnAuto As Boolean = False, _
Optional pdb As DAO.Database) As Long

Dim db As Database
Dim prm As DAO.Parameter
Dim qdf As QueryDef
Dim rst As DAO.Recordset

If Not pdb Is Nothing Then
Set db = pdb
Else
Set db = CurrentDb
End If

Select Case Left(strQuery, 7)
Case "INSERT ", "UPDATE ", "DELETE "
Set qdf = db.CreateQueryDef("", strQuery)
Case Else
Set qdf = db.QueryDefs(strQuery)
End Select

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next

qdf.Execute intOptions debugger stops here

If blnReturnAuto Then
Set rst = db.OpenRecordset("SELECT @@Identity")
fExecuteQuery = rst(0)
rst.Close
End If

Set prm = Nothing
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing


End Function

================questions===============================

Which code contains the mismatch datatype criteria expression? Is it the Private sub cmdUpdate or in Module1?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:26
Joined
Oct 29, 2018
Messages
21,358
Hi. Data type mismatch errors are usually caused by the data and not the code. Wouldn't it be faster (since this is your second thread on the same topic) to post a sample db to get the solution quicker?
 

mreniff

Member
Local time
Today, 16:26
Joined
Nov 30, 2012
Messages
45
Yes, I agree. I need a sample DB. I will try again to build one. I will also review my data.

I closed the other thread. It was getting too LONG>
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:26
Joined
Oct 29, 2018
Messages
21,358
Yes, I agree. I need a sample DB. I will try again to build one. I will also review my data
Without seeing your actual setup, it's hard to pinpoint the source of the problem.
 

moke123

AWF VIP
Local time
Today, 19:26
Joined
Jan 11, 2013
Messages
3,852
strSQL = "UPDATE Students SET Students.UpdateWorkshop = 'Yes' "
strSQL = strSQL & "WHERE Students.[ID] IN "
strSQL = strSQL & "(SELECT TOP " & CLng(Me.Textbox1) & " [ID] FROM Students As S " ' select # or records based textbox1 value
strSQL = strSQL & "WHERE (NZ(S.UpdateWorkshop,'No')='No') AND (S.Major In ('BADM', 'PBA')) " ' UpdateWorkshop must No or Null, Major IN BADM or PBA
strSQL = strSQL & "AND (S.Session='" & [Forms]![WorkshopsSession]![SessionCombo] & "'));"
is Students.UpdateWorkshop a checkbox? if so use true or -1

An in clause needs to be within parenthesis. Looks like your missing some.

do a debug.print strSql
 

June7

AWF VIP
Local time
Today, 15:26
Joined
Mar 9, 2014
Messages
5,423
If UpdateWorkshop is a yes/no field, don't put No between apostrophes. Yes/No field is a Boolean which contains either -1 or 0. Use either the number values or True/False constants without apostrophe delimiters. A Yes/No field is never Null.

I think the IN expression is fine.
 

mreniff

Member
Local time
Today, 16:26
Joined
Nov 30, 2012
Messages
45
is Students.UpdateWorkshop a checkbox? if so use true or -1

An in clause needs to be within parenthesis. Looks like your missing some.

do a debug.print strSql
I had a feeling that UpdateWorkshop checkbox value should be true or -1 or 0

Here is the result from the Immediate window for debug.print strSQL
UPDATE Students SET Students.UpdateWorkshop = -1
WHERE Students.[ID] IN (SELECT TOP 5 [ID] FROM Students As S
WHERE ([S.UpdateWorkshop]=false) AND([S.Workshop]=Null)AND([S.Major] In ('BADM', 'PBA')) AND ([S.Session]='1'));
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:26
Joined
May 7, 2009
Messages
19,169
you are correct. if you go back to the structure you posted yesterday.

on your table (fields):
fieldname--------------fieldtype-----------------acceptable values
UpdateWorkshop---------Yes/No--------------------0 or false/ -1 or true
ID---------------------long integer--------------negative/positive whole number
Major------------------short text(50)------------alpha numeric (up to 50 char long)
Session----------------long integer

therefore, change the query to:
Code:
strSQL = "UPDATE Students SET Students.UpdateWorkshop = -1 "
strSQL = strSQL & "WHERE Students.[ID] IN "
strSQL = strSQL & "(SELECT TOP " & Clng(Me.Textbox1) & " [ID] FROM Students As S " ' select # or records based textbox1 value
strSQL = strSQL & "WHERE (NZ(S.UpdateWorkshop, 0) = 0) AND (S.Major In ('BADM', 'PBA')) " ' UpdateWorkshop must No or Null, Major IN BADM or PBA
strSQL = strSQL & "AND (S.Session = " & Val([Forms]![WorkshopsSession]![SessionCombo] & "")  & "));"
 
Last edited:

June7

AWF VIP
Local time
Today, 15:26
Joined
Mar 9, 2014
Messages
5,423
A yes/no field is either -1 or 0, never Null, so testing for Null with Nz should not be needed. Don't use processing for a function that is not needed.
 

mreniff

Member
Local time
Today, 16:26
Joined
Nov 30, 2012
Messages
45
you are correct. if you go back to the structure you posted yesterday.

on your table (fields):
fieldname--------------fieldtype-----------------acceptable values
UpdateWorkshop---------Yes/No--------------------0 or false/ -1 or true
ID---------------------long integer--------------negative/positive whole number
Major------------------short text(50)------------alpha numeric (up to 50 char long)
Session----------------long integer

therefore, change the query to:
Code:
strSQL = "UPDATE Students SET Students.UpdateWorkshop = -1 "
strSQL = strSQL & "WHERE Students.[ID] IN "
strSQL = strSQL & "(SELECT TOP " & Clng(Me.Textbox1) & " [ID] FROM Students As S " ' select # or records based textbox1 value
strSQL = strSQL & "WHERE (NZ(S.UpdateWorkshop, 0) = 0) AND (S.Major In ('BADM', 'PBA')) " ' UpdateWorkshop must No or Null, Major IN BADM or PBA
strSQL = strSQL & "AND (S.Session = " & Val([Forms]![WorkshopsSession]![SessionCombo] & "")  & "));"
This is the latest SQL statement

UPDATE Students SET Students.UpdateWorkshop = -1 WHERE Students.[ID] IN (SELECT TOP 5 [ID] FROM Students As S WHERE ([S.UpdateWorkshop]=false) AND ([S.Workshop]=Null) AND ([S.Major] In ('BADM', 'PBA')) AND ([S.Session]='1'));UPDATE Students SET Students.UpdateWorkshop = -1 WHERE Students.[ID] IN (SELECT TOP 5 [ID] FROM Students As S WHERE ([S.UpdateWorkshop]=false) AND ([S.Workshop]=Null) AND ([S.Major] In ('BADM', 'PBA')) AND ([S.Session]='1'));
 

moke123

AWF VIP
Local time
Today, 19:26
Joined
Jan 11, 2013
Messages
3,852
Have you added a debug.print so you can see in the immediate window what your sql resolves to?
 

mreniff

Member
Local time
Today, 16:26
Joined
Nov 30, 2012
Messages
45
June 7,

I want you to know that your new code works, but it only checks the very 1st box . Making progress!

Thank you
 

June7

AWF VIP
Local time
Today, 15:26
Joined
Mar 9, 2014
Messages
5,423
Can't use Null directly in an equality. Nothing is ever equal to Null because there is nothing to evaluate, not even Null=Null, will always return False.

Use Is Null or IsNull() or Nz().

Your bracketing is not correct. [S.Major] should be [ S].[Major]. Can remove all since they aren't even needed as your names do not include space nor special characters nor are reserved words. Most of those parens are not needed. You have two UPDATE statements running together.
Code:
UPDATE Students SET Students.UpdateWorkshop = -1 WHERE Students.ID IN (SELECT TOP 5 ID FROM Students AS S WHERE S.UpdateWorkshop=False AND S.Workshop Is Null AND S.Major In ('BADM', 'PBA') AND S.Session='1');
If Session is a number field, remove apostrophes from '1'.
 
Last edited:

mreniff

Member
Local time
Today, 16:26
Joined
Nov 30, 2012
Messages
45
This is the code that is working. Thank you.

Private Sub cmdUpdate_Click()

Dim strSQL As String

strSQL = "UPDATE Students SET Students.UpdateWorkshop = -1 "
strSQL = strSQL & "WHERE Students.[ID] IN "
strSQL = strSQL & "(SELECT TOP " & CLng(Me.Textbox1) & " [ID] FROM Students As S " ' select # or records based textbox1 value
strSQL = strSQL & "WHERE (NZ(S.UpdateWorkshop, 0) = 0) AND (S.Major In ('BADM', 'PBA')) " ' UpdateWorkshop must No or Null, Major IN BADM or PBA
strSQL = strSQL & "AND (S.Session = " & Val([Forms]![WorkshopsSession]![SessionCombo] & "") & "));"
DoCmd.Requery

fExecuteQuery strSQL, dbFailOnError

Me.Textbox1 = Null

End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:26
Joined
Oct 29, 2018
Messages
21,358
This is the code that is working. Thank you.

Private Sub cmdUpdate_Click()

Dim strSQL As String

strSQL = "UPDATE Students SET Students.UpdateWorkshop = -1 "
strSQL = strSQL & "WHERE Students.[ID] IN "
strSQL = strSQL & "(SELECT TOP " & CLng(Me.Textbox1) & " [ID] FROM Students As S " ' select # or records based textbox1 value
strSQL = strSQL & "WHERE (NZ(S.UpdateWorkshop, 0) = 0) AND (S.Major In ('BADM', 'PBA')) " ' UpdateWorkshop must No or Null, Major IN BADM or PBA
strSQL = strSQL & "AND (S.Session = " & Val([Forms]![WorkshopsSession]![SessionCombo] & "") & "));"
DoCmd.Requery

fExecuteQuery strSQL, dbFailOnError

Me.Textbox1 = Null

End Sub
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

June7

AWF VIP
Local time
Today, 15:26
Joined
Mar 9, 2014
Messages
5,423
I am surprised it works because parens and ending quotes don't look right for last line of SQL. If Session is number type, consider:

strSQL = strSQL & "AND (S.Session = " & Val([Forms]![WorkshopsSession]![SessionCombo]) & "));"

Still more parens than are need and just adds to confusion.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:26
Joined
May 7, 2009
Messages
19,169
if the combo Is blank, nothing is selected so far, using Val([combo]) will result in Error (94: Invalid use of Null).

so we concat it to "" (vbNullString) and the expression Val([combo] & "") will now return 0.
 

June7

AWF VIP
Local time
Today, 15:26
Joined
Mar 9, 2014
Messages
5,423
Ah, see that now. Nz() should serve just as well without Val() and having to concatenate empty string.

Always more than one way.
 

Users who are viewing this thread

Top Bottom