CODEWITHSUNDEEP
ms-accessvbams-access-2010
dellair
dellair

Reputation: 437

MS Access VBA concatenate variables dynamically in SQL

I have a form with 7 checkboxes, each checkbox is a status: Open, Closed, Rejected, Postponed, Working, Duplicated and Testing

The value of checkbox can be True or False.

Now I would like to have the value of the checkbox to be concatenated in a SQL statement in VBA:

Dim Status As String
If (Open.value = True) Then Status = " status = 'Open'" End If
If (Postponed.value = True) Then Status = " or status = 'Postponed'" End If
If (Closed.value = True) Then Status = " or status = 'Closed'" End If
If (Rejected.value = True) Then Status = " or status = 'Rejected'" End If
If (Working.value = True) Then Status = " or status = 'Working'" End If
If (Duplicated.value = True) Then Status = " or status = 'Duplicated'" End If
If (Testing.value = True) Then Status = " or status = 'Testing'" End If
sqlstatement = "select * from cr where " & status

The issue with above code is that it is kinda hard coded. Meaning that if open.value is not true, then the whole statement is wrong. So how to make it dynamic to assemble only the criterias in where (any checkbox can be selected or dis-selected)?

Upvotes: 0

Views: 301

Answers (2)

gees
gees

Reputation: 11

Since you have matched names between the checkboxes and the fields, and presuming there are no other checkboxes:

Dim status As String
Dim ctl As Control
status = ""
For Each ctl In Me.Controls
    If ctl.ControlType = acCheckBox Then
        status = status & " or status = '" & ctl.Name & "'"
    End If
Next
if not status = "" then
    sqlstatement = "select * from cr where " & Mid(status, 5)
End if

Upvotes: 1

Andre
Andre

Reputation: 27634

Adapted from the second part of this answer: https://stackoverflow.com/a/33630318/3820271

Status = ""
If (Open.value = True) Then Status = Status & " OR status = 'Open'" 
If (Postponed.value = True) Then Status = Status & " OR status = 'Postponed'"
If (Closed.value = True) Then Status = Status & " OR status = 'Closed'"
' ...
sqlstatement = "select * from cr where 1=0 " & status

How does it work?

Every criterium is added with OR. The statement has a default WHERE clause that is False. If no checkbox if checked, no records are returned (I assume that is what you want).

Note: If you have If ... Then ... in a single line, you must not use End If. Your code wouldn't compile.

Upvotes: 1

Related Questions