Current Date in Query

[Bluesky]

Hi All,

Hoping you can help.

I'm using the following to get current age in a query:

NewCurrAge: (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date())))\12 & " yrs
" & (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))) Mod 12 & " mos"

My BIG problem is that I also want to pull certain ages, especially 9 to 13
for one query and 14 to 17 in another query.

So, in the same query for criteria I used:

Between 9 yrs 0 mos And 13 yrs 12 mos
Error: the expression contains invalid syntax. you entered an operator
without an operand.


Then I tried:

Between 9 "yrs" 0 "mos" And 13 "yrs" 12 "mos" - which again doesn't work.
Error: the expression contains invalid syntax. you entered an operator
without an operand.

Then I tried: Between "yrs"=9 And "mos"=0 And "yrs"=13 And "mos"=12
Error: data type mismatch

Then I tried: Between "9 yrs 0 mos" And "13 yrs 12 mos"
The query returns values starting at 13 yrs 2 mos to 18 yrs 3 mos (which is
problably the oldest age).

I'm obviously not writing the query correctly, but can't figure it out.

Thanks again,
Debbie

 

[ghetto_banjo]

you cant use the between...and operator in that manner with those
string expressions.


perhaps it would be beneficial to calculate the year and month in
separate fields?

for example, the age in years alone would be:

AgeYears: DateDiff("yyyy", [DOB], Now())+ Int( Format(now(),
"mmdd") < Format( [DOB], "mmdd") )


then in the criteria for this AgeYears field, you could do: Between 9
and 13

 

[Daryl S]

Blusky -

Add another column to your query that only calculates the years old (and no
text - you want this to stay a number). You don't have to show this field,
but you might want to while testing. For example:

YearsOld: (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date())))\12

Then in the criteria row you can restrict this to "between 9 and 13" or
"between 14 and 17" but don't put the quotes in the criteria field.

 

[Lynn Trapp]

Debbie,
Give the following a try for the 9 to 13 age ranges.

SELECT LastName, FirstName, DOB,
(DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date())))\12 & " yrs " &
(DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))) Mod 12 & " mos" AS
NewCurrAge
FROM People
WHERE (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))\12)\12 >= 9
AND (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))\12)\12 <=13

 

[Bluesky]

Hi Daryl,

Thanks for your suggestion, but, which I should have noted, I am hoping to
get those with ages 9 yrs 0 mos to 13 yrs 12 mos for one group, and the
second group 14 yrs 0 mos to 17 yrs 9 mos. So I do want the mos in my query.

Thanks,

BS

Blusky -

Add another column to your query that only calculates the years old (and no
text - you want this to stay a number). You don't have to show this field,
but you might want to while testing. For example:

YearsOld: (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date())))\12

Then in the criteria row you can restrict this to "between 9 and 13" or
"between 14 and 17" but don't put the quotes in the criteria field.

--
Daryl S

Hi All,

Hoping you can help.

I'm using the following to get current age in a query:

NewCurrAge: (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date())))\12 & " yrs
" & (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))) Mod 12 & " mos"

My BIG problem is that I also want to pull certain ages, especially 9 to 13
for one query and 14 to 17 in another query.

So, in the same query for criteria I used:

Between 9 yrs 0 mos And 13 yrs 12 mos
Error: the expression contains invalid syntax. you entered an operator
without an operand.


Then I tried:

Between 9 "yrs" 0 "mos" And 13 "yrs" 12 "mos" - which again doesn't work.
Error: the expression contains invalid syntax. you entered an operator
without an operand.

Then I tried: Between "yrs"=9 And "mos"=0 And "yrs"=13 And "mos"=12
Error: data type mismatch

Then I tried: Between "9 yrs 0 mos" And "13 yrs 12 mos"
The query returns values starting at 13 yrs 2 mos to 18 yrs 3 mos (which is
problably the oldest age).

I'm obviously not writing the query correctly, but can't figure it out.

Thanks again,
Debbie

 

[ghetto_banjo]

in that case you might want to store the age as total number of months
in a field.

then you can do stuff like

between 108 and 167 (i.e. between 9 years 0 months and 13 years 11
months)



just a side note, doesn't 13 yrs 12 mos = 14 yrs 0 mos? keep that in
mind

 

[Bluesky]

Hi Lynn,
Thanks for your email.

Since my query returns values: "xx yrs xx mos," I was hoping to write a
simple criteria like: between xx yrs xx mos AND xx yrs xx mos.

But is this not possible?

thaks,
Debbie

Debbie,
Give the following a try for the 9 to 13 age ranges.

SELECT LastName, FirstName, DOB,
(DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date())))\12 & " yrs " &
(DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))) Mod 12 & " mos" AS
NewCurrAge
FROM People
WHERE (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))\12)\12 >= 9
AND (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))\12)\12 <=13
--
Lynn Trapp
MCP, MOS, MCAS

Hi All,

Hoping you can help.

I'm using the following to get current age in a query:

NewCurrAge: (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date())))\12 & " yrs
" & (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))) Mod 12 & " mos"

My BIG problem is that I also want to pull certain ages, especially 9 to 13
for one query and 14 to 17 in another query.

So, in the same query for criteria I used:

Between 9 yrs 0 mos And 13 yrs 12 mos
Error: the expression contains invalid syntax. you entered an operator
without an operand.


Then I tried:

Between 9 "yrs" 0 "mos" And 13 "yrs" 12 "mos" - which again doesn't work.
Error: the expression contains invalid syntax. you entered an operator
without an operand.

Then I tried: Between "yrs"=9 And "mos"=0 And "yrs"=13 And "mos"=12
Error: data type mismatch

Then I tried: Between "9 yrs 0 mos" And "13 yrs 12 mos"
The query returns values starting at 13 yrs 2 mos to 18 yrs 3 mos (which is
problably the oldest age).

I'm obviously not writing the query correctly, but can't figure it out.

Thanks again,
Debbie

 

[Bluesky]

Hi,

thanks for your reply. I was hoping to get more than just "yrs." I have
used this for current age in years:

CurrAge:
DateDiff("yyyy",[DOB],Date())+Int(Format([DOB],"mmdd")>Format(Date(),"mmdd"))

but was hoping to be more precise and get moths as well, since I am able to
return xx yrs and xx mos in the query.

But maybe it is not doable as you said earlier?

-debbie

 

[Lynn Trapp]

Debbie,

You can use Between "9 yrs 0 mos" And "13 yrs 12 mos" for your criteria, but
it will not return what you want. The resultant "xx yrs yy mos" is a string
and, therefore, will be treated as a string when evaluating what is between
what. 11 and 12 are not between 9 and 13 as string values. Therefore,
something like the following will be more correct.

SELECT People.LastName, People.FirstName,
(DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date())))\12 & " yrs " &
(DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))) Mod 12 & " mos" AS
NewCurrAge
FROM People
WHERE ((((DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))\12)\12)
Between 9 And 13));
--
Lynn Trapp
MCP, MOS, MCAS

Hi Lynn,
Thanks for your email.

Since my query returns values: "xx yrs xx mos," I was hoping to write a
simple criteria like: between xx yrs xx mos AND xx yrs xx mos.

But is this not possible?

thaks,
Debbie

Debbie,
Give the following a try for the 9 to 13 age ranges.

SELECT LastName, FirstName, DOB,
(DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date())))\12 & " yrs " &
(DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))) Mod 12 & " mos" AS
NewCurrAge
FROM People
WHERE (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))\12)\12 >= 9
AND (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))\12)\12 <=13
--
Lynn Trapp
MCP, MOS, MCAS

Hi All,

Hoping you can help.

I'm using the following to get current age in a query:

NewCurrAge: (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date())))\12 & " yrs
" & (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))) Mod 12 & " mos"

My BIG problem is that I also want to pull certain ages, especially 9 to 13
for one query and 14 to 17 in another query.

So, in the same query for criteria I used:

Between 9 yrs 0 mos And 13 yrs 12 mos
Error: the expression contains invalid syntax. you entered an operator
without an operand.


Then I tried:

Between 9 "yrs" 0 "mos" And 13 "yrs" 12 "mos" - which again doesn't work.
Error: the expression contains invalid syntax. you entered an operator
without an operand.

Then I tried: Between "yrs"=9 And "mos"=0 And "yrs"=13 And "mos"=12
Error: data type mismatch

Then I tried: Between "9 yrs 0 mos" And "13 yrs 12 mos"
The query returns values starting at 13 yrs 2 mos to 18 yrs 3 mos (which is
problably the oldest age).

I'm obviously not writing the query correctly, but can't figure it out.

Thanks again,
Debbie

 

[John Spencer]

I would use something like the following for efficiency if I were trying to
limit the records being returned. Obviously, you would still need to
calculate Current Age if you wished to display the age.

Field: DOB
Criteria: > DateAdd("yyyy",-14,Date()) and <=("yyyy",-9,Date())

If yo need to specify down to months then the DateAdd Expression would become
more like
DateAdd("m",-[NumberofYears]*12 + [NumberOfMonths],Date())

This would be much more efficient even if the DOB field was not indexed. You
would eliminate calculating the current age for every record and then
filtering the results based on that calculation. If DOB was indexed then the
increase in efficiency is even higher.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Daryl S]

Bluesky -

I wasn't suggesting you get rid of the columns with the year/month data,
just switching the criteria to the new field.

--
Daryl S

Hi Daryl,

Thanks for your suggestion, but, which I should have noted, I am hoping to
get those with ages 9 yrs 0 mos to 13 yrs 12 mos for one group, and the
second group 14 yrs 0 mos to 17 yrs 9 mos. So I do want the mos in my query.

Thanks,

BS

Blusky -

Add another column to your query that only calculates the years old (and no
text - you want this to stay a number). You don't have to show this field,
but you might want to while testing. For example:

YearsOld: (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date())))\12

Then in the criteria row you can restrict this to "between 9 and 13" or
"between 14 and 17" but don't put the quotes in the criteria field.

--
Daryl S

Hi All,

Hoping you can help.

I'm using the following to get current age in a query:

NewCurrAge: (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date())))\12 & " yrs
" & (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))) Mod 12 & " mos"

My BIG problem is that I also want to pull certain ages, especially 9 to 13
for one query and 14 to 17 in another query.

So, in the same query for criteria I used:

Between 9 yrs 0 mos And 13 yrs 12 mos
Error: the expression contains invalid syntax. you entered an operator
without an operand.


Then I tried:

Between 9 "yrs" 0 "mos" And 13 "yrs" 12 "mos" - which again doesn't work.
Error: the expression contains invalid syntax. you entered an operator
without an operand.

Then I tried: Between "yrs"=9 And "mos"=0 And "yrs"=13 And "mos"=12
Error: data type mismatch

Then I tried: Between "9 yrs 0 mos" And "13 yrs 12 mos"
The query returns values starting at 13 yrs 2 mos to 18 yrs 3 mos (which is
problably the oldest age).

I'm obviously not writing the query correctly, but can't figure it out.

Thanks again,
Debbie

 

[Bluesky]

Hi Ghetto_banjo,

I just noticed this reply, thank you for the suggestion.

And yes, 13 yrs 12 mos is 14 years! I meant to write 13 yrs and 9 mos!

thanks again,
BlueSky

 

[Bluesky]

Hi Daryl,

I just noticed your reply as well. For some reason I missed it earlier.
Thanks for the suggestion!!

Thanks again,
BlueSky

Bluesky -

I wasn't suggesting you get rid of the columns with the year/month data,
just switching the criteria to the new field.

--
Daryl S

Hi Daryl,

Thanks for your suggestion, but, which I should have noted, I am hoping to
get those with ages 9 yrs 0 mos to 13 yrs 12 mos for one group, and the
second group 14 yrs 0 mos to 17 yrs 9 mos. So I do want the mos in my query.

Thanks,

BS

Blusky -

Add another column to your query that only calculates the years old (and no
text - you want this to stay a number). You don't have to show this field,
but you might want to while testing. For example:

YearsOld: (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date())))\12

Then in the criteria row you can restrict this to "between 9 and 13" or
"between 14 and 17" but don't put the quotes in the criteria field.

--
Daryl S


:

Hi All,

Hoping you can help.

I'm using the following to get current age in a query:

NewCurrAge: (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date())))\12 & " yrs
" & (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))) Mod 12 & " mos"

My BIG problem is that I also want to pull certain ages, especially 9 to 13
for one query and 14 to 17 in another query.

So, in the same query for criteria I used:

Between 9 yrs 0 mos And 13 yrs 12 mos
Error: the expression contains invalid syntax. you entered an operator
without an operand.


Then I tried:

Between 9 "yrs" 0 "mos" And 13 "yrs" 12 "mos" - which again doesn't work.
Error: the expression contains invalid syntax. you entered an operator
without an operand.

Then I tried: Between "yrs"=9 And "mos"=0 And "yrs"=13 And "mos"=12
Error: data type mismatch

Then I tried: Between "9 yrs 0 mos" And "13 yrs 12 mos"
The query returns values starting at 13 yrs 2 mos to 18 yrs 3 mos (which is
problably the oldest age).

I'm obviously not writing the query correctly, but can't figure it out.

Thanks again,
Debbie