Age calc wrong.

[TOMMY.MYERS668]

Ok I feel really stupid. I need to calculate age from dob in years so i can pull some data from another table.
Should be:
Age=datediff ( "yyyy" me! [dob],date)
however this doesnt work ive aslo tried 1 y and doing it by days then devide by 365.25 , and also using now instead of date. The example I can give is
Dob =7/27/1975


The answe it gives me is 38 and it should be 37 because im pretty sure I know how old I am. Even worked it out on paper because at my sge you can forget things ...like whole years..lol please help

[JoeM]

I think it is rounding off to the nearest whole year. Check out this link to see how to avoid that and just get full years: http://office.microsoft.com/en-us/ac...001055071.aspx

[TOMMY.MYERS668]

Ok tried that and get a compile error. Most like because I think thats for just creating a unbound text box. But I actually need to store the answer for later use. So im in an after update event and will need the vba formatting statement. Only have net on my phone right now so I'm trying to look that up. But yes your right I think its just rounding.

[JoeM]

Why not do the calculation in the underlying query, and just include that calculated field on your Report/Form?

[TOMMY.MYERS668]

Honestly because my knowledge of querys is non existent and I know vb pretty good so I just usually do everything with it. But this is just printing a formatted memo (report) and I need the age to reference a table for max weight.

[Missinglinq]

You can't simply use DataDiff, as you've found out, because in essence, it merely subtracts one year from the other, in essence, and doesn't take into account whether of not the person has already had their birthday for the given year at that point in time! There are several formulas out there; here's one that I use:

Me.AgeToday = DateDiff("yyyy", [DOBField], Date()) - IIf(Format$(Date(), "mmdd") < Format$([DOBField], "mmdd"), 1, 0)

Linq ;0)>

[TOMMY.MYERS668]

You sir are a saint. Works perfect! Ty

[JoeM]

Here is the formula contained in the link that I sent (it sounds like maybe you couldn't see it). It is somewhat similar to Linq's:

=DateDiff("yyyy", [Birthdate], Now())+ Int( Format(now(), "mmdd") < Format( [Birthdate], "mmdd"))

Honestly because my knowledge of querys is non existent and I know vb pretty good so I just usually do everything with it. But this is just printing a formatted memo (report) and I need the age to reference a table for max weight.

Queries are actually much easier than VBA, and one of the main reason to use Access. You can do calculations, filtering, linking to other tables, etc. You can also use queries as the source of Forms and Reports, just as you would with Tables.

If you are going to use Access, you will definitely want to familiarize yourself with them. It will make your life a whole heck of a lot easier!

[TOMMY.MYERS668]

I actually started looking at them trying to help another guy on here. It just seems like for why I'm doing for the army right now theres not much use for them yet. But I'm trying to learn all I can.

[JoeM]

It just seems like for why I'm doing for the army right now theres not much use for them yet.

You might not just have a full understanding of them yet. They are usually one of the primary reasons to use Access in the first place! They are essential to relational databases.
Otherwise, Excel would probably do (unless they only reason you are using Access is because of a size issue).

[TOMMY.MYERS668]

Actually im using it right now because I can perfectly format a military memo and prefill data into them by entering it on a general form. Then print 12 different memos for say 300 instructors all at once. But so much changes from memo to memo such as unit adress and.commanders signature blocks that doing 1 person in word takes about 2 hours. Does that make any sense or its there an easier way than access?

[JoeM]

I see. So you probably have a Table as the Control Source of your Form. If you use a Query as the Control Source instead, you can perform many/all of your calculations in the query instead of doing it on the Form itself (I think they are easier to maintain there).

Also, here is another good example of why you may want to base your Form on a Query instead of a Table. What if you only wanted the Form for a subset of your Instructors table (let's say you have a Location field on that table, and you only wanted to include a certain Location). With a query, you just add that Location value to the Criteria line of that Location field, and your Form will be restricted to just that Location.

[TOMMY.MYERS668]

Thats kind of how another one im doing is but the army has put that on hold till this one is done. And now I have a dlookup that isnt working but ill put that in a new post when I get home because I'm not typing all that code into my phone.

[TOMMY.MYERS668]

Maybe I can get it in here:
Me![max weight]= dlookup ("&me![agebracketpull]&","[height weight table]","[height]="&me![height]&")

I get wrong number of arguements or invalid property assignment.

[JoeM]

Assuming your logic is correct, I think this is what you want:

DLookUp("[agebracketpull]","[height weight table]","[height]=" & [height])