selecting month

[Grant Nicholson]

i am working on a form with command buttons to select a month to list all
employees birthdays for the given months. the query works perfect when the
criteria is set to enter month using the expression
(DatePart("m",[DateOfBirth])). i don't want to have to enter the date but
simply pass the value from the button using the click event. the following
statement:

DoCmd.OpenReport "repEmployeeBirthdays", acViewPreview, , _
((DatePart("m", [tblPeakeEmployees.DateOfBirth])) = 1)

returns an error statement that "access cannot find field "|" referred to in
expression". i've removed the table reference and get the same error
message.

if i put the month number in the criteria section it returns the correct
values.

i am looking for a way to pass the month number on the click event of the
buttons instead of having to create 12 reports with 12 different queries.

any ideas?

 

[Douglas J Steele]

The WhereCondition needs to be in quotes (and you don't need the table
name):

DoCmd.OpenReport "repEmployeeBirthdays", acViewPreview, , _
"DatePart("m", [DateOfBirth]) = 1"

 

[Grant Nicholson]

i've tried this route and when i type it in just as you have, it gives me an
error, highlighting the 'm' in the statement "DatePart("m",[DateOfBirth]) =
1" saying 'expected end of statement'.

i've tried substituting ......."m" = 1 and get a type mismatch error and "m"
= "l" and get no filter.

The WhereCondition needs to be in quotes (and you don't need the table
name):

DoCmd.OpenReport "repEmployeeBirthdays", acViewPreview, , _
"DatePart("m", [DateOfBirth]) = 1"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

i am working on a form with command buttons to select a month to list all
employees birthdays for the given months. the query works perfect when
the
criteria is set to enter month using the expression
(DatePart("m",[DateOfBirth])). i don't want to have to enter the date but
simply pass the value from the button using the click event. the
following
statement:

DoCmd.OpenReport "repEmployeeBirthdays", acViewPreview, , _
((DatePart("m", [tblPeakeEmployees.DateOfBirth])) = 1)

returns an error statement that "access cannot find field "|" referred to in
expression". i've removed the table reference and get the same error
message.

if i put the month number in the criteria section it returns the correct
values.

i am looking for a way to pass the month number on the click event of the
buttons instead of having to create 12 reports with 12 different queries.

any ideas?

 

[Douglas J. Steele]

Sorry, my fault. Since the quotes around "m" are inside of quotes, you need
to use two quotes in a row:

DoCmd.OpenReport "repEmployeeBirthdays", acViewPreview, , _
"DatePart(""m"", [DateOfBirth]) = 1"

Alternatively, you could have used single quotes:

DoCmd.OpenReport "repEmployeeBirthdays", acViewPreview, , _
"DatePart('m', [DateOfBirth]) = 1"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

i've tried this route and when i type it in just as you have, it gives me
an error, highlighting the 'm' in the statement
"DatePart("m",[DateOfBirth]) = 1" saying 'expected end of statement'.

i've tried substituting ......."m" = 1 and get a type mismatch error and
"m" = "l" and get no filter.

The WhereCondition needs to be in quotes (and you don't need the table
name):

DoCmd.OpenReport "repEmployeeBirthdays", acViewPreview, , _
"DatePart("m", [DateOfBirth]) = 1"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

i am working on a form with command buttons to select a month to list
all
employees birthdays for the given months. the query works perfect when
the
criteria is set to enter month using the expression
(DatePart("m",[DateOfBirth])). i don't want to have to enter the date
but
simply pass the value from the button using the click event. the
following
statement:

DoCmd.OpenReport "repEmployeeBirthdays", acViewPreview, , _
((DatePart("m", [tblPeakeEmployees.DateOfBirth])) = 1)

returns an error statement that "access cannot find field "|" referred
to in
expression". i've removed the table reference and get the same error
message.

if i put the month number in the criteria section it returns the correct
values.

i am looking for a way to pass the month number on the click event of
the
buttons instead of having to create 12 reports with 12 different
queries.

any ideas?