update query difficulty

[DJ-Specter]

Hi, I want to update all records with a birth date listed to have an age in a separate 'age' field. I have got the hang of the datediff function but i can't seem to get it to work in an update query.

I need the query to search for all records that have a birth date listed which i have done using an 'is not null' statement as you can see in the 'query criteria' screenshot. the same query needs to then use all those records to calculate the age of the person using the datediff function. however after clicking run, i just get a load of blank records in an age column as shown in the 'query results' screenshot.

i'm certain that access can do what i need it to do, its just that i'm not asking it in the right way.

any ideas are welcomed. cheers

 

[dcb]

I hate to ask but why? Age is something you normally work out at form or report level as it changes all the time.....

 

[DJ-Specter]

hi, i'm doing this as i've been shown this video: http://www.datapigtechnologies.com/flashfiles/searchform.html and my current personnel table only has a birth date field so as it stands at the moment, i'd have to ask users to input the date of birth ranges which isn't very user-friendly.

 

[dcb]

I see why you are trying to do this - however what you want to do (using the video names) is create a calculated column in your "mainquery" called employeeage - has to be query level not table - then query against this using '' > < = '' operators.

You never want to write these to your table - apart from going against just about every rule there is - how would you manage the updates "run query every 5 min?"

Does this make sense?

 

[DJ-Specter]

Thanks dcb, i've got most of it working now apart from the age searching. I did what you said and added the calculating field in the mainquery and i can search in the search form for exact ages but when i use any of the greater than or less than operators it comes up blank. i thought it might be because the age field should be a number data type instead of a text data type but no luck unfortunately. any ideas as to why this is happening? cheers

 

[boblarson]

Thanks dcb, i've got most of it working now apart from the age searching. I did what you said and added the calculating field in the mainquery and i can search in the search form for exact ages but when i use any of the greater than or less than operators it comes up blank. i thought it might be because the age field should be a number data type instead of a text data type but no luck unfortunately. any ideas as to why this is happening? cheers

Can you post what you currently have?

 

[DJ-Specter]

Hi, I've shown the inputs and results for running the search with both a fixed age and a ">" included in the age input. hope this helps? cheers

 

[Brianwarnock]

Hi DJ you should also be told that Datediff by itself will not correctly calculate age, it merely calculates the difference, in your example, of years between the dates, you need to check whether or not a birthday has been reached and adjust accordingly.

age: Datediff("yyyy",dob,Date())+ dateserial(year(date),month(dob),day(dob)>date()

This checks to see if this years birthday has been reached , if not the test will return True ie -1 and the number of years adjusted, else it returns False which is 0.

Brian

 

[Brianwarnock]

I think Bob was hoping to see the query SQL code.

Brian

 

[boblarson]

I think Bob was hoping to see the query SQL code.

Yep, I sure was...

 

[DJ-Specter]

sorry about that, didn't realise. Are these screenshots any more helpful?

 

[DJ-Specter]

Hi DJ you should also be told that Datediff by itself will not correctly calculate age, it merely calculates the difference, in your example, of years between the dates, you need to check whether or not a birthday has been reached and adjust accordingly.

age: Datediff("yyyy",dob,Date())+ dateserial(year(date),month(dob),day(dob)>date()

This checks to see if this years birthday has been reached , if not the test will return True ie -1 and the number of years adjusted, else it returns False which is 0.

Brian

I tried this after editing it to work with the field names i'm using - is this right?

age: DateDiff("yyyy",[Birth Date],Date())+DateSerial(Year([Birth Date]),Month([Birth Date]),Day([Birth Date])>Date())

but when i run the query i get a data mismatch error. I'm presuming that the fields its talking about are birth date which is obviously a date and age which is a number. how do i get around this as i cant change either as i'll lose information wont I?

Thanks

 

[Brianwarnock]

other than the correction shown below I cannot see why it wont work.
Datediff returns a number as does test as explained above.


age: DateDiff("yyyy",[Birth Date],Date())+DateSerial(Year(Date()),Month([Birth Date]),Day([Birth Date])>Date())

brian

 

[DJ-Specter]

Thanks for that update, I'm still getting the data type error, but I'm under more pressure to get the search function working so do you have any ideas as to why I cant use the greater than or the less than symbols in the age search?

I realised that I'd not put up a shot of the query in sql view so here it is.

Thanks for everyone's help on this, its much appreciated.

 

[DCrake]

Can't understand your logic regarding the age. What is the bit about >Date() logic. (Can't cut and paste from a jpeg to show you). Can you post as a string. It appears that you are trying to compare a persons age with today's date.

David

 

[Brianwarnock]

Dave that is a fairly standard approach to calculating age as I explained in an earlier post in the thread.

Can't get my old head round his query though, I prefer to be hands on, better leave the clever stuff to you still active youngsters.

Brian

 

[DCrake]

Brian

Ok I just enter the following in the immediate window

?DateDiff("yyyy",#04/06/1955#,Date())+DateSerial(Year(Date()),Month(#04/06/1955#),Day(#04/06/1955#)>Date())

it came back with
24/05/2009

What''s the date supposed to represent?

David

 

[DJ-Specter]

Can't understand your logic regarding the age. What is the bit about >Date() logic. (Can't cut and paste from a jpeg to show you). Can you post as a string. It appears that you are trying to compare a persons age with today's date.

David

I was just using the expression posted earlier in this thread, but I've put it back to this Age: DateDiff("yyyy",[Birth Date],Date()) as it worked ok with this.

however, I need to be able to search for age ranges i.e. 25-40 but I cant search for any ages other than single ages i.e. just 40 year olds.

 

[Brianwarnock]

Sometimes one cannot see the wood for the trees , try

DateDiff("yyyy",#04/06/1955#,Date())+(DateSerial(Year(Date()),Month(#04/06/1955#),Day(#04/06/1955#))>Date())

Brian

 

[Brianwarnock]

I was just using the expression posted earlier in this thread, but I've put it back to this Age: DateDiff("yyyy",[Birth Date],Date()) as it worked ok with this.

however, I need to be able to search for age ranges i.e. 25-40 but I cant search for any ages other than single ages i.e. just 40 year olds.

Once again I will try to reply to this, hope the forum stays up this time.

Your search form only has one age on it it needs 2 for a range , then use Between age1 And age2 in the criteria.

Please don't continue with just Datediff for the age calculation I explained why it is frequently incorrect earlier, and ok my calc had a syntax error, I'm only surprised that DCrake spent time criticising it rather than giving his own age calculation method.

Brian