IF THEN ELSE in query? 3

[Asspin]

What the heck am I doing wrong here? Probably everything, but hey...
Basically I am trying to get the query to sort by the value of txtLocation if the value of it is anything besides 0.

SELECT
CASE WHEN ([Forms]![fReporting]![txtLocation]=0) THEN tData.sName, tData.sLocation
FROM tData
WHERE (((tData.bService)=-1) AND ((tData.bPending)=-1) AND ((tData.bRequirements)=-1))
ELSE tData.sName, tData.sLocation
FROM tData
WHERE (((tData.sLocation)=[Forms]![fReporting]![txtLocation]) AND ((tData.bService)=-1) AND ((tData.bPending)=-1) AND ((tData.bRequirements)=-1))
END
;

Dan

 

[lespaul]

access syntax for CASE statement is:

IIF(Logical expression, value if true, value if false)

but I'm not sure that what you are trying to do will work with the IIF statement....so depending on the value of txtLocation you want to change the where clause?

Leslie

Come join me at New Mexico Linux Fest!

 

[Asspin]

Yeah, thats what I am trying to do, as it sits right now, I have 2 queries for each report, I would like to just have one as it is getting a bit messy.

Dan

 

[Asspin]

Ok, this seems to work, but I can't figure what statement I need for the 'TRUE' part. I tried >0 but that doesn't seem to be working. My sLocation variables will be either 1, 2, 3, or 4.

SELECT tData.sName, tData.sLocation
FROM tData
WHERE (((tData.sLocation)=IIf([Forms]![fReporting]![txtLocation]=0,"",[Forms]![fReporting]![txtLocation]")) AND ((tData.bService)=-1) AND ((tData.bPending)=-1) AND ((tData.bRequirements)=-1));

Dan

 

[dhookom]

Asspin,
Please just give us some sample records and how you would like them sorted in the report. It looks like you might be confusing "sorting" and "filtering" but I could be wrong.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

I'm confused, your new SQL seems to do something different than the T-SQL like statement you started with.

What are you trying to do with your new SQL statement? You also seem to have an extra double quote in the false part of the IIF.

 

[Asspin]

Hopefully this looks semi-decent.
Basically I am needing to get a list of reps that match several things. The report needs to allow for selecting the location (which I have taken care of with a form) then the form gives them options of different reports. It needs to pull based on location and if all the Yes/No options match. I am not having problems selecting based on location, however, if I want all locations combined, I am running into issues.

Rep:	Super:	Loc:	ID:	Serv:	Cha:	Pend:	Req:	UK:	US:	VA:	S:	VB:
User1	Super1	1	13	Yes	Yes	Yes	Yes	No	No	No	No	No
User2	Super1	2	46	Yes	No	Yes	No	No	No	Yes	Yes	Yes
User3	Super2	1	M2	Yes	No	Yes	No	No	No	No	No	No
User4	Super1	2	5Z	Yes	No	No	No	No	No	Yes	No	Yes
User5	Super2	1	84	Yes	Yes	Yes	Yes	No	No	No	Yes	No

Dan

 

[Asspin]

Yeah the double quote was a typeo by me. The SQL seems to work. That's how I put it into the query editor, it's much cleaner than what I was trying to do. I am still trying to get it. Here is what it is currently...

Like before, everything is working, except for the 'TRUE' statement.

SELECT tData.sName
FROM tData
WHERE (((tData.sLocation)=IIf([Forms]![fReporting]![comLocation]=0,(tData.sLocation)="*",[Forms]![fReporting]![comLocation])) AND ((tData.bServiceClaims)=-1) AND ((tData.bPendingLabs)=-1) AND ((tData.bRequirementsQuoting)=-1));

Dan

 

[lameid]

If the location is text you coud use something like the following but it is numeric (your =0) test. You could run two different queries based on the value of the control or dynamically change your SQL string in code.

SELECT tData.sName, tData.sLocation
FROM tData
WHERE (((tData.sLocation) Like (IIf([Forms]![fReporting]![txtLocation]=0,"*",[Forms]![fReporting]![txtLocation]))) AND ((tData.bService)=-1) AND ((tData.bPending)=-1) AND ((tData.bRequirements)=-1));

 

[Asspin]

I have tried it written as you have it lameid, I get no results on the query. Annoying isn't it?

Dan

 

[lameid]

The data type of tData.sLocation is not text. What is it?

 

[Asspin]

The field is actually a normal text field. I can try changing it to something else if that helps. I think the database started off having names stored in that field, instead of location numbers.

Dan

 

[dhookom]

If all of the fields are in the report's record source, I would not place any dynamic criteria in the query. I find it much easier to use code to build a WHERE CONDITION to use in the DoCmd.OpenReport method.

Dim strWhere as String
Dim strReport as String
strWhere = "1=1 "
strReport = "rptMyReportName"
' assuming sLocation is a string
If Me.txtLocation <> "0" Then
   strWhere = strWhere & " AND sLocation = """ & _
      Me.txtLocation & """ "
End If
' add other ifs to add to strWhere
DoCmd.OpenReport strReport, acViewPreview, , strWhere

I have no idea what you mean by "if all the Yes/No options match".
If


Duane
Hook'D on Access
MS Access MVP

 

[Asspin]

Hmm, that looks like it might be a simpler option dhookom.
I will give that a shot, I should be able to generate it off one query then filter from there. Perfect!

Dan