Conditional CLASS statement in Access / DRW SQL?

  • Thread starter Thread starter Clark
  • Start date Start date
C

Clark

I use the Access query generator to mostly generate SQL statements for use in
the FP DRW,

In a simple Database Results region, I am trying to bring up records showing
data from the MDName, City, and State fields except where someone has selected
"no" in a ShowCity field, in which situation I want to show the word
"Unavailable" instead of their city.

Bought a Learning SQL book, dug out the CLASS statement which looks to fill the
bill, tried it six ways 'till Sunday by modifying the SQL view in the Access
Query Builder and could never make it work.

After 2 or 3 hours of this, dug out my excellent Alison Dolan Access 2000
Development book and to my surprise, not a peep about conditional CLASS
statements in it.

So to the question: Can you use the CLASS statement in an Access query?

For the record, here is my failed SQL statement:

SELECT Results.MDName, CLASS When Results.City Is Not Null and When
Results.ShowCity = 'No' THEN 'Unavailable' ELSE Results.City END, Results.State
FROM Results
ORDER BY Results.MDName;

I've tried it with both Apostrophes and Quote marks, no luck either way.

Is something (probably) wrong with my approach? Is there a better way (I guess
there has to be :=)

Thanks

Clark
 
Use the number 0 with no quotation marks instead. A Yes/No column is a
boolean (bit) data type, which is numeric.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Ambiguity has a certain quality to it.
 
Hi Clark,

You need IIF (Immediate If) eg
SELECT Results.MDName, IIF(ShowCity = 0, 'Unavailable', City) AS TheCity,
Results.State
FROM Results
ORDER BY Results.MDName;
 
OK Thanks guys. I got the IIF statement to work, although I stuck with "no" as
the Yes/No choice instead of number "0" (which would not work for me). Here is
the SQL I wound up with that worked:

SELECT Results.MDName, IIf(showcity = "no","Unavailable",results.City) AS city,
Results.State, Results.Country
FROM Results
ORDER BY Results.MDName;

BTW, the yes/no entries were generated from a FrontPage Form when I created a
database to save the form entry. It does seem to act as a text "No" as opposed
to a number 0.

Also, am I correct in thinking that the conditional CLASS statement may not be
supported in Access?

Thanks again for the quick responses.

Clark
 
Clark,
There's no CLASS in Access, it's not really much help reading books on SQL
as every DB has different statements, there's no such thing as SQL that will
work everywhere. Would be nice if there was :-)

As long as the "no" works stick with it.
 
Do you mean 'CASE' instead of 'CLASS'? I've not seen 'CLASS' in any databse
platform I've worked in - mssql, oracle, mysql, postgres, access.

What's the name of the book you bought?

Bob Lehmann
 
I tested this and it works.

SELECT
ID, mdname,
IIf(showcity =no,"Unavailable", City) AS 'city',
state, Country
FROM results

Troubleshooting points points to remember for the future. For your example.

1. Your table field "showcity" is a logical field which Access calls
yes/no field. It is not a text field with "yes" or "no" values. For
evaluation purpose in SQL statements you need to use:

yes/no without quotes or
true/false without quotes or
-1/0 w/o quotes
If you use quotes or anything else other than the binary options, you
will get unpredictable results like you did.

When you are viewing a record in Access, the yes/no field will display
as check off box, checked if the value is true/yes and unchecked if the
value is no/false.

When you are grabbing records through scripting and ADO, the value
returned for that field will be in the form of true/false. You need to
be careful if you assign this value to a variable and use it in
evaluations. Make sure you cast it in the correct variant type you want
to use, e.g., either logical (use true/false in evaluations) or string
(use "true"/"false" in evaluations)


2. Because "city" is already table field, you need to enclose the alias
for the AS name in quotes or you will get an error if you want to use
the same name, or use a different name.

3. As an alternative to the conditional if in the SQL statement, you can
return the data straight to the recordset and do the evaluation on that
field on the script side using a function.

For your example, this will work:

function DisplayCity(logShowcity,strCity)

If logShowcity=True Then
DisplayCity=strCity
Else
DisplayCity="unavailable"
End If
End function

4. I don't know the purpose of your table design. But if you are using
the showfield to track when the city field is "empty" you don't really
need that field. You can run the evaluation directly on the field "city"
as long as you understand the possible values for "empty" depending
whether you do the evaluation in the SQL or the script side. If the
propse is for other reasons, this comment is not relevant.

Regards
...PC
 
Talk about a Senior Moment(s)!! Yes, yes I did mean Case. Jeez they say this
gets worse as you get older too!!

The SQL book I bought was just a handy vanilla one at Barnes and Noble --
"Learning SQL", an O'Reilly book by Alan Beaulieu. Although I suspect that now
you may be less interested in that, knowing there is no CLASS to track down.

Regards,

Clark
 
Thank you for the very helpful discourse.

Clark

I tested this and it works.

SELECT
ID, mdname,
IIf(showcity =no,"Unavailable", City) AS 'city',
state, Country
FROM results

Troubleshooting points points to remember for the future. For your example.

1. Your table field "showcity" is a logical field which Access calls
yes/no field. It is not a text field with "yes" or "no" values. For
evaluation purpose in SQL statements you need to use:

yes/no without quotes or
true/false without quotes or
-1/0 w/o quotes
If you use quotes or anything else other than the binary options, you
will get unpredictable results like you did.

When you are viewing a record in Access, the yes/no field will display
as check off box, checked if the value is true/yes and unchecked if the
value is no/false.

When you are grabbing records through scripting and ADO, the value
returned for that field will be in the form of true/false. You need to
be careful if you assign this value to a variable and use it in
evaluations. Make sure you cast it in the correct variant type you want
to use, e.g., either logical (use true/false in evaluations) or string
(use "true"/"false" in evaluations)


2. Because "city" is already table field, you need to enclose the alias
for the AS name in quotes or you will get an error if you want to use
the same name, or use a different name.

3. As an alternative to the conditional if in the SQL statement, you can
return the data straight to the recordset and do the evaluation on that
field on the script side using a function.

For your example, this will work:

function DisplayCity(logShowcity,strCity)

If logShowcity=True Then
DisplayCity=strCity
Else
DisplayCity="unavailable"
End If
End function

4. I don't know the purpose of your table design. But if you are using
the showfield to track when the city field is "empty" you don't really
need that field. You can run the evaluation directly on the field "city"
as long as you understand the possible values for "empty" depending
whether you do the evaluation in the SQL or the script side. If the
propse is for other reasons, this comment is not relevant.

Regards
..PC
Click to expand...
 
Back
Top