pdvsa
asked on
Put WHERE criteria in click event i/o in the report's record source
I have rptDepartures with WHERE criteria saved in the report record source like so:
SELECT tblDepartures.*, tblEndUser.[End User], tblDepartures.Compliance_G OPS_YN, Projects.[Project Name], Projects.ContractAward
FROM (tblDepartures LEFT JOIN Projects ON tblDepartures.ProjectID = Projects.ID)
LEFT JOIN tblEndUser ON Projects.EndUserID = tblEndUser.EndUserID
WHERE (((tblEndUser.[End User]) Like [forms]![frmDepartures_fil ter].[txtC lient] & "*" Or (tblEndUser.[End User]) Is Null) AND ((tblDepartures.EntryDate) >=[forms]! [frmDepart ures_filte r]![txtDat eFrom] And (tblDepartures.EntryDate)< [forms]![f rmDepartur es_filter] ![txtDateT o]+1 And (tblDepartures.EntryDate) Is Not Null))
ORDER BY tblEndUser.[End User];
I know experts like to put the where conditions under the button's click event i/o in the report criteria. So, now I want to move this criteria away from the record source of the report and move the WHERE condition to a button's macro event on the filter form (frmDepartures_filter).
I have played around with this for too long. I cant do it. Maybe putting such criteria in the macro's where condition is not a good idea and should instead be put in the click event of the button on frmDepartures_filter.
any help is appreciated.
SELECT tblDepartures.*, tblEndUser.[End User], tblDepartures.Compliance_G
FROM (tblDepartures LEFT JOIN Projects ON tblDepartures.ProjectID = Projects.ID)
LEFT JOIN tblEndUser ON Projects.EndUserID = tblEndUser.EndUserID
WHERE (((tblEndUser.[End User]) Like [forms]![frmDepartures_fil
ORDER BY tblEndUser.[End User];
I know experts like to put the where conditions under the button's click event i/o in the report criteria. So, now I want to move this criteria away from the record source of the report and move the WHERE condition to a button's macro event on the filter form (frmDepartures_filter).
I have played around with this for too long. I cant do it. Maybe putting such criteria in the macro's where condition is not a good idea and should instead be put in the click event of the button on frmDepartures_filter.
any help is appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you MX. That's looks like the solution. I guess a macro might not be best suited for this. Sent from phone...
ASKER
Oopps. I meant to say fyed. Sorry.
If you want to prompt the user for report selection criteria, you can do what we do - call a form from the report, instead of the other way around.
You can open a form from the report's Open event. Open the form in Dialog mode so that the report waits for the form to be closed or hidden before it proceeds. That way you can collect criteria from the user and build a Where clause for the report. It also means that you can call the report directly - you don't need to call it from a form. And the selection form is reusable - it can be called from multiple reports if they need the same criteria.
I've posted examples of this technique on our free J Street Downloads page at http://www.JStreetTech.com/downloads.
See "Report Selection Techniques".
Cheers,
Armen Stein
You can open a form from the report's Open event. Open the form in Dialog mode so that the report waits for the form to be closed or hidden before it proceeds. That way you can collect criteria from the user and build a Where clause for the report. It also means that you can call the report directly - you don't need to call it from a form. And the selection form is reusable - it can be called from multiple reports if they need the same criteria.
I've posted examples of this technique on our free J Street Downloads page at http://www.JStreetTech.com/downloads.
See "Report Selection Techniques".
Cheers,
Armen Stein
ASKER
Fyed:
Why do you think I have this error?
If it matters, txtClient is an unbound field on the filter orm.
[EndUser] is a field in projects table and is text.
thank you. I am sure you are busy doing other stuff so whenever you can get back to me appreciate it.
Armen, thank you for the tip. I will add it to my laundry list of expert tips.
Why do you think I have this error?
If it matters, txtClient is an unbound field on the filter orm.
[EndUser] is a field in projects table and is text.
thank you. I am sure you are busy doing other stuff so whenever you can get back to me appreciate it.
Armen, thank you for the tip. I will add it to my laundry list of expert tips.
Because one of us ;-(
forgot to close the dates with #. Try:
strCriteria = "([End User] Like '" & me.[txtClient] & "*' Or [End User] Is Null) AND " _
& "[EntryDate]>=#" & me.[txtDateFrom] & "# And " _
& "[EntryDate]<# & me.[txtDateTo]+1 & "# And " _
& "[EntryDate] Is Not Null"
forgot to close the dates with #. Try:
strCriteria = "([End User] Like '" & me.[txtClient] & "*' Or [End User] Is Null) AND " _
& "[EntryDate]>=#" & me.[txtDateFrom] & "# And " _
& "[EntryDate]<# & me.[txtDateTo]+1 & "# And " _
& "[EntryDate] Is Not Null"
ASKER
That was it...but I think there was a " missing in line 3.
I might need to ask a follow up question. I need to add an "OR" statement to this. I will try and figure it out though.
thank you
I might need to ask a follow up question. I need to add an "OR" statement to this. I will try and figure it out though.
thank you
ASKER
cant do it. will ask a follow up in 5 min.
Then show the desired changes.
I understand you want to specify the criteria in the form, and clicking the button to run the report, filter for such criteria. Is this correct?