Query-statement error

[cheberdy]

There is a query Goodsmovement with the column [Type of change]. For a row with 1,putaway should be used.
Type of change: If(Goodsmovement.[Type of change]=1,Putaway,Pickup).
I get an error that says the expression you entered contains an invalid number. How to fix this?

 

[jdraw]

I recommend that you provide some additional info.
What is/are Putaway, PickUp? Please show where they are defined.

 

[June7]

If Putaway and Pickup are literal text, then need to enclose in quote marks.

 

[cheberdy]

If Putaway and Pickup are literal text, then need to enclose in quote marks.

I did this Type of change: If(Goods movement.[Type of change]=1, "Putaway", "Pickup"). Then I get the error the expression you entered is syntactically incorrect

 

[theDBguy]

I did this Type of change: If(Goods movement.[Type of change]=1, "Putaway", "Pickup"). Then I get the error the expression you entered is syntactically incorrect

Should it be IIf (two letter i) instead of If (only one letter i)?

Type of change: IIf([Goods movement].[Type of change]=1, "Putaway", "Pickup")

 

[cheberdy]

Should it be IIf (two letter i) instead of If (only one letter i)?

Type of change: IIf([Goods movement].[Type of change]=1, "Putaway", "Pickup")

i know , but that is not the reason

 

[Pat Hartman]

Please put the expression in context. Is it in a query? Is it in a procedure? Is it a controlSource? There are slight syntax variations.

 

[Gasman]

Is goodsmovement 'goodsmovement' or 'goods movement'. If the latter, then surround that with []

 

[cheberdy]

Please put the expression in context. Is it in a query? Is it in a procedure? Is it a controlSource? There are slight syntax variations.

Its in a query

 

[theDBguy]

i know , but that is not the reason

"not the reason" for what?

 

[cheberdy]

"not the reason" for what?

for the error.

 

[theDBguy]

for the error.

Did you try it? Did you get a different error? Did you get the same error?

 

[cheberdy]

Here is a sample of the query

 

[cheberdy]

Did you try it? Did you get a different error? Did you get the same error?

It was not wrong in the first place, I just mistranslated it

 

[MajP]

I doubt that is a properly translated error message, so my first guess is that Type Of Change is a text field and not a numeric field. So maybe
iif(Goodsmovement.[Type of change]="1","Putaway","Pickup").

 

[cheberdy]

I doubt that is a properly translated error message, so my first guess is that Type Of Change is a text field and not a numeric field. So maybe
iif(Goodsmovement.[Type of change]="1","Putaway","Pickup").

it is a numeric field. Is this a problem?

 

[June7]

Numeric field (image shows as number) and literal strings, this should work:

SELECT *, IIf([Type of change]=1, "Putaway", "Pickup") AS someAliasName FROM Goodsmovement;

There is no need to include table name prefix unless pulling multiple fields with same name from various tables.

Post your full query SQL statement.

 

[The_Doc_Man]

OK, let's apply a little logic to the original question.

If(Goodsmovement.[Type of change]=1,Putaway,Pickup).

You said this was in a query context. Your original error says "invalid number." Goodsmovement.[Type of change] is not in the context of a number and would not be presumed to be a literal number. The digit 1 is a valid number so that isn't it. There remain three possible things that could lead to the error.

Putaway and Pickup, appearing without quotes in SQL context would be taken as field names which would have to exist in the named record. But I would anticipate a different message in that case. The other possibility is that whatever is in the record in field [Type of change] might ALSO not be a valid number, which would stop before it got a chance to complain about the meaning of Putaway or Pickup.

When someone expressed the need for quotes, you replied:

I did this Type of change: If(Goods movement.[Type of change]=1, "Putaway", "Pickup"). Then I get the error the expression you entered is syntactically incorrect

If that is a mis-type, your error is the space in (Goods movement.[Type of change] but you suggested a mistranslation. So was the mistranslation to turn that into two words or is the error something else?

You need to give us a bit more to work with. June7 was quite correct to ask to see the SQL statement. This is like swinging at a pinata while blindfolded. Please when you ask a question, explain at least a BIT more. We are at 17 posts and still grasping at straws because some aspect of this problem is not clear yet.

 

[Galaxiom]

The displayed values belong in a lookup table against a numeric key. Join the table to the data so the displayed value is directly available from the table.