468,741 Members | 1,793 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,741 developers. It's quick & easy.


4 Nibble
Trying to open a report with multiple WHEREconditions. I have 4 strings (SelA, SelB, SelC, whereStr) all defined as strings.

SelA, SelB SelC all pieces of the whereStr

If I set whereStr = {any of the individual strings A,B or C}, the following works

DoCmd.OpenReport "rptName", acViewReport , , whereStr

When I try to concatenate the stings into 'whereStr', I only get the first string listed in the report.

Anyone? Help?
1 Week Ago #1
9 1508
172 Expert 128KB
Concatenate? How? They are there already: SelA, SelB SelC all pieces of the whereStr

And do you wish to filter on four fields or one field?
1 Week Ago #2
4 Nibble
If I set
It works fine

how do I get the whereStr to be equal to SelA, SelB, and SelC.

I want to filter on all 3 fields.

Any thoughts?
1 Week Ago #3
172 Expert 128KB
Try with:

Expand|Select|Wrap|Line Numbers
  1. whereStr = SelA & " And " & SelB & " And " & SelC
1 Week Ago #4
4 Nibble
I had tried that before. It only recognizes the first string and ignores the rest. I just tried again triple checking the & and "". Still does not work.

I also tried in the 'WHERE" entry,
SelA + SelB + SelC
1 Week Ago #5
172 Expert 128KB
Without the content of SelA, B, and C, we can't help.
1 Week Ago #6
4 Nibble
Expand|Select|Wrap|Line Numbers
  1. Dim SelLic, SelSW, SelArea As String
  2. Dim whereStr As String
  4. SelLic = "[swLicName]= Forms![frmViewLicToLoc]![txtSelectedLicense] _
  5.     OR Forms![frmViewLicToLoc]![txtSelectedLicense] is null _
  6.     OR Forms![frmViewLicToLoc]![txtSelectedLicense]=''"
  8. SelSW = "[swName]=Forms![frmViewLicToLoc]![txtSelectedSW] _
  9.     OR Forms![frmViewLicToLoc]![txtSelectedSW] is null _
  10.     OR Forms![frmViewLicToLoc]![txtSelectedSW]=''"
  12. SelArea = "[areaLocation] = Forms![frmViewLicToLoc]![txtSelectedArea] _
  13.     OR Forms![frmViewLicToLoc]![txtSelectedArea] is null _
  14.     OR Forms![frmViewLicToLoc]![txtSelectedArea]=''"
  16.     whereStr = SelSW
  18.     DoCmd.OpenReport "rptViewSearch", acViewReport, , whereStr
1 Week Ago #7
276 Expert 256MB
Suggest you look at the code in the two example Databases from my article Multiple Group and Filter.
These are designed to show ways of building filter criteria when several filter criteria are entered
1 Week Ago #8
172 Expert 128KB
Try reducing these like:

Expand|Select|Wrap|Line Numbers
  1. SelLic = "[swLicName] = Nz(Forms![frmViewLicToLoc]![txtSelectedLicense], [swLicName])"
1 Week Ago #9
32,129 Expert Mod 16PB
Hi Bryon.

I would suggest you're getting in your own way here. Massively overcomplicating what should be fundamentally straightforward. See How to Debug SQL String for how to make this much easier for yourself. I suspect once you start following this simple guidance you will no longer need any external help.

Can we assume that you understand and recognise how a WHERE clause is formatted and works within SQL (SQL format closely matches what is expected in the WhereCondition parameter that we're talking about here from your code.)? If not then we have to look at some very basic work with you, but as you'll already have seen, we have many willing helpers who can do that as long as they realise it's necessary.

I suspect once we stop following you round in circles we can reach a solution that not only works for you, but also one that you'll understand and be able to use in future.

Best of luck & welcome to Bytes.com.
1 Week Ago #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
2 posts views Thread by xarzu | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.