Date range help!!

[bananatodd]

Hi,

I need to create a report that will give me results within a specific date range. I have made this possible, but it asks me for the Start Date and then the End Date 3 different times and it is very annoying. Why is it doing this?

[pbaldy]

Most of us would recommend having the user enter the dates on a form, and have your query/report get the criteria from there. It sounds like you have subreports with their own parameters, which would each prompt. The form will avoid that.

[bananatodd]

Ok, can you tell me how to do that??? I tried an unbound form thing but It gave me no results when I entered the dates. Walk me through it if you will.

THANKS!

[NTC]

The technique it sounds like you are using is a parameter prompt in the query.

Instead, as suggested, use a form to hold the 2 dates. Then 'call' those dates by the query by referring to those text boxes. Syntax is:
Forms!YourFormName.YourTextBoxName

You won't need this in the record sources of the subreports - if the subreports were set up with the wizard where you identified a cross referencing field. If the subreports are stand alone, not cross referenced to the main reports records then you would need to apply the same date range technique to the record sources.

Designing a database requires everyone to have a textbook, easily found at Amazon or a big book store. Examples of these tasks will be shown.

[bananatodd]

I've uploaded my form at the moment, and I have your format in the date parameter for my Cross_tab query. Is the fact the the query is crosstab messing things up?

Also, I want the user to enter the dates, click the button, and have the report with the correct dates pop up.

THANKS!

[slave138]

From your original description, the query (or queries) you were using had a [Start Date] and [End Date] parameter in the Criteria section. If that is correct, you want to change the Criteria to use:

Between Forms![ASAP Menu].txtStartDate and Forms![ASAP Menu].txtEndDate

One word of caution for the End Date: If a time is not included Access treats it as midnight. This won't be a problem unless dates are being entered in the table using functions like Now() which includes the time. If so, to get all of the records from Oct 1 2010 through Oct 10 2010 the End Date would need to be Oct 11 2010 (or include a time like Oct 10 2010 11:59:59 PM).

[slave138]

Sorry, I just noticed the part about the crosstab query. You will need to set the data types of the parameters for it to work. See the info on parameters:
http://allenbrowne.com/ser-67.html#Param

When I was testing I noticed a small 'feature' where Access auto-included brackets around my parameter even though it was already bracketed.
For example I entered:

[Forms]![frmDateSample]![txtStartDate]

but Access treated it like:

[[Forms]![frmDateSample]![txtStartDate]]

To get it to work I had to strip the first and last brackets like:

Forms]![frmDateSample]![txtStartDate

[bananatodd]

Ok so then something just pops up to ask me the date and time, so then what is the point of the form? Couldn't I just get rid of the "Start Date" and "end date" boxes and just have the button?

[slave138]

There are a few reasons you should use a form instead of the pop-ups:
1. You can validate the info before running the query (make sure an actual date is entered)

2. The user doesn't have to keep typing the same dates over and over if they just want to 'tweak' their selection (They can change the Start Date without having to re-enter the End Date, for example)

[bananatodd]

Right, but what I have now, though it looks like it should be a form, is actually pop-ups.

[bananatodd]

Here is what my parameters look like

[bananatodd]

Alright Let me just walk you through this step by step:

1)I HAVE a crosstab query with all of the information I want
2)I HAVE a report based on that crosstab query that displays all the information
3) I WANT the report to only show information between my start date and end date. (I am going to want to see the totals for each month)

[pbaldy]

I don't think there was any question about what you have/want. It's a pretty common scenario. I'd use a form for the dates, and since it's a crosstab query it's already been pointed out that you have to add the form controls to the Parameters of the query. If you're still getting prompted, you've either spelled something wrong, closed the form, or still have [Enter whatever] somewhere.

[bananatodd]

I type in to the Criteria Field AND Parameters:

Between Forms![ASAP Menu].txtStartDate AND Forms![ASAP Menu].txtEndDate

But the criteria field always automatically changes to

Between [Forms]![ASAP Menu].[txtStartDate] AND [Forms]![ASAP Menu].[txtEndDate]

.........

After the person enters the date I have them pressing submit, and I have that button opening the report... should I have it open the query too or.....?

[pbaldy]

Access sometimes does that type of thing; it should work fine that way. You just open the report; it runs the query itself.