Query returns a column full of "#ERROR"

[Guest]

We have two databases, on in SQL Server, and another in PervasiveSQL. I am
attempting to compare data in the two via a query in a .mdb.

The query in question joins on two Pervasive tables. One lists a series of
"trades", the other "xrefs", small text tags. They are related via a trade id
field that is "Number" in the trade table, and a fixed-length char field in
the other (so that the xref could contain ID's from many tables, not just
trades).

I wrote a query to convert the text into a number so the two could be
linked. For the trade id, I used:

TaNum: CLng(Trim([PAMID])) / externalId: CLng(Trim([ExternalID]))

Running this query returns a proper looking table of two columns with what
appear to be numbers in both.

However, when I then join that query with the trades table, selecting the
externalId for display, externalId says "#ERROR" for every record. I have no
idea what this means.

I tried various small edits, like removing the CLng, but then it won't even
let you save the query (and it shouldn't). It _appears_ I am doing "the right
thing".

Any ideas?

Maury

 

[[MVP] S.Clark]

There is a colon after the externalid. You can't rename something in the
middle of the equation.


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[Guest]

There is a colon after the externalid. You can't rename something in the
middle of the equation.

No. I simply cut and pasted both columns onto a single line.

Maury