If/Then in Query??

[David Graham]

Here's the deal, I need to create a query that will determine if one table
field is populated, and if it then base the results on that field. If it's
not populated then I need it to pull results based on the content of another
field. All results need to be grouped together in the same recordset. Is
this possible?

Specifically, here's an example:
I'm querying a table that holds information about kids in a school class.
One field in the table is "grade" and another is "birthday". The grade
field is a text field because if the child is not yet in grade 1, then the
field has to be populated with something like "toddler" or "perk". The
birthday field is a date/time field. I need to create a query that will
give me, for example, all kids that are in grade 3. However, if the grade
field is not populated with information, then I need the query to next look
at the birthday field to see if the kid's birthday falls within a preset
date range. All results should be presented in the same recordset so that a
report can be created.

I hope that makes sense. Any feedback is appreciated. Thanks.

 

[Jerry Whittle]

Check out the IIf function. For your needs you may need to nest an IIf inside
another IIf.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctiif.asp

 

[John Spencer]

Genericly, that would look like

SELECT *
FROM TABLE
WHERE Grade = "3" or
(Grade is Null and DOB Between #1/1/1998# and #12/31/1998#)

If you have some rule for setting the date range you might be able to tie
that to the grade number.

 

[Marshall Barton]

Here's the deal, I need to create a query that will determine if one table
field is populated, and if it then base the results on that field. If it's
not populated then I need it to pull results based on the content of another
field. All results need to be grouped together in the same recordset. Is
this possible?

Specifically, here's an example:
I'm querying a table that holds information about kids in a school class.
One field in the table is "grade" and another is "birthday". The grade
field is a text field because if the child is not yet in grade 1, then the
field has to be populated with something like "toddler" or "perk". The
birthday field is a date/time field. I need to create a query that will
give me, for example, all kids that are in grade 3. However, if the grade
field is not populated with information, then I need the query to next look
at the birthday field to see if the kid's birthday falls within a preset
date range. All results should be presented in the same recordset so that a
report can be created.

Try a calculated field similar to:

GradeOrBDate: IIf(Grade Is Null, IIf(Birthday Between
#1/1/99# And #12/31/2002#, Format(Birthday, "m/d/yy"),
Null), Grade)

 

[David Graham]

Thanks to everyone for the feedback I've received on this topic. Here's
what I found finally worked the best ...

I wasn't having much luck manually editing the SQL, but was able to
accomplish my goal by using the Design View in Access. I already had
criteria for the birthday range setup, so I lowered that one level in the
Criteria section. Then I added the grade field to the query and set it to
hard coded values like "3" or "4", etc. The trick was I had to add
"kids.grade is null and" in-front of the birthday range criteria statement
in order for it to properly look at the data and accomplish what I needed.

That was a very poor explanation of my solution, and I apologize for that.
At any rate, I was able to accomplish my goal based on the help I received
from this newsgroup. Thanks again.