Casting with Access (Jet)

[Chasidy]

Does anyone know a work around for casting in a union query in Access. I am trying to do a report using ADO and I would like to use a query. When I create my union query, I want fields seperated
SELECT
Name
Gift Date
Gift Amount
Null
Null
From Folder, gift (blah, blah)
UNION
SELECT
Name
Null
Null
Pledge Date
Pledge Amount
FROM Folder, pledge (blah, blah).

I can retrieve the information from the first query perfectly but the information from the second query (where the nulls are involved) come up garbage.
In SQL, you can just cast the null placeholders to the field type you desire.
Has anyone been successful doing this with Access?
Thanks in advance.

 

[IdoMillet]

No need to cast the nulls in Access. I've done similar things with Access and your query should work fine.

Post the full query so we can see if something else is the problem.

By the way, use UNION ALL if you don't need to remove duplicates. This would speed up the query very significantly.

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Chasidy]

The main reason I was using the union query was to remove the duplicates. We switched from DAO to ADO and all the "easy" links you could use in DAO are not supported in ADO...so the headache begins.

Instead of casting, I just used a 0 as data which moderately works except for the date field. But I can just to DTSToDate ({ado.PledgeDate}) but I can't get that to show data.

My query is as follows:

GiftTransaction.PostedFlag,
Appeal.AppealDate,
Appeal.Description,
Appeal.Goal,
Appeal.TotalExpense,
Appeal.TotalSent,
Appeal.Undelivered,
0 as PledgeBalance,
0 as PledgeDate,
0 as PledgeID,
0 as TotalAmount
FROM (((GiftTransaction LEFT JOIN
Folder ON Folder.FolderID = GiftTransaction.FolderID) LEFT JOIN APPEAL on GiftTransaction.AppealCode = Appeal.AppealCode) LEFT JOIN GiftDesignation
ON GiftDesignation.GiftID = GiftTransaction.GiftID) ;
UNION SELECT
Folder.FolderID,
Folder.FirstGiftDate,
Folder.FirstPledgeDate,
Pledge.AppealCode,
null,
null,
null,
null,
null,
Appeal.AppealDate,
Appeal.Description,
Appeal.Goal,
Appeal.TotalExpense,
Appeal.TotalSent,
Appeal.Undelivered,
Pledge.PledgeBalance,
Pledge.PledgeDate,
Pledge.PledgeID,
Pledge.TotalAmount
FROM ((Folder LEFT JOIN Pledge ON Folder.FolderID = Pledge.FolderID) LEFT JOIN Appeal on Pledge.AppealCode = Appeal.AppealCode);

 

[IdoMillet]

The first part of the query is missing.

Can you try to run a simplified query with just the "problem" columns and try to replicate the problem.

Then post the simplified query.

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Chasidy]

Sorry about that. Here is a simplified version of the query with my problem spot which is the "Pledge" lines of the first query.

SELECT
Folder.FolderID,
GiftTransaction.AppealCode,
GiftTransaction.DateRecvd,
GiftTransaction.GiftID,
0 as PledgeDate,
0 as PledgeID,
0 as TotalAmount
FROM (GiftTransaction LEFT JOIN
Folder ON Folder.FolderID = GiftTransaction.FolderID) ;
UNION SELECT
Folder.FolderID,
Pledge.AppealCode,
null,
null,
Pledge.PledgeDate,
Pledge.PledgeID,
Pledge.TotalAmount
FROM (Folder LEFT JOIN Pledge ON Folder.FolderID = Pledge.FolderID);

 

[IdoMillet]

I think you need to fix
-----------------
0 as PledgeDate
-----------------

Try
------------------
Null as PledgeDate
------------------
instead.

hth,
- Ido
CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Chasidy]

If you say null for pledgedate, nothing shows up at all. When you say 0, then if it's "null" a 0 shows and when it's valid, the date shows. Makes no sense. That's why I wanted to cast.
Thanks for your help with this issue.