Between

[Pete]

I enter the following criteria
Between 9 And 10

which gives me what I expect

I enter the criteria like this
Between[Enter Start Age] And [Enter End Age]

I get every competitor when I give Start age 9 and End Age 10

Can anyone tell me why? I would like to use one query for all reports

cheers

Peter

 

[Pete]

in query grid

AgeGroup: AgeDec([d-o-b]) is the column header (AgeDec is a function
returning an integer)
Between[Enter StartAge] And [Enter EndAge] is the criteria

All I want is the age of competitor on Dec 31 of current year, perhaps you
can tell me another way?

not sure what you mean by your second question (I'm fairly new to access)

I hope you can sort this out

Is this in the query grid or in code?

Have you defined these as parameters or do you expect them to be forms
references??

I enter the following criteria
Between 9 And 10

which gives me what I expect

I enter the criteria like this
Between[Enter Start Age] And [Enter End Age]

I get every competitor when I give Start age 9 and End Age 10

Can anyone tell me why? I would like to use one query for all reports

cheers

Peter

 

[JohnFol]

you could do away with the AgeDec function by using the following

SELECT Table1.[d-o-b], DateDiff("yyyy",[d-o-b],CVDate("31/dec/" &
Year(Now()))) AS AgeGroup
FROM Table1
WHERE (((DateDiff("yyyy",[d-o-b],CVDate("31/dec/" & Year(Now())))) Between
[Enter Start Date] And [Enter End Date]));


This will prompt for the 2 values, and take into account the age when
filtering the records

in query grid

AgeGroup: AgeDec([d-o-b]) is the column header (AgeDec is a function
returning an integer)
Between[Enter StartAge] And [Enter EndAge] is the criteria

All I want is the age of competitor on Dec 31 of current year, perhaps you
can tell me another way?

not sure what you mean by your second question (I'm fairly new to access)

I hope you can sort this out

Is this in the query grid or in code?

Have you defined these as parameters or do you expect them to be forms
references??

I enter the following criteria
Between 9 And 10

which gives me what I expect

I enter the criteria like this
Between[Enter Start Age] And [Enter End Age]

I get every competitor when I give Start age 9 and End Age 10

Can anyone tell me why? I would like to use one query for all reports

cheers

Peter

 

[Pete]

thanks, I'll give it a try.

you could do away with the AgeDec function by using the following

SELECT Table1.[d-o-b], DateDiff("yyyy",[d-o-b],CVDate("31/dec/" &
Year(Now()))) AS AgeGroup
FROM Table1
WHERE (((DateDiff("yyyy",[d-o-b],CVDate("31/dec/" & Year(Now())))) Between
[Enter Start Date] And [Enter End Date]));


This will prompt for the 2 values, and take into account the age when
filtering the records

in query grid

AgeGroup: AgeDec([d-o-b]) is the column header (AgeDec is a function
returning an integer)
Between[Enter StartAge] And [Enter EndAge] is the criteria

All I want is the age of competitor on Dec 31 of current year, perhaps you
can tell me another way?

not sure what you mean by your second question (I'm fairly new to access)

I hope you can sort this out

Is this in the query grid or in code?

Have you defined these as parameters or do you expect them to be forms
references??



I enter the following criteria
Between 9 And 10

which gives me what I expect

I enter the criteria like this
Between[Enter Start Age] And [Enter End Age]

I get every competitor when I give Start age 9 and End Age 10

Can anyone tell me why? I would like to use one query for all reports

cheers

Peter