Update Query/Converting

[goju]

Dear all,


i have searched /researched and looked at hundreds of posts, and no luck.

Im litlle bit out of my depth, i have posted this before but just got confused.

i have two update querys, the first one identifys if a persons age (by date of birth) is under 16 a check box identifys if this person is "ticked" as junior.

the second update query identifys by (date of birth) if the person is over 16 a check box identifys if this person is "ticked" as senior.

The results are returned into my main query in two columns, these columns return -1 or 0 for junior or senior.

I need to produce a report that returns J or S (Junior or Senior) is there a way of converting these results to one column and converting the -1, 0 to J or S.

Please see attached file.

very lost, still searching for the answer. been on this foe three weeks now.

any help appricated.

 

[skea]

Dear all,
I need to produce a report that returns J or S (Junior or Senior) is there a way of converting these results to one column and converting the -1, 0 to J or S.

Please see attached file.

very lost, still searching for the answer. been on this foe three weeks now.

any help appricated.

Without seeing the file, i suggest you make another table with the same structure as your other table and use an append query to insert values where Junior=-1 and senior= 0 ,then you base your report on that append query.

Hope that helps.

 

[ColinEssex]

Put this in the query that runs the report, there's no need to have it in the table because its a changing value and can be recalculated when the report runs

 IIf((Date()-[DateOfBirth])/365.25<16,"J","S")

The 365.25 bit is a rough (recognised) number of days in the year. If you require years,months and days, there are examples of Functions to do that.

Col

 

[goju]

colin

i tried that in the update querys (under16 and over16) it didnt change the main query

then i tried it in the main query and that returned no records.

sorry lost again

 

[ColinEssex]

Forget about the update query and forget about the senior and junior fields - you don't need them. Always recalculate the value in the query that runs the report. If someone is a "J" value today, they may be an "S" value tomorrow so it'll be wrong.

Make a new query (a select query), include the PersonName field (or something) and the DateOfBirth field.

Put the expression I gave you in the field name of the next blank field in the query grid. Run the query and you'll see it posts either a 'J' or an 'S' in the calculated field.

Col