Solved

Report Qry Form

Posted on 2011-02-27
6
332 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

738 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