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;
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;