[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

And vs Or query in VBA

Posted on 2007-08-08
10
Medium Priority
?
258 Views
Last Modified: 2013-11-05
I have a form with 6 fields
From Date
To Date
ApplicntId
Position
County
Referral
I have a query in a report that refers to the fields on this form (frmApplicantRpts).
My query is as follows but the data Im getting is more than I want.
If I ask for ApplicntId: Bob and Position: Programmer I only want the records that meet both criteria.
Instead Im getting all of Bob in any position and all Programmers for any person.
I tried taking out the Like * but then I get nothing at all.
Id appreciate any help.
Thanks


SELECT [LastName] & ", " & [FirstName] & " " & [MI] AS Name, tblApplicntDet.Date, tblApplicnt.ApplicntId, tblApplicntDet.Position, tblApplicnt.County, tblApplicnt.Referral, tblApplicnt.ApplicntId
FROM tblApplicnt INNER JOIN tblApplicntDet ON tblApplicnt.ApplicntId = tblApplicntDet.ApplicntId
WHERE (((tblApplicntDet.Date) Between ([Forms]![frmApplicantRpts]![cboFm]) And ([Forms]![frmApplicantRpts]![cboTo]) Or (tblApplicntDet.Date) Like "*") AND ((tblApplicnt.ApplicntId)=[Forms]![frmApplicantRpts]![cboName] Or (tblApplicnt.ApplicntId) Like "*") AND ((tblApplicntDet.Position)=[Forms]![frmApplicantRpts]![cboPosition] Or (tblApplicntDet.Position) Like "*") AND ((tblApplicnt.County)=[Forms]![frmApplicantRpts]![cboCounty] Or (tblApplicnt.County) Like "*") AND ((tblApplicnt.Referral)=[Forms]![frmApplicantRpts]![cboReferralType] Or (tblApplicnt.Referral) Like "*"));
0
Comment
Question by:BobRosas
  • 4
  • 3
  • 3
10 Comments
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 19658933
Hi BobRosas,

Try This:

SELECT [LastName] & ", " & [FirstName] & " " & [MI] AS Name, tblApplicntDet.Date, tblApplicnt.ApplicntId, tblApplicntDet.Position, tblApplicnt.County, tblApplicnt.Referral, tblApplicnt.ApplicntId
FROM tblApplicnt INNER JOIN tblApplicntDet ON tblApplicnt.ApplicntId = tblApplicntDet.ApplicntId
WHERE (tblApplicnt.ApplicntId=[Forms]![frmApplicantRpts]![cboName]) AND (tblApplicntDet.Position=[Forms]![frmApplicantRpts]![cboPosition]);

This should only display entries where both the Applicntid and cboPosition match the entries in the form.  If you need a set of results that match either then swap the AND for OR.

with your original WHERE statement all your entries contradicted themselves (i.e ID = FormsID OR IDLike *) will give all ID' because of teh Like * section.

I hope this helps.

Mark.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 19659002
by reformating your SQL:

SELECT [LastName] & ", " & [FirstName] & " " & [MI] AS Name, tblApplicntDet.Date, tblApplicnt.ApplicntId, tblApplicntDet.Position, tblApplicnt.County, tblApplicnt.Referral, tblApplicnt.ApplicntId
FROM tblApplicnt INNER JOIN tblApplicntDet ON tblApplicnt.ApplicntId = tblApplicntDet.ApplicntId
WHERE
   (
    (
     (tblApplicntDet.Date) Between
       ([Forms]![frmApplicantRpts]![cboFm]) And ([Forms]![frmApplicantRpts]![cboTo])
      Or (tblApplicntDet.Date) Like "*")
AND (
     (tblApplicnt.ApplicntId)=[Forms]![frmApplicantRpts]![cboName]
       Or (tblApplicnt.ApplicntId) Like "*")
AND (
     (tblApplicntDet.Position)=[Forms]![frmApplicantRpts]![cboPosition]
      Or (tblApplicntDet.Position) Like "*")
AND (
     (tblApplicnt.County)=[Forms]![frmApplicantRpts]![cboCounty]
      Or (tblApplicnt.County) Like "*")
AND (
     (tblApplicnt.Referral)=[Forms]![frmApplicantRpts]![cboReferralType]
      Or (tblApplicnt.Referral) Like "*"));

YOur OR clauses essentially eliminate the utility of the = part of each clause, since if the = part is NOT matched, then the OR part will be, thus selected EVERY possible value in each of the fields where you use the LIKE operator.

AW

0
 

Author Comment

by:BobRosas
ID: 19663080
Thank you both for your responses but I'm hoping to get a little more  help.

Mark, Your idea works if I only fill out the Id and Position but I'd like it to work for all of the fields.  For example if Position was left blank and Name and Referral type was filled in I would like it to display only data with the matching Name AND Referral type.  When I leave Position blank the report is blank.

AW, Your code has a good format which makes it much easier to read but it's acting just like my code and giving me more than I want.  If I enter a name and a referral type I get all with that name and all with that referral type even tho i just want the records matching the Name AND Referral.

I'd really appreciate any input.
Thanks
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 19663298
You could try running a differen SQL statment based on the contents of your fields.

i.e.

 If both are filled in run my SQL above

If only Position is filled in and the rest were null then only have

WHERE (tblApplicntDet.Position=[Forms]![frmApplicantRpts]![cboPosition])

as your where statement etc...

Hope this makes sense.

Mark.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 19663485
If you want to restrict to a SPECIFIC name AND a SPECIFIC Refereal type, then remove the LIKE '*' phrases from those two parts of the WHERE clause:

SELECT [LastName] & ", " & [FirstName] & " " & [MI] AS Name, tblApplicntDet.Date, tblApplicnt.ApplicntId, tblApplicntDet.Position, tblApplicnt.County, tblApplicnt.Referral, tblApplicnt.ApplicntId
FROM tblApplicnt INNER JOIN tblApplicntDet ON tblApplicnt.ApplicntId = tblApplicntDet.ApplicntId
WHERE
   (
    (
     (tblApplicntDet.Date) Between
       ([Forms]![frmApplicantRpts]![cboFm]) And ([Forms]![frmApplicantRpts]![cboTo]))
AND (
     (tblApplicnt.ApplicntId)=[Forms]![frmApplicantRpts]![cboName] )
AND (
     (tblApplicntDet.Position)=[Forms]![frmApplicantRpts]![cboPosition])
AND (
     (tblApplicnt.County)=[Forms]![frmApplicantRpts]![cboCounty])
AND (
     (tblApplicnt.Referral)=[Forms]![frmApplicantRpts]![cboReferralType] ));

AW
0
 

Author Comment

by:BobRosas
ID: 19663550
Thank you both again.  
AW, It sort of works if all the fields are filled out, but if I leave anything blank the whole report is blank.

Mark,
You might be on the right track.  I was just looking for something easier than a bunch of SQL statments based on how the fields are filled out.  With 6 different fields I have a lot of possible combinations.

If you think of any other possible solutions I'd really appreciate it.
Thanks
0
 
LVL 10

Accepted Solution

by:
OnALearningCurve earned 260 total points
ID: 19663769
You could build the SQL dynamicaly based on the fields.

you should be able to do this by setting variables based on the fields i.e.

SQLSTART = "SELECT [LastName] & ", " & [FirstName] & " " & [MI] AS Name, tblApplicntDet.Date, tblApplicnt.ApplicntId, tblApplicntDet.Position, tblApplicnt.County, tblApplicnt.Referral, tblApplicnt.ApplicntId
FROM tblApplicnt INNER JOIN tblApplicntDet ON tblApplicnt.ApplicntId = tblApplicntDet.ApplicntId
WHERE "

if Field1 is null then
  SQL1 = ""
else
   SQL1 = " AND ( (tblApplicntDet.Position)=[Forms]![frmApplicantRpts]![FIELD1] )"
end if

if Field2 is null then
  SQL2 = ""
else
   SQL2 = " AND ( (tblApplicntDet.Position)=[Forms]![frmApplicantRpts]![FIELD2] )"
end if

then at the end create your SQL string by using SQLSTRING = SQLSTART & SQL1 & SQL2 ....

this could be tricky but may be better than having lost of very similar long SQL statments in your code.

Mark.
0
 
LVL 44

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 260 total points
ID: 19664263
If a field is left blank, do you want to not use that field at all in the where clause?  If that is the case, then use the approach suggested by OnALearningCurve, and only include those fields in the where clause that actually have values provided.

Actually, OnALearning cureve is close, but as written, that code will throw an error.  It should be like this:

dim strWhere as String
Dim strSQL as String

strWhere = ""

SQLSTART = "SELECT [LastName] & ", " & [FirstName] & " " & [MI] AS Name, tblApplicntDet.Date, tblApplicnt.ApplicntId, tblApplicntDet.Position, tblApplicnt.County, tblApplicnt.Referral, tblApplicnt.ApplicntId
FROM tblApplicnt INNER JOIN tblApplicntDet ON tblApplicnt.ApplicntId = tblApplicntDet.ApplicntId "

if not (Field1 is null) then
   strWhere = "Where ( (tblApplicntDet.Position)=[Forms]![frmApplicantRpts]![FIELD1] )"
end if

if Not Field2 is null  then
  If strWhere = "" then
      strWhere = "Where ( (tblApplicntDet.Position)=[Forms]![frmApplicantRpts]![FIELD2] )"
  Else
      strWhere = strWhere & " AND ( (tblApplicntDet.Position)=[Forms]![frmApplicantRpts]![FIELD2] )"
   End If
End If

and repeat that PATTERN for all of the fields that you are using in the Query.

then the final SQL becomes

strSQL = SQLStart & strWhere

and you finally use strSQL as the SQL for the query.

AW
0
 

Author Comment

by:BobRosas
ID: 19685910
Thank you both for your help.  I decided to split the points since you were both very helpful but I ended up going a little different direction.  I used the following as my source for the report and any field value that was left null I filled with an * before running the report and I get exactly the results I want.
Thank you again!

SELECT [LastName] & ", " & [FirstName] & " " & [MI] AS Name, tblApplicntDet.Date, tblApplicntDet.Position, tblApplicnt.County, tblApplicnt.Referral, tblApplicnt.ApplicntId
FROM tblApplicnt INNER JOIN tblApplicntDet ON tblApplicnt.ApplicntId = tblApplicntDet.ApplicntId
WHERE (((tblApplicntDet.Date) Between ([Forms]![frmApplicantRpts]![cboFm]) And ([Forms]![frmApplicantRpts]![cboTo])) AND ((tblApplicntDet.Position) Like [Forms]![frmApplicantRpts]![cboPosition]) AND ((tblApplicnt.County) Like [Forms]![frmApplicantRpts]![cboCounty]) AND ((tblApplicnt.Referral) Like [Forms]![frmApplicantRpts]![cboReferralType]) AND ((tblApplicnt.ApplicntId) Like [Forms]![frmApplicantRpts]![cboName]));
0
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 19686094
Hope I helped anyway.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question