Null Value error in Append qry

[Guest]

Can someone answer this for me. I set up an append query starting with a
select query. It ran fine. I try and use the append query and get the
following error.

You tried to assign the null value to a variable that is not a variant data
type.

Would this be the error I received because I have the following in my Field
portion of the grid?
Attachments: IIf(dbo_tblProductversion.Attachments=-1,"Y","N")

We did a test with some test data last week and it worked fine. But with
live data this is what I receivedc. Trying to move 5 records and nothing.
Please help

 

[Duane Hookom]

Is it possible that your live data might have a Null in the Attachments
field? If so, you might want to use an expression like:
Attachments: IIf(Nz(dbo_tblProductversion.Attachments,0)=-1,"Y","N")

 

[Guest]

No I checked that and it has all the values. Any other ideas it is driving me
crazy but I know it must be something simple? Thanks for your help!

 

[Duane Hookom]

It's difficult to tell without seeing your SQL view.

I would limit the records in the append query to find out which records are
causing the error.

 

[Guest]

Well it seems the error is tied to the expression in one of my fields and the
product ID from another table. That does not make sense. If I take out the
creteria from the product ID I can get my results. If I put it back then it
says I have a data type mismatch. Any ideas?

 

[Duane Hookom]

Are you joining on two different data types in any query?
If you truly have found the error, fix it or at least tell us what it is.

 

[Guest]

No I am still triying to figure it out (the total solution). However I did
receive another error stating cant run update due to another table called
productcompany. I dont even have a table called that nor in the qrid. I am
lost now!

 

[Duane Hookom]

Troy,
I see what I think are a lot of posts from you recently. Is this issue being
addressed in any other thread? If so, please give me the subject line so we
don't get two similar threads going.

If this issue is separate from others, then come back with your entire SQL
view.

 

[Guest]

Here you go thanks again.

UPDATE dbo_tblSaaDocument RIGHT JOIN (dbo_tblObligationType INNER JOIN
(dbo_TblProductVersion INNER JOIN (DEV_dbo_TblProduct RIGHT JOIN
dbo_TblProduct ON DEV_dbo_TblProduct.ProductId = dbo_TblProduct.ProductId) ON
dbo_TblProductVersion.ProductId = dbo_TblProduct.ProductId) ON
dbo_tblObligationType.ObligationTypeID = dbo_TblProduct.ObligationTypeID) ON
dbo_tblSaaDocument.SaaDocumentID = dbo_tblObligationType.SaaDocumentID SET
DEV_dbo_TblProduct.ProductId = [dbo_tblProduct]![ProductID],
DEV_dbo_TblProduct.EAndOFlag = [dbo_TblProductVersion].[EAndOFlag],
DEV_dbo_TblProduct.Countersignatures =
[dbo_TblProductVersion].[Countersignatures], DEV_dbo_TblProduct.Attachments =
[dbo_TblProductVersion].[Attachments], DEV_dbo_TblProduct.StateAbbr =
[dbo_TblProduct]![StateAbbr], DEV_dbo_TblProduct.BondAmtMin =
[dbo_TblProductVersion]![BondAmtMin], DEV_dbo_TblProduct.ProductLine =
[dbo_tblObligationType]![ProductLine], DEV_dbo_TblProduct.BondAmtStd =
[dbo_TblProductVersion]![BondAmtStd], DEV_dbo_TblProduct.BondAmtMax =
[dbo_TblProductVersion]![BondAmtMax], DEV_dbo_TblProduct.BondType =
[dbo_tblObligationType]![BondType], DEV_dbo_TblProduct.BondTermMonths =
[dbo_TblProductVersion]![BondTermMonths], DEV_dbo_TblProduct.CancelDays =
[dbo_TblProductVersion]![CancelDays], DEV_dbo_TblProduct.ObligationTypeID =
[dbo_TblProduct]![ObligationTypeID], DEV_dbo_TblProduct.CategoryId =
[dbo_tblObligationType]![CategoryID], DEV_dbo_TblProduct.BondExpirationDate =
[dbo_TblProductVersion]![BondExpirationDate],
DEV_dbo_TblProduct.AvailableOnline = [dbo_TblProduct]![Available Online],
DEV_dbo_TblProduct.MinCreditScore = [dbo_TblProductVersion]![MinCreditScore],
DEV_dbo_TblProduct.BondEffectiveDate =
[dbo_TblProductVersion]![BondEffectiveDate], DEV_dbo_TblProduct.ClassCode =
[dbo_tblSaaDocument]![ClassCode], DEV_dbo_TblProduct.RiskType =
[dbo_TblProductVersion]![RiskType], DEV_dbo_TblProduct.RateType =
[dbo_TblProductVersion]![RateType], DEV_dbo_TblProduct.RenewalMethod =
[dbo_TblProductVersion]![RenewalMethod], DEV_dbo_TblProduct.RateCode =
[dbo_TblProductVersion]![RateCode],
DEV_dbo_TblProduct.BondObligationDescription =
[dbo_TblProduct]![BondObligationDescription], DEV_dbo_TblProduct.ObligeeId =
[dbo_TblProduct]![ObligeeID], DEV_dbo_TblProduct.SpecialInstructions =
[dbo_TblProductVersion]![SpecialInstructions], DEV_dbo_TblProduct.Description
= [dbo_tblProductVersion].[Description], DEV_dbo_TblProduct.FormId =
[dbo_tblProductVersion].[NewBusinessFormId]
WHERE (((DEV_dbo_TblProduct.ProductId) Is Null And
(DEV_dbo_TblProduct.ProductId)="[dbo_tblProduct].[ProductID]") AND
((DEV_dbo_TblProduct.EAndOFlag)=IIf(Nz([dbo_tblProductversion]![EAndOFlag],0)=-1,"Y","N")
And (DEV_dbo_TblProduct.EAndOFlag)<>"[dbo_tblProductVersion].[EAndOFlag]")
AND
((DEV_dbo_TblProduct.Countersignatures)=IIf(Nz([dbo_tblProductversion]![Countersignatures],0)=-1,"Y","N")
And
(DEV_dbo_TblProduct.Countersignatures)<>"[dbo_tblProductVersion].[Countersignatures]")
AND
((DEV_dbo_TblProduct.Attachments)=IIf(Nz([dbo_tblProductversion]![Attachments],0)=-1,"Y","N")
And
(DEV_dbo_TblProduct.Attachments)<>"[dbo_tblProductVersion].[Attachments]")
AND ((DEV_dbo_TblProduct.StateAbbr)<>[dbo_tblProduct].[StateAbbr]) AND
((DEV_dbo_TblProduct.BondAmtMin)<>[dbo_TblProductVersion].[BondAmtMin]) AND
((DEV_dbo_TblProduct.ProductLine) Not Like
[dbo_tblObligationType].[ProductLine]) AND
((DEV_dbo_TblProduct.BondAmtStd)<>[dbo_TblProductVersion].[BondAmtStd]) AND
((DEV_dbo_TblProduct.BondAmtMax)<>[dbo_TblProductVersion].[BondAmtmax]) AND
((DEV_dbo_TblProduct.BondType) Not Like [dbo_tblObligationType].[BondType])
AND
((DEV_dbo_TblProduct.FormId)<>[dbo_TblProductVersion].[NewBusinessFormId]))
OR
(((DEV_dbo_TblProduct.BondTermMonths)<>[dbo_TblProductVersion].[BondTermMonths])
AND
((DEV_dbo_TblProduct.Description)<>[dbo_TblProductVersion].[Description])) OR
(((DEV_dbo_TblProduct.CancelDays)<>[dbo_TblProductVersion].[CancelDays]) AND
((DEV_dbo_TblProduct.ObligationTypeID)<>[dbo_TblProduct].[ObligationTypeID])
AND ((DEV_dbo_TblProduct.ObligeeId)<>[dbo_TblProduct].[ObligeeId])) OR
(((DEV_dbo_TblProduct.CategoryId) Not Like
[dbo_tblObligationType].[CategoryID]) AND
((DEV_dbo_TblProduct.BondExpirationDate)<>[dbo_TblProductVersion].[BondExpirationDate])
AND ((DEV_dbo_TblProduct.AvailableOnline)<>[dbo_TblProduct].[Available
Online]) AND
((DEV_dbo_TblProduct.MinCreditScore)<>[dbo_TblProductVersion].[MinCreditScore]))
OR
(((DEV_dbo_TblProduct.BondEffectiveDate)<>[dbo_TblProductVersion].[BondEffectiveDate])
AND ((DEV_dbo_TblProduct.ClassCode)<>[dbo_tblSaaDocument].[ClassCode]) AND
((DEV_dbo_TblProduct.RiskType)<>[dbo_TblProductVersion].[RiskType])) OR
(((DEV_dbo_TblProduct.RateType)<>[dbo_TblProductVersion].[RateType]) AND
((DEV_dbo_TblProduct.RenewalMethod)<>[dbo_TblProductVersion].[RenewalMethod])
AND ((DEV_dbo_TblProduct.RateCode)<>[dbo_TblProductVersion].[RateCode]) AND
((DEV_dbo_TblProduct.BondObligationDescription)<>[dbo_TblProduct].[BondObligationDescription]))
OR
(((DEV_dbo_TblProduct.SpecialInstructions)<>[dbo_TblProductVersion].[SpecialInstructions]))
WITH OWNERACCESS OPTION;

 

[Duane Hookom]

Did you read my reply? What's up with countinued asking in this thread when
you go and ask the same exact question in a new thread.

If you expect some assistance, you should learn how to be patient, read
replies, and respond appropriately.

--
Duane Hookom
MS Access MVP

Here you go thanks again.

UPDATE dbo_tblSaaDocument RIGHT JOIN (dbo_tblObligationType INNER JOIN
(dbo_TblProductVersion INNER JOIN (DEV_dbo_TblProduct RIGHT JOIN
dbo_TblProduct ON DEV_dbo_TblProduct.ProductId = dbo_TblProduct.ProductId)
ON
dbo_TblProductVersion.ProductId = dbo_TblProduct.ProductId) ON
dbo_tblObligationType.ObligationTypeID = dbo_TblProduct.ObligationTypeID)
ON
dbo_tblSaaDocument.SaaDocumentID = dbo_tblObligationType.SaaDocumentID SET
DEV_dbo_TblProduct.ProductId = [dbo_tblProduct]![ProductID],
DEV_dbo_TblProduct.EAndOFlag = [dbo_TblProductVersion].[EAndOFlag],
DEV_dbo_TblProduct.Countersignatures =
[dbo_TblProductVersion].[Countersignatures],
DEV_dbo_TblProduct.Attachments =
[dbo_TblProductVersion].[Attachments], DEV_dbo_TblProduct.StateAbbr =
[dbo_TblProduct]![StateAbbr], DEV_dbo_TblProduct.BondAmtMin =
[dbo_TblProductVersion]![BondAmtMin], DEV_dbo_TblProduct.ProductLine =
[dbo_tblObligationType]![ProductLine], DEV_dbo_TblProduct.BondAmtStd =
[dbo_TblProductVersion]![BondAmtStd], DEV_dbo_TblProduct.BondAmtMax =
[dbo_TblProductVersion]![BondAmtMax], DEV_dbo_TblProduct.BondType =
[dbo_tblObligationType]![BondType], DEV_dbo_TblProduct.BondTermMonths =
[dbo_TblProductVersion]![BondTermMonths], DEV_dbo_TblProduct.CancelDays =
[dbo_TblProductVersion]![CancelDays], DEV_dbo_TblProduct.ObligationTypeID
=
[dbo_TblProduct]![ObligationTypeID], DEV_dbo_TblProduct.CategoryId =
[dbo_tblObligationType]![CategoryID],
DEV_dbo_TblProduct.BondExpirationDate =
[dbo_TblProductVersion]![BondExpirationDate],
DEV_dbo_TblProduct.AvailableOnline = [dbo_TblProduct]![Available Online],
DEV_dbo_TblProduct.MinCreditScore =
[dbo_TblProductVersion]![MinCreditScore],
DEV_dbo_TblProduct.BondEffectiveDate =
[dbo_TblProductVersion]![BondEffectiveDate], DEV_dbo_TblProduct.ClassCode
=
[dbo_tblSaaDocument]![ClassCode], DEV_dbo_TblProduct.RiskType =
[dbo_TblProductVersion]![RiskType], DEV_dbo_TblProduct.RateType =
[dbo_TblProductVersion]![RateType], DEV_dbo_TblProduct.RenewalMethod =
[dbo_TblProductVersion]![RenewalMethod], DEV_dbo_TblProduct.RateCode =
[dbo_TblProductVersion]![RateCode],
DEV_dbo_TblProduct.BondObligationDescription =
[dbo_TblProduct]![BondObligationDescription], DEV_dbo_TblProduct.ObligeeId
=
[dbo_TblProduct]![ObligeeID], DEV_dbo_TblProduct.SpecialInstructions =
[dbo_TblProductVersion]![SpecialInstructions],
DEV_dbo_TblProduct.Description
= [dbo_tblProductVersion].[Description], DEV_dbo_TblProduct.FormId =
[dbo_tblProductVersion].[NewBusinessFormId]
WHERE (((DEV_dbo_TblProduct.ProductId) Is Null And
(DEV_dbo_TblProduct.ProductId)="[dbo_tblProduct].[ProductID]") AND
((DEV_dbo_TblProduct.EAndOFlag)=IIf(Nz([dbo_tblProductversion]![EAndOFlag],0)=-1,"Y","N")
And (DEV_dbo_TblProduct.EAndOFlag)<>"[dbo_tblProductVersion].[EAndOFlag]")
AND
((DEV_dbo_TblProduct.Countersignatures)=IIf(Nz([dbo_tblProductversion]![Countersignatures],0)=-1,"Y","N")
And
(DEV_dbo_TblProduct.Countersignatures)<>"[dbo_tblProductVersion].[Countersignatures]")
AND
((DEV_dbo_TblProduct.Attachments)=IIf(Nz([dbo_tblProductversion]![Attachments],0)=-1,"Y","N")
And
(DEV_dbo_TblProduct.Attachments)<>"[dbo_tblProductVersion].[Attachments]")
AND ((DEV_dbo_TblProduct.StateAbbr)<>[dbo_tblProduct].[StateAbbr]) AND
((DEV_dbo_TblProduct.BondAmtMin)<>[dbo_TblProductVersion].[BondAmtMin])
AND
((DEV_dbo_TblProduct.ProductLine) Not Like
[dbo_tblObligationType].[ProductLine]) AND
((DEV_dbo_TblProduct.BondAmtStd)<>[dbo_TblProductVersion].[BondAmtStd])
AND
((DEV_dbo_TblProduct.BondAmtMax)<>[dbo_TblProductVersion].[BondAmtmax])
AND
((DEV_dbo_TblProduct.BondType) Not Like
[dbo_tblObligationType].[BondType])
AND
((DEV_dbo_TblProduct.FormId)<>[dbo_TblProductVersion].[NewBusinessFormId]))
OR
(((DEV_dbo_TblProduct.BondTermMonths)<>[dbo_TblProductVersion].[BondTermMonths])
AND
((DEV_dbo_TblProduct.Description)<>[dbo_TblProductVersion].[Description]))
OR
(((DEV_dbo_TblProduct.CancelDays)<>[dbo_TblProductVersion].[CancelDays])
AND
((DEV_dbo_TblProduct.ObligationTypeID)<>[dbo_TblProduct].[ObligationTypeID])
AND ((DEV_dbo_TblProduct.ObligeeId)<>[dbo_TblProduct].[ObligeeId])) OR
(((DEV_dbo_TblProduct.CategoryId) Not Like
[dbo_tblObligationType].[CategoryID]) AND
((DEV_dbo_TblProduct.BondExpirationDate)<>[dbo_TblProductVersion].[BondExpirationDate])
AND ((DEV_dbo_TblProduct.AvailableOnline)<>[dbo_TblProduct].[Available
Online]) AND
((DEV_dbo_TblProduct.MinCreditScore)<>[dbo_TblProductVersion].[MinCreditScore]))
OR
(((DEV_dbo_TblProduct.BondEffectiveDate)<>[dbo_TblProductVersion].[BondEffectiveDate])
AND ((DEV_dbo_TblProduct.ClassCode)<>[dbo_tblSaaDocument].[ClassCode]) AND
((DEV_dbo_TblProduct.RiskType)<>[dbo_TblProductVersion].[RiskType])) OR
(((DEV_dbo_TblProduct.RateType)<>[dbo_TblProductVersion].[RateType]) AND
((DEV_dbo_TblProduct.RenewalMethod)<>[dbo_TblProductVersion].[RenewalMethod])
AND ((DEV_dbo_TblProduct.RateCode)<>[dbo_TblProductVersion].[RateCode])
AND
((DEV_dbo_TblProduct.BondObligationDescription)<>[dbo_TblProduct].[BondObligationDescription]))
OR
(((DEV_dbo_TblProduct.SpecialInstructions)<>[dbo_TblProductVersion].[SpecialInstructions]))
WITH OWNERACCESS OPTION;

Troy,
I see what I think are a lot of posts from you recently. Is this issue
being
addressed in any other thread? If so, please give me the subject line so
we
don't get two similar threads going.

If this issue is separate from others, then come back with your entire
SQL
view.