Why wont this work in a update query

[Guest]

Attachments: IIf([dbo_tblProductversion].[Attachments]=True,"Y","N")

It works fine in the select query but when I change to my update query it
say it is not a valid name and to make sure it includes the right () {} ""
etc.

This expression works perfectly in my Append query so why not this one? My
Update query that I am trying to creat is just a cut and paste of the Append.
So why does it not work?

PLEASE HELP!

 

[Duane Hookom]

Please post your entire SQL. Your sample doesn't look anything like an
update query.

 

[Guest]

Try replacing True with -1 (minus one).

 

[Michel Walsh]

Hi,


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.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

OK Thank you but let me see if I understand this correctly. I need to place
my filed that I am going to move to the first ine in the grid. Then I select
my table where the actual data is coming from. Then I do the above expression
in the "update to" line of the grid? That sounds fine but two questions.

1. I still get the -1 and or 0 as a result
2. how does access know what table or field I want the data to go into.
Thank you very much you are getting me in the right direction. I still think
I am missing something tohough. Can you help?

Hi,


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.


Hoping it may help,
Vanderghast, Access MVP

Attachments: IIf([dbo_tblProductversion].[Attachments]=True,"Y","N")

It works fine in the select query but when I change to my update query it
say it is not a valid name and to make sure it includes the right () {} ""
etc.

This expression works perfectly in my Append query so why not this one? My
Update query that I am trying to creat is just a cut and paste of the
Append.
So why does it not work?

PLEASE HELP!

 

[Guest]

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_TblProductVersion.Attachments =
IIf([dbo_tblProductversion].[Attachments]=True,"Y","N"),
DEV_dbo_TblProduct.ProductId = DEV_dbo_TblProduct.ProductId,
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;

Please post your entire SQL. Your sample doesn't look anything like an
update query.
--
Duane Hookom
MS Access MVP

Attachments: IIf([dbo_tblProductversion].[Attachments]=True,"Y","N")

It works fine in the select query but when I change to my update query it
say it is not a valid name and to make sure it includes the right () {} ""
etc.

This expression works perfectly in my Append query so why not this one? My
Update query that I am trying to creat is just a cut and paste of the
Append.
So why does it not work?

PLEASE HELP!

 

[Guest]

I think I forgot to mention that Attachments is coming from a source table
and being moved to the target table. Do I do it the same way? Your idea makes
sense but still a little confused! Can you help?

Hi,


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.


Hoping it may help,
Vanderghast, Access MVP

Attachments: IIf([dbo_tblProductversion].[Attachments]=True,"Y","N")

It works fine in the select query but when I change to my update query it
say it is not a valid name and to make sure it includes the right () {} ""
etc.

This expression works perfectly in my Append query so why not this one? My
Update query that I am trying to creat is just a cut and paste of the
Append.
So why does it not work?

PLEASE HELP!

 

[Van T. Dinh]

It looks to me that you are trying to update a Boolean Field (Bit Field in
Microsoft SQL Server) with a Text / Char value???

--
HTH
Van T. Dinh
MVP (Access)

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_TblProductVersion.Attachments =
IIf([dbo_tblProductversion].[Attachments]=True,"Y","N"),
DEV_dbo_TblProduct.ProductId = DEV_dbo_TblProduct.ProductId,
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;

Please post your entire SQL. Your sample doesn't look anything like an
update query.
--
Duane Hookom
MS Access MVP

Attachments: IIf([dbo_tblProductversion].[Attachments]=True,"Y","N")

It works fine in the select query but when I change to my update query
it
say it is not a valid name and to make sure it includes the right () {}
""
etc.

This expression works perfectly in my Append query so why not this one?
My
Update query that I am trying to creat is just a cut and paste of the
Append.
So why does it not work?

PLEASE HELP!

 

[Michel Walsh]

Hi,


Nope, the first line is the field that receive the modification, the second
line is the table having that field to be updated, not the table where the
data comes from.

As example, to set the field x from table tableA the value 5, then the first
line is x, the second line is TableA and the UpdateTo is 5


In the UpdateTo line, if the data can come from multiple table, use the
syntax

tableName.fieldName


to remove any ambiguity (about which tables supply fields). Since in

IIf([dbo_tblProductversion].[Attachments]=True,"Y","N")


you already use that syntax, that should be quite fine. Note that any
involved table must be in the upper part of the query designer, with the
required "joins" between the tables: You should be able to use a standard
SELECT and see the data you want update. But since you have that part right,
already, I don't suspect any problem from that technique, at this point.



Hoping it may help,
Vanderghast, Access MVP

OK Thank you but let me see if I understand this correctly. I need to
place
my filed that I am going to move to the first ine in the grid. Then I
select
my table where the actual data is coming from. Then I do the above
expression
in the "update to" line of the grid? That sounds fine but two questions.

1. I still get the -1 and or 0 as a result
2. how does access know what table or field I want the data to go into.
Thank you very much you are getting me in the right direction. I still
think
I am missing something tohough. Can you help?

Hi,


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.


Hoping it may help,
Vanderghast, Access MVP

Attachments: IIf([dbo_tblProductversion].[Attachments]=True,"Y","N")

It works fine in the select query but when I change to my update query
it
say it is not a valid name and to make sure it includes the right () {}
""
etc.

This expression works perfectly in my Append query so why not this one?
My
Update query that I am trying to creat is just a cut and paste of the
Append.
So why does it not work?

PLEASE HELP!

 

[Guest]

OK thank you for helping me and let me see if I have this stright?

In update query..

Line 1 (Field) EAndOFlag: IIf(dbo_tblProductversion.EAndOFlag=True,"Y","N")
Line 2 (Table) Is the table where the data needs to be updated to and not
the table where line 1 is from (the table)
Line 3 [DEV_dbo_TblProduct].[EAndOFlag] _this is the table where the data is
going and the filed onthat table.

Is all this correct? Thanks again for all your help!

Hi,


Nope, the first line is the field that receive the modification, the second
line is the table having that field to be updated, not the table where the
data comes from.

As example, to set the field x from table tableA the value 5, then the first
line is x, the second line is TableA and the UpdateTo is 5


In the UpdateTo line, if the data can come from multiple table, use the
syntax

tableName.fieldName


to remove any ambiguity (about which tables supply fields). Since in

IIf([dbo_tblProductversion].[Attachments]=True,"Y","N")


you already use that syntax, that should be quite fine. Note that any
involved table must be in the upper part of the query designer, with the
required "joins" between the tables: You should be able to use a standard
SELECT and see the data you want update. But since you have that part right,
already, I don't suspect any problem from that technique, at this point.



Hoping it may help,
Vanderghast, Access MVP

OK Thank you but let me see if I understand this correctly. I need to
place
my filed that I am going to move to the first ine in the grid. Then I
select
my table where the actual data is coming from. Then I do the above
expression
in the "update to" line of the grid? That sounds fine but two questions.

1. I still get the -1 and or 0 as a result
2. how does access know what table or field I want the data to go into.
Thank you very much you are getting me in the right direction. I still
think
I am missing something tohough. Can you help?

Hi,


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.


Hoping it may help,
Vanderghast, Access MVP

Attachments: IIf([dbo_tblProductversion].[Attachments]=True,"Y","N")

It works fine in the select query but when I change to my update query
it
say it is not a valid name and to make sure it includes the right () {}
""
etc.

This expression works perfectly in my Append query so why not this one?
My
Update query that I am trying to creat is just a cut and paste of the
Append.
So why does it not work?

PLEASE HELP!

 

[Guest]

HU?

Yes I changed my Y/N to a Text filed so my update query will pass to a text
filed not a Y/N datatype

Attachments: IIf(dbo_tblProductversion.Attachments=True,"Y","N")

It looks to me that you are trying to update a Boolean Field (Bit Field in
Microsoft SQL Server) with a Text / Char value???

--
HTH
Van T. Dinh
MVP (Access)

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_TblProductVersion.Attachments =
IIf([dbo_tblProductversion].[Attachments]=True,"Y","N"),
DEV_dbo_TblProduct.ProductId = DEV_dbo_TblProduct.ProductId,
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;

Please post your entire SQL. Your sample doesn't look anything like an
update query.
--
Duane Hookom
MS Access MVP

Attachments: IIf([dbo_tblProductversion].[Attachments]=True,"Y","N")

It works fine in the select query but when I change to my update query
it
say it is not a valid name and to make sure it includes the right () {}
""
etc.

This expression works perfectly in my Append query so why not this one?
My
Update query that I am trying to creat is just a cut and paste of the
Append.
So why does it not work?

PLEASE HELP!

 

[Van T. Dinh]

If you look at this part of the SQL you posted:

" ... SET dbo_TblProductVersion.Attachments =
IIf([dbo_tblProductversion].[Attachments]=True,"Y","N") ... "

If [Attachments] is a Text Field, the first argument of the IIf is incorrect
since you compared a Text value with a Boolean value.

If [Attachments] is a Boolean Field, you cannot update its value with either
Text value "Y" or "N".

Either way (Text or Boolean Field), you will have error(s) with the Update
SQL due to data type mismatch!

 

[Michel Walsh]

Hi,


noope.

Line 1, Field, is the field that will receive the data, it cannot be an
expression, it must be a field
Line 2, Table, is the table that owns the field you mentioned in line 1
Line 3, Update To, is the expression for which the result, once evaluated,
will be put into field of line 1. Your iif( ) should be on that line.



Hoping it may help,
Vanderghast, Access MVP

OK thank you for helping me and let me see if I have this stright?

In update query..

Line 1 (Field) EAndOFlag:
IIf(dbo_tblProductversion.EAndOFlag=True,"Y","N")
Line 2 (Table) Is the table where the data needs to be updated to and not
the table where line 1 is from (the table)
Line 3 [DEV_dbo_TblProduct].[EAndOFlag] _this is the table where the data
is
going and the filed onthat table.

Is all this correct? Thanks again for all your help!

Hi,


Nope, the first line is the field that receive the modification, the
second
line is the table having that field to be updated, not the table where
the
data comes from.

As example, to set the field x from table tableA the value 5, then the
first
line is x, the second line is TableA and the UpdateTo is 5


In the UpdateTo line, if the data can come from multiple table, use the
syntax

tableName.fieldName


to remove any ambiguity (about which tables supply fields). Since in

IIf([dbo_tblProductversion].[Attachments]=True,"Y","N")


you already use that syntax, that should be quite fine. Note that any
involved table must be in the upper part of the query designer, with the
required "joins" between the tables: You should be able to use a
standard
SELECT and see the data you want update. But since you have that part
right,
already, I don't suspect any problem from that technique, at this point.



Hoping it may help,
Vanderghast, Access MVP

OK Thank you but let me see if I understand this correctly. I need to
place
my filed that I am going to move to the first ine in the grid. Then I
select
my table where the actual data is coming from. Then I do the above
expression
in the "update to" line of the grid? That sounds fine but two
questions.

1. I still get the -1 and or 0 as a result
2. how does access know what table or field I want the data to go into.
Thank you very much you are getting me in the right direction. I still
think
I am missing something tohough. Can you help?

:

Hi,


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.


Hoping it may help,
Vanderghast, Access MVP

Attachments: IIf([dbo_tblProductversion].[Attachments]=True,"Y","N")

It works fine in the select query but when I change to my update
query
it
say it is not a valid name and to make sure it includes the right ()
{}
""
etc.

This expression works perfectly in my Append query so why not this
one?
My
Update query that I am trying to creat is just a cut and paste of
the
Append.
So why does it not work?

PLEASE HELP!

 

[Guest]

OK Got it. One thing. When I show my datasheet view it still show the -1 or
0. Will it convert on its way over to Y and N or is the expression wrong? It
has worked in the past. Here is what I have..
IIf([dbo_tblProductversion].[Attachments]=-1,"Y","N")="[DEV_dbo_TblProduct].[Attachments]"

THANK YOU SO MUCH FOR YOUR HELP!

 

[Michel Walsh]

Hi,


This is the job of the "format". In Design view, select the column in the
grid, then right click on this column to be formatted, select
"Properties..." to display the property sheet (if it is not already
visible). The second line in the properties is about Format, select Yes/No.


Hoping it may help,
Vanderghast, Access MVP

OK Got it. One thing. When I show my datasheet view it still show the -1
or
0. Will it convert on its way over to Y and N or is the expression wrong?
It
has worked in the past. Here is what I have..
IIf([dbo_tblProductversion].[Attachments]=-1,"Y","N")="[DEV_dbo_TblProduct].[Attachments]"

THANK YOU SO MUCH FOR YOUR HELP!

Attachments: IIf([dbo_tblProductversion].[Attachments]=True,"Y","N")

It works fine in the select query but when I change to my update query it
say it is not a valid name and to make sure it includes the right () {}
""
etc.

This expression works perfectly in my Append query so why not this one?
My
Update query that I am trying to creat is just a cut and paste of the
Append.
So why does it not work?

PLEASE HELP!

 

[Guest]

Thank you for staying with me on this. GREAT HELP!

Hi,


This is the job of the "format". In Design view, select the column in the
grid, then right click on this column to be formatted, select
"Properties..." to display the property sheet (if it is not already
visible). The second line in the properties is about Format, select Yes/No.


Hoping it may help,
Vanderghast, Access MVP

OK Got it. One thing. When I show my datasheet view it still show the -1
or
0. Will it convert on its way over to Y and N or is the expression wrong?
It
has worked in the past. Here is what I have..
IIf([dbo_tblProductversion].[Attachments]=-1,"Y","N")="[DEV_dbo_TblProduct].[Attachments]"

THANK YOU SO MUCH FOR YOUR HELP!

Attachments: IIf([dbo_tblProductversion].[Attachments]=True,"Y","N")

It works fine in the select query but when I change to my update query it
say it is not a valid name and to make sure it includes the right () {}
""
etc.

This expression works perfectly in my Append query so why not this one?
My
Update query that I am trying to creat is just a cut and paste of the
Append.
So why does it not work?

PLEASE HELP!