Re: Query returns a column full of "#ERROR"

#1 (**permalink**) 28-Jul-2006, 08:00 AM

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

"Maury Markowitz" wrote in
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/9188-re-query-returns-column-full-error.html
message news:7627617C-D8A9-4E1D-B77A-DEB6F34A18A3@microsoft.com...
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9188
> even
> let you save the query (and it shouldn't). It _appears_ I am doing "the
> right
> thing".
>
> Any ideas?
>
> Maury

#2 (**permalink**) 28-Jul-2006, 08:00 AM

"[MVP] S.Clark" wrote:

> There is a colon after the externalid. You can't rename something in the
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9188
> middle of the equation.

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

Maury