Union query with filter - different results on different computers

N

Nick

Hi,

I have a union query / filter problem that is driving me crazy.
The filter of the query will work on one computer but not
another.

I am using Access 2000. Sp 1, 2, and 3 have been installed on all
the computers.

It works on a notebook that has Windows 2000 server on it.
It doesn't work on other computers that have Windows 2000 server
or Windows 98 on them.

When I run the union query about 10,000 records are shown. If I
right click on one of the job numbers and select 'filter by
selection' the result shows no records (unless you are using the
notebook computer, where it shows the record selected)

I have tried creating a new database and importing all the
objects. I have tried compact and repair. (Many times)

The union query is very large. It is accessing many tables.

What should I do? Help please.

Nick.
 
N

Nick

Hi Allen,

The job number field is already text. It is a combination of
letters and numbers eg: "U97-0015" and "U99-0214" etc. After
reading your "Working with Variables" web page, I am wondering if
the "-" in the job number could be causing a problem. But that
still doesn't explain why it does work on the notebook computer.

What do you think?

Nick.
 
A

Allen Browne

Yes, matching the dashes can be a problem in A2000 and later.

See:
Query Returns no Records with an Indexed Field That Contains Dashes
at:

http://support.microsoft.com/default.aspx?scid=kb;en-us;271661&Product=acc2000

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Nick said:
The job number field is already text. It is a combination of
letters and numbers eg: "U97-0015" and "U99-0214" etc. After
reading your "Working with Variables" web page, I am wondering if
the "-" in the job number could be causing a problem. But that
still doesn't explain why it does work on the notebook computer.

What do you think?

Nick.
In a UNION query, the data types are not clearly defined, and so Access can
get them wrong.

Try explicitly typecasting the field:
SELECT ... CLng([JobNumber]) As JobNum, ... FROM ...
UNION ... CLng([SomeField]) AS JobNum, ... FROM ...
Click to expand...
Click to expand...
 
N

Nick

Yippee!! The latest Jet4 Service pack fixed the problem.

Thanks very very much Allen. :)

Nick.
 
Top