Stumped with an upcoming birthdays query

F

Fred Boer

Hello:



I have been helping someone with a database. I am stumped and would like
some suggestions on what to try next to solve a stubborn problem.

The dtabase is a simple "Contacts" application. The table "tblContacts"
contains contact information, including "DOB" (Date of Birth) and "DOA"
(Date of Anniversary).



We are trying to create an "Upcoming events" query which will select
upcoming birthdays or anniversaries. So, a union query is created. Then a
query based on the union query is created. (qryUnionForEvents, and
qryUpcomingEvents). I get a persistent "Data Type Mismatch" error. I have
tried the following things without success. I have successfully created
these queries from scratch in a new database, so I know that it *should*
work. Any other ideas?



Thanks! Fred Boer



1. Changed references to DAO

2. Put "Option Explicit" in all modules

3. Compact and repair

4. Removed Name AutoCorrect

5. Checked data types in table (both DOB and DOA are Date/Time)

6. Query on upcoming Birthdays works.

7. Query on upcoming Anniversaries works.



But qryUpcomingEvents fails with Data Type Mismatch error



8. Import all objects into new file, compile, save, compact and repair.



Query fails...



9. Decompile.



Query fails...



Here's the SQL......



Query SQL:



SELECT qryUnionForEvents.LastName, qryUnionForEvents.FirstName,
qryUnionForEvents.CombinedDate,
DateSerial(Year(Date()),Month([CombinedDate]),Day([CombinedDate])) AS
HappyHappy, qryUnionForEvents.CardType,
Format(qryUnionForEvents.CombinedDate,"mm/dd") AS EventDate,
Format(qryUnionForEvents.CombinedDate,"mmmm") AS EventMonth,
Format(qryunionforevents.combineddate,"mm") AS EventMonthNumeric

FROM qryUnionForEvents

WHERE (((DateSerial(Year(Date()),Month([CombinedDate]),Day([CombinedDate])))
Between Date() And Date()+30));



Union Query SQL



SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate

FROM tblContacts

UNION ALL SELECT LastName,FirstName, "Anniversary" As CardType, DOA As
CombinedDate

FROM tblContacts

ORDER BY CombinedDate;
 
R

RBear3

Can't you just modify your Upcoming Birthdays query (which works) to include
an *OR* statement with the upcoming anniversary statement that works?
 
F

Fred Boer

Hi:

Yes, me too.. but I've reset the references and it doesn't help.

Thanks!
Fred

Jerry Whittle said:
Anytime I see a problem with the Date() function, I have a knee-jerk
reaction
about references. Go to the following web page for information on checking
references:

http://www.mvps.org/access/bugs/bugs0001.htm
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Fred Boer said:
Hello:

I have been helping someone with a database. I am stumped and would like
some suggestions on what to try next to solve a stubborn problem.

The dtabase is a simple "Contacts" application. The table "tblContacts"
contains contact information, including "DOB" (Date of Birth) and "DOA"
(Date of Anniversary).

We are trying to create an "Upcoming events" query which will select
upcoming birthdays or anniversaries. So, a union query is created. Then a
query based on the union query is created. (qryUnionForEvents, and
qryUpcomingEvents). I get a persistent "Data Type Mismatch" error. I have
tried the following things without success. I have successfully created
these queries from scratch in a new database, so I know that it *should*
work. Any other ideas?

Thanks! Fred Boer

1. Changed references to DAO

2. Put "Option Explicit" in all modules

3. Compact and repair

4. Removed Name AutoCorrect

5. Checked data types in table (both DOB and DOA are Date/Time)

6. Query on upcoming Birthdays works.

7. Query on upcoming Anniversaries works.

But qryUpcomingEvents fails with Data Type Mismatch error

8. Import all objects into new file, compile, save, compact and repair.

Query fails...

9. Decompile.

Query fails...

Here's the SQL......

Query SQL:

SELECT qryUnionForEvents.LastName, qryUnionForEvents.FirstName,
qryUnionForEvents.CombinedDate,
DateSerial(Year(Date()),Month([CombinedDate]),Day([CombinedDate])) AS
HappyHappy, qryUnionForEvents.CardType,
Format(qryUnionForEvents.CombinedDate,"mm/dd") AS EventDate,
Format(qryUnionForEvents.CombinedDate,"mmmm") AS EventMonth,
Format(qryunionforevents.combineddate,"mm") AS EventMonthNumeric
FROM qryUnionForEvents
WHERE
(((DateSerial(Year(Date()),Month([CombinedDate]),Day([CombinedDate])))
Between Date() And Date()+30));

Union Query SQL

SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate
FROM tblContacts
UNION ALL SELECT LastName,FirstName, "Anniversary" As CardType, DOA As
CombinedDate
FROM tblContacts
ORDER BY CombinedDate;
Click to expand...
Click to expand...
 
F

Fred Boer

Hi:

I think I've got it, but I'd like some explanation....

I have discovered that the query will work if every contact has both a
DateOfBirth (DOA) and a DateOfAnniversary(DOA). It gives "Data Type
Mismatch" if one of these are null. If either are null, then the union query
(qryUnionForEvents) will contain records in which the field "CombinedDate"
is null.

I have solved the problem by creating yet another query
(qryUnionForEventsNoNulls), based on the union query (qryUnionForEvents),
which filters out records in which "CombinedDate" is null.

1. I solved this by trial and error: I'd like to be sure I understand the
error - is the following correct?

The error is generated because when Access tries to implement the criteria
"Between Date() And Date()+30". It has to make an evaluation against a null
value: i.e. "Between Date() And Date()+30" compared against "Null", which
fails because you can't do such a comparison against an unknown value.

2. Is there a way to filter out the nulls in the union query itself and
avoid creating "qryUnionForEventsNoNulls"?

Thanks!
Fred


Fred Boer said:
Hello:



I have been helping someone with a database. I am stumped and would like
some suggestions on what to try next to solve a stubborn problem.

The dtabase is a simple "Contacts" application. The table "tblContacts"
contains contact information, including "DOB" (Date of Birth) and "DOA"
(Date of Anniversary).



We are trying to create an "Upcoming events" query which will select
upcoming birthdays or anniversaries. So, a union query is created. Then a
query based on the union query is created. (qryUnionForEvents, and
qryUpcomingEvents). I get a persistent "Data Type Mismatch" error. I have
tried the following things without success. I have successfully created
these queries from scratch in a new database, so I know that it *should*
work. Any other ideas?



Thanks! Fred Boer



1. Changed references to DAO

2. Put "Option Explicit" in all modules

3. Compact and repair

4. Removed Name AutoCorrect

5. Checked data types in table (both DOB and DOA are Date/Time)

6. Query on upcoming Birthdays works.

7. Query on upcoming Anniversaries works.



But qryUpcomingEvents fails with Data Type Mismatch error



8. Import all objects into new file, compile, save, compact and repair.



Query fails...



9. Decompile.



Query fails...



Here's the SQL......



Query SQL:



SELECT qryUnionForEvents.LastName, qryUnionForEvents.FirstName,
qryUnionForEvents.CombinedDate,
DateSerial(Year(Date()),Month([CombinedDate]),Day([CombinedDate])) AS
HappyHappy, qryUnionForEvents.CardType,
Format(qryUnionForEvents.CombinedDate,"mm/dd") AS EventDate,
Format(qryUnionForEvents.CombinedDate,"mmmm") AS EventMonth,
Format(qryunionforevents.combineddate,"mm") AS EventMonthNumeric

FROM qryUnionForEvents

WHERE
(((DateSerial(Year(Date()),Month([CombinedDate]),Day([CombinedDate])))
Between Date() And Date()+30));



Union Query SQL



SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate

FROM tblContacts

UNION ALL SELECT LastName,FirstName, "Anniversary" As CardType, DOA As
CombinedDate

FROM tblContacts

ORDER BY CombinedDate;
Click to expand...
 
J

John Spencer

Fred,

Does the query run correctly if you hard code the dates?

SELECT ...

FROM qryUnionForEvents

WHERE (((DateSerial(Year(Date()),Month([CombinedDate]),Day([CombinedDate])))
Between #1/1/2007# And #1/31/2007#

How about if you use
WHERE isDate(CombinedDate)

How about if you use
WHERE
DateSerial(Year(Date()),Month(DateValue([CombinedDate])),Day(DateValue([CombinedDate])))
Between Date() and Date()+30

The other possibility would be to apply the criteria in the two parts of the
union query and filter the results there.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
A

Amy Blankenship

Fred Boer said:
Hi:

I think I've got it, but I'd like some explanation....

I have discovered that the query will work if every contact has both a
DateOfBirth (DOA) and a DateOfAnniversary(DOA). It gives "Data Type
Mismatch" if one of these are null. If either are null, then the union
query (qryUnionForEvents) will contain records in which the field
"CombinedDate" is null.

I have solved the problem by creating yet another query
(qryUnionForEventsNoNulls), based on the union query (qryUnionForEvents),
which filters out records in which "CombinedDate" is null.

1. I solved this by trial and error: I'd like to be sure I understand the
error - is the following correct?

The error is generated because when Access tries to implement the criteria
"Between Date() And Date()+30". It has to make an evaluation against a
null value: i.e. "Between Date() And Date()+30" compared against "Null",
which fails because you can't do such a comparison against an unknown
value.

2. Is there a way to filter out the nulls in the union query itself and
avoid creating "qryUnionForEventsNoNulls"?
Click to expand...

Why not just use the Nz function?

-Amy
 
F

Fred Boer

Hi John:

I think I found the problem... see above in the thread. The "Data Type
Mismatch" had me scurrying down the references/corruption road for the
longest time. One of those problems like hitting yourself on the head with a
brick. It feels so nice now that I've stopped!

Thanks!
Fred
 
F

Fred Boer

Dear Amy:

I am aware of the NZ function, but I'm not sure how I'd use it here - could
you expand a little bit? Would I try to use it in the union query? The
qryUpcomingEvents? I've never used the NZ function with a date/time data
type before...


Thanks!
Fred
 
J

John Spencer

So it sounds as if the real culprit was the month and day functions trying
to handle "Null" values. I suspect that they were really zero-length
strings.

Month("") returns a mismatch error
Day("") returns a mismatch error
Month(Null) Returns Null

DateSerial(2007,null,null) returns invalid use of null
DateSerial(2007,"","") returns a mismatch error

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
F

Fred Boer

Ah! I am sure you are correct, though I can't check it until tomorrow.
Thanks, John!

Fred
 
A

Amy Blankenship

Fred Boer said:
Dear Amy:

I am aware of the NZ function, but I'm not sure how I'd use it here -
could you expand a little bit? Would I try to use it in the union query?
The qryUpcomingEvents? I've never used the NZ function with a date/time
data type before...
Click to expand...

I haven't either, but I think if you just use it as is, the value returned
by default by the Nz function will evaluate to some time in the distant
past, which would prevent it from affecting your query for future dates, but
not prevent the records with a value in the future in the other column from
being pulled.

So something like

SELECT qryUnionForEvents.LastName, qryUnionForEvents.FirstName,
qryUnionForEvents.CombinedDate,
DateSerial(Year(Date()),Month([CombinedDate]),Day([CombinedDate])) AS
HappyHappy, qryUnionForEvents.CardType,
Format(qryUnionForEvents.CombinedDate,"mm/dd") AS EventDate,
Format(qryUnionForEvents.CombinedDate,"mmmm") AS EventMonth,
Format(qryunionforevents.combineddate,"mm") AS EventMonthNumeric
FROM qryUnionForEvents
WHERE
(((DateSerial(Year(Date()),Month(NZ([CombinedDate])),NZ(Day([CombinedDate])))) Between Date() And Date()+30));If that doesn't work, give the second parameter an actual default date#1/1/1950# or something.HTH;Amy
 
F

Fred Boer

Hello, John:

I spoke too soon. I do not have this working. The query runs - but only
without any criteria. When I put in "Between date() and date()+21" then I
get the data type mismatch.

If I remove all nulls from the table, the process works. But removing the
nulls from the union query doesn't - and I don't understand why not...

I am still stumped.

Fred
 
J

John Spencer

I would guess that the union is turning the value for combined date into
a string that looks like a date - although it should honor the data type
of the two fields. HOWEVER, if either field is a text field storing
strings that look like a date, then both fields will be dates.

Try screening the dates in the two halves of the union query.

or try forcing the "dates" to date value in the where clause

WHERE DateSerial(Year(Date()),
Month(DateValue(CombinedDate)),
Day(DateValue(CombinedDate))) Between Date() and Date()+21

If that fails, and you have a cut down version for test purposes I could
take a look at it.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
F

Fred Boer

Hi John:

Sent ... with many thanks!

Fred

John Spencer said:
I would guess that the union is turning the value for combined date into a
string that looks like a date - although it should honor the data type of
the two fields. HOWEVER, if either field is a text field storing strings
that look like a date, then both fields will be dates.

Try screening the dates in the two halves of the union query.

or try forcing the "dates" to date value in the where clause

WHERE DateSerial(Year(Date()),
Month(DateValue(CombinedDate)),
Day(DateValue(CombinedDate))) Between Date() and Date()+21

If that fails, and you have a cut down version for test purposes I could
take a look at it.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Click to expand...
 
F

Fred Boer

Well, I'm no longer stumped thanks to John Spencer! He came up with the
solution (actually with a couple of solutions, but since the simpler
solution works, I'll stick with it!).

The "Data Type Mismatch" was being caused by Nulls in the "CombinedDate"
field. The Nulls somehow persisted even though the intermediate query
(qryUnionForUpcomingEventsNoNulls) was *supposed* to filter them out.

John suggested the following union query, which works and solves the
problem:

SELECT LastName, FirstName, "Birthday" As CardType, DOB As CombinedDate
FROM tblProblem
WHERE DOB Is Not Null
UNION ALL
SELECT LastName, FirstName, "Anniversary" As CardType, DOA As
CombinedDate
FROM tblProblem
WHERE DOA is Not NullSo that's that... Thanks again to John!
HTH
Fred Boer
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

I want a date (range). 4
Still stumped - can't get a date (range). 6
I'd sure love to be able to do this. 1
Birthdays 1
Problem with Next Birthday Query 3
Please help with this query 1

Top