Report Qry Form

Experts,

I have a report with a qry form: frmDateRange_LCIssued
I enter date ranges in this form
I want to add a criteria [ExpiredYN] with a combo box drop down of Yes or No on  frmDateRange_LCIssued.  (I can do this though)

What I need to do is modify the SQL that if user chooses [ExpiredYN] = No (or could be False) from frmDateRange_LCIssued then show all the records that have not expired.   This field [ExpiredYN] is a combo box, value list in the table with row source of "Yes";"No" and allow Zero Length String as No.

thank you (any questions please let me know)
pdvsaProject financeAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Yes, my comment would show the Inputbox.

If you want to use your Filter form to allow the user to manage this, the best way to do this is to remove ALL the Prompts from the query, and in fact to remove the WHERE clauses from the query. Then, you use the OpenReport method to build a valid WHERE argument. This is, by far, the most robust method to use, and provides you with unlimited possibilities.

If you'd prefer not to do that, and instead wish to "bind" the query to your new filter form, then you'd make changes like this:

WHERE (((tblEndUser.[End User]) Like "*" & Forms!YourFilterForm.YourCoNameTextbox & "*") AND ((tblCountry.Country) Like "*" & Forms!YourFilterForm.YourCountryControl & "*") AND [ExpiredYN]=" & Forms!YourFilterForm.YourExpiredCheckbox & ");"

 
0
 
pdvsaProject financeAuthor Commented:
here is the SQL: (I dont know how to make it easier to read)
SELECT
tblLetterOfCredit.DateOfIssueSB,
tblLetterOfCredit.BankGTIssueDate,
tblLetterOfCredit.Amount,
Projects.ID,
tblEndUser.[End User],
tblLetterOfCredit.LetterOfCreditID, tblCountry.Country, tblCurrencyExchange.CurrencyID, tblCurrencyExchange.Currency, tblLetterOfCredit.EndUserID, tblCurrencyExchange.ExchangeRate, Projects.contractingEntity, tblLetterOfCredit.AdvisingBank, tblLetterOfCredit.LocalBankName, tblLetterOfCredit.lcno, tblLetterOfCredit.lctype, Projects.[project name], tblBanks.BankName, tblLetterOfCredit.Comments, tblLetterOfCredit.InitialExpireDate, tblLetterOfCredit.FinalMaturity, tblLetterOfCredit.ExpiredYN,

IIf(IsNull([tblLetterOfCredit].[DateOfIssueSB]),"Not Issued (No Issue Date Entered)","Issued LC's") AS MyGroup FROM tblCurrencyExchange RIGHT JOIN (Projects RIGHT JOIN (((tblLetterOfCredit LEFT JOIN tblCountry ON tblLetterOfCredit.Country = tblCountry.CntryID) LEFT JOIN tblBanks ON tblLetterOfCredit.BankID = tblBanks.BankID)

LEFT JOIN tblEndUser ON tblLetterOfCredit.EndUserID = tblEndUser.EndUserID) ON Projects.ID = tblLetterOfCredit.ProjectID) ON tblCurrencyExchange.CurrencyID = tblLetterOfCredit.Currency

WHERE (((tblEndUser.[End User]) Like "*" & [Enter Part of Co Name or Hit Enter to See All] & "*") AND ((tblCountry.Country) Like "*" & [COUNTRY? Hit Enter to See All] & "*"));
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
LEFT JOIN tblEndUser ON tblLetterOfCredit.EndUserID = tblEndUser.EndUserID) ON Projects.ID = tblLetterOfCredit.ProjectID) ON tblCurrencyExchange.CurrencyID = tblLetterOfCredit.Currency

WHERE (((tblEndUser.[End User]) Like "*" & [Enter Part of Co Name or Hit Enter to See All] & "*") AND ((tblCountry.Country) Like "*" & [COUNTRY? Hit Enter to See All] & "*") AND [ExpiredYN]=[Expired? Type Yes to show all Expired]);

However, once you begin to work with multiple criteria as you're doing now, you are much better off building a Form that allows the user to enter their choices. This provides you a lot more flexibility in determining the syntax of the WHERE clause, and is a much cleaner interface. Users get tired of constantly entering values at those Prompts (at least the ones I deal with do).
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
pdvsaProject financeAuthor Commented:
LSM:
<However, once you begin to work with multiple criteria as you're doing now, you are much better off building a Form that allows the user to enter their choices.
I am building this form now.  I think your answer is with the prompt correct?  How could I move away from the prompt and use the form?  The name of the form is frmDateRange_LCIssued

let me know if I have not  explained correct...thanks for the help.
0
 
pdvsaProject financeAuthor Commented:
Lsm: thanks for the advice.   i have to ask your thoughts about a Yes No combo on the filter form.  I think i need to have a handler if the user selects "NO" in the filter form.  If there are records that are null i will need to capture the nulls too for when thr user selects "NO".  I think i might drag a copy of the [ExpiredYN] and type criteria of <>False?  

Thank you
0
 
pdvsaProject financeAuthor Commented:
"I think i might drag a copy of the [ExpiredYN] and type criteria of <>False?  "
==>Drag a copy of the field in the qry design.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.