A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Age calculation problem in a query



 
 
Thread Tools Display Modes
  #1  
Old December 7th, 2004, 06:50 PM
top_jimmy44 top_jimmy44 is offline
Member
 
First recorded activity by OfficeFrustration: Dec 2004
Posts: 2
Default 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  
Old December 7th, 2004, 09:00 PM
top_jimmy44 top_jimmy44 is offline
Member
 
First recorded activity by OfficeFrustration: Dec 2004
Posts: 2
Default

Quote:
Originally Posted by top_jimmy44
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

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  
Old January 2nd, 2005, 02:14 PM
Douglas J. Steele
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 05:53 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.