Mail merge problems... involving numbers 1

[ISTodd]

I am using a Query as a (dde) data source for a Word document. I have an expression in my query to calculate a person's age from the birth date ..(Now()-[bdate])/365.25. the problem is that the age in the query is limited to 1 decimal place (which is what I want) but in the Word Document it has (12) decimal places... TOOO many....

Any help would be greatly appreciated... Thanks, Todd

 

[dhookom]

Try format the expression in the query:
Age:Format((Date()-[BDate])/365.25,"0.0")

You might want to check out Doug Steele's page regarding datediffs

http://members.rogers.com/douglas.j.steele/Diff2Dates.html.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ISTodd]

Hi Duane, Thanks for your reply. When I entered the expression I received an error -

extra ) in query expression

I feel that I should know what to do with this.... I greatly appreciate any suggestions..

Thanks, Todd

 

[dhookom]

Check your expression again. If you don't see the error, then paste your expression here for us to look at.

Do you really want 11 years 4 months to display as 11.3?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ISTodd]

Hi Duane,

Age:Format((Date()-[BDate])/365.25,"0.0")

This was cut and pasted from your intitial e-mail... Since the field codes were correct I went with your exact expression...

Since I work in an elementary school a more precise date (to the tenth for example) helps with certain things.

Ideally it would read 11 years 4 months... I'm a little new at this game so a lot of this is brand new to me... I am grateful for your help....

Todd

 

[dhookom]

I'm confused since there are 3 (s and 3 )s. Maybe paste your entire sql view into a reply.

Also, the link I provided in the first reply has a function that can return exactly the format you want. If you are confused by code/modules/functions, then let us know and we can walk you through implementing the function.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ISTodd]

Hi, I believe I am confused. My excuse is that I am totally self taught and much of what I learned is by looking the answer up in the help menu or sample databases...
I think I found part of the problem however, I seleced tblName prior to running the query, and I don't think I was supposed to do that. When I removed the Table: tblName, the query ran, but the field [Age] was empty...

I may need a walk through... thank you... Todd

 

[dhookom]

I'm confused now. I don't know your table structures or your sql of the query in question. You may have a field named "Date" which would probably cause issues (but I can't see your tables from here).

If you want to use the function from Doug's page:
1) open a new blank module
2) copy the entire function from the web page
3) save the module as "basCalcs"
You are now free to use the function in your query. If you want to find the Years and Months between [BDate] and today then use the expression:
Age: Diff2Dates("ym",[BDate],Date())


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ISTodd]

Thank you very much Duane, for your time and patience! It worked wonderfully and your instructions were very clear even to a novice... Have an enjoyable week, Todd