Records remain in Append and Update queries

G

Guest

I have two tables with exact fields and datatypes. One table is linked and
the other is SQL back end. I run a history append or an update query and I
get the same append error msg. You know the one says datatypes or null etc.
Here is my SQL for the update query. Should be easy but driving me nuts. The
records move into the tables but for some reason the original 4 records stay
in the query. HELP!

UPDATE dbo_TblForm INNER JOIN DEV_dbo_TblForm ON dbo_TblForm.FormId =
DEV_dbo_TblForm.FormId SET DEV_dbo_TblForm.FormNumber =
dbo_TblForm.formnumber, DEV_dbo_TblForm.Description =
dbo_TblForm.description, DEV_dbo_TblForm.DigitalSignature =
dbo_TblForm.digitalsignature, DEV_dbo_TblForm.FileName =
dbo_TblForm.filename, DEV_dbo_TblForm.EffDate = dbo_TblForm.effdate,
DEV_dbo_TblForm.Attachment = dbo_TblForm.attachment,
DEV_dbo_TblForm.FormAvailable = dbo_TblForm.formavailable
WHERE (((DEV_dbo_TblForm.Description) Not Like [dbo_TblForm].[description]))
OR (((DEV_dbo_TblForm.FileName) Not Like [dbo_TblForm].[filename])) OR
(((DEV_dbo_TblForm.EffDate)<>[dbo_TblForm].[effdate])) OR
(((DEV_dbo_TblForm.DigitalSignature)<>[dbo_TblForm].[digitalsignature])) OR
(((DEV_dbo_TblForm.Attachment)<>[dbo_TblForm].[attachment])) OR
(((DEV_dbo_TblForm.FormAvailable)<>[dbo_TblForm].[formavailable]))
WITH OWNERACCESS OPTION;
 
G

Guest

No. I cant find out what is wrong. Very starnge it is on all the append
queries this other developer set up. Also on some of the update queries.
TSome seem to move ok but stay in the record set?

Danny J. Lesandrini said:
Are any of the fields in the WHERE clause based on MEMO fields?

--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast


troy said:
I have two tables with exact fields and datatypes. One table is linked and
the other is SQL back end. I run a history append or an update query and I
get the same append error msg. You know the one says datatypes or null etc.
Here is my SQL for the update query. Should be easy but driving me nuts. The
records move into the tables but for some reason the original 4 records stay
in the query. HELP!

UPDATE dbo_TblForm INNER JOIN DEV_dbo_TblForm ON dbo_TblForm.FormId =
DEV_dbo_TblForm.FormId SET DEV_dbo_TblForm.FormNumber =
dbo_TblForm.formnumber, DEV_dbo_TblForm.Description =
dbo_TblForm.description, DEV_dbo_TblForm.DigitalSignature =
dbo_TblForm.digitalsignature, DEV_dbo_TblForm.FileName =
dbo_TblForm.filename, DEV_dbo_TblForm.EffDate = dbo_TblForm.effdate,
DEV_dbo_TblForm.Attachment = dbo_TblForm.attachment,
DEV_dbo_TblForm.FormAvailable = dbo_TblForm.formavailable
WHERE (((DEV_dbo_TblForm.Description) Not Like [dbo_TblForm].[description]))
OR (((DEV_dbo_TblForm.FileName) Not Like [dbo_TblForm].[filename])) OR
(((DEV_dbo_TblForm.EffDate)<>[dbo_TblForm].[effdate])) OR
(((DEV_dbo_TblForm.DigitalSignature)<>[dbo_TblForm].[digitalsignature])) OR
(((DEV_dbo_TblForm.Attachment)<>[dbo_TblForm].[attachment])) OR
(((DEV_dbo_TblForm.FormAvailable)<>[dbo_TblForm].[formavailable]))
WITH OWNERACCESS OPTION;
 
D

Danny J. Lesandrini

Tryo:

I had a hard time reading the sql, so I reformatted it a little. I don't see anything
funky, but why are you using NOT LIKE when you could use <> ?


UPDATE dbo_TblForm
INNER JOIN DEV_dbo_TblForm
ON dbo_TblForm.FormId =DEV_dbo_TblForm.FormId

SET DEV_dbo_TblForm.FormNumber =dbo_TblForm.formnumber
, DEV_dbo_TblForm.Description =dbo_TblForm.description
, DEV_dbo_TblForm.DigitalSignature =dbo_TblForm.digitalsignature
, DEV_dbo_TblForm.FileName =dbo_TblForm.filename
, DEV_dbo_TblForm.EffDate = dbo_TblForm.effdate
, DEV_dbo_TblForm.Attachment = dbo_TblForm.attachment
, DEV_dbo_TblForm.FormAvailable = dbo_TblForm.formavailable

WHERE (((DEV_dbo_TblForm.Description) Not Like [dbo_TblForm].[description]))
OR (((DEV_dbo_TblForm.FileName) Not Like [dbo_TblForm].[filename]))
OR (((DEV_dbo_TblForm.EffDate)<>[dbo_TblForm].[effdate]))
OR (((DEV_dbo_TblForm.DigitalSignature)<>[dbo_TblForm].[digitalsignature]))
OR (((DEV_dbo_TblForm.Attachment)<>[dbo_TblForm].[attachment]))
OR (((DEV_dbo_TblForm.FormAvailable)<>[dbo_TblForm].[formavailable]))

WITH OWNERACCESS OPTION;
 
G

Guest

Good question. I did not build this so caled database. I has approx 15
different ones with approx 200 queries and most are action ones. Nothing
works or ??? I am the lucky one to fix it. Dont know why it does not work. Do
you think because the two tables in the query are joined and one is attached
and the other is on SQL. Same databases etc? Dont know just thought I would
ask

Danny J. Lesandrini said:
Tryo:

I had a hard time reading the sql, so I reformatted it a little. I don't see anything
funky, but why are you using NOT LIKE when you could use <> ?


UPDATE dbo_TblForm
INNER JOIN DEV_dbo_TblForm
ON dbo_TblForm.FormId =DEV_dbo_TblForm.FormId

SET DEV_dbo_TblForm.FormNumber =dbo_TblForm.formnumber
, DEV_dbo_TblForm.Description =dbo_TblForm.description
, DEV_dbo_TblForm.DigitalSignature =dbo_TblForm.digitalsignature
, DEV_dbo_TblForm.FileName =dbo_TblForm.filename
, DEV_dbo_TblForm.EffDate = dbo_TblForm.effdate
, DEV_dbo_TblForm.Attachment = dbo_TblForm.attachment
, DEV_dbo_TblForm.FormAvailable = dbo_TblForm.formavailable

WHERE (((DEV_dbo_TblForm.Description) Not Like [dbo_TblForm].[description]))
OR (((DEV_dbo_TblForm.FileName) Not Like [dbo_TblForm].[filename]))
OR (((DEV_dbo_TblForm.EffDate)<>[dbo_TblForm].[effdate]))
OR (((DEV_dbo_TblForm.DigitalSignature)<>[dbo_TblForm].[digitalsignature]))
OR (((DEV_dbo_TblForm.Attachment)<>[dbo_TblForm].[attachment]))
OR (((DEV_dbo_TblForm.FormAvailable)<>[dbo_TblForm].[formavailable]))

WITH OWNERACCESS OPTION;


--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast


troy said:
No. I cant find out what is wrong. Very starnge it is on all the append
queries this other developer set up. Also on some of the update queries.
TSome seem to move ok but stay in the record set?
 
D

Danny J. Lesandrini

Well, I had something like this recently, where the target table had a field that
was 10 characters shorter than the source table. While the INSERT worked, it
truncated the field. So, when I went to check for "missing" records, those that
were identified by the WHERE clause, there were some that continued to show
up, even though they were INSERTED.

So, you should check the size of text columns very closely. Also, try replacing
the NOT LIKE with the not equal operators, <>.

This is just a guess, but it's where I'd start looking.
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast


troy said:
Good question. I did not build this so caled database. I has approx 15
different ones with approx 200 queries and most are action ones. Nothing
works or ??? I am the lucky one to fix it. Dont know why it does not work. Do
you think because the two tables in the query are joined and one is attached
and the other is on SQL. Same databases etc? Dont know just thought I would
ask

Danny J. Lesandrini said:
Tryo:

I had a hard time reading the sql, so I reformatted it a little. I don't see anything
funky, but why are you using NOT LIKE when you could use <> ?


UPDATE dbo_TblForm
INNER JOIN DEV_dbo_TblForm
ON dbo_TblForm.FormId =DEV_dbo_TblForm.FormId

SET DEV_dbo_TblForm.FormNumber =dbo_TblForm.formnumber
, DEV_dbo_TblForm.Description =dbo_TblForm.description
, DEV_dbo_TblForm.DigitalSignature =dbo_TblForm.digitalsignature
, DEV_dbo_TblForm.FileName =dbo_TblForm.filename
, DEV_dbo_TblForm.EffDate = dbo_TblForm.effdate
, DEV_dbo_TblForm.Attachment = dbo_TblForm.attachment
, DEV_dbo_TblForm.FormAvailable = dbo_TblForm.formavailable

WHERE (((DEV_dbo_TblForm.Description) Not Like [dbo_TblForm].[description]))
OR (((DEV_dbo_TblForm.FileName) Not Like [dbo_TblForm].[filename]))
OR (((DEV_dbo_TblForm.EffDate)<>[dbo_TblForm].[effdate]))
OR (((DEV_dbo_TblForm.DigitalSignature)<>[dbo_TblForm].[digitalsignature]))
OR (((DEV_dbo_TblForm.Attachment)<>[dbo_TblForm].[attachment]))
OR (((DEV_dbo_TblForm.FormAvailable)<>[dbo_TblForm].[formavailable]))

WITH OWNERACCESS OPTION;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top