Expressions

  • Thread starter Thread starter Cindy
  • Start date Start date
C

Cindy

I am trying to have my query take a participants birthdate, calculate how old
they are, and then tell me the first day of the month they will turn age 62.
I keep getting a syntax error but I'm not sure how to say "if then"
expressions. Here's what I have so far. Thanks for any input!

=DATEDIFF("d"[DOB],[DATE])/"362",when=62,then «Expr» FirstDayOfMonth (
[prt_birth_date] )
 
Several things wrong --
DATEDIFF("d"[DOB],[DATE]) must be this =DateDiff("d",[DOB],Date())

Next if you use quotes - /"362" then it is text. A number can not be
divided by text as the is like trying to devide 325 by Orange.

A year is 265.25 days long.
DateDiff("d",[DOB],Date())/365.25

WHEN is SQL language and not a function.
Expr1: IIf(DateDiff("d",[DOB],Date())/365.25>=62,Format([DOB],"mmmm ") &
Format([DOB],"yyyy")+62,"")

I do not know what you [prt_birth_date] data is for.
 
The date they turn 62 is
DateAdd("yyyy",62,DOB)

The first day of the month they turn 62 is.
DateSerial(Year(DateAdd("yyyy",62,DOB)),Month(DateAdd("yyyy",62,DOB)),1)



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Karl, I'm trying your's first and I've entered the string just as you have
it. It works OK except when I try to run it, it keeps asking me to enter a
parameter value DOB. No matter what date I use or the format I type it in,
it give me an error and tells me the expression is typed incorrectly. Any
ideas?

KARL DEWEY said:
Several things wrong --
DATEDIFF("d"[DOB],[DATE]) must be this =DateDiff("d",[DOB],Date())

Next if you use quotes - /"362" then it is text. A number can not be
divided by text as the is like trying to devide 325 by Orange.

A year is 265.25 days long.
DateDiff("d",[DOB],Date())/365.25

WHEN is SQL language and not a function.
Expr1: IIf(DateDiff("d",[DOB],Date())/365.25>=62,Format([DOB],"mmmm ") &
Format([DOB],"yyyy")+62,"")

I do not know what you [prt_birth_date] data is for.
--
KARL DEWEY
Build a little - Test a little


Cindy said:
I am trying to have my query take a participants birthdate, calculate how old
they are, and then tell me the first day of the month they will turn age 62.
I keep getting a syntax error but I'm not sure how to say "if then"
expressions. Here's what I have so far. Thanks for any input!

=DATEDIFF("d"[DOB],[DATE])/"362",when=62,then «Expr» FirstDayOfMonth (
[prt_birth_date] )
Click to expand...
Click to expand...
 
John, I got a little further with your expression. It let me run it but also
asked me for a DOB and then gave me a blank query with no information. The
birthdates are already in an existing table and it should calculate from
those, correct? I don't understand why it's asking me for a DOB, I could
understand if it were asking me for a current date to work off of. Still
Confused, Cindy

John Spencer said:
The date they turn 62 is
DateAdd("yyyy",62,DOB)

The first day of the month they turn 62 is.
DateSerial(Year(DateAdd("yyyy",62,DOB)),Month(DateAdd("yyyy",62,DOB)),1)



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
I am trying to have my query take a participants birthdate, calculate how old
they are, and then tell me the first day of the month they will turn age 62.
I keep getting a syntax error but I'm not sure how to say "if then"
expressions. Here's what I have so far. Thanks for any input!

=DATEDIFF("d"[DOB],[DATE])/"362",when=62,then «Expr» FirstDayOfMonth (
[prt_birth_date] )
Click to expand...
Click to expand...
 
I assumed that DOB was a DateTime field you stored their 'date of birth' in.
I t is apparently seeing your responce to the prompt as text and therefore
can not perform the date functions.
If you edit [DOB] to be CVDate([DOB]) it should work.
--
KARL DEWEY
Build a little - Test a little


Cindy said:
Karl, I'm trying your's first and I've entered the string just as you have
it. It works OK except when I try to run it, it keeps asking me to enter a
parameter value DOB. No matter what date I use or the format I type it in,
it give me an error and tells me the expression is typed incorrectly. Any
ideas?

KARL DEWEY said:
Several things wrong --
DATEDIFF("d"[DOB],[DATE]) must be this =DateDiff("d",[DOB],Date())

Next if you use quotes - /"362" then it is text. A number can not be
divided by text as the is like trying to devide 325 by Orange.

A year is 265.25 days long.
DateDiff("d",[DOB],Date())/365.25

WHEN is SQL language and not a function.
Expr1: IIf(DateDiff("d",[DOB],Date())/365.25>=62,Format([DOB],"mmmm ") &
Format([DOB],"yyyy")+62,"")

I do not know what you [prt_birth_date] data is for.
--
KARL DEWEY
Build a little - Test a little


Cindy said:
I am trying to have my query take a participants birthdate, calculate how old
they are, and then tell me the first day of the month they will turn age 62.
I keep getting a syntax error but I'm not sure how to say "if then"
expressions. Here's what I have so far. Thanks for any input!

=DATEDIFF("d"[DOB],[DATE])/"362",when=62,then «Expr» FirstDayOfMonth (
[prt_birth_date] )
Click to expand...
Click to expand...
Click to expand...
 
What is the name of the field that has the birthdate in it. I assumed
DOB, if it is something else the replace DOB with the name of your
field. And looking at your original post, I guess the field is
prt_birth_date



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John, I got a little further with your expression. It let me run it but also
asked me for a DOB and then gave me a blank query with no information. The
birthdates are already in an existing table and it should calculate from
those, correct? I don't understand why it's asking me for a DOB, I could
understand if it were asking me for a current date to work off of. Still
Confused, Cindy

John Spencer said:
The date they turn 62 is
DateAdd("yyyy",62,DOB)

The first day of the month they turn 62 is.
DateSerial(Year(DateAdd("yyyy",62,DOB)),Month(DateAdd("yyyy",62,DOB)),1)



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
I am trying to have my query take a participants birthdate, calculate how old
they are, and then tell me the first day of the month they will turn age 62.
I keep getting a syntax error but I'm not sure how to say "if then"
expressions. Here's what I have so far. Thanks for any input!

=DATEDIFF("d"[DOB],[DATE])/"362",when=62,then «Expr» FirstDayOfMonth (
[prt_birth_date] )
Click to expand...
Click to expand...
Click to expand...
 
Back
Top