Using NZ but isn't working...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Access 2003. I have a database (may be pre-Access 2k3) that has a
table, several queries (Delete, Update, and Regular), a Parameter form and
Report.

The table has 3 columns, Event, When, Mailbox.

The Parameter Form lets the user select which items they want reported based
on the Event, between start and end dates, and the option to specify a
mailbox or leave it blank so all are returned.

The Events all have the event Number and a date/time associated with it in
the table. Only Busy, RNA, OpReq, FindMe, NMA, and NMR have mailbox
information associated with them in the table. PTUI, CMC, DCO, DCI, and TTS
never have any mailbox information associated with the event in the table.

My regular query is listed below to produce information for the report.

SELECT [OPHist Local].Event, [OPHist Local].When, [OPHist Local].Mailbox
FROM [OPHist Local]
WHERE ((([OPHist Local].Event)=[Forms]![OpHist Parameters]![PTUI] Or
([OPHist Local].Event)=[Forms]![OpHist Parameters]![CMC] Or ([OPHist
Local].Event)=[Forms]![OpHist Parameters]![DCO] Or ([OPHist
Local].Event)=[Forms]![OpHist Parameters]![DCI] Or ([OPHist
Local].Event)=[Forms]![OpHist Parameters]![Busy] Or ([OPHist
Local].Event)=[Forms]![OpHist Parameters]![RNA] Or ([OPHist
Local].Event)=[Forms]![OpHist Parameters]![OpReq] Or ([OPHist
Local].Event)=[Forms]![OpHist Parameters]![FindMe] Or ([OPHist
Local].Event)=[Forms]![OpHist Parameters]![NMA] Or ([OPHist
Local].Event)=[Forms]![OpHist Parameters]![NMR] Or ([OPHist
Local].Event)=[Forms]![OpHist Parameters]![TTS]) AND (([OPHist Local].When)
Between [Forms]![OpHist Parameters]![startdate] And [Forms]![OpHist
Parameters]![endDate]) AND (([OPHist Local].Mailbox) Like NZ([Forms]![OPHist
Parameters]![Mailbox],"*")))
ORDER BY [OPHist Local].When;

Mailbox in the Table is DataType: Text, Required: No, Allow Zero Length:
Yes, Indexed: No.

When the query is run using all of the Event parameters and no mailbox
entered, none of the PTUI, CMC, DCO, DCI, and TTS data is ever shown. The
data is not in the query nor the report. The Busy, RNA, OpReq, FindMe, NMA,
and NMR is shown. Removing the Like NZ statement lets all of the data be
shown, but then the ability to look for data for a specific mailbox is not
available.

Any ideas what I am doing wrong and how to fix this?
 
Back
Top