Sql Help!!!

[Mickster]

Hi there,

I currently am trying to generate a report. I have a bunch of option buttons on my form. What I am tryign to do is generate a list of each option selected.
So a list of how many times each option is selected. Sicne each option has its own field in the table i'm having trouble executing a if statment. This what i Have so far and i keep getting a syntax error:

SELECT complaints.[comp num], complaints.[last name], complaints.[first name], complaints.services, complaints.programs, complaints.treatment,
FROM complaints
WHERE
If [Forms]![ReportConcernForm]![TypeofConcern] = "services" Then
complaints.services = "True"
Else if [Forms]![ReportConcernForm]![TypeofConcern] = "programs" Then
complaints.programs = "True"
Else if [Forms]![ReportConcernForm]![TypeofConcern] = "treatment" Then
complaints.treatment = "True";

So services, programs, treatment are the options (there are more but too amny to list), a user can select one or more on the form.

Thanks,

 

[Bodisathva]

I assume that you are trying to create this query in the query builder, in which case, you want to use the IIF() statement, not IF-THEN-ELSE, which you would use if you were actually building the SQL string in code.

 

[Mickster]

So just replace the If with the IFF and remove the "Then" ?? are the Else if ok?

 

[Bodisathva]

IIF(condition, true, false)

WHERE IIf( [Forms]![ReportConcernForm]![TypeofConcern] = "services" ,complaints.services = "True",1)...

etc. The '1' is a place holder because you already have the WHERE in the string so the last IIF should at least put in something, the preceeding IIFs you would make the false part ""

 

[Mickster]

Thanks for the help bodisathva, but i'm still getting and error. It says the query is too complex???

 

[KeithG]

You will need to change all your if statements to the format below.

iif(Forms!ReportConcernForm!TypeOfConcern="Service",complaints.services=True, complaints.services=False)

 

[Mickster]

Thanks for the help Keith, but I am still getting an error? Would a Case statement work at all?

 

[Bodisathva]

You will need to change all your if statements to the format below.

iif(Forms!ReportConcernForm!TypeOfConcern="Service",complaints.services=True, complaints.services=False)

That would apply if the criteria is always True/False but NOT if the object is to filter on these properties only if they are selected.

this brings about a few questions...

1. are these criteria ALWAYS evaluated or are they only evaluated when selected?
2. How many criteria are you adding?
3. Are you trying to do this only in the query builder or are you able to do this in VBA?

 

[The_Doc_Man]

CASE statements are a VBA construct.

The way some folks have done this is to do some VBA behind the scenes to build a query on-the-fly.

Like

stQry = "SELECT mumble FROM mumbletable WHERE "

Then execute an if-ladder in VBA

if not IsNull( box1 ) then stQry = stQry & "[Field1]=[Box1]"

You would also have to set flags to remember if you had stored anything so you could put an " AND " in between criteria. You would also have to remember if NONE of your criteria were set so that you knew you didn't really have a query to begin with.

 

[KeithG]

I actually just helped someone else who was needing a query built on the fly.

here is a link to the post, there should be some sample db's.

http://www.access-programmers.co.uk/forums/showthread.php?t=108701

 

[Mickster]

That would apply if the criteria is always True/False but NOT if the object is to filter on these properties only if they are selected.

this brings about a few questions...

1. are these criteria ALWAYS evaluated or are they only evaluated when selected?
2. How many criteria are you adding?
3. Are you trying to do this only in the query builder or are you able to do this in VBA?

What is happening is that I have a bunch of service names such as service treatment, programs etc.. these are all in a drop down box in my form. On the table they each have their own field with either yes or no. So the user can select which service they want to generate a report on and query will filter them out depending on what they want.

I'm doing this in a query builder