471,044 Members | 948 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,044 software developers and data experts.


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?
Jul 20 '21 #1
9 3423
200 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?
Jul 20 '21 #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?
Jul 20 '21 #3
200 Expert 128KB
Try with:

Expand|Select|Wrap|Line Numbers
  1. whereStr = SelA & " And " & SelB & " And " & SelC
Jul 20 '21 #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
Jul 20 '21 #5
200 Expert 128KB
Without the content of SelA, B, and C, we can't help.
Jul 20 '21 #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
Jul 20 '21 #7
364 Expert Mod 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
Jul 20 '21 #8
200 Expert 128KB
Try reducing these like:

Expand|Select|Wrap|Line Numbers
  1. SelLic = "[swLicName] = Nz(Forms![frmViewLicToLoc]![txtSelectedLicense], [swLicName])"
Jul 20 '21 #9
32,336 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.
Jul 20 '21 #10

Post your reply

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

Similar topics

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.