Date criteria problem

If SEX can be null and DOB is NEVER null then change your declaration line to

Public Function RetirementDate(ByVal DOB As Date, sex As VARIANT) As Date

IF either sex or DOB can be NULL then change your declaration line to

Public Function RetirementDate(ByVal DOB As Variant, sex As Variant) As Date



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

PayeDoc wrote:
John

Now I fully understand what you're saying ... but something's still wrong!
For some reason my modified RetirementDate function is failing to catch the
null DOBs. When IsNull(DOB) didn't work, I tried IsDate(DOB), but that was
no better. The function now starts:

Public Function RetirementDate(ByVal DOB As Date, sex As String) As Date
Dim rd As Date
If IsNull(sex) Or IsDate(DOB) = False Then
RetirementDate = #1/1/1899#
Else
If sex = "M" Then
rd = DateAdd("yyyy", 65, DOB)
Else
Select Case DOB
'changes2 only below here
Case Is #4/6/1955#
rd = DateAdd("yyyy", 65, DOB)
... and ends
Case Else
rd = DateAdd("yyyy", 60, DOB)
End Select
End If
RetirementDate = rd
End If
End Function

I hope you can see why this still doesn't work, because I've been staring at
it for a long time and it looks perfect to me!
Thanks once again for your help.
Les



"John Spencer" wrote in message
...
Is SEX ever null? IF so, then you are going to generate an error since a
NULL
is not a string.

AND you are going to run the RetirementDate function for EVERY record in
your
database since every record has to be checked for the where clause. One
null
and you will have an error (usually a "mismatched type" error.

You can modify the function to accept a null and
Public Function RetirementDate(ByVal DOB As Date, SEX as Variant) As
Variant
IF IsNull(SEX) then
RetirementDate = Null
ELSE
...

OR you can force a specific invalid date

Public Function RetirementDate(ByVal DOB As Date, sex As Variant) As Date
IF isNull(SEX) then
RetirementDate = #1/1/1899#
ELSE
....

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

PayeDoc wrote:
Hello John

Many thanks for your reply.

Although I'm sure what you suggest is what's going on here, I don't
really
understand why!
My RetirementDate function is below, and I can't see how it can fail to
return a valid date as long as a valid (i.e. genuine date) value for DOB
is
provided. When I remove the criteria for the DOB and run the query, I
get
valid DOB values for every record. So why does just adding the criteria
upset the query?

It's probably staring me in the face, but I just can't see the problem!

Hope you can help.
Thanks again
Les


The query I now have is:
SELECT staffs.practice, staffs.DOB, retirementdate([dob],[sex]) AS
retdate,
staffs.sex
FROM staffs
WHERE (((staffs.practice)=[Forms]![frm x main]![prac name]) AND
((staffs.DOB)#1/1/1901# And (staffs.DOB) Is Not Null) AND
((retirementdate([dob],[sex]))#7/7/2010#) AND ((staffs.sex) Is Not
Null));
The function is:
Public Function RetirementDate(ByVal DOB As Date, sex As String) As Date
Dim rd As Date
If sex = "M" Then
rd = DateAdd("yyyy", 65, DOB)
Else
Select Case DOB
Case Is #4/6/1955#
rd = DateAdd("yyyy", 65, DOB)
Case Is #3/6/1955#
rd = #3/6/2019#
(various other Cases are here, then ...)
Case Else
rd = DateAdd("yyyy", 60, DOB)
End Select
End If
RetirementDate = rd
End Function


"John Spencer" wrote in message
...
I forgot to mention that if your function returns a null value then
CDATE
will
error.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

John Spencer wrote:
It could be a problem with RetirementDate function. You did not post
that.
For instance, if dob or sex is null in ANY RECORD in your database the
function MIGHT fail and if it does then your query will fail because
you
are checking every record in the database.

We need to see the code for RetirementDate.

You might check be able to work around this by using a subquery in the
FROM clause. My assumption is that SEX is not a boolean (yes/no)
field.
SELECT Temp.DOB, CDate(retirementdate([dob],[sex])) AS retdate
FROM
(SELECT * FROM staffs
WHERE DOB#1/1/1901#
AND Sex is Not Null) as TEMP

WHERE CDate(retirementdate([dob],[sex])#7/7/2010#


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

PayeDoc wrote:
Hello All

What's wrong with this:

SELECT staffs.DOB, CDate(retirementdate([dob],[sex])) AS retdate
FROM staffs
WHERE (((staffs.DOB)#1/1/1901#) AND
((CDate(retirementdate([dob],[sex])))#7/7/2010#));

My function 'retirementdate' works elsewhere perfectly, and
definitely
returns a date - the CDate above should be superfluous, but it's
there
bacause I keep getting a "data type mismatch in criteria expression"
message, so I wanted to 'force' it to be a date (which it already
is!).
Initially the crieria for retirementdate([dob],[sex]) was an
expression
derived from a date field on a form, but I kept getting the error and
gradually simplified the criteria, until it is now #7/7/2010#: can't
get
much simpler than that!

Hope someone can help.
Many thanks
Les

John

BINGO - that's it!
Very many thanks for your help - as ever!

Les


"John Spencer" wrote in message
...
If SEX can be null and DOB is NEVER null then change your declaration line
to

Public Function RetirementDate(ByVal DOB As Date, sex As VARIANT) As Date

IF either sex or DOB can be NULL then change your declaration line to

Public Function RetirementDate(ByVal DOB As Variant, sex As Variant) As
Date



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

PayeDoc wrote:
John

Now I fully understand what you're saying ... but something's still
wrong!
For some reason my modified RetirementDate function is failing to catch
the
null DOBs. When IsNull(DOB) didn't work, I tried IsDate(DOB), but that
was
no better. The function now starts:

Public Function RetirementDate(ByVal DOB As Date, sex As String) As Date
Dim rd As Date
If IsNull(sex) Or IsDate(DOB) = False Then
RetirementDate = #1/1/1899#
Else
If sex = "M" Then
rd = DateAdd("yyyy", 65, DOB)
Else
Select Case DOB
'changes2 only below here
Case Is #4/6/1955#
rd = DateAdd("yyyy", 65, DOB)
... and ends
Case Else
rd = DateAdd("yyyy", 60, DOB)
End Select
End If
RetirementDate = rd
End If
End Function

I hope you can see why this still doesn't work, because I've been
staring at
it for a long time and it looks perfect to me!
Thanks once again for your help.
Les



"John Spencer" wrote in message
...
Is SEX ever null? IF so, then you are going to generate an error since
a
NULL
is not a string.

AND you are going to run the RetirementDate function for EVERY record
in
your
database since every record has to be checked for the where clause. One
null
and you will have an error (usually a "mismatched type" error.

You can modify the function to accept a null and
Public Function RetirementDate(ByVal DOB As Date, SEX as Variant) As
Variant
IF IsNull(SEX) then
RetirementDate = Null
ELSE
...

OR you can force a specific invalid date

Public Function RetirementDate(ByVal DOB As Date, sex As Variant) As
Date
IF isNull(SEX) then
RetirementDate = #1/1/1899#
ELSE
....

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

PayeDoc wrote:
Hello John

Many thanks for your reply.

Although I'm sure what you suggest is what's going on here, I don't
really
understand why!
My RetirementDate function is below, and I can't see how it can fail
to
return a valid date as long as a valid (i.e. genuine date) value for
DOB
is
provided. When I remove the criteria for the DOB and run the query, I
get
valid DOB values for every record. So why does just adding the
criteria
upset the query?

It's probably staring me in the face, but I just can't see the
problem!

Hope you can help.
Thanks again
Les


The query I now have is:
SELECT staffs.practice, staffs.DOB, retirementdate([dob],[sex]) AS
retdate,
staffs.sex
FROM staffs
WHERE (((staffs.practice)=[Forms]![frm x main]![prac name]) AND
((staffs.DOB)#1/1/1901# And (staffs.DOB) Is Not Null) AND
((retirementdate([dob],[sex]))#7/7/2010#) AND ((staffs.sex) Is Not
Null));
The function is:
Public Function RetirementDate(ByVal DOB As Date, sex As String) As
Date
Dim rd As Date
If sex = "M" Then
rd = DateAdd("yyyy", 65, DOB)
Else
Select Case DOB
Case Is #4/6/1955#
rd = DateAdd("yyyy", 65, DOB)
Case Is #3/6/1955#
rd = #3/6/2019#
(various other Cases are here, then ...)
Case Else
rd = DateAdd("yyyy", 60, DOB)
End Select
End If
RetirementDate = rd
End Function


"John Spencer" wrote in message
...
I forgot to mention that if your function returns a null value then
CDATE
will
error.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

John Spencer wrote:
It could be a problem with RetirementDate function. You did not
post
that.
For instance, if dob or sex is null in ANY RECORD in your database
the
function MIGHT fail and if it does then your query will fail because
you
are checking every record in the database.

We need to see the code for RetirementDate.

You might check be able to work around this by using a subquery in
the
FROM clause. My assumption is that SEX is not a boolean (yes/no)
field.
SELECT Temp.DOB, CDate(retirementdate([dob],[sex])) AS retdate
FROM
(SELECT * FROM staffs
WHERE DOB#1/1/1901#
AND Sex is Not Null) as TEMP

WHERE CDate(retirementdate([dob],[sex])#7/7/2010#


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

PayeDoc wrote:
Hello All

What's wrong with this:

SELECT staffs.DOB, CDate(retirementdate([dob],[sex])) AS retdate
FROM staffs
WHERE (((staffs.DOB)#1/1/1901#) AND
((CDate(retirementdate([dob],[sex])))#7/7/2010#));

My function 'retirementdate' works elsewhere perfectly, and
definitely
returns a date - the CDate above should be superfluous, but it's
there
bacause I keep getting a "data type mismatch in criteria
expression"
message, so I wanted to 'force' it to be a date (which it already
is!).
Initially the crieria for retirementdate([dob],[sex]) was an
expression
derived from a date field on a form, but I kept getting the error
and
gradually simplified the criteria, until it is now #7/7/2010#:
can't
get
much simpler than that!

Hope someone can help.
Many thanks
Les

Ah, I'd missed the restriction on the return value of the function.

Ken Sheridan
Stafford, England

John Spencer wrote:
That would work except the poster is using the expression in the WHERE clause
so even though you are excluding nulls in the where clause the engine may be
processing the record anyway in the expression. If the sql engine is very
smart it could exclude the records based on the tests for null BEFORE it
processes the function. I would not trust it to do so without a lot of
testing, since based on past experience I have not seen that the sql engine
(jet) is smart enough to do that.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

The dob and sex arguments are declared as Date and String data types, so will
not accept Nulls. For this you'll either need to declare them as Variant
[quoted text clipped - 44 lines]
Many thanks
Les

--
Message posted via http://www.accessmonster.com

John Spencer wrote in
:

Is SEX ever null?

I stopped reading there and wondered what newsgroup I was in.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Hello "Vanderghast"

Thanks for your reply.
It was a typo!

Les

"vanderghast" vanderghast@com wrote in message
...
Your ( don't match your ), in the where clause, have you typed or pasted
the code? Take a look at the where clause you posted:



WHERE (
(
(staffs.DOB)#1/1/1901#
)
AND
(
(
CDate( retirementdate([dob],[sex] )
)
)
#7/7/2010#
)
) -- too many )

and the comparison with the constant date is at the wrong level.



Vanderghast, Access MVP


"PayeDoc" wrote in message
...
Hello All

What's wrong with this:

SELECT staffs.DOB, CDate(retirementdate([dob],[sex])) AS retdate
FROM staffs
WHERE (((staffs.DOB)#1/1/1901#) AND
((CDate(retirementdate([dob],[sex])))#7/7/2010#));

My function 'retirementdate' works elsewhere perfectly, and definitely
returns a date - the CDate above should be superfluous, but it's there
bacause I keep getting a "data type mismatch in criteria expression"
message, so I wanted to 'force' it to be a date (which it already is!).

Initially the crieria for retirementdate([dob],[sex]) was an expression
derived from a date field on a form, but I kept getting the error and
gradually simplified the criteria, until it is now #7/7/2010#: can't get
much simpler than that!

Hope someone can help.
Many thanks
Les

On 20 Apr 2010 19:42:11 GMT, "David W. Fenton"
wrote:

John Spencer wrote in
:

Is SEX ever null?

I stopped reading there and wondered what newsgroup I was in.

snork!!!

It could also have been a Yes/No field.
--

John W. Vinson [MVP]

At our age a Double is probably no longer advisable!

Ken Sheridan
Stafford, England

John W. Vinson wrote:
Is SEX ever null?

I stopped reading there and wondered what newsgroup I was in.

snork!!!

It could also have been a Yes/No field.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201004/1

On Wed, 21 Apr 2010 09:52:51 GMT, "KenSheridan via AccessMonster.com"
u51882@uwe wrote:

I checked with my wife and she said that Multivalue Fields are *OUT*.

At our age a Double is probably no longer advisable!

Ken Sheridan
Stafford, England

John W. Vinson wrote:
Is SEX ever null?

I stopped reading there and wondered what newsgroup I was in.

snork!!!

It could also have been a Yes/No field.
--

John W. Vinson [MVP]

John W. Vinson wrote in
:

On 20 Apr 2010 19:42:11 GMT, "David W. Fenton"
wrote:

John Spencer wrote in
:

Is SEX ever null?

I stopped reading there and wondered what newsgroup I was in.

snork!!!

It could also have been a Yes/No field.

Hmm. would FALSE be male or female?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On 21 Apr 2010 18:30:36 GMT, "David W. Fenton"
wrote:

John W. Vinson wrote in
:

On 20 Apr 2010 19:42:11 GMT, "David W. Fenton"
wrote:

John Spencer wrote in
:

Is SEX ever null?

I stopped reading there and wondered what newsgroup I was in.

snork!!!

It could also have been a Yes/No field.

Hmm. would FALSE be male or female?

Male: "All men are false, says my mother/They'll tell you wicked, lovin'
lies..." (Silver Dagger, from the album _Joan Baez_)

--

John W. Vinson [MVP]