Solved

Report Qry Form

Posted on 2011-02-27
6
333 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 85
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 85

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

729 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