Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Create Form for selection criteria for Report 2
- Thread starter was1
- Start date
-
1
- #2
Phil4tektips
Technical User
Yes this can be done and I use it in my database I'm currently developing. I was given some Code by FancyPrairie from this site.
It is not really important to know how the code works, or essential that you understand. You just need to be able to set up the form and report correctly and source the code when opening the report.
If you would like, I will talk you through how to do it.
Would you like me to?
~Phil4tektips~
Grant us peace in our days work!
It is not really important to know how the code works, or essential that you understand. You just need to be able to set up the form and report correctly and source the code when opening the report.
If you would like, I will talk you through how to do it.
Would you like me to?
~Phil4tektips~
Grant us peace in our days work!
-
1
- #3
FancyPrairie
Programmer
Thanks Phil4tektips for mentioning my code. Here's the link to the FAQ faq181-5497 It builds and returns the where clause for you and works for single and multi-select list boxes, combo boxes, text boxes, date ranges, option groups, and check boxes.
- Thread starter
- #4
The code referred to is very good but I'm not sure you want to go to that effort - especially if you don't have any VB knowledge.
This may be an easier way to go.
Create your form with all the unbound, text, combo and list boxes you want. Create your query selecting all the fields you want displayed including those from your form to be used in the search criteria. Type in you criteria for every search field, referring to the boxes on the form. eg
Like [Forms]![FormName]![BoxName] & "*" Or Is Null
The bit at the end is important - & "*" Or Is Null
This ensures that if no data is entered into the box on the form, the query will effectively ignore that box in the search. If all boxes are left empty, every record will be retrieved.
This may be an easier way to go.
Create your form with all the unbound, text, combo and list boxes you want. Create your query selecting all the fields you want displayed including those from your form to be used in the search criteria. Type in you criteria for every search field, referring to the boxes on the form. eg
Like [Forms]![FormName]![BoxName] & "*" Or Is Null
The bit at the end is important - & "*" Or Is Null
This ensures that if no data is entered into the box on the form, the query will effectively ignore that box in the search. If all boxes are left empty, every record will be retrieved.
Phil4tektips
Technical User
VB Knowledge isnt needed.
Just the ability to copy and paste the code.
Then its a matter of setting the Tag properties in the properties of the text box/combo box/list box/option groups/check boxes/ and more importantly date ranges.
If you want to go ahead with this method, first copy and paste the code from the thread FancyPrairie has given you above into a New Module and save it.
Create a new form, with an action button on that opens the report that you want, Instructions:
Insert an action button and name it "View report" from combo123 or text123 for instance. On the properties of the button, type [Event Procedure] in the "on click" event box. Click the three dots that appear on the right hand side. This brings up the VB window. Delete any code you see and copy the following code and rename "Generic Report" to your report name.
Private Sub View_report_Click()
Dim stDocName As String
stDocName = "Generic Report"
DoCmd.OpenReport stDocName, acPreview
End Sub
Test the button to see if it works (opens your report) and report back!
Good luck.
~Phil4tektips~
Grant us peace in our days work!
Just the ability to copy and paste the code.
Then its a matter of setting the Tag properties in the properties of the text box/combo box/list box/option groups/check boxes/ and more importantly date ranges.
If you want to go ahead with this method, first copy and paste the code from the thread FancyPrairie has given you above into a New Module and save it.
Create a new form, with an action button on that opens the report that you want, Instructions:
Insert an action button and name it "View report" from combo123 or text123 for instance. On the properties of the button, type [Event Procedure] in the "on click" event box. Click the three dots that appear on the right hand side. This brings up the VB window. Delete any code you see and copy the following code and rename "Generic Report" to your report name.
Private Sub View_report_Click()
Dim stDocName As String
stDocName = "Generic Report"
DoCmd.OpenReport stDocName, acPreview
End Sub
Test the button to see if it works (opens your report) and report back!
Good luck.
~Phil4tektips~
Grant us peace in our days work!
- Thread starter
- #7
Phil4tektips
Technical User
Thats not everything from my end.....when I said "Report back" the reason was so I could give you the next step!!
Good Luck.
~Phil4tektips~
Grant us peace in our days work!
Good Luck.
~Phil4tektips~
Grant us peace in our days work!
- Thread starter
- #9
Thank you Phil4tektips and nq for being so patient with me. I think I'm going to give nq's method a try first.
nq, this is what i did but i think i'm missing the command buttons to make it run. (which i don't know how to)
i created an unbound form with the following fields:
1)"txtsdr#" (text)
2)"cboapplication" (combo box)
3)"ApprBegDt" (date/time)
4)"ApprEndDt" (date/time)
I created a query and in the criteria for the above fields, I keyed in the following:
1)txtsdr# - [Forms]![FRM02-Report Criteria]![txtsdr#] & "*" Or Is Null
and so for the rest of the fields.
I also received the following error message:
"This expression is typed incorrectly or is too complex to be evaluated"
nq, this is what i did but i think i'm missing the command buttons to make it run. (which i don't know how to)
i created an unbound form with the following fields:
1)"txtsdr#" (text)
2)"cboapplication" (combo box)
3)"ApprBegDt" (date/time)
4)"ApprEndDt" (date/time)
I created a query and in the criteria for the above fields, I keyed in the following:
1)txtsdr# - [Forms]![FRM02-Report Criteria]![txtsdr#] & "*" Or Is Null
and so for the rest of the fields.
I also received the following error message:
"This expression is typed incorrectly or is too complex to be evaluated"
I assume that you mean a button to call the query from the form. In form design mode, click the "Command Button" icon, go to your form and click somewhere. When the Command Button Wizard opens, select Miscellaneous, Run Query and click Next. Select the query you want to call. Follow the instuction until the button is complete.
The criterion should start with the word "Like":
Like [Forms]![FRM02-Report Criteria]![txtsdr#] & "*" Or Is Null
It looks like ApprBegDt and ApprEndDt may be the start and end date for your selection. If this is being evaluated on a single date in your query, use something like this on the criterion for that date:
Between [Forms]![FRM02-Report Criteria]![ApprBegDt] And [Forms]![FRM02-Report Criteria]![ApprEndDt]
This will return all records between the dates typed in your form.
If you have two dates in your table eg Beginning and End dates, then you may need those two dates in your query and something like these criteria:
>=[Forms]![FRM02-Report Criteria]![ApprBegDt]
<=[Forms]![FRM02-Report Criteria]![ApprEndDt]
See how that goes.
The criterion should start with the word "Like":
Like [Forms]![FRM02-Report Criteria]![txtsdr#] & "*" Or Is Null
It looks like ApprBegDt and ApprEndDt may be the start and end date for your selection. If this is being evaluated on a single date in your query, use something like this on the criterion for that date:
Between [Forms]![FRM02-Report Criteria]![ApprBegDt] And [Forms]![FRM02-Report Criteria]![ApprEndDt]
This will return all records between the dates typed in your form.
If you have two dates in your table eg Beginning and End dates, then you may need those two dates in your query and something like these criteria:
>=[Forms]![FRM02-Report Criteria]![ApprBegDt]
<=[Forms]![FRM02-Report Criteria]![ApprEndDt]
See how that goes.
- Thread starter
- #11
I got another error: "you canceled the previous operation"
This is what I've done so far. I created a command button using the wizard to "run the query". I added the "like" to the sdr and application fields. Used the between for the dates. (the like was added in the query under the criteria)
When I just try to run the query, I still get this message "This expression is typed incorrectly or is too complex to be evaluated"
Any suggestions will be greatly appreciated.
This is what I've done so far. I created a command button using the wizard to "run the query". I added the "like" to the sdr and application fields. Used the between for the dates. (the like was added in the query under the criteria)
When I just try to run the query, I still get this message "This expression is typed incorrectly or is too complex to be evaluated"
Any suggestions will be greatly appreciated.
Let's concentrate on the query first - process of elimination.
Open your form and leave all fields blank.
Leave the form open and open the query in design mode.
Delete all criteria.
Type in each criterion, one at a time and run the query to make sure it works.
If you typed in the criterion correctly, the query will return all records since the field on your form is empty. You could then type in a selection for that criterion on the form to ensure that it works.
Do this one by one until all criteria have been typed in correctly and the query works.
Once the query works, then go to the form and try it with the command button. If that fails, then there is a problem with the form and we can try to solve that problem.
Open your form and leave all fields blank.
Leave the form open and open the query in design mode.
Delete all criteria.
Type in each criterion, one at a time and run the query to make sure it works.
If you typed in the criterion correctly, the query will return all records since the field on your form is empty. You could then type in a selection for that criterion on the form to ensure that it works.
Do this one by one until all criteria have been typed in correctly and the query works.
Once the query works, then go to the form and try it with the command button. If that fails, then there is a problem with the form and we can try to solve that problem.
- Thread starter
- #13
Thank you, I broke it down and went back to the query to try it. Its working for the most part.
Had to change some a little...instead of Like [Forms]![FRM02-Report Criteria]![txtsdr#] & "*" Or Is Null
I changed it to AND is Not Null. It works fine.
Now, I'm having problems with the date field. I have one date field that I created 2 form boxes for >> Beg box and End Box. I tried using the following criteria but it seems that it has to be populated otherwise it doesn't return any info.
Between [Forms]![FRM02-Report Criteria]![ApprBegDt] And [Forms]![FRM02-Report Criteria]![ApprEndDt]
I have 2 date fields (Approved Date and Requested Date)that I want to use.
What criteria do I use to either populate both date fields (Approved or Requested) or just populate one date field or no date fields?
Again thank you for being such a great help and having so much patience with me.
Had to change some a little...instead of Like [Forms]![FRM02-Report Criteria]![txtsdr#] & "*" Or Is Null
I changed it to AND is Not Null. It works fine.
Now, I'm having problems with the date field. I have one date field that I created 2 form boxes for >> Beg box and End Box. I tried using the following criteria but it seems that it has to be populated otherwise it doesn't return any info.
Between [Forms]![FRM02-Report Criteria]![ApprBegDt] And [Forms]![FRM02-Report Criteria]![ApprEndDt]
I have 2 date fields (Approved Date and Requested Date)that I want to use.
What criteria do I use to either populate both date fields (Approved or Requested) or just populate one date field or no date fields?
Again thank you for being such a great help and having so much patience with me.
Good that it's starting to work.
There is a subtle difference between the two statements:
And Is Not Null
& "*" Or Is Null
"And Is Not Null" MUST have something in EVERY form box to work correctly. "& "*" Or Is Null" will return records if a box is empty. Be aware that an empty box may not be "empty", there are subtle differences between Nulls, nothings and spaces. Check very carefully what you want and what you get.
You are correct, the "Between" statement will require a beginning AND ending date. My apologies, I thought you were looking for records only between those two dates - searching on a single date field in your query.
I am not really sure if you have two date fields in you query or only one.
If you are searching for records using the dates as seperate items - two date fields, you will need to use both dates in your query, each with the appropriate criterion. eg
>=[Forms]![FRM02-Report Criteria]![ApprBegDt]or Like "*"
<=[Forms]![FRM02-Report Criteria]![ApprEndDt]or Like "*"
The "*" tells the query to return all records for this criterion if the box is left empty. Also note that the > or < symbol must come before the = symbol if you use both symbols.
There is a subtle difference between the two statements:
And Is Not Null
& "*" Or Is Null
"And Is Not Null" MUST have something in EVERY form box to work correctly. "& "*" Or Is Null" will return records if a box is empty. Be aware that an empty box may not be "empty", there are subtle differences between Nulls, nothings and spaces. Check very carefully what you want and what you get.
You are correct, the "Between" statement will require a beginning AND ending date. My apologies, I thought you were looking for records only between those two dates - searching on a single date field in your query.
I am not really sure if you have two date fields in you query or only one.
If you are searching for records using the dates as seperate items - two date fields, you will need to use both dates in your query, each with the appropriate criterion. eg
>=[Forms]![FRM02-Report Criteria]![ApprBegDt]or Like "*"
<=[Forms]![FRM02-Report Criteria]![ApprEndDt]or Like "*"
The "*" tells the query to return all records for this criterion if the box is left empty. Also note that the > or < symbol must come before the = symbol if you use both symbols.
- Thread starter
- #15
I apologize to all..I'm just an Access challenged user and can't get it. I looked at my "is not null" more carefully and it was picking up stuff I didn't want so I changed it back to the "is null" and still got other things that I didn't need and the date range didn't work.
I started from scratch again and tried FancyPrairie's code.
I found another thread that was explaining how to copy and paste the code in.
When I try to run the report, if I use the first criteria box (field), it works fine. Any other box after that, it gives me incorrect info.
This is what I did so far:
1. Copied and Pasted FancyPrairie's code
2. Created an unbound form - one by one added the criteria fields.
ex: name: txtSDR
tag: Where=[TBL01-Log].[SDR],long;
name: cboClient
tag: Where=[TBL01-Log].[Client],string;
name: txtReq_BeginR
tag: Where=[TBL01-Log].[Date Requested],date
name: txtReq_EndR
tag: Where=[TBL01-log].[Date Requested],date
3. Added a command button to check the code:
Msgbox BuildWhere(Me)
(everything looked correct)
4. Changed the command button to preview the report
DoCmd.OpenReport "RPT01-Log",acPreview,,BuildWhere(Me)
Again, when I input a number into the "SDR" box, it works fine. If I try to key in anything in combo with the SDR box or any other field, it returns blank.
Thank you for your time.
I started from scratch again and tried FancyPrairie's code.
I found another thread that was explaining how to copy and paste the code in.
When I try to run the report, if I use the first criteria box (field), it works fine. Any other box after that, it gives me incorrect info.
This is what I did so far:
1. Copied and Pasted FancyPrairie's code
2. Created an unbound form - one by one added the criteria fields.
ex: name: txtSDR
tag: Where=[TBL01-Log].[SDR],long;
name: cboClient
tag: Where=[TBL01-Log].[Client],string;
name: txtReq_BeginR
tag: Where=[TBL01-Log].[Date Requested],date
name: txtReq_EndR
tag: Where=[TBL01-log].[Date Requested],date
3. Added a command button to check the code:
Msgbox BuildWhere(Me)
(everything looked correct)
4. Changed the command button to preview the report
DoCmd.OpenReport "RPT01-Log",acPreview,,BuildWhere(Me)
Again, when I input a number into the "SDR" box, it works fine. If I try to key in anything in combo with the SDR box or any other field, it returns blank.
Thank you for your time.
I can't help with FancyPrairie's code but it's good that you try both approaches.
The problem with the "Is Not Null" code may be your data or criteria - they may be mutually exclusive - one criterion selects records and another criterion knocks them out. Think carefully about what data you have and how your criteria are selecting records.
Also, "Empty" fields in records may cause havoc unless you include the & "*" Or Is Null part in the criterion.
The problem with the "Is Not Null" code may be your data or criteria - they may be mutually exclusive - one criterion selects records and another criterion knocks them out. Think carefully about what data you have and how your criteria are selecting records.
Also, "Empty" fields in records may cause havoc unless you include the & "*" Or Is Null part in the criterion.
Phil4tektips
Technical User
First few things to check is that you can actually do the query you are trying to set up with this form.
Set up a query and in the criteria boxes of the query try to duplicate what you want to do.
So filter for a Client and SDR number.... what results does it give you if any?
Your _EndR text box does not need a tag property since the code assumes the tag from the _BeginR tag property. Try putting a semi colon after 'date' in your tag property for _BeginR.
The code assumes the AND statement. Therefore it will return anything with Client AND SDR number. If you would like or set you VBA code to:
DoCmd.OpenReport "RPT01-Log",acPreview,,BuildWhere(Me, " OR ")
See if those help....
~Phil4tektips~
Grant us peace in our days work!
Set up a query and in the criteria boxes of the query try to duplicate what you want to do.
So filter for a Client and SDR number.... what results does it give you if any?
Your _EndR text box does not need a tag property since the code assumes the tag from the _BeginR tag property. Try putting a semi colon after 'date' in your tag property for _BeginR.
The code assumes the AND statement. Therefore it will return anything with Client AND SDR number. If you would like or set you VBA code to:
DoCmd.OpenReport "RPT01-Log",acPreview,,BuildWhere(Me, " OR ")
See if those help....
~Phil4tektips~
Grant us peace in our days work!
FancyPrairie
Programmer
Phil4tektips, has a good handle on this and if you do what he says it should work. The only thing I would add is that you might want to use Debug.Print BuildWhere(me) rather than Msgbox BuildWhere(Me). Then copy the results from debug's immediate window. In design view, open the query your report is using, switch to SQL view and paste the where clause (from debug's immediate window) at the end of your SQL statement. Now run the query. This way you can see if you have things setup correctly.
- Thread starter
- #19
Phil4tektips
Technical User
Good feeling when it works isn't it!
Thanks goes to FancyPrairie for use of the code.
~Phil4tektips~
Grant us peace in our days work!
Thanks goes to FancyPrairie for use of the code.
~Phil4tektips~
Grant us peace in our days work!
- Status
Similar threads
