union query not working on win2000 or winXP

[Hildegoat15]

Hi,

I've run into a bit of a problem that's got me stumped. I have two queries with the same columns -- clientid (text), due (date), future (date). in the first query, the due column is set to null and in the second query, the future column is set to null. A third query was created to stick the two sets of results together, and that seems to cause my problem. For some reason the future column contains no data for ALL of the records in the union query results. i'm running winXP Professional on this comp, and it's also been tried on win2000 with the same result. We have one last computer in our office running win98, and the union query runs like a charm. why would different operating systems produce different results in the union query? and more importantly, how can i make the query run properly on win2000 and XP? -Matt

http://www.ranamedical.com

 

[Steve101]

Matt,
No simple answer to your questions. Suggest that it will help if you publish the three queries in your next post.
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Hildegoat15]

ok, we tried running it on another win98 machine, and now it doesnt work on there. we're guessing now that it's not an OS problem, but that Access may be configured differently on the computer where the query actually works.

here's the SQL for the queries, so if you find anything that may be the culprit, please let me know.


here's what this thing is supposed to do. "nextserv1" finds the maximum next service date for each client:

SELECT [complete client list no bill ref].clientid, Max(SERVICE.NEXTSERV) AS MaxOfNEXTSERV
FROM [complete client list no bill ref] LEFT JOIN SERVICE ON [complete client list no bill ref].clientid = SERVICE.CLIENTID
GROUP BY [complete client list no bill ref].clientid;


"nextserv1 as of" finds the records from nextserv1 whose next service falls in the month of the As Of date entered:

SELECT nextserv1.clientid, nextserv1.MaxOfNEXTSERV AS due, Null AS future
FROM nextserv1
WHERE (((Month([MaxOfNEXTSERV]))=Month([as of:])) AND ((Year([MaxOfNEXTSERV]))=Year([as of:])));


"nextserv1 not as of" finds the other records, meaning the ones from nextserv1 that weren't in nextserv1 as of:

SELECT nextserv1.clientid, Null AS due, nextserv1.MaxOfNEXTSERV AS future
FROM nextserv1 LEFT JOIN [nextserv1 as of] ON nextserv1.clientid = [nextserv1 as of].clientid
WHERE ((([nextserv1 as of].clientid) Is Null));


nextserv2 unions the two previous queries together:

SELECT [clientid],[due],[future]
FROM [nextserv1 as of]

UNION SELECT [clientid],[due],[future]
FROM [nextserv1 not as of];


the union query is the one that is causing the future column to show null for each client. -Matt

http://www.ranamedical.com

 

[Guest_imported]

Hi
we are experiencing this problem currently and it is driving us nuts. All the Windows 98 machines work perfectly
(we have tried 4) and none of the windows 2000 machines (three)work. We have tried service packs (both office and windows sp3) to no avail.

Access 2000 seems to create a binary datatype for null queries returns, win98 machines are happy to merge this with text fields in a union query, win2000 won't. You can convert down to access97 and it works fine. I have spent ages comparing dll versions and come up with nothing.

The only way we have round it is to force the datatype, but it is a pain when you are developing on a 98 machine as you can't check it

I have a sample mdb which encapsulates the problem if anyone is interested