Need MAJOR HELP to complete this query problem

G

Guest

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!
 
P

Phil

What kind of error do you get?
What field type is the field you are updating? IS it TEXT or is it a
YES/NO? These two are not the same. I am going to assume that it is a
Yes/NO field. You acnnot update a Yes/NO field to "Y" or "N" as these
are strings.

A yes no field is really a numeric field, that can only hold a one or a
zero. The answer is actually in your statement.

"IIf([dbo_tblProductversion].[Attachments]=True,"Y","N")"
This will display the TEXT "Y" or "N".
If you put it in an update query, you are updating the field to YES or
NO, TRUE or FALSE, 1 or 0.

Replace the "Y","N" with
TRUE,FALSE
or
YES,NO
Note, there are NO QUOTES. You are not setting it to the WORD, but the
CONSTANT. TRUE and YES = 1. FALSE and NO =0.

IIf([dbo_tblProductversion].[Attachments]=True,TRUE,FALSE)

Should do you right.
 
G

Guest

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.
 
P

Phil

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?






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!
Click to expand...
Click to expand...
 
G

Guest

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;


Phil said:
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?






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!
Click to expand...
Click to expand...
Click to expand...
 
P

Phil

Since the expression itself is not in the SQL you gave me, I can't help
much. I do not see anything wrong with what you have, but it is thick
enough I could have easily missed something.
I am going to suggest you drasticly simplify your query. Remove as much
of the criteria, field references,etc. as you can. get it to something
really simple, that still fails. It will be a lot easier to look at.
You say it takes from the field to the Table. Not sure what you mean.

You go to create a new field, thusly,

testfield:IIF(([dbo_tblproductversion]].[[eandoflag]=true,"y","n"))
and it moves it somewhere else?

Judging from the error, I think it is something really stupid, (and
really hard to find,) like a bracket or parenthesis out of place. This
is why I am suggesting making it simple. Cut out as much stuff not
relevant to the error as you can, and it should be a lot easier to find
the problem.


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!
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Expression in query 2
Problem with Cdate field not working in Update Query 0
Why wont this work in a update query 15
Update Query Help 2
UPDATE QUERY PLEASE HELP! 7
Issues with Update query PLEASE HELP! 3
Access Query problem 1
Data Type mismatch question 6

Top