Tom,
I have brought this down to the thread and back up again. This errors out
at
the point of the Update query and where or what I am putting in the
"field,
table, and Updateto" colunms. For some reason it just wont take what I am
putting into it.
One guy told me that the filed is my expression. That I beleive is
correct,
the "table" line is where I am sending my data to (the target table)and
not
the table from where my field is from. He also said to put my
tablename.fieldname in the updateto line. I believe that is correct. So
playing with everything from expressions to positions etc I am all out of
options. This has to be something very simple that I am overlooking. Here
is
the
SQL without the expressions.
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
dbo_TblProduct.ProductId = [DEV_dbo_TblProduct].[ProductId],
dbo_TblProduct.StateAbbr = [DEV_dbo_TblProduct].[stateabbr],
dbo_TblProductVersion.BondAmtMin = [DEV_dbo_TblProduct].[BondAmtMin],
dbo_tblObligationType.ProductLine = [DEV_dbo_TblProduct].[ProductLine],
dbo_TblProductVersion.BondAmtStd = [DEV_dbo_TblProduct].[BondAmtStd],
dbo_TblProductVersion.BondAmtMax = [DEV_dbo_TblProduct].[BondAmtMax],
dbo_tblObligationType.BondType = [DEV_dbo_TblProduct].[BondType],
dbo_TblProductVersion.BondTermMonths =
[DEV_dbo_TblProduct].[BondTermMonths],
dbo_TblProductVersion.CancelDays = [DEV_dbo_TblProduct].[CancelDays],
dbo_TblProduct.ObligationTypeID = [DEV_dbo_TblProduct].[ObligationTypeID],
dbo_tblObligationType.CategoryID = [DEV_dbo_TblProduct].[CategoryID],
dbo_TblProductVersion.BondExpirationDate =
[DEV_dbo_TblProduct].[BondExpirationDate], dbo_TblProduct.[Available
Online]
= [DEV_dbo_TblProduct].[AvailableOnline],
dbo_TblProductVersion.MinCreditScore =
[DEV_dbo_TblProduct].[MinCreditScore],
dbo_TblProductVersion.NewBusinessFormID = [DEV_dbo_TblProduct].[FormId],
dbo_TblProductVersion.BondEffectiveDate =
[DEV_dbo_TblProduct].[BondEffectiveDate], dbo_tblSaaDocument.ClassCode =
[DEV_dbo_TblProduct].[ClassCode], dbo_TblProductVersion.RiskType =
[DEV_dbo_TblProduct].[RiskType], dbo_TblProductVersion.RateType =
[DEV_dbo_TblProduct].[RateType], dbo_TblProductVersion.RenewalMethod =
[DEV_dbo_TblProduct].[RenewalMethod], dbo_TblProductVersion.RateCode =
[DEV_dbo_TblProduct].[RateCode], dbo_TblProduct.BondObligationDescription
=
[DEV_dbo_TblProduct].[BondObligationDescription], dbo_TblProduct.ObligeeId
=
[DEV_dbo_TblProduct].[ObligeeID],
dbo_TblProductVersion.SpecialInstructions =
[DEV_dbo_TblProduct].[SpecialInstructions]
WHERE (((DEV_dbo_TblProduct.ProductId) Is Not Null)) OR
(((dbo_TblProduct.ProductId)<>[dbo_tblProduct].[ProductId]) AND
((dbo_TblProduct.StateAbbr)<>[dbo_tblProduct].[StateAbbr]) AND
((dbo_TblProductVersion.BondAmtMin)<>[dbo_TblProductVersion].[BondAmtMin]))
OR (((dbo_tblObligationType.ProductLine) Not Like
[dbo_tblObligationType].[ProductLine]) AND
((dbo_TblProductVersion.BondAmtStd)<>[dbo_TblProductVersion].[BondAmtStd])
AND
((dbo_TblProductVersion.BondAmtMax)<>[dbo_TblProductVersion].[BondAmtmax]))
OR (((dbo_tblObligationType.BondType) Not Like
[dbo_tblObligationType].[BondType]) AND
((dbo_TblProductVersion.BondTermMonths)<>[dbo_TblProductVersion].[BondTermMonths])
AND
((dbo_TblProductVersion.CancelDays)<>[dbo_TblProductVersion].[CancelDays])
AND
((dbo_TblProduct.ObligationTypeID)<>[dbo_TblProduct].[ObligationTypeID]))
OR (((dbo_tblObligationType.CategoryID) Not Like
[dbo_tblObligationType].[CategoryID]) AND
((dbo_TblProductVersion.BondExpirationDate)<>[dbo_TblProductVersion].[BondExpirationDate])
AND ((dbo_TblProduct.[Available Online])<>[dbo_TblProduct].[Available
Online]) AND
((dbo_TblProductVersion.MinCreditScore)<>[dbo_TblProductVersion].[MinCreditScore]))
OR
(((dbo_TblProductVersion.NewBusinessFormID)<>[dbo_TblProductVersion].[NewBusinessFormID])
AND
((dbo_TblProductVersion.BondEffectiveDate)<>[dbo_TblProductVersion].[BondEffectiveDate])
AND ((dbo_tblSaaDocument.ClassCode)<>[dbo_tblSaaDocument].[ClassCode]) AND
((dbo_TblProductVersion.RiskType)<>[dbo_TblProductVersion].[RiskType])) OR
(((dbo_TblProductVersion.RateType)<>[dbo_TblProductVersion].[RateType])
AND
((dbo_TblProductVersion.RenewalMethod)<>[dbo_TblProductVersion].[RenewalMethod])
AND ((dbo_TblProductVersion.RateCode)<>[dbo_TblProductVersion].[RateCode])
AND
((dbo_TblProduct.BondObligationDescription)<>[dbo_TblProduct].[BondObligationDescription]))
OR (((dbo_TblProduct.ObligeeId)<>[dbo_TblProduct].[ObligeeId]) AND
((dbo_TblProductVersion.SpecialInstructions)<>[dbo_TblProductVersion].[SpecialInstructions]))
WITH OWNERACCESS OPTION;
Tom Ellison said:
Dear Troy:
Please post the SQL of your entire query. I'll look at it and try to
find
an error.
Alternatively, remove portions of the query till you find where the error
is. Once you know where it is, fixing it is relatively easy.
Tom Ellison
Thank you for your help. I get a syntax error with this. It seems my
expression worked in the select query and the append query that I did.
It
also worked up to the point until I made it an pdate query. That is
where
the
problem is (I think?) in the update portion of this mess. Any
additional
ideas would be appreciated. I have received approx 10 different
responses
and
getting closer everytime. I really appreciate everyones help in helping
me
get through this. My time limit is almost up and I really need to get
on
the
road with this thing. Thanks again
:
Dear Troy:
I think you're so, so close.
When you test the Attachments column, you use the keyword "True" in
the
test
(without the quotes!). If you are setting another yes/no column's
value,
use True and False, not "Y" and "N".
This thing:
dbo_tblproductversion.IIF(([dbo_tblproductversion]].[[eandoflag]=true,"y","n")]
Your use of square brackets is not balanced, and probably not needed.
Parens are also not balanced. Each left bracket or paren must be
balanced
by a right one. How about:
dbo_tblproductversion.IIf(dbo_tblproductversion.eandoflag = true,
true,
false)
I leave testing the spelling of your table and column names to you.
Tom Ellison
I have been working on this for two days and can not find a solution.
It
works perfectly as an select and Append query but not as an update
query.
In a nutshell:
I have a query (select) with 5 tables. One of the tables is a target
table
and the other 4 are source tables. In 3 of the fields I have this
expression..
Attachments: IIf(dbo_tblProductversion.Attachments=True,"Y","N")
I have this because these fields are Y/N datatypes.
I am tryig to update these three fields (different names of
course)with
the
same expressions. I want them and other fileds (approx 15) to update
into
my
target table.
I am now ready for my update table and selct it. When I add this
expression
it will not allow me to select the table where this expression came
from
nor
will it allow me to add in my update to info. I get the following
error
along
with trying to run the update query..
"dbo_tblproductversion.IIF(([dbo_tblproductversion]].[[eandoflag]=true,"y","n")]is
not a valid name. Make sure that it does not include invalid
characters
or
punctuation and tha it is not too long."
