hi im really really new to access and need some help. I set up a table for a football league and its called players and this table displays name date of birth, team and age. The age needs to automatically update each year and it needs to be seen in the table. i have set up a query and it does this perfectly for me but it does not display the result in the player table but it does show it in the age field in the query. can i get the table field "age" to show the query field "age" all help appreciated greatly
tadgh
Function AgeYear(dtmBD As Date, Optional dtmDate As Date = 0) _
As Integer
' Calculate a person's age, given the person's birth date and
' an optional "current" date.
If dtmDate = 0 Then
' Did the caller pass in a date? If not, use
' the current date.
dtmDate = Date
End If
dhAge = DateDiff("yyyy", dtmBD, dtmDate) + _
(dtmDate < DateSerial(Year(dtmDate), Month(dtmBD), _
Day(dtmBD)))
End Function
sorry im confused as i say im very new but thanks for trying
so if im right your saying go back into query design and insert module. if this is true then in what field do i insert module and how does this transfer data from query age field to player table age field.....im sorry i think i really need it step by step ????
steps:
1. on your main db, press Alt-F11 to go to visual basic editor. on top is the menu, select Insert->Module. paste the code in that window and save.
2. exit VBE, modify your query and add:
Field: Age: AgeYears([age field in your table here])
you must supply the correct fieldname for the blue colored text above.
ok getting clearer so i open my player table (do i click in field age ) and press alt f11 insert module and paste code
1) do i just x out of that module when code is pasted ?
2) after exiting vbe i go to table query and insert in the age field what you have above is that correct ?
tablequery means nothing. There are tables and there are queries. The solution arne is giving you has nothing to do with your table. You do not store calculated values...you calculate them. And the place to do so is in a query.
The function code he gave goes in a module, the other code goes as a field in a query.
ok, one more time.
close your db.
open it again, press Alt-F11 to go to vbe.
in menu, Insert->Module. paste the code. save (ctrl-s) and exit vbe (click the x on top of the window).
open your query in design mode.
on the window below, left side says Field:
on this row find a blank one and type:
Field: Age: AgeYears([birthday field in your table here])
LOL OK THINK I GOT THAT LOL and thanks for the patience as i said i really am very new..... will this display the current age in my table as opposed to only displaying it in a query ???? please say yes and again many thanks
What's so magical about a table? Why must you have the correct age there? Just reference the query.
Also, while arne gave you code to update your table with a person's age, it's only guaranteed to be correct for 1 day. As soon as someone has a birthday, your age field is now incorrect and you are referencing incorrect data. This is why you don't store calculated values in a table....you calculate them when you need them....in a query.
The reason to use the module is because the module works and your DateDiff code doesn't.
Find someone with a birthday of December 8 to December 31 and tell me if your code produces the right age. You can't just subtract the current year from the birth year to get someone's age.
Also, if you look at arne's code he did you one better than calculate someone's current age. With his code you can determine anyone's age on any particular date. If you wanted to find all employees ages on 1/1/2014 you could do so with his function:
when i type this" Field: Age: AgeYears([Date of Birth])"into the query in the age field it is saying it is saying the expression i entered contains invalid syntax "you may have entered an operand without an operator
To answer a question that occurred in passing, and since you claim to be new to access, tadgh, here is a little fact to help you get oriented.
Don't get bound up on having tables display anything other than raw data. You can write queries to drive forms because (here's the key thing I want you know) both tables and queries supply RECORDSETS and all of the Access components work with RECORDSETS. There is nothing magical about tables as sources for data - but there IS something magical about queries, because they can filter, reformat, or compute the things you see. They can bring things together, take them apart, and manipulate lots of stuff - yet forms, reports, and datasheet views don't care that you aren't looking at a table. They just show recordset contents, whatever was presented to them.
You can get a feel for what I'm saying by opening a table in datasheet view and then opening a SELECT query in datasheet view. They look the same. (Because to the implied form that is displaying contents, they really ARE the same.)
Now, as to the module, which is certainly correct: There is another way to do this that might be easier to write in a query without needing a module:
Code:
Int( ( CDbl(Now()) - Cdbl(Birthday)) / 365.25 )
I think the ONLY time this will be wrong (and it will be wrong only one day per leap year) would be on Feb 29th of a leap year for someone whose birthday was Mar 1st of a non-leap year. Note also that if you happen to be computing the age of Methuselah, the correct fraction is 356.2422.
This works because dates are internally expressed in the system as DOUBLE-format real numbers that are the difference in days and fractions thereof since the system reference date. The above formula computes the difference between now and the birthday as a number of days and fractions, then divides that by the number of days in a year, then truncates it. (Do NOT use a rounding function for this purpose!)
For someone older than, say, 15 years old, the odds are pretty good for this to be right 99.93% of the time. The older the person is, the better the odds get.
Finally, this will illustrate another important principle. I am ABSOLUTELY NOT saying that Arnel's post is wrong. I am demonstrating that there are usually several ways to attack a problem in Access. He went one way, I went another way. You, as a person new to Access, will need to learn that getting good in Access involves seeing the different ways that people approach problems. Sometimes one way will work better, sometimes another way. "Getting good" means "having more than one tool in the toolkit" when problem-solving.
The Doc Man has it right. Your basic arrangement is Table -> Query -> Form. The table is where you put your raw data, the query is where you process the data (calculations, joins, etc.), and the form is the user interface. You can set a form to display in datasheet view, if that is what you prefer, or you can arrange the controls in any manner that better suits your needs.
Creating a general module for the function works; however, a form can support its own code module, and that is a good idea for such code that is not likely to be shared with other forms. It helps to keep things tidy.
Listen guys thanks a mill I am 5 days into teaching myself through tutorials. When i use arnels it tells me its invalid syntax even after i make the adjustments plog suggests. When I use doc mans code in a query which i copied and pasted it tells me the expression was typed incorrectly or is too complicated to calculate
