Expressions

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] )
 
K

KARL DEWEY

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.
 
J

John Spencer

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
 
C

Cindy

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...
 
C

Cindy

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...
 
K

KARL DEWEY

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...
 
J

John Spencer

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL expressions in MS Access: What do they mean ? 4
Display the lesser of two DateDiff expressions 1
basage modular 4
query help 2
Calculating Age 8
Combining three expressions together to create one expression 4
Age from Date Function 5
Age Display Error 4

Top