MS Access VBA concatenate variables dynamically in SQL

Question

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)?

Community · Accepted Answer · 2017-05-23 12:15:10Z

1

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.

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Nov 26, 2015 at 20:23

Andre

27.7k7 gold badges43 silver badges85 bronze badges

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

1 Comment

dellair Over a year ago

Thanks, I accepted your answer because it is a simpler solution.

gees · Accepted Answer · 2015-11-26 23:25:02Z

1

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

answered Nov 26, 2015 at 23:25

gees

115 bronze badges

2 Comments

dellair Over a year ago

Thanks, I have more checkboxes on the same page, so I wonder if this would work in the scenarios.

gees Over a year ago

If you have other checkboxes that you don't want to include then your could try adding a tag to the ones you want and add a check for that tag with the check for the controltype.

Collectives™ on Stack Overflow

MS Access VBA concatenate variables dynamically in SQL

2 Answers 2

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Linked

Related