Solved

Report Qry Form

Posted on 2011-02-27
6
319 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now