Suppressing Fields in Query

[netrusher]

I have four fields in a Table. They are all four date fields. I want to do a query and see information in all four fields, but I only want to see the information if it is before a certain date.
So far I have tried this:

<Date().

This works but it shows all four fields even if it is after this date. How can I suppress the fields to appear blank if the date is not before today's date?

 

[dhookom]

Could you provide some sample records and desired display? Your question is somewhat vague. We don't know if you want to remove entire records or just values of dates in fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[netrusher]

Below is the SQL of the Query. I do not want to delete records, I just don't want the date values to now show up in their fields if their dates are not before today's date. Thanks a bunch!


SELECT [1-StationVerifyTbl].WorkerNameFirst, [1-StationVerifyTbl].WorkerNameLast, [1-StationVerifyTbl].Locations, [1-StationVerifyTbl].ReVerifyDateDue, [1-StationVerifyTbl].StaUpReverifyDateDue, [1-StationVerifyTbl].StaBackReverifyDateDue, [1-StationVerifyTbl].OtherStaSchedDate
FROM [1-StationVerifyTbl]
WHERE ((([1-StationVerifyTbl].ReVerifyDateDue)<Date())) OR ((([1-StationVerifyTbl].StaUpReverifyDateDue)<Date())) OR ((([1-StationVerifyTbl].StaBackReverifyDateDue)<Date())) OR ((([1-StationVerifyTbl].OtherStaSchedDate)<Date()));

 

[dhookom]

What's wrong with typing in a few records and the desired display? I would never expect that you would want to delete records.

Can you respond to:[red]"We don't know if you want to remove entire records or just values of dates in fields. "[/red] In other words, do you want to see all the records but see blanks where some field values are earlier than today?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Take a look at the IIf function:
SELECT WorkerNameFirst, WorkerNameLast, Locations
, IIf(ReVerifyDateDue<Date,ReVerifyDateDue,"") AS ReVerify
, IIf(StaUpReverifyDateDue<Date,StaUpReverifyDateDue,"") AS StaUpReverify
, IIf(StaBackReverifyDateDue<Date,StaBackReverifyDateDue,"") AS StaBackReverify
, IIf(OtherStaSchedDate<Date,OtherStaSchedDate,"") AS OtherStaSched
FROM [1-StationVerifyTbl]
WHERE ReVerifyDateDue<Date OR StaUpReverifyDateDue<Date OR StaBackReverifyDateDue<Date OR OtherStaSchedDate<Date;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

In each of PHV's calls to the Date() function, I believe the ()s are mandatory in SQL (not necessary in code). Also, I would use Null rather than "":
[blue]IIf(ReVerifyDateDue<Date(),ReVerifyDateDue,Null) AS ReVerify[/blue]

This is still some what of a WAG.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]