Combining 3 strings in Access VBA

Question

I have a form that filters data by user, date range and specific value for a column, depending if checkbox is ticked or not, as below;

    strWhere = " WHERE [user] in (" & Left(strIN, Len(strIN) - 1) & ") And [Month] Between [forms]![frm_user]![txtStartDate] And [forms]![frm_user]![txtEndDate]"

    If Me!checkbox = True Then
        strtcheck = " (Satified Vs Dissatisfied Like Dissatisfied) "
    End If

    strSql = strSql & strwhere & strtcheck

So what I want and I can't get it to work is, if Me!checkbox is true, than the Satified Vs Dissatisfied must be equal to Dissatisfied and then I want to pass it to the strSql, however when i run it in Access it doesn't work, can someone help?

some quotes appear to be missing in your synthesized where clause. try strWhere = " WHERE [user] in ('" & Left(strIN, Len(strIN) - 1) & "') And [Month] Between [forms]![frm_user]![txtStartDate] And [forms]![frm_user]![txtEndDate]". what do the Vs operator in strtcheck mean? are Satisfied, Dissatisfied table attributes, form attributes or literals? the like operator only makes sense when used together with the sql wildcard (%). if you append strtcheck to the partial where clause, you forgot the operator (AND?). what's the content of strIN?. — collapsar
– collapsar, Commented Jul 12, 2013 at 13:45
ok, just saw it in hansup's answer (which together with single quotes around the literal in the first part of the where clause should do the trick). — collapsar
– collapsar, Commented Jul 12, 2013 at 13:57

HansUp · Accepted Answer · 2013-07-12 14:05:08Z

1

If your string is intended to include a WHERE condition for a field named Satified Vs Dissatisfied, enclose the field name in square brackets. And enclose the string you compare to the field in quotes.

strtcheck = " ([Satified Vs Dissatisfied] Like 'Dissatisfied') "

Actually that condition doesn't use pattern matching, so you could just use = instead of Like.

strtcheck = " ([Satified Vs Dissatisfied] = 'Dissatisfied') "

Also you likely need to include SQL AND to combine that condition with the other WHERE conditions.

strtcheck = " AND ([Satified Vs Dissatisfied] = 'Dissatisfied') "

edited Jul 12, 2013 at 14:05

answered Jul 12, 2013 at 13:44

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

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

4 Comments

qebas Over a year ago

thanks, but my real issue is with strSql = strSql & strwhere & strtcheck, it doesn't work

qebas Over a year ago

doesn't contain anything, it's empty. Can I combine 3 strings like shown above?

collapsar Over a year ago

@qebas: you need to append strtcheck with an operator (AND i guess) to get a syntacticaly valid where clause. turn strtcheck = " (Satified Vs Dissatisfied Like Dissatisfied) " into strtcheck = " AND ([Satified Vs Dissatisfied] = 'Dissatisfied') ". and make sure that you have no spelling error ( Sati_s_fied ) in your column name.

HansUp Over a year ago

@collapsar Good point, thanks. I added that to my answer. However I can't understand why strSql is empty.

Collectives™ on Stack Overflow

Combining 3 strings in Access VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related