When I run my select query it is perfect. Only whhen I try and select the
table and the update to and put my creteria in it gives me the following
message..
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.
I have checked this inside and out and nothing. For some reason when I put
in the expression in the "field" grid it takes out the "table". Someone told
me it has to be #1 in line for it to work. Please help or respons if you can.
I have been stuck for 2 days. Here is the SQL.. Well not I have to take out
the tables because it gives me that error message so the SQL will be
incomplete somewhat.
Here is my expression
EAndOFlag: IIf([dbo_tblProductversion].[EAndOFlag]=True,"Y","N")
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;
:
You are sure it is a TEXT field you are trying to update?, not a Y or N
field? If that is the case, I see no reason why that expression would
not work.
What is the error you get when you run?
Can you post the SQL?
troy wrote:
The expression works perfectly but what I am trying to do is update a TEXT Y
or N on another table from my query that has another table that the field is
set up as a Y/N datatype. I then want to do an update query. That is where my
problem is. How do I take the expression I have and make it work on an update
query. It works as a select query and as an append query but will not work as
a update query.
:
I received this response from Mr. Walsh and it almost answers my question but
is not 100% complete. I need to take my expression (see below) and update it
to another table and field that is a text field and is already a Y or N. I
have the expression and it is correct. But I need to know how and the heck do
I get it from my query into my table with an update query? No matter what I
do I get expression errors. The expression works in a select query and an
append query but not an update. HELP PLEASE!
Please review Mr Walse answer..
You cannot update a computed expression, as you cannot update 2+2 to the
value 5.
Assuming you want to update a field in the table, you have to move the
computed expression
IIf([dbo_tblProductversion].[Attachments]=True,"Y","N")
in the UpdateTo line of the grid, UNDER a column where the field to be
updated will be in the first line. Note that the alias, Attachments: , is
absent in the UpdateTo. You have the line UpdateTo when you edit an UPDATE
query. That line is absent for a normal SELECT query.
help! help! anyone help!