DatePart Criteria in Query

  • Thread starter Thread starter Thomas Troutwine
  • Start date Start date
T

Thomas Troutwine

I'm running Windows 2000 and Office 2000 and I want to show only data from a
certain month in a report.
In my (design view) query criteria I tried
DatePart("mm",[Date]) = [Month]
with no luck, error said "too complicated"
I've tried help and "Access Bible" and a lot of guessing to no avail.
Can you show me what I need to do? Thanks
 
I am making several assumptions here. I assume that field [Month] is a field
in your table that contains a 2 digit representation of the month. I also
assume that your [Date] is meant to be the current date. If these
assumptions are correct then you should have as criteria for the field Month
this --
DatePart("mm",Date())

If you are storing January as 1 and not 01 then you need this --
DatePart("m",Date())
 
Sorry. I knew after I posted that I should have included more info.
I intended for [Month] to cause a prompt for a 2 digit representation of the
month desired (to be entered by the user at that time) and my [Date] is the
field containing the date of each record in my database.
I am using the Between [Beginning Date] and [Ending Date] currently and it
works but am trying to tweak it to be a little easier for the user.
I hope this is clearer. Thanks for the response. Can it be done the way I
am trying to change it to?
Tom T
KARL DEWEY said:
I am making several assumptions here. I assume that field [Month] is a field
in your table that contains a 2 digit representation of the month. I also
assume that your [Date] is meant to be the current date. If these
assumptions are correct then you should have as criteria for the field Month
this --
DatePart("mm",Date())

If you are storing January as 1 and not 01 then you need this --
DatePart("m",Date())

Thomas Troutwine said:
I'm running Windows 2000 and Office 2000 and I want to show only data from a
certain month in a report.
In my (design view) query criteria I tried
DatePart("mm",[Date]) = [Month]
with no luck, error said "too complicated"
I've tried help and "Access Bible" and a lot of guessing to no avail.
Can you show me what I need to do? Thanks
Click to expand...
Click to expand...
 
Do you want the data for the month for all the years in your database? Or
do you want the data for a specific month and year?

If you want a specific month and year then the following should do that and
it will take advantage of any index you have the field to give you a faster
search. Make sure your prompts are exactly the same in the two places or
you will get prompted for each variant of the prompt (Watch out for extra
spaces)

Field: [Date]
Criteria: Between DateSerial([What Year?],[What Month Number?],1) and
DateSerial([What Year?],[What Month Number?]+1,0)

Thomas Troutwine said:
Sorry. I knew after I posted that I should have included more info.
I intended for [Month] to cause a prompt for a 2 digit representation of
the
month desired (to be entered by the user at that time) and my [Date] is
the
field containing the date of each record in my database.
I am using the Between [Beginning Date] and [Ending Date] currently and it
works but am trying to tweak it to be a little easier for the user.
I hope this is clearer. Thanks for the response. Can it be done the way I
am trying to change it to?
Tom T
KARL DEWEY said:
I am making several assumptions here. I assume that field [Month] is a field
in your table that contains a 2 digit representation of the month. I
also
assume that your [Date] is meant to be the current date. If these
assumptions are correct then you should have as criteria for the field Month
this --
DatePart("mm",Date())

If you are storing January as 1 and not 01 then you need this --
DatePart("m",Date())

Thomas Troutwine said:
I'm running Windows 2000 and Office 2000 and I want to show only data from a
certain month in a report.
In my (design view) query criteria I tried
DatePart("mm",[Date]) = [Month]
with no luck, error said "too complicated"
I've tried help and "Access Bible" and a lot of guessing to no avail.
Can you show me what I need to do? Thanks
Click to expand...
Click to expand...
Click to expand...
 
John,
Thank you. I replaced the [What Year?] with the current year (I can change
that each year) and it cuts way down on the client input time. This does
just what I wanted. Thanks again. Tom T

John Spencer said:
Do you want the data for the month for all the years in your database? Or
do you want the data for a specific month and year?

If you want a specific month and year then the following should do that and
it will take advantage of any index you have the field to give you a faster
search. Make sure your prompts are exactly the same in the two places or
you will get prompted for each variant of the prompt (Watch out for extra
spaces)

Field: [Date]
Criteria: Between DateSerial([What Year?],[What Month Number?],1) and
DateSerial([What Year?],[What Month Number?]+1,0)

Thomas Troutwine said:
Sorry. I knew after I posted that I should have included more info.
I intended for [Month] to cause a prompt for a 2 digit representation of
the
month desired (to be entered by the user at that time) and my [Date] is
the
field containing the date of each record in my database.
I am using the Between [Beginning Date] and [Ending Date] currently and it
works but am trying to tweak it to be a little easier for the user.
I hope this is clearer. Thanks for the response. Can it be done the way I
am trying to change it to?
Tom T
KARL DEWEY said:
I am making several assumptions here. I assume that field [Month] is a field
in your table that contains a 2 digit representation of the month. I
also
assume that your [Date] is meant to be the current date. If these
assumptions are correct then you should have as criteria for the field Month
this --
DatePart("mm",Date())

If you are storing January as 1 and not 01 then you need this --
DatePart("m",Date())

:

I'm running Windows 2000 and Office 2000 and I want to show only data from a
certain month in a report.
In my (design view) query criteria I tried
DatePart("mm",[Date]) = [Month]
with no luck, error said "too complicated"
I've tried help and "Access Bible" and a lot of guessing to no avail.
Can you show me what I need to do? Thanks
Click to expand...
Click to expand...
Click to expand...
 
IF it is always the "current year" you might be able to use
Year(Date())

Between DateSerial(Year(Date()),[What Month Number?],1) and
DateSerial(Year(Date()),What Month Number?]+1,0)

That may or may not work for you depending on your requirements.

Thomas Troutwine said:
John,
Thank you. I replaced the [What Year?] with the current year (I can
change
that each year) and it cuts way down on the client input time. This does
just what I wanted. Thanks again. Tom T

John Spencer said:
Do you want the data for the month for all the years in your database?
Or
do you want the data for a specific month and year?

If you want a specific month and year then the following should do that and
it will take advantage of any index you have the field to give you a faster
search. Make sure your prompts are exactly the same in the two places or
you will get prompted for each variant of the prompt (Watch out for
extra
spaces)

Field: [Date]
Criteria: Between DateSerial([What Year?],[What Month Number?],1) and
DateSerial([What Year?],[What Month Number?]+1,0)

Thomas Troutwine said:
Sorry. I knew after I posted that I should have included more info.
I intended for [Month] to cause a prompt for a 2 digit representation
of
the
month desired (to be entered by the user at that time) and my [Date] is
the
field containing the date of each record in my database.
I am using the Between [Beginning Date] and [Ending Date] currently and it
works but am trying to tweak it to be a little easier for the user.
I hope this is clearer. Thanks for the response. Can it be done the
way I
am trying to change it to?
Tom T
I am making several assumptions here. I assume that field [Month] is
a
field
in your table that contains a 2 digit representation of the month. I
also
assume that your [Date] is meant to be the current date. If these
assumptions are correct then you should have as criteria for the field
Month
this --
DatePart("mm",Date())

If you are storing January as 1 and not 01 then you need this --
DatePart("m",Date())

:

I'm running Windows 2000 and Office 2000 and I want to show only
data
from a
certain month in a report.
In my (design view) query criteria I tried
DatePart("mm",[Date]) = [Month]
with no luck, error said "too complicated"
I've tried help and "Access Bible" and a lot of guessing to no
avail.
Can you show me what I need to do? Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top