selecting month

  • Thread starter Thread starter Grant Nicholson
  • Start date Start date
G

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?
 
The WhereCondition needs to be in quotes (and you don't need the table
name):

DoCmd.OpenReport "repEmployeeBirthdays", acViewPreview, , _
"DatePart("m", [DateOfBirth]) = 1"
 
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.

Douglas J Steele said:
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!)


Grant Nicholson said:
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?
Click to expand...
Click to expand...
 
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!)



Grant Nicholson said:
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.

Douglas J Steele said:
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!)


Grant Nicholson said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top