Using dates in a dialog form
- Thread starter retro
- Start date
R
Rich
Guest
What exactly are you trying to do , what's the combo box for?
The combo box isn't mine... i guess that last post was confusing.
I have a report that is based on a query. The report lists entries by week (its a sales by week report). Both work fine.
However, I need to use a form to enter criteria. I need to be able to look at any week's report, not just this week's. As the query works by asking start and end date, I put two text boxes on my form: StartDate and EndDate. This also works great... type in the start date, type in the end date, hit go and hey presto!
However, I decided to use the calendar example to automate the process. This too works... click on StartDate and a calendar pops up. The value you select then becomes the value for StartDate. However, I still have to manually enter the end date.
As the start date is always a Monday, and the end date is always a Saturday, all I want is for EndDate to automatically be StartDate + 5 days. I tried Val([StartDate]) +5, but this only changes the first value, i.e. Start date 1/2/2004, End date becomes 6/1/1900!!
I figure that the answer lies in the function from the MS help thing:
Public Function LstDayCurWeek(InDate As Date) As Date
LstDayCurWeek = InDate - Weekday(InDate) + 7
End Function
...but how to use it to auto update my EndDate value?
As I mentioned, I've seen it work on a form with a combo box (the combo box selects which function you want - end of week, start of week, end of month.....) but mine should be simpler, as I only want 1 fixed function. The code for working on that combo box was:
Me.txtReqDate = Eval (Me.cmbFunction & "(#" & Me.txtBaseDate & "#)")
...so I figured it must be something along those lines?
Thanks for all the help guys!
I have a report that is based on a query. The report lists entries by week (its a sales by week report). Both work fine.
However, I need to use a form to enter criteria. I need to be able to look at any week's report, not just this week's. As the query works by asking start and end date, I put two text boxes on my form: StartDate and EndDate. This also works great... type in the start date, type in the end date, hit go and hey presto!
However, I decided to use the calendar example to automate the process. This too works... click on StartDate and a calendar pops up. The value you select then becomes the value for StartDate. However, I still have to manually enter the end date.
As the start date is always a Monday, and the end date is always a Saturday, all I want is for EndDate to automatically be StartDate + 5 days. I tried Val([StartDate]) +5, but this only changes the first value, i.e. Start date 1/2/2004, End date becomes 6/1/1900!!
I figure that the answer lies in the function from the MS help thing:
Public Function LstDayCurWeek(InDate As Date) As Date
LstDayCurWeek = InDate - Weekday(InDate) + 7
End Function
...but how to use it to auto update my EndDate value?
As I mentioned, I've seen it work on a form with a combo box (the combo box selects which function you want - end of week, start of week, end of month.....) but mine should be simpler, as I only want 1 fixed function. The code for working on that combo box was:
Me.txtReqDate = Eval (Me.cmbFunction & "(#" & Me.txtBaseDate & "#)")
...so I figured it must be something along those lines?
Thanks for all the help guys!
R
Rich
Guest
Me.EndDate = Me.StartDate+5 or Me.EndDate = DateAdd("d",5,Me.StartDate)
What do you mean by fixed Function, I still don't know what you're selecting in the combo?
What do you mean by fixed Function, I still don't know what you're selecting in the combo?
its not my combo box 
If you look back in this thread (page 1), there's an attached database. It is a sample for some date functions. Here's a pic: (if the pic doesn't work go here )
In that database, the combo box selects which function you use. It is then applied to the base date, and the resulting date is displayed in a text box (covered in pic by the drop-down menu).
they accomplish this by using:
Me.txtReqDate = Eval (Me.cmbFunction & "(#" & Me.txtBaseDate & "#)")
Where txtReqDate is the resulting date text box, and cmbFunction is the combo box.
I myself want basically to be able to use that, but instead of having the combo box, i want the value to be FIXED to only one of those options, i.e. I want to remove the combo box and have only 1 function.
If you look back in this thread (page 1), there's an attached database. It is a sample for some date functions. Here's a pic: (if the pic doesn't work go here )
In that database, the combo box selects which function you use. It is then applied to the base date, and the resulting date is displayed in a text box (covered in pic by the drop-down menu).
they accomplish this by using:
Me.txtReqDate = Eval (Me.cmbFunction & "(#" & Me.txtBaseDate & "#)")
Where txtReqDate is the resulting date text box, and cmbFunction is the combo box.
I myself want basically to be able to use that, but instead of having the combo box, i want the value to be FIXED to only one of those options, i.e. I want to remove the combo box and have only 1 function.
R
Rich
Guest
Which Function?
From what I can gather, this is what makes it work:
In a module:
=========
Public Function LstDayCurWeek(InDate As Date) As Date
LstDayCurWeek = InDate - Weekday(InDate) + 7
End Function
An After Update Event in the Form:
=========================
Private Sub cmbFunction_AfterUpdate()
Me.txtReqDate = Eval (Me.cmbFunction & "(#" & Me.txtBaseDate & "#)")
In a module:
=========
Public Function LstDayCurWeek(InDate As Date) As Date
LstDayCurWeek = InDate - Weekday(InDate) + 7
End Function
An After Update Event in the Form:
=========================
Private Sub cmbFunction_AfterUpdate()
Me.txtReqDate = Eval (Me.cmbFunction & "(#" & Me.txtBaseDate & "#)")
R
Rich
Guest
You don't need the Function, assuming that your users are always going to select a Monday then it's just Me.EndDate = Me.StartDate+5 or the DateAdd function I gave you
As mentioned above, I used Rich's solution and all was fine and dandy. However, I then went back to OldSoftBoss' calendar method, and it no longer auto updates. It will if I type in a value, but not if I use the calendar.
To recap, I have two text boxes: StartDate and EndDate, both of which are short date format.
The After Update for StartDate is:
Me.EndDate = Me.StartDate + 5
Works fine when i type in a date (I have to press Return or Tab before it updates), but when I use the calendar method, the EndDate box stays blank.
To recap, double clicking on the StartDate box brings up a calendar on a seperate form, and whatever date is double clicked on the calendar then goes to the StartDate box (and the form closes).
How can I make it update EndDate at that point?
ALSO
How do I set the default value of StartDate to the beginning of the current week? Can I use:
Date() - WeekDay(Date()) + 1
To recap, I have two text boxes: StartDate and EndDate, both of which are short date format.
The After Update for StartDate is:
Me.EndDate = Me.StartDate + 5
Works fine when i type in a date (I have to press Return or Tab before it updates), but when I use the calendar method, the EndDate box stays blank.
To recap, double clicking on the StartDate box brings up a calendar on a seperate form, and whatever date is double clicked on the calendar then goes to the StartDate box (and the form closes).
How can I make it update EndDate at that point?
ALSO
How do I set the default value of StartDate to the beginning of the current week? Can I use:
Date() - WeekDay(Date()) + 1
Any ideas on this? The beginning of the current week part isn't so important, but I'd like to get it so the EndDate text box auto updates after selecting a value in the calendar.
I'm sure its something simple, but i'm still a n00b
I'm sure its something simple, but i'm still a n00b
Ahh, new page. To recap (if you don't want to look back):
My StartDate text box after update is: Me.EndDate = Me.StartDate + 5
This works great. However, I added OldSoftBoss' calendar method to StartDate, so that it pops up a calendar, and the date selected in the calendar becomes the value of StartDate. However, doing it via this method, the EndDate box stays blank.
Thanks in advance! All help so far has been most useful! I'm learning a lot here!!!
My StartDate text box after update is: Me.EndDate = Me.StartDate + 5
This works great. However, I added OldSoftBoss' calendar method to StartDate, so that it pops up a calendar, and the date selected in the calendar becomes the value of StartDate. However, doing it via this method, the EndDate box stays blank.
Thanks in advance! All help so far has been most useful! I'm learning a lot here!!!
Mile-O
Back once again...
- Local time
- Today, 22:29
- Joined
- Dec 10, 2002
- Messages
- 11,305
retro said:
Set the EndDate's ControlSource to:
=DateAdd("d", 5, [StartDate])
I'd recommend calling your textboxes txtStartDate and txtEndDate and using naming conventions throughout - makes things easier to understand at a glance.
