result is "0"

[slimjen]

Hi all, using Access 2010. When I run a query with the following:



[CODE]Type: IIf([qryA].

="50", [qryB].[Type]=Raw, [qryB].[Type])

I get "0" instead of "Raw". Can anyone tell me what I am doing wrong please. Thanks

[JoeM]

Is the field you are checking a numeric or text field?
If it is a numeric field, you do NOT want double-quotes surrounding the number 50.

[ranman256]

Do want an IF?

[JoeM]

Do want an IF?

It looks like it is there, but somehow got messed up in his code tags (note the text just above the code box).
I wonder if his field name was "Code", and that is confusing things.

[slimjen]

the fields are text. Sorry, My code is an IIF statement. Tags didn't grab all

[JoeM]

Can you post your formula again without the CODE tags?
This part looks bad too:

[qryB].[Type]=Raw

If you just want to return the word "Raw", you would have just have

...,"Raw",...

in the second argument.

[slimjen]

I had a field named Code which threw the code tags off. Here you go:

IIf([qryA].[ID]="50", [qryB].[Type]=Raw, [qryB].[Type])AS type

[JoeM]

What exactly are you trying to do?
Are you just trying to return a value, or replace the value in your "Type" field.

Note that in Select Queries, calculated fields will only return calculated values. They will not "reset" the value on another field. You would need to use an Update Query to do that.

Maybe this is the calculaton you are looking for (if just wanting to return a value)?

IIf([qryA].[ID]="50","Raw",[qryB].[Type]) as cType

[ssanfu]

NOTE: "Type" is a reserved word in Access and shouldn't be as object names. It confuses Access and isn't very descriptive.

http://www.allenbrowne.com/AppIssueBadWord.html

[slimjen]

Poor choice of field names. See this:

IIf([qryA].[txtCID]="50", [qryB].[CType]=Raw, [qryB].[CType]) AS LType

txtCID is a text field with different CIDs such a 10, 20 , 30, 40, 50 etc. CType the type of txtCID also text. I want if txtCID = "50", fill in the CTYpe as "Raw", else use whatever comes up as the CType.
Thanks

[JoeM]

I want if txtCID = "50", fill in the CTYpe as "Raw", else use whatever comes up as the CType.

See my reply back in post #8.

The problem is this part right here:

[qryB].[CType]=Raw

There are many issues with this:
1. Any literal text you want to return needs to be enclosed in double quotes ("Raw")
2. If you just want to return this value for the sake of this calculation, for this second argument you would not use:

[qryB].[CType]="Raw"

but rather just:

"Raw"

3. If you want to permanently change the underlying value of your [CType] field, you cannot do it in an IIF statement like that in a calculated field, you need to use an Update Query instead.

[ssanfu]

Re-read posts #2 and #6 by Joe.
Then re-read post #8. (third sentence)

[qryB].[CType]=Raw

Missing quotes around RAW......should be

[qryB].[CType]= "Raw"

If the "txtCID" field (column) in "qrya" equals a string that is 50 (different than a number 50),
return the result of "[qryB].[CType]="Raw" "
else
return the value from the field (column) "[qryB].[CType]".

in the the field (column) named "LType".


If the string RAW was enclosed in quotes, the returned value for the TRUE part of the IIF() statement would be TRUE if the value in CType = "RAW" and FALSE for all other values of CType.



This is the corrected IIF() statement.... IF "txtCID" is a number and not text.

IIf([qryA].[txtCID]=50, "Raw", [qryB].[CType]) AS LType

[slimjen]

Sorry; Raw is surrounded by quotes. Failed to copy and paste my code rather I typed it out such short code. txtCID is a text field as well as the type field. Still returning 0. Please see post #10 outlining fields

[JoeM]

Did you address all three issues I commented on in post #11?

If so, please post your current formula, and answer the question about wanting to just return a value or wanting to update the underlying field.

[slimjen]

I do not want to change the underlying field. I just want this in the query. He's the correction with "Raw" in quotes and it works:

IIf([qryA].[txtCID]="50","Raw”, [qryB].[CType]) AS LType

Thanks so much