Urgent - Calendar - Need 7 days for week selected

[Jani]

Using Access2003. This db has a calendar popup form (thanks Allen Browne).
The textbox for the date selected is named 'weekloaddate' and the default
value is =Date()-Weekday(Date(),0)+1 so that when query is run it pulls
weekly data from the Sunday of the current week.

The query that was created to pull the weekly data was formatted for the
week field as 'Format([Load Date],"ww")' with criteria of
'Format([Forms]![frmMain]![weekloaddate],"ww")' and another field for the
year was formatted as 'Format([Load Date],"yy")' with criteria of
'Format([Forms]![frmMain]![weekloaddate],"yy")'. This has worked fine up
until this week when the new year began - HAPPY NEW YEAR! But to my problem,
I need help with the parameter that will pull the weekly data for the date
selected in the 'weekloaddate' text box. Hope this is clear. Thanks in
advance for your help!

Jani

 

[Jani]

Additional Information - trying to simplify... Instead of using the calendar,
I have a text box (SundayDate) that defaults to the previous Sunday. I need a
query to pull data from the SundayDate field for the next seven days. My
criteria is: Between [Forms]![frmMain]![SundayDate] And
[Forms]![frmMain]![SundayDate]+7. For some reason the 1st time clicked, it
worked. Now I'm getting an error: '... typed incorrectly or too complex...
try simplifying the expression by assigning parts of the expression to
variables'... and now I'm stuck again. Can anyone help? Thanks,

 

[John W. Vinson]

Additional Information - trying to simplify... Instead of using the calendar,
I have a text box (SundayDate) that defaults to the previous Sunday. I need a
query to pull data from the SundayDate field for the next seven days. My
criteria is: Between [Forms]![frmMain]![SundayDate] And
[Forms]![frmMain]![SundayDate]+7. For some reason the 1st time clicked, it
worked. Now I'm getting an error: '... typed incorrectly or too complex...
try simplifying the expression by assigning parts of the expression to
variables'... and now I'm stuck again. Can anyone help? Thanks,

It may help to open the query in design view; right click the background
behind the tables, and select Parameters. Copy and paste (it must match
exactly!) the parameter

[Forms]![frmMain]![SundayDate]

into the left column, and select Date/Time from the right column.

John W. Vinson [MVP]

 

[Jani]

Hi John - that didn't work. Can you provide the syntax to get the next Monday
date? If I had that I think can do a work around. Nothing I've tried has been
successful so far! Happy New Year... wondering why I'm at work as there's
hardly anyone left! jms

Additional Information - trying to simplify... Instead of using the calendar,
I have a text box (SundayDate) that defaults to the previous Sunday. I need a
query to pull data from the SundayDate field for the next seven days. My
criteria is: Between [Forms]![frmMain]![SundayDate] And
[Forms]![frmMain]![SundayDate]+7. For some reason the 1st time clicked, it
worked. Now I'm getting an error: '... typed incorrectly or too complex...
try simplifying the expression by assigning parts of the expression to
variables'... and now I'm stuck again. Can anyone help? Thanks,

It may help to open the query in design view; right click the background
behind the tables, and select Parameters. Copy and paste (it must match
exactly!) the parameter

[Forms]![frmMain]![SundayDate]

into the left column, and select Date/Time from the right column.

John W. Vinson [MVP]

 

[John W. Vinson]

Hi John - that didn't work. Can you provide the syntax to get the next Monday
date? If I had that I think can do a work around. Nothing I've tried has been
successful so far! Happy New Year... wondering why I'm at work as there's
hardly anyone left! jms

DateAdd("d", 8-Weekday(Date(), 2), Date())

will do it for you. Not sure why the other query isn't working - if you'ld
like to post the SQL someone might be able to help.

Have a great New Year!

John W. Vinson [MVP]

 

[Steve Sanford]

PMFJI,

You could try adding another text box next to [SundayDate] (I'll call it
[NextDate]) amd set the control source to

=CDate([SundayDate])+7


(BTW, you do know that [SundayDate]+7 will give you the next SUNDAY date???
If you want 7 days, add 6 to [SundayDate], which will be Saturday.)

Then in the query use

Between [Forms]![frmMain]![SundayDate] And [Forms]![frmMain]![NextDate].


If that works, set the Visible property of the control [NextDate] to false.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi John - that didn't work. Can you provide the syntax to get the next Monday
date? If I had that I think can do a work around. Nothing I've tried has been
successful so far! Happy New Year... wondering why I'm at work as there's
hardly anyone left! jms

Additional Information - trying to simplify... Instead of using the calendar,
I have a text box (SundayDate) that defaults to the previous Sunday. I need a
query to pull data from the SundayDate field for the next seven days. My
criteria is: Between [Forms]![frmMain]![SundayDate] And
[Forms]![frmMain]![SundayDate]+7. For some reason the 1st time clicked, it
worked. Now I'm getting an error: '... typed incorrectly or too complex...
try simplifying the expression by assigning parts of the expression to
variables'... and now I'm stuck again. Can anyone help? Thanks,

It may help to open the query in design view; right click the background
behind the tables, and select Parameters. Copy and paste (it must match
exactly!) the parameter

[Forms]![frmMain]![SundayDate]

into the left column, and select Date/Time from the right column.

John W. Vinson [MVP]

 

[Jani]

Steve and John - thanks for the responses. I'm using Steve's as I was
starting down that path. It works! Except now and I've tried everything and
this should be soooo easy but I can't figure it out. I can not get the second
text box to automatically update when a different date is selected in the
first one. I've tried refresh, recalc, requery, from both boxes using on
change, on activate, etc., etc. but need some help. Thanks again - hope to
hear from one of you soon and Happy New Year! Hope it's a great one for you!
jms

 

[John W. Vinson]

Steve and John - thanks for the responses. I'm using Steve's as I was
starting down that path. It works! Except now and I've tried everything and
this should be soooo easy but I can't figure it out. I can not get the second
text box to automatically update when a different date is selected in the
first one. I've tried refresh, recalc, requery, from both boxes using on
change, on activate, etc., etc. but need some help. Thanks again - hope to
hear from one of you soon and Happy New Year! Hope it's a great one for you!
jms

What's the Control Source of the second textbox, and what code do you have?

John W. Vinson [MVP]

 

[Jani]

John - thanks for getting back on this. There is no Control Source for either
the first or second text box. The 1st text box date has a default value of
=Date()-Weekday(Date(),0)+1 so that the current week Sunday is always
displayed. The second text box does not have a Control Source and has a
default value of =CDate([SundayDate])+6 so that it displays the next Saturday
date (1/13/08 and 1/19/08 for example). The query works - using Between to
pull data in between these two dates. If I manually change the 2nd text box
and change it back again, than it works correctly. Make sense? Am I making to
much of this? I simply want two text boxes - 1st one defaults to current week
Sunday, and 2nd one defaults to current week Saturday. Thanks!!! jms

 

[John W. Vinson]

John - thanks for getting back on this. There is no Control Source for either
the first or second text box. The 1st text box date has a default value of
=Date()-Weekday(Date(),0)+1 so that the current week Sunday is always
displayed. The second text box does not have a Control Source and has a
default value of =CDate([SundayDate])+6 so that it displays the next Saturday
date (1/13/08 and 1/19/08 for example). The query works - using Between to
pull data in between these two dates. If I manually change the 2nd text box
and change it back again, than it works correctly. Make sense? Am I making to
much of this? I simply want two text boxes - 1st one defaults to current week
Sunday, and 2nd one defaults to current week Saturday. Thanks!!! jms

The DefaultValue applies only at the moment that a new record is created on
the form.

Why have the second textbox on your form AT ALL? Just use a criterion on your
query of

BETWEEN [Forms]![YourFormName]![firsttextbox] AND DateAdd("d", 7,
[Forms]![YourFormName]![firsttextbox])


John W. Vinson [MVP]

 

[Steve Sanford]

John,

Because of the errors Jani was getting, I used the second text box as a test
- she seemed to be more familar with using a text box in a query.

What I missed is that she is using the *DEFAULT VALUE* property instead of
the control source. My bad....

Jani,

In my example, "=CDate([SundayDate])+6" should be in the *control source* of
the second text box to automatically update to the new date when the first
text box is changed.

When you query is returning the records you want, change the criteria to
what John recommended:


BETWEEN [Forms]![YourFormName]![firsttextbox] AND DateAdd("d", 6,
[Forms]![YourFormName]![firsttextbox])


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

John - thanks for getting back on this. There is no Control Source for either
the first or second text box. The 1st text box date has a default value of
=Date()-Weekday(Date(),0)+1 so that the current week Sunday is always
displayed. The second text box does not have a Control Source and has a
default value of =CDate([SundayDate])+6 so that it displays the next Saturday
date (1/13/08 and 1/19/08 for example). The query works - using Between to
pull data in between these two dates. If I manually change the 2nd text box
and change it back again, than it works correctly. Make sense? Am I making to
much of this? I simply want two text boxes - 1st one defaults to current week
Sunday, and 2nd one defaults to current week Saturday. Thanks!!! jms

The DefaultValue applies only at the moment that a new record is created on
the form.

Why have the second textbox on your form AT ALL? Just use a criterion on your
query of

BETWEEN [Forms]![YourFormName]![firsttextbox] AND DateAdd("d", 7,
[Forms]![YourFormName]![firsttextbox])


John W. Vinson [MVP]

 

[Jani]

John and Steve - Sorry for not sending this a couple days ago but I couldn't
get online. Just wanted to say I'm back on track and THANKS for all your help
and support! jms

John,

Because of the errors Jani was getting, I used the second text box as a test
- she seemed to be more familar with using a text box in a query.

What I missed is that she is using the *DEFAULT VALUE* property instead of
the control source. My bad....

Jani,

In my example, "=CDate([SundayDate])+6" should be in the *control source* of
the second text box to automatically update to the new date when the first
text box is changed.

When you query is returning the records you want, change the criteria to
what John recommended:


BETWEEN [Forms]![YourFormName]![firsttextbox] AND DateAdd("d", 6,
[Forms]![YourFormName]![firsttextbox])


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

John - thanks for getting back on this. There is no Control Source for either
the first or second text box. The 1st text box date has a default value of
=Date()-Weekday(Date(),0)+1 so that the current week Sunday is always
displayed. The second text box does not have a Control Source and has a
default value of =CDate([SundayDate])+6 so that it displays the next Saturday
date (1/13/08 and 1/19/08 for example). The query works - using Between to
pull data in between these two dates. If I manually change the 2nd text box
and change it back again, than it works correctly. Make sense? Am I making to
much of this? I simply want two text boxes - 1st one defaults to current week
Sunday, and 2nd one defaults to current week Saturday. Thanks!!! jms

The DefaultValue applies only at the moment that a new record is created on
the form.

Why have the second textbox on your form AT ALL? Just use a criterion on your
query of

BETWEEN [Forms]![YourFormName]![firsttextbox] AND DateAdd("d", 7,
[Forms]![YourFormName]![firsttextbox])


John W. Vinson [MVP]