PARAMETER

[Debbie]

HOW DO I SET PARAMETERS FOR A BIRTHDAY QUERY. I NEED IT TO PULL OUT
ALL STAFF THAT HAVE A BIRTHDAY IN JANUARY, FOR EXAMPLE. SOMETIMES I
HAVE TO HAVE A TWO MONTH REPORT. CAN A PARAMETER WORK FOR THIS TYPE OF
INFORMATION. THE FIELD IS THE STAFF'S DATE OF BIRTH

 

[JJEWELL]

Try typing the following In the criteria of the field:

Between [Enter start date] and [enter end date]

The query should then prompt you to enter the start and end dates your care
about when it runs.

 

[Debbie]

I tried that but because it has actual birth date it pulls nobody in to
query because it is looking for */*/05 dates, therefore, all dates are
previous years

 

[Debbie]

how would i do a parameter using BETWEEN and LIKE

 

[Marshall Barton]

HOW DO I SET PARAMETERS FOR A BIRTHDAY QUERY. I NEED IT TO PULL OUT
ALL STAFF THAT HAVE A BIRTHDAY IN JANUARY, FOR EXAMPLE. SOMETIMES I
HAVE TO HAVE A TWO MONTH REPORT. CAN A PARAMETER WORK FOR THIS TYPE OF
INFORMATION. THE FIELD IS THE STAFF'S DATE OF BIRTH

Caps Lock key still broken ;-)

Under the calculated field Month(DOB) use the criteria:
[Enter month number]

 

[John Spencer]

Calculate the birthday using the following:

Add a column to calculate the birthdate for this year
DateSerial(Year(Date()),Month([DateOfBirth]),Day ([DateOfBirth]))

Set the criteria for this Birthday column to:
Between CDate([Date Start]) and CDate([Date End])

That will work fine for birthdays in any date range as long as you don't go
from Dec to Jan. If you do that you could add

OR
Between DateAdd("yyyy",-1,CDate([Date Start])) and
DateAdd("yyyy",-1,CDate([Date End]))

P.S. PLEASE turn off the all caps. It is difficult to read. As a matter of
fact, I almost passed this request over just for that reason.

 

[Debbie]

Okay, my caps are off. Thanks. What you suggested works great if I just
want 1 month displayed. What if I want 2 or more months in this
report. Can that be done also.

 

[Debbie]

John,
What does CDate stand for? Still trying to get it to work. I am just
a beginner.

 

[John Spencer]

CDate means convert this to a date. I was expecting you to enter something
like 12/1/2005 and 12/31/2005

If it is not working, then what error(s) are you getting?

By the way, thanks for turning off the caps. It is a lot easier to read you
request.

 

[John Spencer]

CDate stands for Convert to Date. I was just attempting to force any entry
to be a date. I assumed you would be entering 12/1/2005 to 12/31/2005.

If it is not working, then what error messages are you getting?

 

[Marshall Barton]

Okay, my caps are off. Thanks. What you suggested works great if I just
want 1 month displayed. What if I want 2 or more months in this
report. Can that be done also.

Set the criteria to:

Between [Enter Start Month] And [Enter End Month]

 

[John Vinson]

HOW DO I SET PARAMETERS FOR A BIRTHDAY QUERY. I NEED IT TO PULL OUT
ALL STAFF THAT HAVE A BIRTHDAY IN JANUARY, FOR EXAMPLE. SOMETIMES I
HAVE TO HAVE A TWO MONTH REPORT. CAN A PARAMETER WORK FOR THIS TYPE OF
INFORMATION. THE FIELD IS THE STAFF'S DATE OF BIRTH

Typing in all caps is hard to read, and considered impolite: it looks
like you are SHOUTING AT US. It won't get you a quicker or a better
answer.

One way to do this is to put a calculated field in your Query, getting
the next birthday anniversary for each entry. You can do this by
typing

HappyHappy: DateSerial(Year(Date()) + IIF(Format([DOB], "mmdd") <
Format(Date()), "mmdd"), 1, 0), Month([DOB]), Day([DOB]))

in a vacant Field cell. A criterion on this field of

BETWEEN Date() AND DateAdd("d", 30, Date())

will get all birthdays coming up in the next thirty days. Any other
date criteria you like will work as well.

John W. Vinson[MVP]

 

[Debbie]

John,

I copied, changed field name and I get an error stating "the expression
you entered contains invalid syntax or you need to enclose your text
data in quotes. It shows highlight around the 0),Month if this
helps.

HappyHappy: DateSerial(Year(Date()) + IIF(Format([Birth Date], "mmdd")
< Format(Date()), "mmdd"),1,0),Month([Birth Date]), Day([Birth Date]))

I apologize about CAPS. I did not know. I guarantee I will not do
that again.

 

[Debbie]

John,

This expression is typed incorrectly, or it is too complex to be
evaluated. For example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning parts
of the expression to variables. (Error 3071)

I did enter 12/1/05 to 12/31/05

 

[Debbie]

With the help you have given me, so far I have been able to sort the
dates properly, give a value of 1 through 12.

I can enter this criteria: >"1" And <"9"and it works great; however,
this does not recognize >"1" And =<"12". Also it does not recognize
10, 11, or 12 if I just type >"3". So I guess I am still looking for
help for the criteria and parameter.

If I put this into criteria >="01**" And <="12**" for field:
Format([Birth Date],"mmdd"), it recognizes all but "12


If I type >="01**" And <="1231" then it works great. Now with this,
can someone help me with a parameter that will except these entries as
such?

HELP!

Thanks,Debbie

 

[Douglas J. Steele]

John had a slight typo there. It should be:

HappyHappy: DateSerial(Year(Date()) + IIF(Format([Birth Date], "mmdd")
< Format(Date(), "mmdd"),1,0),Month([Birth Date]), Day([Birth Date]))

 

[Douglas J. Steele]

You can't use wildcards (*) with inequalities: they only work with the Like
operator.

I think Marshall's solution is what you're looking for.

 

[John Vinson]

I copied, changed field name and I get an error stating "the expression
you entered contains invalid syntax or you need to enclose your text
data in quotes. It shows highlight around the 0),Month if this
helps.

Sorry: typo on my part, an extra parenthesis. Try

HappyHappy: DateSerial(Year(Date()) +
IIF(Format([Birth Date], "mmdd") < Format(Date(), "mmdd"),1,0),
Month([Birth Date]), Day([Birth Date]))


John W. Vinson[MVP]

 

[Debbie]

Thank you John V., Marshall's was correct, I just needed to type
criteria in different field criteria. Thank you so much. Problem
solved. Thanks to all of you. Each one had a piece of the answer.
This is great. It sure saves alot of time for us beginners but yet we
are learning a lot for our next project. Again, thank you all!