For a database report, I need to show whether individuals are children are
adults via a Yes/No field.

On the data entry form, I already have a field that calculates the age from
the dob.
This is the formula that works perfectly there: =DateDiff("yyyy",[DOB],Date())

However, when I have tried to use that same calculation in a query, it
doesn't work. So I went to your previous q/a and copied two formulas given
there, and neither of them worked in my query either.

Age:
Abs(DateDiff("yyyy",[DOB],Date())+(Format(Date(),"mmdd")<Format([DOB],"mmdd")))

Age: DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") <
Format([DOB], "mmdd"), 1, 0)


If I use "Group By" in the query, it tells me that I have a syntax
error(comma) in the query expression.
If I use "Expression", it apparently doesn't recognize the DOB field. Even
tho the DOB is itself in the query and gives the birthdates correctly.

Everything else in the query works! What am I doing wrong?
I was planning to get the age in a query, then use the IIf command to
determine if the individual is over 18 and get a yes/no response for my
report.

Thanks for any help you can offer!
--
Nona

Add the table to name to the query formula like this

Age: DateDiff("yyyy",[TableName]![DOB],Date())

Its the same with the IIF

OverUnder: IIf(DateDiff("yyyy",[TableName]![DOB],Date())>18,"Over 18","Under
18")

This assumes that DOB field contains a date ?




--
Wayne
Manchester, England.



"Nona" wrote:

> For a database report, I need to show whether individuals are children are
> adults via a Yes/No field.
>
> On the data entry form, I already have a field that calculates the age from
> the dob.
> This is the formula that works perfectly there: =DateDiff("yyyy",[DOB],Date())
>
> However, when I have tried to use that same calculation in a query, it
> doesn't work. So I went to your previous q/a and copied two formulas given
> there, and neither of them worked in my query either.
>
> Age:
> Abs(DateDiff("yyyy",[DOB],Date())+(Format(Date(),"mmdd")<Format([DOB],"mmdd")))
>
> Age: DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") <
> Format([DOB], "mmdd"), 1, 0)
>
>
> If I use "Group By" in the query, it tells me that I have a syntax
> error(comma) in the query expression.
> If I use "Expression", it apparently doesn't recognize the DOB field. Even
> tho the DOB is itself in the query and gives the birthdates correctly.
>
> Everything else in the query works! What am I doing wrong?
> I was planning to get the age in a query, then use the IIf command to
> determine if the individual is over 18 and get a yes/no response for my
> report.
>
> Thanks for any help you can offer!
> --
> Nona

Thanks for your reply. I inserted the table name ([tblConsumers]!) exactly as
you suggested. Unfortunately (for me), this is not working either. I'm
getting the exact same error messages as before. I'm sure I'm doing something
wrong, but I have no idea what that could be! Should it be listed as an
Expression? I've tried both your formulas as Group By and as Expressions, but
that didn't help.

--
Nona


"Wayne-I-M" wrote:

> Add the table to name to the query formula like this
>
> Age: DateDiff("yyyy",[TableName]![DOB],Date())
>
> Its the same with the IIF
>
> OverUnder: IIf(DateDiff("yyyy",[TableName]![DOB],Date())>18,"Over 18","Under
> 18")
>
> This assumes that DOB field contains a date ?
>
>
>
>
> --
> Wayne
> Manchester, England.
>
>
>
> "Nona" wrote:
>
> > For a database report, I need to show whether individuals are children are
> > adults via a Yes/No field.
> >
> > On the data entry form, I already have a field that calculates the age from
> > the dob.
> > This is the formula that works perfectly there: =DateDiff("yyyy",[DOB],Date())
> >
> > However, when I have tried to use that same calculation in a query, it
> > doesn't work. So I went to your previous q/a and copied two formulas given
> > there, and neither of them worked in my query either.
> >
> > Age:
> > Abs(DateDiff("yyyy",[DOB],Date())+(Format(Date(),"mmdd")<Format([DOB],"mmdd")))
> >
> > Age: DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") <
> > Format([DOB], "mmdd"), 1, 0)
> >
> >
> > If I use "Group By" in the query, it tells me that I have a syntax
> > error(comma) in the query expression.
> > If I use "Expression", it apparently doesn't recognize the DOB field. Even
> > tho the DOB is itself in the query and gives the birthdates correctly.
> >
> > Everything else in the query works! What am I doing wrong?
> > I was planning to get the age in a query, then use the IIf command to
> > determine if the individual is over 18 and get a yes/no response for my
> > report.
> >
> > Thanks for any help you can offer!
> > --
> > Nona

I think you put the formula in the "field" row of a column.

Look for an empty column and you put the formula in the top row of that.

- This was my 1st ever answer to a question I REALLY hope it's right

Mick
Preston City, UK


"Nona" wrote:

> Thanks for your reply. I inserted the table name ([tblConsumers]!) exactly as
> you suggested. Unfortunately (for me), this is not working either. I'm
> getting the exact same error messages as before. I'm sure I'm doing something
> wrong, but I have no idea what that could be! Should it be listed as an
> Expression? I've tried both your formulas as Group By and as Expressions, but
> that didn't help.
>
> --
> Nona
>
>
> "Wayne-I-M" wrote:
>
> > Add the table to name to the query formula like this
> >
> > Age: DateDiff("yyyy",[TableName]![DOB],Date())
> >
> > Its the same with the IIF
> >
> > OverUnder: IIf(DateDiff("yyyy",[TableName]![DOB],Date())>18,"Over 18","Under
> > 18")
> >
> > This assumes that DOB field contains a date ?
> >
> >
> >
> >
> > --
> > Wayne
> > Manchester, England.
> >
> >
> >
> > "Nona" wrote:
> >
> > > For a database report, I need to show whether individuals are children are
> > > adults via a Yes/No field.
> > >
> > > On the data entry form, I already have a field that calculates the age from
> > > the dob.
> > > This is the formula that works perfectly there: =DateDiff("yyyy",[DOB],Date())
> > >
> > > However, when I have tried to use that same calculation in a query, it
> > > doesn't work. So I went to your previous q/a and copied two formulas given
> > > there, and neither of them worked in my query either.
> > >
> > > Age:
> > > Abs(DateDiff("yyyy",[DOB],Date())+(Format(Date(),"mmdd")<Format([DOB],"mmdd")))
> > >
> > > Age: DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") <
> > > Format([DOB], "mmdd"), 1, 0)
> > >
> > >
> > > If I use "Group By" in the query, it tells me that I have a syntax
> > > error(comma) in the query expression.
> > > If I use "Expression", it apparently doesn't recognize the DOB field. Even
> > > tho the DOB is itself in the query and gives the birthdates correctly.
> > >
> > > Everything else in the query works! What am I doing wrong?
> > > I was planning to get the age in a query, then use the IIf command to
> > > determine if the individual is over 18 and get a yes/no response for my
> > > report.
> > >
> > > Thanks for any help you can offer!
> > > --
> > > Nona

Turn off the grouping (its the sideways Z at the top of the screen)

Put the formula in a calculated column (the field row)

--
Wayne
Manchester, England.



"Nona" wrote:

> Thanks for your reply. I inserted the table name ([tblConsumers]!) exactly as
> you suggested. Unfortunately (for me), this is not working either. I'm
> getting the exact same error messages as before. I'm sure I'm doing something
> wrong, but I have no idea what that could be! Should it be listed as an
> Expression? I've tried both your formulas as Group By and as Expressions, but
> that didn't help.
>
> --
> Nona
>
>
> "Wayne-I-M" wrote:
>
> > Add the table to name to the query formula like this
> >
> > Age: DateDiff("yyyy",[TableName]![DOB],Date())
> >
> > Its the same with the IIF
> >
> > OverUnder: IIf(DateDiff("yyyy",[TableName]![DOB],Date())>18,"Over 18","Under
> > 18")
> >
> > This assumes that DOB field contains a date ?
> >
> >
> >
> >
> > --
> > Wayne
> > Manchester, England.
> >
> >
> >
> > "Nona" wrote:
> >
> > > For a database report, I need to show whether individuals are children are
> > > adults via a Yes/No field.
> > >
> > > On the data entry form, I already have a field that calculates the age from
> > > the dob.
> > > This is the formula that works perfectly there: =DateDiff("yyyy",[DOB],Date())
> > >
> > > However, when I have tried to use that same calculation in a query, it
> > > doesn't work. So I went to your previous q/a and copied two formulas given
> > > there, and neither of them worked in my query either.
> > >
> > > Age:
> > > Abs(DateDiff("yyyy",[DOB],Date())+(Format(Date(),"mmdd")<Format([DOB],"mmdd")))
> > >
> > > Age: DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") <
> > > Format([DOB], "mmdd"), 1, 0)
> > >
> > >
> > > If I use "Group By" in the query, it tells me that I have a syntax
> > > error(comma) in the query expression.
> > > If I use "Expression", it apparently doesn't recognize the DOB field. Even
> > > tho the DOB is itself in the query and gives the birthdates correctly.
> > >
> > > Everything else in the query works! What am I doing wrong?
> > > I was planning to get the age in a query, then use the IIf command to
> > > determine if the individual is over 18 and get a yes/no response for my
> > > report.
> > >
> > > Thanks for any help you can offer!
> > > --
> > > Nona

Thanks, Mick and Wayne,

One of the things I really enjoy about working with Access is the problem
solving. I enjoy the challenges. But this one is getting to me. It seems so
simple, but is so frustrating!

I entered the formula, including the table name, in the field row, with no
grouping, but I get the same syntax error message.

Any other suggestions?????






--
Nona


"Wayne-I-M" wrote:

> Turn off the grouping (its the sideways Z at the top of the screen)
>
> Put the formula in a calculated column (the field row)
>
> --
> Wayne
> Manchester, England.
>
>
>
> "Nona" wrote:
>
> > Thanks for your reply. I inserted the table name ([tblConsumers]!) exactly as
> > you suggested. Unfortunately (for me), this is not working either. I'm
> > getting the exact same error messages as before. I'm sure I'm doing something
> > wrong, but I have no idea what that could be! Should it be listed as an
> > Expression? I've tried both your formulas as Group By and as Expressions, but
> > that didn't help.
> >
> > --
> > Nona
> >
> >
> > "Wayne-I-M" wrote:
> >
> > > Add the table to name to the query formula like this
> > >
> > > Age: DateDiff("yyyy",[TableName]![DOB],Date())
> > >
> > > Its the same with the IIF
> > >
> > > OverUnder: IIf(DateDiff("yyyy",[TableName]![DOB],Date())>18,"Over 18","Under
> > > 18")
> > >
> > > This assumes that DOB field contains a date ?
> > >
> > >
> > >
> > >
> > > --
> > > Wayne
> > > Manchester, England.
> > >
> > >
> > >
> > > "Nona" wrote:
> > >
> > > > For a database report, I need to show whether individuals are children are
> > > > adults via a Yes/No field.
> > > >
> > > > On the data entry form, I already have a field that calculates the age from
> > > > the dob.
> > > > This is the formula that works perfectly there: =DateDiff("yyyy",[DOB],Date())
> > > >
> > > > However, when I have tried to use that same calculation in a query, it
> > > > doesn't work. So I went to your previous q/a and copied two formulas given
> > > > there, and neither of them worked in my query either.
> > > >
> > > > Age:
> > > > Abs(DateDiff("yyyy",[DOB],Date())+(Format(Date(),"mmdd")<Format([DOB],"mmdd")))
> > > >
> > > > Age: DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") <
> > > > Format([DOB], "mmdd"), 1, 0)
> > > >
> > > >
> > > > If I use "Group By" in the query, it tells me that I have a syntax
> > > > error(comma) in the query expression.
> > > > If I use "Expression", it apparently doesn't recognize the DOB field. Even
> > > > tho the DOB is itself in the query and gives the birthdates correctly.
> > > >
> > > > Everything else in the query works! What am I doing wrong?
> > > > I was planning to get the age in a query, then use the IIf command to
> > > > determine if the individual is over 18 and get a yes/no response for my
> > > > report.
> > > >
> > > > Thanks for any help you can offer!
> > > > --
> > > > Nona

Can you see the table in the top section of the query in design view
Is the field DOB in the table a date field
Does it contain a date
Have you changed the TabeName in the formula to the real name of the table
Is your query a simple select query

If all these are yes can you post the sql of the query -
go to design view
then select View
then Select SQL then copy and post the sql so we can look at it


--
Wayne
Manchester, England.



"Nona" wrote:

> Thanks, Mick and Wayne,
>
> One of the things I really enjoy about working with Access is the problem
> solving. I enjoy the challenges. But this one is getting to me. It seems so
> simple, but is so frustrating!
>
> I entered the formula, including the table name, in the field row, with no
> grouping, but I get the same syntax error message.
>
> Any other suggestions?????
>
>
>
>
>
>
> --
> Nona
>
>
> "Wayne-I-M" wrote:
>
> > Turn off the grouping (its the sideways Z at the top of the screen)
> >
> > Put the formula in a calculated column (the field row)
> >
> > --
> > Wayne
> > Manchester, England.
> >
> >
> >
> > "Nona" wrote:
> >
> > > Thanks for your reply. I inserted the table name ([tblConsumers]!) exactly as
> > > you suggested. Unfortunately (for me), this is not working either. I'm
> > > getting the exact same error messages as before. I'm sure I'm doing something
> > > wrong, but I have no idea what that could be! Should it be listed as an
> > > Expression? I've tried both your formulas as Group By and as Expressions, but
> > > that didn't help.
> > >
> > > --
> > > Nona
> > >
> > >
> > > "Wayne-I-M" wrote:
> > >
> > > > Add the table to name to the query formula like this
> > > >
> > > > Age: DateDiff("yyyy",[TableName]![DOB],Date())
> > > >
> > > > Its the same with the IIF
> > > >
> > > > OverUnder: IIf(DateDiff("yyyy",[TableName]![DOB],Date())>18,"Over 18","Under
> > > > 18")
> > > >
> > > > This assumes that DOB field contains a date ?
> > > >
> > > >
> > > >
> > > >
> > > > --
> > > > Wayne
> > > > Manchester, England.
> > > >
> > > >
> > > >
> > > > "Nona" wrote:
> > > >
> > > > > For a database report, I need to show whether individuals are children are
> > > > > adults via a Yes/No field.
> > > > >
> > > > > On the data entry form, I already have a field that calculates the age from
> > > > > the dob.
> > > > > This is the formula that works perfectly there: =DateDiff("yyyy",[DOB],Date())
> > > > >
> > > > > However, when I have tried to use that same calculation in a query, it
> > > > > doesn't work. So I went to your previous q/a and copied two formulas given
> > > > > there, and neither of them worked in my query either.
> > > > >
> > > > > Age:
> > > > > Abs(DateDiff("yyyy",[DOB],Date())+(Format(Date(),"mmdd")<Format([DOB],"mmdd")))
> > > > >
> > > > > Age: DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") <
> > > > > Format([DOB], "mmdd"), 1, 0)
> > > > >
> > > > >
> > > > > If I use "Group By" in the query, it tells me that I have a syntax
> > > > > error(comma) in the query expression.
> > > > > If I use "Expression", it apparently doesn't recognize the DOB field. Even
> > > > > tho the DOB is itself in the query and gives the birthdates correctly.
> > > > >
> > > > > Everything else in the query works! What am I doing wrong?
> > > > > I was planning to get the age in a query, then use the IIf command to
> > > > > determine if the individual is over 18 and get a yes/no response for my
> > > > > report.
> > > > >
> > > > > Thanks for any help you can offer!
> > > > > --
> > > > > Nona

The answer to all of your questions is yes. The DOB is a date field, and the
query runs correctly with correct output in all fields until I add these
formulas to get the age.


SELECT tblConsumers.CLN, tblConsumers.CFN, tblConsumers.ClientStatus,
tblConsumers.LMEID, tblConsumers.D0B, tblConsumers.TLFN, tblConsumers.TLLN,
tblConsumers.CtyofRes, tblConsumers.DOA, tblConsumers.Source,
tblConsumers.SSN, IIf(DateDiff("yyyy",[tblConsumers]![DOB],Date())>18,"Over
18","Under 18") AS OverUnder
FROM tblConsumers
WHERE (((tblConsumers.ClientStatus)="Active"));


I really, really, really appreciate your time and patience.

--
Nona


"Wayne-I-M" wrote:

> Add the table to name to the query formula like this
>
> Age: DateDiff("yyyy",[TableName]![DOB],Date())
>
> Its the same with the IIF
>
> OverUnder: IIf(DateDiff("yyyy",[TableName]![DOB],Date())>18,"Over 18","Under
> 18")
>
> This assumes that DOB field contains a date ?
>
>
>
>
> --
> Wayne
> Manchester, England.
>
>
>
> "Nona" wrote:
>
> > For a database report, I need to show whether individuals are children are
> > adults via a Yes/No field.
> >
> > On the data entry form, I already have a field that calculates the age from
> > the dob.
> > This is the formula that works perfectly there: =DateDiff("yyyy",[DOB],Date())
> >
> > However, when I have tried to use that same calculation in a query, it
> > doesn't work. So I went to your previous q/a and copied two formulas given
> > there, and neither of them worked in my query either.
> >
> > Age:
> > Abs(DateDiff("yyyy",[DOB],Date())+(Format(Date(),"mmdd")<Format([DOB],"mmdd")))
> >
> > Age: DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") <
> > Format([DOB], "mmdd"), 1, 0)
> >
> >
> > If I use "Group By" in the query, it tells me that I have a syntax
> > error(comma) in the query expression.
> > If I use "Expression", it apparently doesn't recognize the DOB field. Even
> > tho the DOB is itself in the query and gives the birthdates correctly.
> >
> > Everything else in the query works! What am I doing wrong?
> > I was planning to get the age in a query, then use the IIf command to
> > determine if the individual is over 18 and get a yes/no response for my
> > report.
> >
> > Thanks for any help you can offer!
> > --
> > Nona

I just set up a table with the same field names as yours. The following query
works exactly as expected;

SELECT tblConsumers.CLN, tblConsumers.CFN, tblConsumers.ClientStatus,
tblConsumers.LMEID, tblConsumers.DOB, tblConsumers.TLFN, tblConsumers.TLLN,
tblConsumers.CtyofRes, tblConsumers.DOA, tblConsumers.Source,
tblConsumers.SSN,
IIf(DateDiff("yyyy",[DOB],Date())-IIf(Format(Date(),"mmdd")<Format([DOB],"mmdd"),1,0)>18,"Over 18","Under 18") AS OverUnder
FROM tblConsumers
WHERE (((tblConsumers.ClientStatus)="Active"));

--
_________

Sean Bailey


"Nona" wrote:

> The answer to all of your questions is yes. The DOB is a date field, and the
> query runs correctly with correct output in all fields until I add these
> formulas to get the age.
>
>
> SELECT tblConsumers.CLN, tblConsumers.CFN, tblConsumers.ClientStatus,
> tblConsumers.LMEID, tblConsumers.D0B, tblConsumers.TLFN, tblConsumers.TLLN,
> tblConsumers.CtyofRes, tblConsumers.DOA, tblConsumers.Source,
> tblConsumers.SSN, IIf(DateDiff("yyyy",[tblConsumers]![DOB],Date())>18,"Over
> 18","Under 18") AS OverUnder
> FROM tblConsumers
> WHERE (((tblConsumers.ClientStatus)="Active"));
>
>
> I really, really, really appreciate your time and patience.
>
> --
> Nona
>
>
> "Wayne-I-M" wrote:
>
> > Add the table to name to the query formula like this
> >
> > Age: DateDiff("yyyy",[TableName]![DOB],Date())
> >
> > Its the same with the IIF
> >
> > OverUnder: IIf(DateDiff("yyyy",[TableName]![DOB],Date())>18,"Over 18","Under
> > 18")
> >
> > This assumes that DOB field contains a date ?
> >
> >
> >
> >
> > --
> > Wayne
> > Manchester, England.
> >
> >
> >
> > "Nona" wrote:
> >
> > > For a database report, I need to show whether individuals are children are
> > > adults via a Yes/No field.
> > >
> > > On the data entry form, I already have a field that calculates the age from
> > > the dob.
> > > This is the formula that works perfectly there: =DateDiff("yyyy",[DOB],Date())
> > >
> > > However, when I have tried to use that same calculation in a query, it
> > > doesn't work. So I went to your previous q/a and copied two formulas given
> > > there, and neither of them worked in my query either.
> > >
> > > Age:
> > > Abs(DateDiff("yyyy",[DOB],Date())+(Format(Date(),"mmdd")<Format([DOB],"mmdd")))
> > >
> > > Age: DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") <
> > > Format([DOB], "mmdd"), 1, 0)
> > >
> > >
> > > If I use "Group By" in the query, it tells me that I have a syntax
> > > error(comma) in the query expression.
> > > If I use "Expression", it apparently doesn't recognize the DOB field. Even
> > > tho the DOB is itself in the query and gives the birthdates correctly.
> > >
> > > Everything else in the query works! What am I doing wrong?
> > > I was planning to get the age in a query, then use the IIf command to
> > > determine if the individual is over 18 and get a yes/no response for my
> > > report.
> > >
> > > Thanks for any help you can offer!
> > > --
> > > Nona

Congratulations, Beetle. I'm glad it worked for you. I just wish you could
tell me why it won't run for me. I copied your statement and pasted it onto
my query. It still didn't work. Won't recognize DOB in the calculated fields.
(It does recognize and gives the correct dob without the calculations.)

It does give output for the Over/Under field, but it's wrong. Says all 290
consumers are under 18, which is not the case.

Here is the SQL stmt I just used...
SELECT tblConsumers.CLN, tblConsumers.CFN, tblConsumers.ClientStatus,
tblConsumers.LMEID, tblConsumers.D0B, tblConsumers.TLFN, tblConsumers.TLLN,
tblConsumers.CtyofRes, tblConsumers.DOA, tblConsumers.Source,
tblConsumers.SSN,
IIf(DateDiff("yyyy",[DOB],Date())-IIf(Format(Date(),"mmdd")<Format([DOB],"mmdd"),1,0)>18,"Over 18","Under 18") AS OverUnder
FROM tblConsumers
WHERE (((tblConsumers.ClientStatus)="Active"));

I can't believe this is happening...! I appreciate your efforts.








--
Nona


"Beetle" wrote:

> I just set up a table with the same field names as yours. The following query
> works exactly as expected;
>
> SELECT tblConsumers.CLN, tblConsumers.CFN, tblConsumers.ClientStatus,
> tblConsumers.LMEID, tblConsumers.DOB, tblConsumers.TLFN, tblConsumers.TLLN,
> tblConsumers.CtyofRes, tblConsumers.DOA, tblConsumers.Source,
> tblConsumers.SSN,
> IIf(DateDiff("yyyy",[DOB],Date())-IIf(Format(Date(),"mmdd")<Format([DOB],"mmdd"),1,0)>18,"Over 18","Under 18") AS OverUnder
> FROM tblConsumers
> WHERE (((tblConsumers.ClientStatus)="Active"));
>
> --
> _________
>
> Sean Bailey
>
>
> "Nona" wrote:
>
> > The answer to all of your questions is yes. The DOB is a date field, and the
> > query runs correctly with correct output in all fields until I add these
> > formulas to get the age.
> >
> >
> > SELECT tblConsumers.CLN, tblConsumers.CFN, tblConsumers.ClientStatus,
> > tblConsumers.LMEID, tblConsumers.D0B, tblConsumers.TLFN, tblConsumers.TLLN,
> > tblConsumers.CtyofRes, tblConsumers.DOA, tblConsumers.Source,
> > tblConsumers.SSN, IIf(DateDiff("yyyy",[tblConsumers]![DOB],Date())>18,"Over
> > 18","Under 18") AS OverUnder
> > FROM tblConsumers
> > WHERE (((tblConsumers.ClientStatus)="Active"));
> >
> >
> > I really, really, really appreciate your time and patience.
> >
> > --
> > Nona
> >
> >
> > "Wayne-I-M" wrote:
> >
> > > Add the table to name to the query formula like this
> > >
> > > Age: DateDiff("yyyy",[TableName]![DOB],Date())
> > >
> > > Its the same with the IIF
> > >
> > > OverUnder: IIf(DateDiff("yyyy",[TableName]![DOB],Date())>18,"Over 18","Under
> > > 18")
> > >
> > > This assumes that DOB field contains a date ?
> > >
> > >
> > >
> > >
> > > --
> > > Wayne
> > > Manchester, England.
> > >
> > >
> > >
> > > "Nona" wrote:
> > >
> > > > For a database report, I need to show whether individuals are children are
> > > > adults via a Yes/No field.
> > > >
> > > > On the data entry form, I already have a field that calculates the age from
> > > > the dob.
> > > > This is the formula that works perfectly there: =DateDiff("yyyy",[DOB],Date())
> > > >
> > > > However, when I have tried to use that same calculation in a query, it
> > > > doesn't work. So I went to your previous q/a and copied two formulas given
> > > > there, and neither of them worked in my query either.
> > > >
> > > > Age:
> > > > Abs(DateDiff("yyyy",[DOB],Date())+(Format(Date(),"mmdd")<Format([DOB],"mmdd")))
> > > >
> > > > Age: DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") <
> > > > Format([DOB], "mmdd"), 1, 0)
> > > >
> > > >
> > > > If I use "Group By" in the query, it tells me that I have a syntax
> > > > error(comma) in the query expression.
> > > > If I use "Expression", it apparently doesn't recognize the DOB field. Even
> > > > tho the DOB is itself in the query and gives the birthdates correctly.
> > > >
> > > > Everything else in the query works! What am I doing wrong?
> > > > I was planning to get the age in a query, then use the IIf command to
> > > > determine if the individual is over 18 and get a yes/no response for my
> > > > report.
> > > >
> > > > Thanks for any help you can offer!
> > > > --
> > > > Nona