Creating a field that is one of four answers depending on other values in each query

  • Thread starter Thread starter Mike H
  • Start date Start date
M

Mike H

Hello again...

Thanks to everyone for being so helpful. I have been able to get the
date functions to do what I needed, however, now I have a new issue.
Here's the situation:

Depending on how the data queried is calculated (for each recordset),
I want the last field to display one of four options. The logic for
this would be something like "If date1 > today's date then option 1;
if date1 > date2 then option 2; if date 1 < date2 then option 3; if
date1 = null then option4"

Problem is, I don't know how to code that in Access so that field
figures this out. I'm assuming a nested If/Else statement but I'm not
sure how to implement that.

This way, when someone runs the report, it will list one of the four
options and they will know what action to take from the option listed.

---

Secondly, is there a way to add a drop down menu as an option when
running a report? Say you had 100 different Clients. You want to be
able to run a report listing all of them and their various services,
but you also want to be able to run a report that justs lists the
services for one client. Ideally this would be a drop-down box.
Is there an easy way to do that?

Thank you all!!

-Mike
 
Depending on how the data queried is calculated (for each recordset),
I want the last field to display one of four options. The logic for
this would be something like "If date1 > today's date then option 1;
if date1 > date2 then option 2; if date 1 < date2 then option 3; if
date1 = null then option4"

Problem is, I don't know how to code that in Access so that field
figures this out. I'm assuming a nested If/Else statement but I'm not
sure how to implement that.
Click to expand...

The Choose() function is perfect for this requirement. It takes
arguments in pairs, and goes through the pairs left to right; the
first time it encounters a TRUE expression as the first member of a
pair, it returns the second. Thus

Choose([Date1] > Date(), 1, [Date1] > [Date2], 2, [Date1] < [Date2],
3, [Date1] IS NULL, 4, TRUE, NULL)

The fifth expression will return a NULL result if none of the terms
apply (for instance, Date1 and Date2 are equal and are less than or
equal to today's date).
 
Do you know how to add ALL to a combo box? If not:, there are many
examples on the 'net, including:

http://www.tek-tips.com/gfaqs.cfm/lev2/4/lev3/27/pid/701/fid/2330

Once you have that, you can set the report's source to one of two
queries or get fancy and edit the query in code.

Sub SetRptRecordsource()
DoCmd.OpenReport "rpt1", acViewDesign
If Forms("frm1")("cbo1").Column(0) = "ALL" Then
Reports("rpt1").RecordSource = "qry2"
Else
Reports("rpt1").RecordSource = "qry2"
End If
DoCmd.Close acReport, "rpt1"
End Sub

Peter
 
Back
Top