With OR option in querry, when ever run asking for input of Variable

[deepakg27]

Hi guys,



I am having a query where BillNumber is passed from either a form or Report to a query to generate Bill (in report format). I have set up the query in which Bill Number is either passed from Form (text box input) or through report (from number of bills user can click of desired one). Both functions work fine, But whenever I am trying to pass the argument through Form (textbox), it ask me for the input of report, also pressing ENTER (with blank input) bypasses the problem but on first place it should not happen. The screen is quiet annoying. For your reference query is given below:

SELECT tblSale.SaleDate, tblSale.Challan, tblSale.BillNumber, tblSale.Transport, tblSale.GRNumber, tblParties.PartyName, tblParties.Address, tblParties.City, tblParties.State, tblParties.GSTNo, tblParties.StateCode, tblSale.InvoiceAmt, tblSale.InvCGST, tblSale.InvSGST, tblSale.InvIGST, tblSale.FinalAmt, tblSale.Freight, tblSale.ReverseCharges, tblSale.RoundingOff, tblSale.GrandTotal, tblSale.ReverseCharge, tblSale.InvoiceType, tblSale.EComm, tblSale.Cess, tblParties_1.PartyName, tblParties_1.Address, tblParties_1.City, tblParties_1.State, tblParties_1.GSTNo, tblParties_1.StateCode
FROM tblParties INNER JOIN (tblSale INNER JOIN tblParties AS tblParties_1 ON tblSale.ShipToID_FK = tblParties_1.BuyerID_PK) ON tblParties.BuyerID_PK = tblSale.BuyerID_FK
WHERE (((tblSale.BillNumber)=[Forms]![PrintBill]![BillNum] Or (tblSale.BillNumber)=[Reports]![rptBills1Criteria]![BillNumber]));

I can always create a separate query, But want to fix this one & learn my mistake.

Thanks for your help.
Regards
Deepak Gupta

[pbaldy]

Since you're opening a report you could take the criteria out of the query and use this method:

http://www.baldyweb.com/wherecondition.htm

[Minty]

The report should really use the calling form as the controlling element, not something in the report itself.
Or use a where condition as Paul has suggested.

[deepakg27]

Dear Minty,

I have used calling from form only (It worked perfect). Then added function to search records and then do the calling through report as Paul Suggested, did that and it worked perfectly. Now When I am calling through search fuction works fine no issues but when uses form with direct user input, query asks for report's function input WHY? when in query I have given criteria with OR function why ask for the input if one is met. That is what my problem is.

Hope I HAve been able to explain my problem.

Regards
Deepak Gupta

[Minty]

I think I understand your question.
Even though you have the OR the query will always ask for both parameters, it has to, to be able to evaluate the expression, regardless of it being needed or not.

[deepakg27]

yes that is my question why asking for the parameter for second option when in OR query.

Regards
Deepak Gupta

[Minty]

Because it has to. A queries parameters aren't optional.
The only way around it would be to use something like this in the criteria

IIf(IsNull([Reports]![rptBills1Criteria]![BillNumber]),"*",[Reports]![rptBills1Criteria]![BillNumber])

[deepakg27]

Not Working, first asks for the parameter and then gives error Equation too long, closing all macros.

[pbaldy]

If that's intended to test for the object being open, testing for Null won't work. Perhaps:

http://www.theaccessweb.com/forms/frm0022.htm

[Minty]

If that's intended to test for the object being open, testing for Null won't work. Perhaps:

http://www.theaccessweb.com/forms/frm0022.htm

Doh, no of course it won't.

@OP - you can't do what you want without some VBA trickery as explained in the linked item.
In which case you may as well just run one of two queries based on the forms being actually loaded anyway.

[deepakg27]

Hi guys,

Thanks for all the help. Could find out how to rectify the issue. I have worked out a way around but not very convincing one.

Thanks anyway for all your help and guidance.
Regards
Deepak gupta