Pass 2 different Search Options to same Report

K

kalesb

Registered User.
Local time
, 20:22
Joined
Oct 1, 2011
Messages
20
I am new to forum and Access 2010.

I have one Form (FormA) with 2 options:

From Radio Button, I open ReportA (for SDATE and EDate Searches)

ReportA uses following code:

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT ECP_T.ECP_N0, ECP_T.PRI, ECP_T.POC, ECP_T.E_TITLE, ECP_T.HH1N, ECP_T.UH1N, ECP_T.H1W, ECP_T.H1Y, ECP_T.H1Z, ECP_T.IPT, ECP_T.RFP_DTE, ECP_T.D_ECP_DUE_DTE, ECP_T.CHG_DESC_DTE, ECP_T.PMA_INPT_DTE, ECP_T.D_ECP_SUB_DTE, ECP_T.F_ECP_DTE, ECP_T.ECP_RMKS
FROM ECP_T
WHERE (((ECP_T.F_ECP_DTE) Between [Enter Start Date:] And [Enter End Date:]));


The other option off FormA lets user select a specific Fiscal Year. The Fiscal Year is linked to a specific SDATE and EDate in form.

After selecting Fiscal Year and clicking OK, ReportB is opened with the following SQL:

SELECT ECP_T.ECP_N0, ECP_T.PRI, ECP_T.POC, ECP_T.E_TITLE, ECP_T.HH1N, ECP_T.UH1N, ECP_T.H1W, ECP_T.H1Y, ECP_T.H1Z, ECP_T.IPT, ECP_T.RFP_DTE, ECP_T.D_ECP_DUE_DTE, ECP_T.CHG_DESC_DTE, ECP_T.PMA_INPT_DTE, ECP_T.D_ECP_SUB_DTE, ECP_T.F_ECP_DTE, ECP_T.ECP_RMKS
FROM ECP_T
WHERE (((ECP_T.F_ECP_DTE) Between [Forms]![Frm_ECP_CompletedAB]![SDate] And [Forms]![Frm_ECP_CompletedAB]![EDate]));

Can someone show me the codeing/solution so that I only use one Report to load the information into one Where cluase depending on the option selected?
 
You can use the IIF() function. E.g.
Code: 
Between IIF([Forms]![[COLOR=Red]FormA[/COLOR]]![[COLOR=Red]OptionControl[/COLOR]] = 1, [Enter Start Date:], [Forms]![Frm_ECP_CompletedAB]![SDate]) AND ...
Complete the rest. Also amend the bits in red

You will need to get rid of the parameters and Format your SDate and EDate controls with the appropriate date format.
 
Thanks for replying

I'm not a programmer and I have never used the IIF function.

Please review coding. I believe you were trying to show me how IIF function could be inserted into SQL where clause...

WHERE (((ECP_T.F_ECP_DTE) Between IIF [Forms]![Frm_ECP_CompletedAB]![SDate], "[Forms]![Frm_ECP_CompletedAB]![SDate]And [Forms]![Frm_ECP_CompletedAB]![EDate]))", "[Enter Start Date:] And [Enter End Date:]))" ;

Am I on track?? If so I will cut and paste into my Report
 
You need two IIF() functions, one for the start date part and the other (after the AND in my code) for the end date part.
 
I got:

Syntax error (comma) in query expression 'WHERE (((ECP_T.F_ECP_DTE) Between IIF ([Forms]![Frm_ECP_CompletedAB]![SDate], [Forms]![Frm_ECP_CompletedAB]![SDate]And [Forms]![Frm_ECP_CompletedAB]![EDate]))), [Enter Start Date:] And [Enter End Date:]));'



Not sure how to handle error since IIF function requires comma.....
 
Did you look at the link I provided? It explains how the IIF() function work, it's nothing to worry about. It's pretty simple.
 
Is this correct?


WHERE (((ECP_T.F_ECP_DTE) Between IIF ([Forms]![Frm_ECP_CompletedAB]![SDate], [Forms]![Frm_ECP_CompletedAB]![SDate], [Enter Start Date:]) AND IIF (([Forms]![Frm_ECP_CompletedAB]![EDate], [Forms]![Frm_ECP_CompletedAB]![EDate], [Enter Start Date:]);
 
Almost, but here:
Code: 
WHERE ECP_T.F_ECP_DTE BEWTEEN IIF([Forms]![Frm_ECP_CompletedAB]![SDate] [COLOR=Red]= 1[/COLOR], [Forms]![Frm_ECP_CompletedAB]![SDate], [Enter Start Date:]) AND IIF ([Forms]![Frm_ECP_CompletedAB]![EDate][COLOR=Red] = 1[/COLOR], [Forms]![Frm_ECP_CompletedAB]![EDate], [Enter Start Date:]);
The one means that when option 1 is selected in your option group then it should use SDate and EDate, else it will use the pop-up parameters. If the first value of your option group is not 1 then you need to change that number accordingly.
 
This time I got:

Syntax error (missing operator) in query expression 'WHERE ECP_T.F_ECP_DTE BEWTEEN IIF([Forms]![Frm_ECP_CompletedAB]![SDate] = 1, [Forms]![Frm_ECP_CompletedAB]![SDate], [Enter Start Date:]) AND IIF ([Forms]![Frm_ECP_CompletedAB]![EDate] = 1, [Forms]![Frm_ECP_CompletedAB]![EDate], [Enter End Date:]);'
 
Thanks again for taking the time and looking at this. Also what Time Zone are you in?

Code: 
SELECT ECP_T.ECP_N0, ECP_T.PRI, ECP_T.POC, ECP_T.E_TITLE, ECP_T.HH1N, ECP_T.UH1N, ECP_T.H1W, ECP_T.H1Y, ECP_T.H1Z, ECP_T.IPT, ECP_T.RFP_DTE, ECP_T.D_ECP_DUE_DTE, ECP_T.CHG_DESC_DTE, ECP_T.PMA_INPT_DTE, ECP_T.D_ECP_SUB_DTE, ECP_T.F_ECP_DTE, ECP_T.ECP_RMKS
FROM ECP_T
WHERE ECP_T.F_ECP_DTE BEWTEEN IIF([Forms]![Frm_ECP_CompletedAB]![SDate] = 1, [Forms]![Frm_ECP_CompletedAB]![SDate], [Enter Start Date:]) AND IIF ([Forms]![Frm_ECP_CompletedAB]![EDate] = 1, [Forms]![Frm_ECP_CompletedAB]![EDate], [Enter End Date:]);
 
GMT: UK - London :)

I don't see anything wrong with the SQL statement, but try this:
Code: 
SELECT ECP_N0, PRI, POC, E_TITLE, HH1N, UH1N, H1W, H1Y, H1Z, IPT, RFP_DTE, D_ECP_DUE_DTE, CHG_DESC_DTE, PMA_INPT_DTE, D_ECP_SUB_DTE, F_ECP_DTE, ECP_RMKS
FROM ECP_T
WHERE F_ECP_DTE BEWTEEN IIF([Forms]![Frm_ECP_CompletedAB]![SDate] = 1, [Forms]![Frm_ECP_CompletedAB]![SDate], [Enter Start Date:]) AND IIF([Forms]![Frm_ECP_CompletedAB]![EDate] = 1, [Forms]![Frm_ECP_CompletedAB]![EDate], [Enter End Date:]);
Paste this into the SQL view of a new query, go to Design View and switch to Datasheet View to test it.

Ensure that the form Frm_ECP_CompletedAB is open before running the query.
 
When I try saving the SQL, I get Syntax error (missing operator) in query expression.

Also can you tell me in IIF statement: Does = 1 mean TRUE?
 
oops... my typo there. Change BEWTWEEN to BETWEEN :)

Have a look at my post #8, it explains the idea behind =1.
 
Success!!!!
I should have caught the spelling also but apparently I can't spell.

I have several reports requiring this function and with your help instead of 10 reports to build and maintain, I now only have 5!!!!!

Thanks again.
Good Job!!!!!:):):):)
 
SQL statement works but I now have created another opportunity.:)

code provided with changes in form name only:

Code: 
[SIZE=3][FONT=Calibri]WHERE (((ECP_T.F_ECP_DTE) Between (IIf([Forms]![Frm_ECP_Date_Range_Search_Parameters]![SDate],[Forms]![Frm_ECP_Date_Range_Search_Parameters]![SDate],[Enter Start Date:])) And (IIf([Forms]![Frm_ECP_Date_Range_Search_Parameters]![EDate],[Forms]![Frm_ECP_Date_Range_Search_Parameters]![EDate],[Enter End Date:]))));[/FONT][/SIZE]
[\code]
 
Form using [SDate] and [EDate] provides Report with Search parameters.
I was hoping, if not using [SDate] and [EDate] values then user is prompted to Enter Start and End Date.
 
But that does not happen. Even if user provides [SDate] and [EDate] on Form, the user is still prompted again to Enter Start and End Date. I guess that exactly what the code states to do in Access. 
 
I thought I could avoid the additional prompt if I added another Form for Start and End Date but user prompted for Start and End Date (ref. [frm_Date_Range_Search]![StartDateR] and [frm_Date_Range_Search]![EndDateR]. See updated code below. 
 
[code]
WHERE (((ECP_T.F_ECP_DTE) Between (IIf([Forms]![Frm_ECP_Date_Range_Search_Parameters]![SDate],[Forms]![Frm_ECP_Date_Range_Search_Parameters]![SDate],[Forms]![frm_Date_Range_Search]![StartDateR])) And (IIf([Forms]![Frm_ECP_Date_Range_Search_Parameters]![EDate],[Forms]![Frm_ECP_Date_Range_Search_Parameters]![EDate],[Forms]![frm_Date_Range_Search]![EndDateR]))));
[\code] 
 
But there again the code is only as smart as the developer. Apparently, the developer in my case (me) is not very smart.:eek:
 
So my question is how avoid additional prompts? Could I use something like a ISNULL(RTRIM([Forms]![Frm_name]![Field_name])
for: SDATE, EDate, StartDateR, and EndDateR?
 
Or do you have a better solution?hohoho:D
 
I see your dilemma. The problem is the IIF() function evaluates both parts, i.e. the True and False parts even though only one condition is met.

I know you can build your SQL in code but I'm just thinking if there are other ways ;)
 
I haven't read the whole thread, so excuse me if I'm wrong, but I assume you are using the form Frm_ECP_Date_Range_Search_Parameters not only to input the criteria but to hold the command button to open the appropriate report?

In which case you could check for null values in the 2 date controls before opening the report.

Something like:

Code: 
If IsNull([DateField1] Then
   DateField1 = inputbox "Please enter start date"
End If

You could add additional checks to ensure that the person didn't just enter through the input box without entering anything. This would either leave it as null or possible set it to a zero length string.
 
vbaInet said:
I know you can build your SQL in code but I'm just thinking if there are other ways ;)
Click to expand...
So as promised, here's one way you could do it without needing to build your sql in code. Yes, it might be a little bit complicated for you but you asked for it :p:)
 

Attachments

You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom