If then i en sql query

IIf([DinTabel]![L02_TA]=2;[DinTabel]![L02_pris]*-1;[DinTabel]![L02_pris])

læste noget om at det ikke virker i ne sqll, men kun vba?

og når jeg bruger vba så crasher access

når jeg sætter den ind i sql'en siger den syntax fejl:


IIf([PUBLIC_LP]![L02_TA]=2;[PUBLIC_LP]![L02_PRIS]*-1;[PUBLIC_LPl]![L02_PRIS]) AS udtryk3

Der sneg sig en lille fejl ind korrekte er:

IIf([PUBLIC_LP]![L02_TA]=2;[PUBLIC_LP]![L02_PRIS]*-1;[PUBLIC_LP]![L02_PRIS]) AS udtryk3

men der er stadi syntaks fejl

If you are entering direct into SQL then change ; to ,

Terry you sure?

would'nt commas conflict in this:

SELECT PUBLIC_LP.L02_B_DATO_HH, PUBLIC_LP.L02_B_NR, PUBLIC_LP.L02_VARENR, PUBLIC_LP.L02_NAVN, PUBLIC_LP.L02_ANTAL, PUBLIC_LP.L02_PRIS, PUBLIC_LP.L02_KPRIS, PUBLIC_LP.L02_DEB_KRE, [L02_ANTAL]*[L02_KPRIS] AS Udtryk1, [L02_ANTAL]*[L02_PRIS] AS Udtryk2, PUBLIC_LP.L02_TA, IIf([PUBLIC_LP]![L02_TA]=2;[PUBLIC_LP]![L02_PRIS]*-1;[PUBLIC_LP]![L02_PRIS]) AS udtryk3
FROM PUBLIC_LP
WHERE (((PUBLIC_LP.L02_B_DATO_HH) Between [fra dato ååååmmdd ] And [til dato ååååmmdd]) AND ((PUBLIC_LP.L02_DEB_KRE)=[indtast kunde nr]))
ORDER BY PUBLIC_LP.L02_B_DATO_HH;

; is only used in DK  as parameter separator because , is used as decimal character.


If you enter this into a column in query design view then when you go to SQL view it will have altered; to ,


IIf([PUBLIC_LP]![L02_TA]=2;[PUBLIC_LP]![L02_PRIS]*-1;[PUBLIC_LP]![L02_PRIS])

Well the complete query from above still makes syntax error.

The complete query is copy pasted from access, except the iff sentence.

can you send dB with table(s)?

ekspertenATsanthell.dk
AT=@

Funny thing happened.

Had just exported tables and queries to a new database, cleaned it up (hide confidential data) and then tested it again, and voila it worked.

Tested again in the real database and worked too...

I pasted in the "generator-tool" in the query. That was the diffence apparently.

Inserted directly in SQL is:

IIf([PUBLIC_LP]![L02_TA]=2,[PUBLIC_LP]![L02_PRIS]*-1,[PUBLIC_LP]![L02_PRIS]) AS Pris




Well post answer and you'll get points

Thx for saving my day.

and dont forget Jensen363 :-)

thanks

Takker