2 combo boxes

[cager]

I have a query that when run shows 2 fields. Week begin dates (always the
Monday date) and week end dates (always the Friday date).

I want to link one combo box to the week begin date and depending on which
Monday date I chose I want the 2nd combo box to automatically pull the
corresponding Friday date for that week.

I'm not too good with SQL so any help would be greatly appreciated.

These 2 dates would then be referenced in my main query and that query would
run based on those 2 dates in the combo boxes on the form.

Thanks in advance for you help.

 

[Steve]

When you choose a Monday date, you can always get the Friday date by adding
4 to the Monday date. You can use this fact in your situation and eliminate
the second combobox.

Assuming your form name is MyForm and the name of the first combobox is
MyMondayDate, put the following criteria in the MondayDate field of your
masin query:
Forms!MyForm!MyMondayDate

and put the following criteria in the FridayDate field of your main query:
DateAdd("d",4,Forms!MyForm!MyMondayDate)

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]

 

[cager]

Steve,

Thanks for the quick reply. What if in my main query I don't have a Monday
date and a Friday date field. All I have is an entry date field which are
dates for all business days (monday through friday).

So what I was going to do was use two combo boxes (hiding one on the form)
and use in the main query [entrydt] between Forms!MyForm!MyMondayDate and
Forms!MyForm!MyFridayDate.

Is there a way I can use only one combo boxe and have the [entrydt] run all
dates between the monday date on the form and the friday date of that week
without having a monday date and friday date field in the main query?

 

[cager]

or could i do this in in the criteria of the [entrydt] field of my query
using one combo box:

between Forms!MyForm!MyMondayDate and DateAdd("d",4,Forms!MyForm!MyMondayDate)

which would run the query for all entry dates between the monday date and
the Friday date of the same week?

Is this correct?

 

[Steve]

Yes, that will work. Congratulations to you for coming up with that!!!

Have you considered using a Calendar control for selecting the EntryDt?


PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]

or could i do this in in the criteria of the [entrydt] field of my query
using one combo box:

between Forms!MyForm!MyMondayDate and
DateAdd("d",4,Forms!MyForm!MyMondayDate)

which would run the query for all entry dates between the monday date and
the Friday date of the same week?

Is this correct?

When you choose a Monday date, you can always get the Friday date by
adding
4 to the Monday date. You can use this fact in your situation and
eliminate
the second combobox.

Assuming your form name is MyForm and the name of the first combobox is
MyMondayDate, put the following criteria in the MondayDate field of your
masin query:
Forms!MyForm!MyMondayDate

and put the following criteria in the FridayDate field of your main
query:
DateAdd("d",4,Forms!MyForm!MyMondayDate)

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]

 

[Douglas J. Steele]

Yes, you could do that. Note, though, that if your date field actually
contains date and time (such as it will if you're using the Now() function
to populate it), you'll need

between Forms!MyForm!MyMondayDate and
DateAdd("d",5,Forms!MyForm!MyMondayDate)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

or could i do this in in the criteria of the [entrydt] field of my query
using one combo box:

between Forms!MyForm!MyMondayDate and
DateAdd("d",4,Forms!MyForm!MyMondayDate)

which would run the query for all entry dates between the monday date and
the Friday date of the same week?

Is this correct?

When you choose a Monday date, you can always get the Friday date by
adding
4 to the Monday date. You can use this fact in your situation and
eliminate
the second combobox.

Assuming your form name is MyForm and the name of the first combobox is
MyMondayDate, put the following criteria in the MondayDate field of your
masin query:
Forms!MyForm!MyMondayDate

and put the following criteria in the FridayDate field of your main
query:
DateAdd("d",4,Forms!MyForm!MyMondayDate)

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]

 

[cager]

Steve,

I haven't considered using a calendar control. Would you mind expanding on
that since I'm not sure what that entails?

Douglas: My [entrydt] field only contains date. No times are listed.

Yes, you could do that. Note, though, that if your date field actually
contains date and time (such as it will if you're using the Now() function
to populate it), you'll need

between Forms!MyForm!MyMondayDate and
DateAdd("d",5,Forms!MyForm!MyMondayDate)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

or could i do this in in the criteria of the [entrydt] field of my query
using one combo box:

between Forms!MyForm!MyMondayDate and
DateAdd("d",4,Forms!MyForm!MyMondayDate)

which would run the query for all entry dates between the monday date and
the Friday date of the same week?

Is this correct?

When you choose a Monday date, you can always get the Friday date by
adding
4 to the Monday date. You can use this fact in your situation and
eliminate
the second combobox.

Assuming your form name is MyForm and the name of the first combobox is
MyMondayDate, put the following criteria in the MondayDate field of your
masin query:
Forms!MyForm!MyMondayDate

and put the following criteria in the FridayDate field of your main
query:
DateAdd("d",4,Forms!MyForm!MyMondayDate)

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]



I have a query that when run shows 2 fields. Week begin dates (always
the
Monday date) and week end dates (always the Friday date).

I want to link one combo box to the week begin date and depending on
which
Monday date I chose I want the 2nd combo box to automatically pull the
corresponding Friday date for that week.

I'm not too good with SQL so any help would be greatly appreciated.

These 2 dates would then be referenced in my main query and that query
would
run based on those 2 dates in the combo boxes on the form.

Thanks in advance for you help.

 

[Steve]

Open the Toolbox and select More Controls (button with hammer and wrench). A
list of more controls appears. Select the Calendar Control. Draw the
calendar on your form just like you would a listbox. Open properties and
name the calendar MyCalendar. Delete the combobox and add a textbox named
MyMondayDate. Go to the code window and type in the following subroutine:
Private Sub MyCalendar_Click()
Me!MyMondayDate = Me!MyCalendar.Value
End Sub

When you click on a date on the calendar, the date will be entered in the
MyMondayDate textbox.

Note: You might want to add additional code to check thata Monday was
clicked on the calendar.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]

Steve,

I haven't considered using a calendar control. Would you mind expanding
on
that since I'm not sure what that entails?

Douglas: My [entrydt] field only contains date. No times are listed.

Yes, you could do that. Note, though, that if your date field actually
contains date and time (such as it will if you're using the Now()
function
to populate it), you'll need

between Forms!MyForm!MyMondayDate and
DateAdd("d",5,Forms!MyForm!MyMondayDate)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

or could i do this in in the criteria of the [entrydt] field of my
query
using one combo box:

between Forms!MyForm!MyMondayDate and
DateAdd("d",4,Forms!MyForm!MyMondayDate)

which would run the query for all entry dates between the monday date
and
the Friday date of the same week?

Is this correct?

:

When you choose a Monday date, you can always get the Friday date by
adding
4 to the Monday date. You can use this fact in your situation and
eliminate
the second combobox.

Assuming your form name is MyForm and the name of the first combobox
is
MyMondayDate, put the following criteria in the MondayDate field of
your
masin query:
Forms!MyForm!MyMondayDate

and put the following criteria in the FridayDate field of your main
query:
DateAdd("d",4,Forms!MyForm!MyMondayDate)

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]



I have a query that when run shows 2 fields. Week begin dates (always
the
Monday date) and week end dates (always the Friday date).

I want to link one combo box to the week begin date and depending on
which
Monday date I chose I want the 2nd combo box to automatically pull
the
corresponding Friday date for that week.

I'm not too good with SQL so any help would be greatly appreciated.

These 2 dates would then be referenced in my main query and that
query
would
run based on those 2 dates in the combo boxes on the form.

Thanks in advance for you help.

 

[cager]

Thanks Steve. You have been utmost helpful.

One more question for you. You mention adding in code. I do agree but I'm
a bit green (actually a lot green) around the ears when it comes to writing
code.

I'm guessing there is some code that will ensure that the user picks a
Monday date and if he/she doesn't then a messag would appear telling the user
that a Monday date must be picked.

Can you help with that coding?

Open the Toolbox and select More Controls (button with hammer and wrench). A
list of more controls appears. Select the Calendar Control. Draw the
calendar on your form just like you would a listbox. Open properties and
name the calendar MyCalendar. Delete the combobox and add a textbox named
MyMondayDate. Go to the code window and type in the following subroutine:
Private Sub MyCalendar_Click()
Me!MyMondayDate = Me!MyCalendar.Value
End Sub

When you click on a date on the calendar, the date will be entered in the
MyMondayDate textbox.

Note: You might want to add additional code to check thata Monday was
clicked on the calendar.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]

Steve,

I haven't considered using a calendar control. Would you mind expanding
on
that since I'm not sure what that entails?

Douglas: My [entrydt] field only contains date. No times are listed.

Yes, you could do that. Note, though, that if your date field actually
contains date and time (such as it will if you're using the Now()
function
to populate it), you'll need

between Forms!MyForm!MyMondayDate and
DateAdd("d",5,Forms!MyForm!MyMondayDate)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


or could i do this in in the criteria of the [entrydt] field of my
query
using one combo box:

between Forms!MyForm!MyMondayDate and
DateAdd("d",4,Forms!MyForm!MyMondayDate)

which would run the query for all entry dates between the monday date
and
the Friday date of the same week?

Is this correct?

:

When you choose a Monday date, you can always get the Friday date by
adding
4 to the Monday date. You can use this fact in your situation and
eliminate
the second combobox.

Assuming your form name is MyForm and the name of the first combobox
is
MyMondayDate, put the following criteria in the MondayDate field of
your
masin query:
Forms!MyForm!MyMondayDate

and put the following criteria in the FridayDate field of your main
query:
DateAdd("d",4,Forms!MyForm!MyMondayDate)

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]



I have a query that when run shows 2 fields. Week begin dates (always
the
Monday date) and week end dates (always the Friday date).

I want to link one combo box to the week begin date and depending on
which
Monday date I chose I want the 2nd combo box to automatically pull
the
corresponding Friday date for that week.

I'm not too good with SQL so any help would be greatly appreciated.

These 2 dates would then be referenced in my main query and that
query
would
run based on those 2 dates in the combo boxes on the form.

Thanks in advance for you help.

 

[John Marshall, MVP]

He probably could, but unlike the rest of the participants of this
newsgroup, he would want to get paid.

John... Visio MVP

 

[Steve]

Private Sub MyCalendar_Click()
If WeekDay(Me!MyCalendar.Value) <> VbMonday Then
MsgBox "Pick a Monday"
Else
Me!MyMondayDate = Me!MyCalendar.Value
End If
End Sub

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]

Thanks Steve. You have been utmost helpful.

One more question for you. You mention adding in code. I do agree but
I'm
a bit green (actually a lot green) around the ears when it comes to
writing
code.

I'm guessing there is some code that will ensure that the user picks a
Monday date and if he/she doesn't then a messag would appear telling the
user
that a Monday date must be picked.

Can you help with that coding?

Open the Toolbox and select More Controls (button with hammer and
wrench). A
list of more controls appears. Select the Calendar Control. Draw the
calendar on your form just like you would a listbox. Open properties and
name the calendar MyCalendar. Delete the combobox and add a textbox named
MyMondayDate. Go to the code window and type in the following subroutine:
Private Sub MyCalendar_Click()
Me!MyMondayDate = Me!MyCalendar.Value
End Sub

When you click on a date on the calendar, the date will be entered in the
MyMondayDate textbox.

Note: You might want to add additional code to check thata Monday was
clicked on the calendar.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]

Steve,

I haven't considered using a calendar control. Would you mind
expanding
on
that since I'm not sure what that entails?

Douglas: My [entrydt] field only contains date. No times are listed.

:

Yes, you could do that. Note, though, that if your date field actually
contains date and time (such as it will if you're using the Now()
function
to populate it), you'll need

between Forms!MyForm!MyMondayDate and
DateAdd("d",5,Forms!MyForm!MyMondayDate)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


or could i do this in in the criteria of the [entrydt] field of my
query
using one combo box:

between Forms!MyForm!MyMondayDate and
DateAdd("d",4,Forms!MyForm!MyMondayDate)

which would run the query for all entry dates between the monday
date
and
the Friday date of the same week?

Is this correct?

:

When you choose a Monday date, you can always get the Friday date
by
adding
4 to the Monday date. You can use this fact in your situation and
eliminate
the second combobox.

Assuming your form name is MyForm and the name of the first
combobox
is
MyMondayDate, put the following criteria in the MondayDate field of
your
masin query:
Forms!MyForm!MyMondayDate

and put the following criteria in the FridayDate field of your main
query:
DateAdd("d",4,Forms!MyForm!MyMondayDate)

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
[email protected]



I have a query that when run shows 2 fields. Week begin dates
(always
the
Monday date) and week end dates (always the Friday date).

I want to link one combo box to the week begin date and depending
on
which
Monday date I chose I want the 2nd combo box to automatically
pull
the
corresponding Friday date for that week.

I'm not too good with SQL so any help would be greatly
appreciated.

These 2 dates would then be referenced in my main query and that
query
would
run based on those 2 dates in the combo boxes on the form.

Thanks in advance for you help.