New Field to my Query

[Bob]

I want to add this into a field in my query, this has been copied from a
text box on a form, I do have DateOfBirth in my Query
Thanks for any Help......Bob

=IIf([cbDateOfBirth]="" Or
IsNull([cbDateOfBirth]),"",funCalcAge(Format("01-Aug-" &
[cbDateOfBirth],"dd/mm/yyyy"),Format(Now(),"dd/mm/yyyy"),1))






..........Jenny Vance

 

[Douglas J. Steele]

How is funCalcAge defined? Because you're using the Format function, you're
passing two strings to it: if it's expecting something other than text, you
could run into problems.

Now, presumably cbDateOfBirth is a combo box on your form. You need to refer
to it as Forms!NameOfForm!cbDateOfBirth. Also, unless you've actually got a
row where DateOfBirth is "", there's no need for that comparison to "" in
that statement.

To be honest, I don't understand why you need to use funCalcAge when you're
always passing it a date of August 1st in the year specified by
cbDateOfBirth and the current date/time.

 

[Arvin Meyer [MVP]]

Assuming that: funCalcAge() is a standard function in a module, not in a
form, remove the = sign and add an alias to the column, so that it looks
like this (without line wrapping):

DOB: IIf([DateOfBirth]="" Or
IsNull([DateOfBirth]),"",funCalcAge(Format("01-Aug-" &
[DateOfBirth],"dd/mm/yyyy"),Format(Now(),"dd/mm/yyyy"),1))

If the function isn't in a standard module, move it there. Also you want to
use the field [DateOfBirth] not the value in the combo box [cbDateOfBirth],
unless you are referring to the form, which you do like:
[Forms]![FormName]![cbDateOfBirth]

One more thing. It is better to use Format(Date(),"dd/mm/yy" instead of
Now() for a birthday, since Now() includes the time, which will usually be
unknown.

 

[Bob]

Thanks I figured it out from taking it out of the middle of another field
Expr1: funCalcAge(Format('01-Aug-' &
[DateOfBirth],'dd/mmm/yyyy'),Format(Now(),'dd/mmm/yyyy'),1)
Bob

Assuming that: funCalcAge() is a standard function in a module, not in a
form, remove the = sign and add an alias to the column, so that it looks
like this (without line wrapping):

DOB: IIf([DateOfBirth]="" Or
IsNull([DateOfBirth]),"",funCalcAge(Format("01-Aug-" &
[DateOfBirth],"dd/mm/yyyy"),Format(Now(),"dd/mm/yyyy"),1))

If the function isn't in a standard module, move it there. Also you want
to use the field [DateOfBirth] not the value in the combo box
[cbDateOfBirth], unless you are referring to the form, which you do like:
[Forms]![FormName]![cbDateOfBirth]

One more thing. It is better to use Format(Date(),"dd/mm/yy" instead of
Now() for a birthday, since Now() includes the time, which will usually be
unknown.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I want to add this into a field in my query, this has been copied from a
text box on a form, I do have DateOfBirth in my Query
Thanks for any Help......Bob

=IIf([cbDateOfBirth]="" Or
IsNull([cbDateOfBirth]),"",funCalcAge(Format("01-Aug-" &
[cbDateOfBirth],"dd/mm/yyyy"),Format(Now(),"dd/mm/yyyy"),1))






.........Jenny Vance

 

[John Vinson]

Thanks I figured it out from taking it out of the middle of another field
Expr1: funCalcAge(Format('01-Aug-' &
[DateOfBirth],'dd/mmm/yyyy'),Format(Now(),'dd/mmm/yyyy'),1)

I'm sorry, this makes no sense at all. If DateOfBirth is (say)
#7/21/1999# the first Format expression will give

01-Aug-21/Jul/1999

which isn't a date at all. How the Format() function will treat this
string, other than as an error, is beyond me!

What does funCalcAge expect as paramters?
What are you trying to ACCOMPLISH? (you haven't said).

John W. Vinson[MVP]

 

[Bob]

John, the row source to Enter date is 1996;1997;1998;1999;2000;2001....So on
Horses birthdays are Aug 1st and actually up untill last year if a horse was
born July 31 he actually turned 1 the next day but things have changed
slightly , the stallion cant serve a mare untill Sep 1 being an 11 month
Peggy so if he is born July 31 he is not disadvantaged any more
Could you help me with this I have a Query on client owing totals
QryOverdue, 3 fields ClientID, Payable, Name
At the moment it is sorted Descending on Payable
How I wanted them sorted is [Payable] >1 then [Name] Descending
Thanks for any Help.....Bob

Thanks I figured it out from taking it out of the middle of another field
Expr1: funCalcAge(Format('01-Aug-' &
[DateOfBirth],'dd/mmm/yyyy'),Format(Now(),'dd/mmm/yyyy'),1)

I'm sorry, this makes no sense at all. If DateOfBirth is (say)
#7/21/1999# the first Format expression will give

01-Aug-21/Jul/1999

which isn't a date at all. How the Format() function will treat this
string, other than as an error, is beyond me!

What does funCalcAge expect as paramters?
What are you trying to ACCOMPLISH? (you haven't said).

John W. Vinson[MVP]

 

[John Vinson]

John, the row source to Enter date is 1996;1997;1998;1999;2000;2001....So on

The parameter [Enter date] does not appear in your query at all. Do
you mean [DateOfBirth]???

Horses birthdays are Aug 1st and actually up untill last year if a horse was
born July 31 he actually turned 1 the next day but things have changed
slightly , the stallion cant serve a mare untill Sep 1 being an 11 month
Peggy so if he is born July 31 he is not disadvantaged any more
Could you help me with this I have a Query on client owing totals
QryOverdue, 3 fields ClientID, Payable, Name
At the moment it is sorted Descending on Payable
How I wanted them sorted is [Payable] >1 then [Name] Descending

Again - you're not making this very clear. What does the date of birth
have to do with [Payable] or with sorting? What is your Expr1
*INTENDED* to calculate?

Remember: You know your database. You know your business (until you
posted this nobody out here had ANY idea that you were talking about
horseracing or about August 1 being a special date). We're not
sorcerers or mindreaders, and we cannot see your computer.

Please think through your posts with that in mind, and read them over
on the assumption that we would like to help, but that we do need to
understand the question and your context.

Thanks for any Help.....Bob

Thanks I figured it out from taking it out of the middle of another field
Expr1: funCalcAge(Format('01-Aug-' &
[DateOfBirth],'dd/mmm/yyyy'),Format(Now(),'dd/mmm/yyyy'),1)

I'm sorry, this makes no sense at all. If DateOfBirth is (say)
#7/21/1999# the first Format expression will give

01-Aug-21/Jul/1999

which isn't a date at all. How the Format() function will treat this
string, other than as an error, is beyond me!

What does funCalcAge expect as paramters?
What are you trying to ACCOMPLISH? (you haven't said).

John W. Vinson[MVP]

John W. Vinson[MVP]