UPDATE DATE FIELD IN QUERY

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Report & 3 sub-reports all based on the same Query. Every Month I
run this report, going into Design & changing the criteria "Between
#3/1/2007# And #3/31/2007#".
I have used "Between [BeginningDate] And [EndingDate]" for other reports
that Do not have any Sub-reports. But, when I attempt for my Large report W/
Subforms, it does not work.
I would like to create a Macro ??? I thought that should work. I have a
Macro that simply has "Open Query", I have set it to open the query in Edit
mode, That will allow me to change the Date "Between #3/1/2007# And
#3/31/2007#". I can simplay change it to 4/1/2007-4/30/2007.
I thought the next item could be "Close".
Next item "Open Report"
When I try this, It does not open the Query in Edit Mode, I think it is
opening, then closing, then opening the report ???

Any Help or Ideas ????

Aaron
My next item was "Close"
I would like to complete the Macro so that after it opens the Query in
Design & the date gets changed, It opens the Corresponding Report....
 
I have used "Between [BeginningDate] And [EndingDate]" for other reports
that Do not have any Sub-reports. But, when I attempt for my Large report W/
Subforms, it does not work.
I assume the 'Subforms' is meant to be 'Sub-reports.'
What errors do you get? What does it do wrong or does not do?
 
Sorry for the typo, yes I did mean 'Sub Report'.
It's strange. When I open the report it does not give me an error, I am
prompted to enter Start Date, then prompted to enter End Date. After I enter
those two dates, it re-prompts Start Date. After re-entering that date, it
prompts End Date. It stays in that Cycle.... I tried entering the Start &
End dates 6 times & it just kept bringing up the prompt. It would never open
the report.
When I open just the Query, I am prompted for a start & end date (only once)
then the query pops open no problem.
I figured since my Query works correctly, it had to be something about using
multiple Sub-Reports, all based on the same query using the same date field.

The report tracks supply usage by our Sale Reps. My report first shows the
individual Rep Totals. I then use a Sub-Report to Total All of the Reps
cost's for the County. I then have a final Sub-report that gives a Grand
Total for Every County. I generate this report each month, today I need to
run it for the entire month of April, (the data in my table goes back to June
2006)

I greatly appreciate any help or sugestions, I am going to be leaving a
person in charge of this report who has Never used Access before. I don't
mind going into the design of the report & changing the Query Dates, But, I
would really like to make this as easy as possible for the new person....

Thanks Again !!!

Aaron



KARL DEWEY said:
I have used "Between [BeginningDate] And [EndingDate]" for other reports
Click to expand...
Click to expand...
that Do not have any Sub-reports. But, when I attempt for my Large report W/
Subforms, it does not work.
I assume the 'Subforms' is meant to be 'Sub-reports.'
What errors do you get? What does it do wrong or does not do?
--
KARL DEWEY
Build a little - Test a little


Aaron said:
I have a Report & 3 sub-reports all based on the same Query. Every Month I
run this report, going into Design & changing the criteria "Between
#3/1/2007# And #3/31/2007#".
I have used "Between [BeginningDate] And [EndingDate]" for other reports
that Do not have any Sub-reports. But, when I attempt for my Large report W/
Subforms, it does not work.
I would like to create a Macro ??? I thought that should work. I have a
Macro that simply has "Open Query", I have set it to open the query in Edit
mode, That will allow me to change the Date "Between #3/1/2007# And
#3/31/2007#". I can simplay change it to 4/1/2007-4/30/2007.
I thought the next item could be "Close".
Next item "Open Report"
When I try this, It does not open the Query in Edit Mode, I think it is
opening, then closing, then opening the report ???

Any Help or Ideas ????

Aaron
My next item was "Close"
I would like to complete the Macro so that after it opens the Query in
Design & the date gets changed, It opens the Corresponding Report....
Click to expand...
Click to expand...
 
Aaron,

I think the Parameter Query is prompting you for the date criteria each
time it needs them, which is for each subreport.

I would recommend making a form, which will be open at the time that you
print the report, with two unbound textboxes, where you can enter the
start and end dates. Then, change the criteria in your query, to the
equivalent of:
Between [Forms]![NameOfForm]![StartDate] And
[Forms]![NameOfForm]![StartDate]

Enter the dates in the StartDate and EndDate textboxes, print the
report, and you will not be prompted.
 
Steve,
Thanks for the assistance...I have seen examples of this, and I believe I
have even tried to duplicate using the NorthWind sample DB, for some reason I
have Never gotten this to work !!! Not sure what I am doing wrong !!!

I created the Form, FRM_DATE. I only have two unbound txt boxes (Control
source blank) named StartDate and EndDate. I have removed the Label from the
Txt box, does that matter ??? I've tried noth ways, neither seem to work, is
that where I am going wrong ???

In the Date Field of my Query I have entered the following into the Criteria
space:

Between [Forms]![FRM_DATE]![StartDate] And [Forms]![FRM_DATE]![EndDate]

I can open the query, and it does prompt start & end dates, but it opens the
small window "enter parameter value" "Forms!FRM_DATE!StartDate" then repeats
for End date. Same window as when I run "Between [BeginningDate] And
[EndingDate]".

When I open the report it opens the same parameter window & not my form, any
ideas what I keep missing ???

Aaron


Steve Schapel said:
Aaron,

I think the Parameter Query is prompting you for the date criteria each
time it needs them, which is for each subreport.

I would recommend making a form, which will be open at the time that you
print the report, with two unbound textboxes, where you can enter the
start and end dates. Then, change the criteria in your query, to the
equivalent of:
Between [Forms]![NameOfForm]![StartDate] And
[Forms]![NameOfForm]![StartDate]

Enter the dates in the StartDate and EndDate textboxes, print the
report, and you will not be prompted.

--
Steve Schapel, Microsoft Access MVP
Sorry for the typo, yes I did mean 'Sub Report'.
It's strange. When I open the report it does not give me an error, I am
prompted to enter Start Date, then prompted to enter End Date. After I enter
those two dates, it re-prompts Start Date. After re-entering that date, it
prompts End Date. It stays in that Cycle.... I tried entering the Start &
End dates 6 times & it just kept bringing up the prompt. It would never open
the report.
When I open just the Query, I am prompted for a start & end date (only once)
then the query pops open no problem.
I figured since my Query works correctly, it had to be something about using
multiple Sub-Reports, all based on the same query using the same date field.

The report tracks supply usage by our Sale Reps. My report first shows the
individual Rep Totals. I then use a Sub-Report to Total All of the Reps
cost's for the County. I then have a final Sub-report that gives a Grand
Total for Every County. I generate this report each month, today I need to
run it for the entire month of April, (the data in my table goes back to June
2006)

I greatly appreciate any help or sugestions, I am going to be leaving a
person in charge of this report who has Never used Access before. I don't
mind going into the design of the report & changing the Query Dates, But, I
would really like to make this as easy as possible for the new person....
Click to expand...
Click to expand...
 
Aaron,

The FRM_DATE form needs to be open, with the criteria dates already
entered into the textboxes, at the time that you run the query, or print
the report. Is that the case?
 
Back
Top