18. september 2012 - 08:57
Der er
11 kommentarer og 2 løsninger
If then i en sql query
Jeg har følgende felter i en query: L02_pris (indeholder et positivt tal) L02_TA (indeholder tallet 2 eller 4) Jeg vil lave lave L02_pris om til et negativt tal hvis L02_TA er 2: If(L02_TA = 2 ;L02_pris*-1;L02_pris) AS udtryk3 Men jeg kan ikke få den til det. Er på ingen måde en haj til det og får tit roddet rundt i ' " () mv.
Annonceindlæg fra SoftwareOne
18. september 2012 - 09:06
#1
IIf([DinTabel]![L02_TA]=2;[DinTabel]![L02_pris]*-1;[DinTabel]![L02_pris])
18. september 2012 - 10:15
#2
læste noget om at det ikke virker i ne sqll, men kun vba? og når jeg bruger vba så crasher access
18. september 2012 - 10:20
#3
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
18. september 2012 - 10:21
#4
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
18. september 2012 - 10:48
#5
If you are entering direct into SQL then change ; to ,
18. september 2012 - 11:14
#6
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;
18. september 2012 - 11:27
#7
; 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])
18. september 2012 - 11:35
#8
Well the complete query from above still makes syntax error. The complete query is copy pasted from access, except the iff sentence.
18. september 2012 - 11:46
#9
can you send dB with table(s)? ekspertenATsanthell.dk AT=@
18. september 2012 - 13:45
#10
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.
18. september 2012 - 14:03
#11
and dont forget Jensen363 :-)
18. september 2012 - 16:09
#12
thanks
19. september 2012 - 09:07
#13
Takker
Dyk ned i databasernes verden på et af vores praksisnære Access-kurser