Help needed with Query and form

[beatleman]

Great forum guys! Just had to join it

I've been doing Access for just under 2 weeks now, so far I'm doing ok, but I've just run into 2 problems. I hope someone can help me on them.

The first problem is on a query. I have a Column called "Type" in my query and the choices under Type could be RECEIPT, REJECT, BOTH

(BTW: BOTH should show all records RECEIPT and REJECT)

By using case statements as shown below, i can easily return records for RECEIPT and REJECT, however, i cannot get BOTH to work

The criteria i have set for TYPE in my query is,

[forms]![frm_Vendor]![opta] OR [forms]![frm_Vendor]![opta] Is Null

Case 1
opta.Value = "RECEIPT"
stDocName = "rep_search"
DoCmd.OpenReport stDocName, acPreview

Case 2
opta.Value = "REJECT"
stDocName = "rep_search"
DoCmd.OpenReport stDocName, acPreview
Case 3
opta.Value = " "
stDocName = "rep_search"
DoCmd.OpenReport stDocName, acPreview


Case 3 does not work. with " " . So what do i do here to show Both records?

My second problem is with the reports opened with the above Case code. The search routine (apart from Both) work fine. It finds the records i ask of it and it opens a report to show them, however, there are instances where no records are found, and the report still opens!!. In my search form, what code can i use to test my query for "null" data before
it even gets a chance to open the report?

Thanks in advance for any help.

 

[cah1982]

rarther then " " as this will search for a blank space try "*" as this will bring every thing or "RECEIPT" & "REJECT"

 

[beatleman]

re:

Neither of the above work my friend, even though i understand your logic.. its weird..!

 

[boblarson]

In my search form, what code can i use to test my query for "null" data before
it even gets a chance to open the report?

In the Report's NO DATA event you can put:

MsgBox "There is no data available for this report", VbOKOnly, "No Data"
Cancel = True

You will also have to have an error handler in the code behind the button, or whichever method, you use to open the report. It will have to trap for error 2501, which is something like "Open was canceled."

 

[namliam]

Opta.value = Null
Will that work?

If not.... simply make:
Opta.value = "Both"
and add in your query:
OR [forms]![frm_Vendor]![opta] ="Both"

Presto report done

 

[beatleman]

Opta.value = Null
Will that work?

If not.... simply make:
Opta.value = "Both"
and add in your query:
OR [forms]![frm_Vendor]![opta] ="Both"

Presto report done

Hi mate, Null did not work and using "Both" did not work either. How does opta know that it needs to return RECEIPTS and REJECTS in the above format?

The quickest solution (after i thought about it) was to create a new query and report for "BOTH" and it works fine now

The 2501 trap code mentioned above also fixed my close report problem.
Many thanks to all that replied to this post!