Date code problem

[James T.]

Access 2000

I recently inherited a database where in a query the
Criteria for the date field is:

#3/31/2004# And <#5/30/2004#

Each time the user runs his reports, he has to go into the
query in design view (!) and change the date. Then he goes
to a macro that runs 7 reports based on that date.

I tried using this code:

Between [Enter start date (mm/dd/yy):] And [Enter end date
(mm/dd/yy):]

so that he would not have to be changing dates in design
view, but it then is asking him for a date for each report
(7 times), where the previous code doesn't.

Is there a way to use code similar to my Between ... and
NOT have it ask for a date for each report?

Thanks,

James

 

[Allen Browne]

Use a form where the user can enter the dates.

Assuming the form is named "Form1", and the two text boxes are named
"StartDate" and "EndDate", the Criteria row in query design under your date
field would read:
Between [Forms].[Form1]![StartDate] And [Forms].[Form1]![EndDate]

To guarantee that Access understands these 2 dates correctly, set the Format
property of both text boxes to "Short Date" or similar. Then in your query,
declare the two parameters by choosing Parameters from the Query menu and
entering:
[Forms].[Form1]![StartDate] Date/Time
[Forms].[Form1]![EndDate] Date/Time

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Access 2000

I recently inherited a database where in a query the
Criteria for the date field is:

#3/31/2004# And <#5/30/2004#

Each time the user runs his reports, he has to go into the
query in design view (!) and change the date. Then he goes
to a macro that runs 7 reports based on that date.

I tried using this code:

Between [Enter start date (mm/dd/yy):] And [Enter end date
(mm/dd/yy):]

so that he would not have to be changing dates in design
view, but it then is asking him for a date for each report
(7 times), where the previous code doesn't.

Is there a way to use code similar to my Between ... and
NOT have it ask for a date for each report?

Thanks,

James

 

[Van T. Dinh]

What you can do is to create a Form "frmParameters" with 2 TextBox Controls
"txtStartDate" & "txtEndDate" and a CommandButton to run the Macro.

Change the criteria in your Query to:

BETWEEN Forms!frmParameters!txtStartDate
AND Forms!frmParameters!txtEndDate

When the user want to run the Reports, open this Form first. The user can
type in the dates and click the CommandButton to run the Reports. The Query
(Queries???) will pick up the dates from the Form and use them in the Query.

--
HTH
Van T. Dinh
MVP (Access)

Access 2000

I recently inherited a database where in a query the
Criteria for the date field is:

#3/31/2004# And <#5/30/2004#

Each time the user runs his reports, he has to go into the
query in design view (!) and change the date. Then he goes
to a macro that runs 7 reports based on that date.

I tried using this code:

Between [Enter start date (mm/dd/yy):] And [Enter end date
(mm/dd/yy):]

so that he would not have to be changing dates in design
view, but it then is asking him for a date for each report
(7 times), where the previous code doesn't.

Is there a way to use code similar to my Between ... and
NOT have it ask for a date for each report?

Thanks,

James

 

[James T.]

Thanks guys! That works.

James

-----Original Message-----
Access 2000

I recently inherited a database where in a query the
Criteria for the date field is:

#3/31/2004# And <#5/30/2004#

Each time the user runs his reports, he has to go into the
query in design view (!) and change the date. Then he goes
to a macro that runs 7 reports based on that date.

I tried using this code:

Between [Enter start date (mm/dd/yy):] And [Enter end date
(mm/dd/yy):]

so that he would not have to be changing dates in design
view, but it then is asking him for a date for each report
(7 times), where the previous code doesn't.

Is there a way to use code similar to my Between ... and
NOT have it ask for a date for each report?

Thanks,

James
.