By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,657 Members | 1,682 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,657 IT Pros & Developers. It's quick & easy.

Criteria on a derived field

P: 77
Hi,

I have a column in my query that derives Age like this,
Age: DateDiff("yyyy",[birth date],[Entry Date]).
I tried giving a value '18' as the criteria and its not letting me do it. Without the criteria everything works fine.

I am sure someone might have come across similar situation, please help me with your findings.

Thanks
Jan 23 '08 #1
Share this Question
Share on Google+
2 Replies


missinglinq
Expert 2.5K+
P: 3,532
Are you entering it as

'18'

as you posted or simply

18


The 18 without the single quotes should work, assuming the names of the two fields are spelled correctly.

What exactly happens when you try to run the query with the criteria?

When you run it without the criteria, does it return ages according to the calculation?

Does anyone have an age of 18?

As an aside, your formula may work in your current application, depending on your needs, but it doesn't always return an accurate age. It returns the difference
of years, but doesn't take into account whether or not the person has had their birthday for the [Entry Date] year.

Linq ;0)>
Jan 23 '08 #2

P: 77
Are you entering it as

'18'

as you posted or simply

18


The 18 without the single quotes should work, assuming the names of the two fields are spelled correctly.

What exactly happens when you try to run the query with the criteria?

When you run it without the criteria, does it return ages according to the calculation?

Does anyone have an age of 18?

As an aside, your formula may work in your current application, depending on your needs, but it doesn't always return an accurate age. It returns the difference
of years, but doesn't take into account whether or not the person has had their birthday for the [Entry Date] year.

Linq ;0)>

Let me explain what I am doing,

I have Birth Date as one of the columns in my query. Using Mon([Birth Date]) and Day([Birth Date]) I get the Birth Month and Birth Day for every record. I also ask for the Year from the user. Using the Year they enter, I derive Entry Date= DateSerial([Year],[Birth Month],[Birth Day]).
Then in the next column I calculate Age= DateDiff("yyyy",[Birth date],[Entry Date]).
Without giving any criteria it calculates the Age correctly in years, but when I give 18 or '18' as criteria I get a pop up to enter the Entry Date, which is not what I want.

Deriving the Entry Date as I explained above, my idea was to calculate the age on the birth day itself on the year entered by the user.
For example if the user entered 2005, for a person whose Birth Date = 01/04/1987, the Entry Date will be 01/04/2005 according to my method.

Sorry if I confused everybody,

Thanks
Jan 23 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.