Solved: Access 2003 - How to search by criteria in a report?

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

smooth

Garrett
Thread Starter
Joined
Sep 26, 2005
Messages
4,029
Hey everybody :D

I have created a report. For the record source, I have it pulling only records where their Active Status is "In-active". That works great.

I need to have it where when someone pulls this report, they can then do a search for certain dates. There are about 4 date fields on the report.

For example, if someone pulled up the report, and they needed to pull up a list of these agents that their Contract Mail date was between 4/11/2006 and 4/25/2006.

Is it possible to do this, to where they can put whatever criteria they need?
 
Joined
Jul 29, 2001
Messages
21,334
Is the report based on a query? If so put the beginning and ending date as the criteria for the query. If there is more than one date field you want to query you need to set up a form as a reporting interface.
 
Joined
Aug 5, 2005
Messages
3,086
Hey smooth,

Reports don't really have events like forms do. I mean, they have some events, but I think you might want to look at creating a form to open (preview!!) and customize a given report. You could then set and apply filters to the report, which would allow you to limit dates or anything else.

HTH

chris.
 

smooth

Garrett
Thread Starter
Joined
Sep 26, 2005
Messages
4,029
Well, I have the query inside of the RecordSource field, when I look at the properties of the report.

I need it where, someone using the database can come in, and look for certain dates on the fly.
 

smooth

Garrett
Thread Starter
Joined
Sep 26, 2005
Messages
4,029
So you mean, set the forms source as the Report that I have created? And then just bring the fields over to the form.

Would I still be able to print them nicely like a report? Printing it where it fits on a sheet of paper (all the columns, multiple sheets if a lot of rows), so it can be easily read as well.
 
Joined
Aug 5, 2005
Messages
3,086
No, I mean, make a little form that will preview a report when you click a button. Have some controls on the form so the user can manipulate either the report's RecordSource or its filters. In other words, you'd have two windows open: the form and the report preview.

Is that a little clearer?

chris.
 

smooth

Garrett
Thread Starter
Joined
Sep 26, 2005
Messages
4,029
I see what you mean. I'm not sure how to do the preview, and things like that though.

How do I do a preview window? Also, how would I do the record source to pull dates that were between a certain date?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
smooth, one of the easiest ways is in the query that supplies the report with it's data put

between Forms![form name]![date 1] and Forms![form name]![date 2]

in the criteria row for the date field you wnat to make the selection on.

Where [form name] is the name of your form in the square brackets and
[date 1] and [date 2] are the names of the fields on your form.
 
Joined
Aug 5, 2005
Messages
3,086
Check out the OpenReport method of DoCmd. You'll want to make sure you set the view type to acViewPreview. That's the default when you double-click a report in the database window, but if you OpenReport the default is to print the report.

Say you had two textbox controls on the form: txtStartDate and txtEndDate. The filter string might be something like

"[reportDateControl] Between #" & Me.txtStartDate.Value & "# And #" & Me.txtEndDate.Value & "#"​
You'd use a command button to build that filter, then apply that filter to the report preview.

There are other ways to do it. I just think you should use a form to get what you're after, is all.

chris.
 
Joined
Aug 5, 2005
Messages
3,086
[bump]

OBP, the issue isn't the report's RecordSource (as I understand). smooth wants users to be able to view a complete report then filter it after it's already open.

Or am I misunderstanding?

chris.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Chris, can you "requery" a report?
I have never tried it.
As you said the form with inputs is the obvious answer.
 

smooth

Garrett
Thread Starter
Joined
Sep 26, 2005
Messages
4,029
Hey guys, thanks for all the advice.

I'll start out trying what you were talking about chris. :) Yes, I want to open up the report first, and then requery it to where it has the dates. I think I know what you mean with the buttons that you press to search for certain dates.

I'll try looking for those things, and repost questions here.
 
Joined
Aug 5, 2005
Messages
3,086
You can't requery a report, per se. You'd set its filter and then turn its filter on. I've never tried either; I never work with reports. But if that doesn't work, a quick close and reopen with the filter applied should work (that can be done programmatically).

chris.
 

smooth

Garrett
Thread Starter
Joined
Sep 26, 2005
Messages
4,029
I created a new form, set it's record source to my agents table. I have a report that has the record source to only pull agents whose ActiveStatus = -2.

This is what I have for the command button on the new form for pulling the dates:

Code:
Private Sub cmdSubmit_Click()
DoCmd.OpenReport "agentsReport", acViewPreview, , WHERE agents.CarePackageSent Between #" & Me.txtFirstDate.Value & "# And #" & Me.txtSecondDate.Value & "#"
End Sub
I get a syntax error. I'm not sure how to end the code here.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top