MS Access Query Question

[ruthmarjot]

Hi there,

I'm trying to teach myself Access whilst creating a database and want to produce a report pulling certain records from multiple tables. I have created a query to pull out the records that I need on the report but was wondering if there is anyway that I can only show a field if another field has a certain value in it.

e.g. If the Status field has WPS in it then I want to show the "WPS Sent date" field, if it has Authorised in it then I want to show the "Auth to proceed date" field and if it has anything else I don't want to show either field.

Any ideas would be greatly appreciated.

Many Thanks
Ruth

 

[Kelanen]

Well approaching the problem from a logical point of view rather than a technical one, do you actually need to look back at the dates it reached different stages for any reason?

If not then just have a single field called "Next Action" or similar, and store all the dates there. The context to the date is already provided by the Status field.

The fudge solution is to maintain all the different date fields, and have an update query populate the "Current Action" date field based on the contents of the Status field. This would work, but is a little clunky.

I'm sure there is a more elegant solution if you are willing to code around it, but I will leave that advice to another more qualified.

Jonathan Challis
IT Manager

 

[TonyJollans]

Hi Ruth,

You want to look at the IIF (Immediate If) Function. You can embed them in your Query so that the Query returns the value(s) you want subject to your condition(s), or you can embed them in the Control Source(s) for Textbox(es) in your Report. You can also embed one within another. The syntax is ..

[blue][tt]IIF(Test,Value-if-True,Value-if-False)[/tt][/blue]

For the one you asked about, try something like this ..

[blue]Iif([Status]="WPS",[WPS Sent Date],Iif([Status]="Authorised",[Auth to proceed date],""))[/blue]

For more complicated conditions you might also want to look at the Switch Function, or possibly the Choose Function.

Finally, although many people here know Access, there are several Access-specific Fora where you will find more Access-specific knowledge. Amongst others, check out ..

Microsoft: Access Queries and Jet SQL
Microsoft: Access Reports
Microsoft: Access Forms
Microsoft: Access Other Topics

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[ruthmarjot]

Thanks Tony,

I used a combination of what you suggested and something from one of the other Forums and it works a treat now.

Once again, thank you very much!!