Access query problems

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tfort - 02 Apr 2009 19:11 GMT

This query takes data from two tables so they can be compared . They are linked
together using the PWS_ID. When I run it I get the error....

Element DATECCRDELIVERED is undefined in TBL07COMPLYDETAIL. Query is below.

SELECT tbl07ComplyDetail2.PWS_ID, tbl07ComplyDetail.PWS_ID,
tbl07ComplyDetail.DateCCRDelivered, tbl07ComplyDetail2.DateCCRDelivered,
tbl07ComplyDetail.DateCCRReceived, tbl07ComplyDetail2.DateCCRReceived,
tbl07ComplyDetail.DateCertLetterReceived,
tbl07ComplyDetail2.DateCertLetterReceived, tbl07ComplyDetail.TNRCC_CCR,
tbl07ComplyDetail2.TNRCC_CCR, tbl07ComplyDetail.URL, tbl07ComplyDetail2.URL
FROM tbl07ComplyDetail
INNER JOIN tbl07ComplyDetail2
ON tbl07ComplyDetail.PWS_ID = tbl07ComplyDetail2.PWS_ID
WHERE tbl07ComplyDetail.PWS_ID="#url.pws#"

Reply to this Message

Dan Bracuk - 02 Apr 2009 20:20 GMT

Your error message means that table tbl07ComplyDetail does not have a field named dateccrdelivered.

Reply to this Message

Ian Skinner - 02 Apr 2009 20:37 GMT

> Your error message means that table tbl07ComplyDetail does not have a field named dateccrdelivered.

Actually does it not mean that CF thinks there is a structure variable
named 'tbl07ComplyDetail' and that it does not have an element named
'datecrdelivered'. If it was a field not being in a table would that
not be a SQL|Database error not a CF error?

Why it thinks this - I could not see a reason for in the code you provided.

Reply to this Message

Tfort - 02 Apr 2009 20:28 GMT

Right, I looked into that. I did a cfdump and copied the field names directly from that.

Reply to this Message

GArlington - 03 Apr 2009 17:05 GMT

> This query takes data from two tables so they can be compared . They are linked
> together using the PWS_ID. When I run it I get the error....
[quoted text clipped - 3 lines]
> SELECT tbl07ComplyDetail2.PWS_ID, tbl07ComplyDetail.PWS_ID,
> tbl07ComplyDetail.DateCCRDelivered, tbl07ComplyDetail2.DateCCRDelivered,

Your problem is in the above (and bellow) lines: you select
identically named fields without giving them aliases, that meant SQL
will create alias for each on it's own...
Dump the query to see what the names are, but generally - NEVER select
identically named fields without "AS" part...

> tbl07ComplyDetail.DateCCRReceived, tbl07ComplyDetail2.DateCCRReceived,
> tbl07ComplyDetail.DateCertLetterReceived,
[quoted text clipped - 4 lines]
> ON tbl07ComplyDetail.PWS_ID = tbl07ComplyDetail2.PWS_ID
> WHERE tbl07ComplyDetail.PWS_ID="#url.pws#"

Reply to this Message

Webmaster Forum / ColdFusion / Database Access / April 2009

Access query problems