Delete query - Linked by CLNG won't work - But why not?!

[steve lord]

I have a table, tblHCM and a query, THistory. What I want to do is
simple:

DELETE DISTINCTROW tblHCM.*, THistory.Patient_ID
FROM tblHCM LEFT JOIN THistory ON tblHCM.Patient_ID = THistory.Patient_ID
WHERE THistory.Patient_ID Is Not Null;

This *should* delete all the records in tblHCM where the linking field is
also found in THistory.

Here's the catch: THistory is a query that exactly represents its
underlying table, TransactionHistory with one crucial detail: the
Patient_ID in TransactionHistory is a text(16). Patient_ID in tblHCM is
a LONG. Therefore, in THistory I recast the text as a long using the
CLNG function.

NOW, I get the oh-so-informative error 'Could not delete from specified
tables.'

IF, I first make a temporary table from TransactionHistory and cast the
text as a LONG in the make table query and use that temporary table in my
delete query then the delete query works fine.

SO, my question is: why doesn't my original delete query work when
linking on a LONG to a derived (i.e., cast) LONG?

Thanks for any help!!

 

[Brian Camire]

You might try a query whose SQL looks something like this:

DELETE
TransactionHistory.*
FROM
TransactionHistory
WHERE
CLng([TransactionHistory].[Patient_ID]) IN (SELECT [tblHCM].[Patient_ID]
FROM tblHCM)

 

[steve lord]

Thanks Brian,

Sadly that query takes too long. What I find interesting is that the
Select query works fine - it's only on delete that it barfs.

Thanks again,
Steve

You might try a query whose SQL looks something like this:

DELETE
TransactionHistory.*
FROM
TransactionHistory
WHERE
CLng([TransactionHistory].[Patient_ID]) IN (SELECT
[tblHCM].[Patient_ID] FROM tblHCM)

I have a table, tblHCM and a query, THistory. What I want to do is
simple:

DELETE DISTINCTROW tblHCM.*, THistory.Patient_ID
FROM tblHCM LEFT JOIN THistory ON tblHCM.Patient_ID =
THistory.Patient_ID WHERE THistory.Patient_ID Is Not Null;

This *should* delete all the records in tblHCM where the linking
field is also found in THistory.

Here's the catch: THistory is a query that exactly represents its
underlying table, TransactionHistory with one crucial detail: the
Patient_ID in TransactionHistory is a text(16). Patient_ID in tblHCM
is a LONG. Therefore, in THistory I recast the text as a long using
the CLNG function.

NOW, I get the oh-so-informative error 'Could not delete from
specified tables.'

IF, I first make a temporary table from TransactionHistory and cast
the text as a LONG in the make table query and use that temporary
table in my delete query then the delete query works fine.

SO, my question is: why doesn't my original delete query work when
linking on a LONG to a derived (i.e., cast) LONG?

Thanks for any help!!

 

[Brian Camire]

How about:

DELETE
TransactionHistory.*
FROM
TransactionHistory
WHERE
[TransactionHistory].[Patient_ID] IN (SELECT CStr([tblHCM].[Patient_ID])
FROM tblHCM)

This assumes Patient_ID in TransactionHistory is formatted without leading
zeros, and without any other characters other than digits. If this
assumption is not valid, you might use the Format function instead of CStr
to produce the format you need.

If you haven't already, it might also help to define an index in Patient_ID
in either TransactionHistory or tblHCM (whichever one you're not applying
the function to, which would be TransactionHistory in the suggested
statement above).

Thanks Brian,

Sadly that query takes too long. What I find interesting is that the
Select query works fine - it's only on delete that it barfs.

Thanks again,
Steve

You might try a query whose SQL looks something like this:

DELETE
TransactionHistory.*
FROM
TransactionHistory
WHERE
CLng([TransactionHistory].[Patient_ID]) IN (SELECT
[tblHCM].[Patient_ID] FROM tblHCM)

I have a table, tblHCM and a query, THistory. What I want to do is
simple:

DELETE DISTINCTROW tblHCM.*, THistory.Patient_ID
FROM tblHCM LEFT JOIN THistory ON tblHCM.Patient_ID =
THistory.Patient_ID WHERE THistory.Patient_ID Is Not Null;

This *should* delete all the records in tblHCM where the linking
field is also found in THistory.

Here's the catch: THistory is a query that exactly represents its
underlying table, TransactionHistory with one crucial detail: the
Patient_ID in TransactionHistory is a text(16). Patient_ID in tblHCM
is a LONG. Therefore, in THistory I recast the text as a long using
the CLNG function.

NOW, I get the oh-so-informative error 'Could not delete from
specified tables.'

IF, I first make a temporary table from TransactionHistory and cast
the text as a LONG in the make table query and use that temporary
table in my delete query then the delete query works fine.

SO, my question is: why doesn't my original delete query work when
linking on a LONG to a derived (i.e., cast) LONG?

Thanks for any help!!