date filters

[Pedro Lerma]

I want to do a filter by ages from query, I have as fields "birthday" and
"today", however when I write for example, on field_from_age(0) and
field_until_age(3), my query filter show me person that have 1,2,3,15,17,
years old.
Someone can helpme???
This is my WHERE CODE:

WHERE ((((([consultas].[fecha]/365))-(([paciente].[fecha de
nacimiento]/365))) between [edad1] And [edad2]));

consulta=table name
fecha=date
paciente=date
fecha de nacimiento=birthday
edad1=age1
edad2=age2
thanks and I'm sorry my english isn't very well
Pedro

 

[John Vinson]

I want to do a filter by ages from query, I have as fields "birthday" and
"today", however when I write for example, on field_from_age(0) and
field_until_age(3), my query filter show me person that have 1,2,3,15,17,
years old.
Someone can helpme???
This is my WHERE CODE:

WHERE ((((([consultas].[fecha]/365))-(([paciente].[fecha de
nacimiento]/365))) between [edad1] And [edad2]));

consulta=table name
fecha=date
paciente=date
fecha de nacimiento=birthday
edad1=age1
edad2=age2
thanks and I'm sorry my english isn't very well
Pedro

There is an easier way to calculate age.

In a vacant Field cell in the query type

Edad: DateDiff("yyyy", [paciente].[fecha de nacimiento],
[consultas].[fecha]) - IIF(Format([paciente].[fecha de nacimiento],
"mmdd") > Format([consultas].[fecha], 1, 0)

Put a criterion on this of

BETWEEN [edad1] AND [edad2]

You may need to change the arguments to the DateDiff or Format
function - I don't have the Spanish version of Access so I'm not sure
if the arguments change. Buen suerte!

John W. Vinson[MVP]

 

[John Vinson]

My query follow giving me a missing results, I'm attaching the results when
I typing from 1 year until 2 year
This is my query

Try running the query with no criteria at all. Does it give correct
(or at least reasonable) values for edad?

John W. Vinson[MVP]

 

[John Vinson]

Yes it give correct values without Between command, may be field´s
propieties for edad field???
see attachment

Please don't post binary attachments - many people with dialup
connections find that they slow down the newsgroup very badly!

I don't know why, but it appears that the [edad] field is being
treated as Text. Try changing the Where clause to

WHERE (((CInt(DateDiff("yyyy",([paciente].[fecha de
nacimiento]),([consultas].[fecha]))-IIf(Format([paciente].[fecha de
nacimiento],"mmdd")>Format([consultas].[fecha]),1,0))) Between [edad1]
And [edad2]));


John W. Vinson[MVP]