Solved

Report Qry Form

Posted on 2011-02-27
6
328 Views
Last Modified: 2012-05-11
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)
0
Comment
Question by:pdvsa
  • 4
  • 2
6 Comments
 

Author Comment

by:pdvsa
ID: 34993293
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
 
LVL 84
ID: 34993616
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
 

Author Comment

by:pdvsa
ID: 34993658
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 34996490
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
 

Author Comment

by:pdvsa
ID: 34996949
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
 

Author Comment

by:pdvsa
ID: 34997470
"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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question