How to correctly query if checkbox is checked in Access from Excel

Question

Checkbox6 on a userform in Excel determines the selection criteria for a query. I need to use the property of checkbox6 True/False to query column [Archived] that is located in a table in Access. For example, if the Checkbox6 is checked on the userform the column [Archived] is queried for “True” values. The column [Archived] is data type Yes/No. I’ve tried both “True” and “False” and also -1 and 0 but it doesn’t seem to work. Please look at my code below and suggest how to make this work.

Thanks

Dim sar4 As String
sar4 = "False"

SQLwhere = "WHERE "


If Len(sar2 & vbNullString) <> 0 Then

If CheckBox7 = True Then
        SQLwhere = SQLwhere & "[MDL_Comments].[AA_change] = '" & sar2 & "' AND "
    Else

SQLwhere = SQLwhere & "[MDL_Comments].[AA_change] LIKE '" & "%" & sar2 & "%" & "' AND "
End If

End If

If CheckBox6 = True Then
SQLwhere = SQLwhere & "[MDL_Table1].[Archived] = '" & sar4 & "' AND "
End If

StrSql = "SELECT * FROM [MDL_Comments] "
 'Remove the last AND applicable
If SQLwhere = "WHERE " Then
    SQLwhere = ""
Else
    SQLwhere = Left(SQLwhere, Len(SQLwhere) - 5)
End If
StrSql = StrSql & SQLwhere

jhTuppeny · Accepted Answer · 2015-08-04 15:28:36Z

1

I think the problem is your use of single quotes around your variable - ('" & sar4 & "'). This will set the variable to be a string regardless of the value you assign to it.

answered Aug 4, 2015 at 15:28

jhTuppeny

9901 gold badge12 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3781528 Over a year ago

Sorry, please clarify What do I need to change? Thanks

jhTuppeny Over a year ago

You have single quotes inside you double quotes < ' " & sar4 & " ' > remove the single quote so that you have just < " & sar4 & ">

user3781528 Over a year ago

How should I declare sar4? I changed the quotes but got the same error."no value given for one or more required parameters" Thanks

jhTuppeny Over a year ago

You can keep the variable as a string. I suspect there's an error somewhere else. Try replacing the variable with just the SQL string and take a look at the complete SQL string to check it.

user3781528 Over a year ago

Should I try True/False or 0/-1. I think the values are stored as -1 and 0 in Access, or am I wrong? Thanks

|

Collectives™ on Stack Overflow

How to correctly query if checkbox is checked in Access from Excel

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related