If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Age calculation problem in a query
I am using an age calculation query in an Access database, but we are having problems. The age calculation seems to work well in Access XP or 2003, but any user that has Access 2000 installed cannot get the query to work.
This is what I have in place now: AgeCalc: FormatNumber(((IIf([Date_Entered]#7/1/2003# Or [Date_Entered] Is Null,#7/1/2003#,[Date_Entered])-[DOB])/365),1) & " Years" The age calculation is found based on the Date in which a person entered the educational program, that is where the Date_Entered field comes in. Access 2000 does not recognize the FormatNumber function. What do I need to do to get around this issue? Thanks. Jim |
#2
|
|||
|
|||
Quote:
I forgot to add a less than () symbol after the first mention of the Date_Entered field in the expression. It should appear as: AgeCalc: FormatNumber(((IIf([Date_Entered]#7/1/2003# Or [Date_Entered] Is Null,#7/1/2003#,[Date_Entered])-[DOB])/365),1) & " Years" |
#3
|
|||
|
|||
Don't use FormatNumber: if you need to format it, use the Format function
instead (with a format parameter of "0" or "General Number") I'm not sure what you're trying to do: formatting #7/1/2003# as a number will return 37803 (the number of days since 30 Dec, 1899) Further, dividing the number of days by 365 is not 100% accurate for calculating age. Use DateDiff("yyyy", [DOB], [Date_Entered]) - _ IIf(Format([Date_Entered], "mmdd") Format([DOB], "mmdd"), 1, 0) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "top_jimmy44" wrote in message ... I am using an age calculation query in an Access database, but we are having problems. The age calculation seems to work well in Access XP or 2003, but any user that has Access 2000 installed cannot get the query to work. This is what I have in place now: AgeCalc: FormatNumber(((IIf([Date_Entered]#7/1/2003# Or [Date_Entered] Is Null,#7/1/2003#,[Date_Entered])-[DOB])/365),1) & " Years" The age calculation is found based on the Date in which a person entered the educational program, that is where the Date_Entered field comes in. Access 2000 does not recognize the FormatNumber function. What do I need to do to get around this issue? Thanks. Jim -- top_jimmy44 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Calculated query problem | sad | Running & Setting Up Queries | 3 | November 25th, 2004 02:35 PM |
Calculated query problem | sad | Running & Setting Up Queries | 0 | November 24th, 2004 10:05 PM |
Linking problem - Access query to MS query to extract data to Exce | FatMan | Running & Setting Up Queries | 0 | November 23rd, 2004 07:43 PM |
Report based on crosstab query - problem printing | Malcolm P | Setting Up & Running Reports | 5 | October 1st, 2004 09:42 PM |