Converting a Date Rage into a simple format

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

Guest

I am trying to create a query that will run a date range for an entire month.
To make it easier for users I want them to be able to just enter the # of the
month. For example, If they were to run a report for 09/01/05 to 09/30/05 all
they would need to do is enter "9" for the entire month. Can this be done by
converting the date range to just a month number?
 
In the Criteria row of query design, under your date field, enter:
Between DateSerial(Year(Date()), [WotMonth], 1) And
DateSerial(Year(Date()), [WotMonth] + 1, 0)

Choose Parameters on the Query menu.
In the dialog, enter:
WotMonth Integer

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

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

message
news:[email protected]...
 
Ok that worked perfectly! Thank you! Now I have one more question, when the
query is run and the dialog box comes up to enter the month # it says
"WotMonth". Can that be changed to something like "Enter Month"?

Allen Browne said:
In the Criteria row of query design, under your date field, enter:
Between DateSerial(Year(Date()), [WotMonth], 1) And
DateSerial(Year(Date()), [WotMonth] + 1, 0)

Choose Parameters on the Query menu.
In the dialog, enter:
WotMonth Integer

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

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

message
I am trying to create a query that will run a date range for an entire
month.
To make it easier for users I want them to be able to just enter the # of
the
month. For example, If they were to run a report for 09/01/05 to 09/30/05
all
they would need to do is enter "9" for the entire month. Can this be done
by
converting the date range to just a month number?
Click to expand...
Click to expand...
 
Yes. That is the text of the prompt, since you aren't Australian and your
machine won't understand 'Wot' ;-) Just keep it inside the '[ .... ]'.

Access will prompt for these unless they happen to correspond to a field
from a table or a control on your form.

--

Chaim


Secret Squirrel said:
Ok that worked perfectly! Thank you! Now I have one more question, when the
query is run and the dialog box comes up to enter the month # it says
"WotMonth". Can that be changed to something like "Enter Month"?

Allen Browne said:
In the Criteria row of query design, under your date field, enter:
Between DateSerial(Year(Date()), [WotMonth], 1) And
DateSerial(Year(Date()), [WotMonth] + 1, 0)

Choose Parameters on the Query menu.
In the dialog, enter:
WotMonth Integer

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

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

message
I am trying to create a query that will run a date range for an entire
month.
To make it easier for users I want them to be able to just enter the # of
the
month. For example, If they were to run a report for 09/01/05 to 09/30/05
all
they would need to do is enter "9" for the entire month. Can this be done
by
converting the date range to just a month number?
Click to expand...
Click to expand...
Click to expand...
 
LOL! :-)

Chaim is correct. WotMonth is just an example. Use any name you like there.

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

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

Chaim said:
Yes. That is the text of the prompt, since you aren't Australian and your
machine won't understand 'Wot' ;-) Just keep it inside the '[ .... ]'.

Access will prompt for these unless they happen to correspond to a field
from a table or a control on your form.

--

Chaim


Secret Squirrel said:
Ok that worked perfectly! Thank you! Now I have one more question, when the
query is run and the dialog box comes up to enter the month # it says
"WotMonth". Can that be changed to something like "Enter Month"?

Allen Browne said:
In the Criteria row of query design, under your date field, enter:
Between DateSerial(Year(Date()), [WotMonth], 1) And
DateSerial(Year(Date()), [WotMonth] + 1, 0)

Choose Parameters on the Query menu.
In the dialog, enter:
WotMonth Integer


message
I am trying to create a query that will run a date range for an entire
month.
To make it easier for users I want them to be able to just enter the
# of
the
month. For example, If they were to run a report for 09/01/05 to 09/30/05
all
they would need to do is enter "9" for the entire month. Can this be done
by
converting the date range to just a month number?
Click to expand...
Click to expand...
Click to expand...
 
How would I do this if I wanted a starting and ending month. For example,
what if I wanted to select more than one month? Can it prompt me to enter a
beginning month and ending month?

Allen Browne said:
LOL! :-)

Chaim is correct. WotMonth is just an example. Use any name you like there.

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

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

Chaim said:
Yes. That is the text of the prompt, since you aren't Australian and your
machine won't understand 'Wot' ;-) Just keep it inside the '[ .... ]'.

Access will prompt for these unless they happen to correspond to a field
from a table or a control on your form.

--

Chaim


Secret Squirrel said:
Ok that worked perfectly! Thank you! Now I have one more question, when the
query is run and the dialog box comes up to enter the month # it says
"WotMonth". Can that be changed to something like "Enter Month"?

:

In the Criteria row of query design, under your date field, enter:
Between DateSerial(Year(Date()), [WotMonth], 1) And
DateSerial(Year(Date()), [WotMonth] + 1, 0)

Choose Parameters on the Query menu.
In the dialog, enter:
WotMonth Integer


message
I am trying to create a query that will run a date range for an entire
month.
To make it easier for users I want them to be able to just enter the
# of
the
month. For example, If they were to run a report for 09/01/05 to 09/30/05
all
they would need to do is enter "9" for the entire month. Can this be done
by
converting the date range to just a month number?
Click to expand...
Click to expand...
Click to expand...
 
Sure. You can use 2 variables, but you are going to get some pretty deeply
embedded IIF() statements trying to figure out which year goes with which
date over Dec - Jan for example.

Probably easier to have a form where the user enters the dates, as explained
in:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

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

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

message
How would I do this if I wanted a starting and ending month. For example,
what if I wanted to select more than one month? Can it prompt me to enter
a
beginning month and ending month?

Allen Browne said:
LOL! :-)

Chaim is correct. WotMonth is just an example. Use any name you like
there.

Chaim said:
Yes. That is the text of the prompt, since you aren't Australian and
your
machine won't understand 'Wot' ;-) Just keep it inside the '[ .... ]'.

Access will prompt for these unless they happen to correspond to a
field
from a table or a control on your form.


message Ok that worked perfectly! Thank you! Now I have one more question,
when
the
query is run and the dialog box comes up to enter the month # it says
"WotMonth". Can that be changed to something like "Enter Month"?

:

In the Criteria row of query design, under your date field, enter:
Between DateSerial(Year(Date()), [WotMonth], 1) And
DateSerial(Year(Date()), [WotMonth] + 1, 0)

Choose Parameters on the Query menu.
In the dialog, enter:
WotMonth Integer


in
message
I am trying to create a query that will run a date range for an
entire
month.
To make it easier for users I want them to be able to just enter
the
#
of
the
month. For example, If they were to run a report for 09/01/05 to
09/30/05
all
they would need to do is enter "9" for the entire month. Can this
be
done
by
converting the date range to just a month number?
Click to expand...
Click to expand...
Click to expand...
 
The years are kept in different tables so they won't be able to run mix year
queries. This would only be for one year at a time. Can you show me how this
could be done?

Allen Browne said:
Sure. You can use 2 variables, but you are going to get some pretty deeply
embedded IIF() statements trying to figure out which year goes with which
date over Dec - Jan for example.

Probably easier to have a form where the user enters the dates, as explained
in:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

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

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

message
How would I do this if I wanted a starting and ending month. For example,
what if I wanted to select more than one month? Can it prompt me to enter
a
beginning month and ending month?

Allen Browne said:
LOL! :-)

Chaim is correct. WotMonth is just an example. Use any name you like
there.

Yes. That is the text of the prompt, since you aren't Australian and
your
machine won't understand 'Wot' ;-) Just keep it inside the '[ .... ]'.

Access will prompt for these unless they happen to correspond to a
field
from a table or a control on your form.


message Ok that worked perfectly! Thank you! Now I have one more question,
when
the
query is run and the dialog box comes up to enter the month # it says
"WotMonth". Can that be changed to something like "Enter Month"?

:

In the Criteria row of query design, under your date field, enter:
Between DateSerial(Year(Date()), [WotMonth], 1) And
DateSerial(Year(Date()), [WotMonth] + 1, 0)

Choose Parameters on the Query menu.
In the dialog, enter:
WotMonth Integer


in
message
I am trying to create a query that will run a date range for an
entire
month.
To make it easier for users I want them to be able to just enter
the
#
of
the
month. For example, If they were to run a report for 09/01/05 to
09/30/05
all
they would need to do is enter "9" for the entire month. Can this
be
done
by
converting the date range to just a month number?
Click to expand...
Click to expand...
Click to expand...
 
Go back to the first example.

You see that we used WotMonth twice.

Use 2 different names.

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

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

message
The years are kept in different tables so they won't be able to run mix
year
queries. This would only be for one year at a time. Can you show me how
this
could be done?

Allen Browne said:
Sure. You can use 2 variables, but you are going to get some pretty
deeply
embedded IIF() statements trying to figure out which year goes with which
date over Dec - Jan for example.

Probably easier to have a form where the user enters the dates, as
explained
in:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

message
How would I do this if I wanted a starting and ending month. For
example,
what if I wanted to select more than one month? Can it prompt me to
enter
a
beginning month and ending month?

:

LOL! :-)

Chaim is correct. WotMonth is just an example. Use any name you like
there.

Yes. That is the text of the prompt, since you aren't Australian
and
your
machine won't understand 'Wot' ;-) Just keep it inside the '[
.... ]'.

Access will prompt for these unless they happen to correspond to a
field
from a table or a control on your form.


in
message Ok that worked perfectly! Thank you! Now I have one more question,
when
the
query is run and the dialog box comes up to enter the month # it
says
"WotMonth". Can that be changed to something like "Enter Month"?

:

In the Criteria row of query design, under your date field,
enter:
Between DateSerial(Year(Date()), [WotMonth], 1) And
DateSerial(Year(Date()), [WotMonth] + 1, 0)

Choose Parameters on the Query menu.
In the dialog, enter:
WotMonth Integer


"Secret Squirrel" <[email protected]>
wrote
in
message
I am trying to create a query that will run a date range for an
entire
month.
To make it easier for users I want them to be able to just
enter
the
#
of
the
month. For example, If they were to run a report for 09/01/05
to
09/30/05
all
they would need to do is enter "9" for the entire month. Can
this
be
done
by
converting the date range to just a month number?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top