Date criteria problem

[PayeDoc]

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 Spencer]

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

 

[John Spencer]

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

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

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

 

[Jerry Whittle]

One problem with CDate is that it will fail miserably if it runs into
something that it cannot convert into a date. I always, and I mean always,
use the IsDate function first to see if the text can possibly be evaluated as
a date before passing it to CDate. Try this:

SELECT staffs.DOB,
IsDate(retirementdate([dob],[sex])) AS retdate
FROM staffs
WHERE IsDate(retirementdate([dob],[sex])) = False ;

If this returns any records, you need to fix that data.

I sometimes use an IIf statement to return a bogus date if CDate can't
convert it. Something like below:

SELECT IIf(IsDate([date])=True,Cdate([date]),#1/1/1950#) AS NotDate
FROM tblDatesText;

 

[PayeDoc]

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

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

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

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

 

[vanderghast]

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

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 Spencer]

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

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

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

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

 

[PayeDoc]

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

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

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

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

 

[KenSheridan via AccessMonster.com]

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
data type or exclude any rows with Null dob or sex columns being returned by
the query which calls the function by restricting it to:

WHERE dob IS NOT NULL AND sex IS NOT NULL

Ken Sheridan
Stafford, England

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

Is SEX ever null? IF so, then you are going to generate an error since a NULL
is not a string.

[quoted text clipped - 124 lines]

 

[John Spencer]

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
data type or exclude any rows with Null dob or sex columns being returned by
the query which calls the function by restricting it to:

WHERE dob IS NOT NULL AND sex IS NOT NULL

Ken Sheridan
Stafford, England

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

Is SEX ever null? IF so, then you are going to generate an error since a NULL
is not a string.

[quoted text clipped - 124 lines]

Many thanks
Les

 

[John Spencer]

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

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

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

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


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

 

[PayeDoc]

John

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

Les

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

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

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


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

 

[KenSheridan via AccessMonster.com]

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

Ken Sheridan
Stafford, England

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]

 

[David W. Fenton]

Is SEX ever null?

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

 

[Leslie Isaacs]

Hello "Vanderghast"

Thanks for your reply.
It was a typo!

Les

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

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 W. Vinson]

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

<snork!!!>

It could also have been a Yes/No field.

 

[KenSheridan via AccessMonster.com]

At our age a Double is probably no longer advisable!

Ken Sheridan
Stafford, England

 

[John W. Vinson]

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

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

 

[David W. Fenton]

<snork!!!>

It could also have been a Yes/No field.

Hmm. would FALSE be male or female?

 

[John W. Vinson]

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_)