Access2000: Query problem

[Arvi Laanemets]

Hi

The query below returns no records

SELECT a.QueryDate, a.DeviceID, a.LastTransactDate As TransactDate, b.TabN
FROM qUserDevices0 AS a, tblTransactions b WHERE b.DeviceID=a.DeviceID And
b.TransactDate=a.LastTransactDate;

The same query without field b.TabN returns excepted amount of rows

SELECT a.QueryDate, a.DeviceID, a.LastTransactDate As TransactDate
FROM qUserDevices0 AS a, tblTransactions b WHERE b.DeviceID=a.DeviceID And
b.TransactDate=a.LastTransactDate;


The field TabN in table tblTransactions is a text field, and contains both
text and numeric strings, like
"Xxxx9999", or "333". (The numeric string values are imported from our staff
management program, and are updated from there, text string values are
created, managed, and updated in my Access application locally). That the
query was unable to create any rows with TabN as returned filed, was a quite
a bad surprise for me. And using a concantenation expression ("" & b.TabN)
was for no good too.

Has someone here encountered such a problem, and find some solution for it?
Thanks in advance!

 

[Arvi Laanemets]

Hi again

An additional remark. When I save the result of query qUserDevices0 to a
table, and then use this table as source for my problem query, it works.
I.e. the query

SELECT a.QueryDate, a.DeviceID, a.LastTransactDate As TransactDate, b.TabN
FROM test AS a, tblTransactions b WHERE b.DeviceID=a.DeviceID And
b.TransactDate=a.LastTransactDate;

, where test is the table, the results of qUserDevices0 were saved into,
then this query returns the proper number of records. But this isn't a
solution for me at moment - the query will be used as source for a report,
and the query qUserDevices0 , used as a subsource for it, is a parameter
query - the value for QueryDate is entered by user when the report is
started.

As result, I must get a report, where are listed all latest transactions
(TransactDate, TabN) for every DeviceID from table tblTransactions, where
the transactDate<=[QueryDate]