Alternate key if no results 2

[jv6886]

Say I have a table that looks something like the following:

State     Value
-----     -----
 FL         1
 FL         5
 FL         9
 AR         2
 AR         5
 MI         7
 MI         8
 XX         1
 XX         5

I use an SQL query to retrieve the records for a given state. If the state requested turns up no records, I want to use the alternate results from 'XX'.

Does anyone know of any way to do this using a single SQL statement?

Thank you!

 

[PaulBricker]

No I don't think there is. The reason being that the result set has to be evaluated (in this case to get the RecordCount) and that isn't the responsibility of a Select statement. If you need help with doing it with more than one Select statement let us know.

Paul

 

[dhookom]

You might need either a subquery or DCount(). The subquery solution is
SELECT tblStateVals.*
FROM tblStateVals
WHERE tblStateVals.State=IIf((Select Count(*) FROM tblStateVals WHERE State = [Enter State])=0,"XX",[Enter State]);

Duane
MS Access MVP

 

[jv6886]

Works perfectly dhookom. Thanks a million!

wcprog