MS Access If Statement 2

[aarellano]

Hello,

I created a query that has a field lets call it field1 field1 can be 3,4 or P
then I have another field, lets call it field2.
field2 is a number and last field, field3. It is also a number
I wold like to do something like this

If field1=3 then field2/1000 and then multiply by field3 else If field1=4 then field210000 and then multiply by field3 else If field1=P then 1/field2 and then multiply by field3

I am really lost on this one

any help is appreciated.

 

[SkipVought]

Hi

"field1 can be 3,4 or P"
[tt]
IIf(field1="3",field2*field3/1000,
IIf(field1="4",field2*field3/10000,field3/field2))
[/tt]

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[aarellano]

I gave it a go and I get a enter parameter value box

 

[dhookom]

What does your "enter parameter value box" ask for? Are se supposed to guess without seeing your table? Did you use your field names?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[aarellano]

Field3   Field2  Field1    Field4

DDARQT	RUNLB	TBCOD	HOURS
520	0.57	3	
520	0.57	3	
0	0.57	3	
0	0.57	3	
540	0.57	3

I put the forumula on the Criteria of filed4

when I added field4 to my table, then re-created the query and inserted the formula it ran but I just got this

DDARQT	RUNLB	TBCOD	HOURS
                             0

 

[dhookom]

The expression should not be in the criteria. I would expect to see the expression used with your field names as a calculated column in the query. Try again with the expression in the SELECT clause of your SQL and using your field names.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[aarellano]

ok got it

this is what I got on the select in the sql

SELECT EDGARQRY_GRSSROUT.FEAAC3, EDGARQRY_GRSSROUT.DDARQT, EDGARQRY_GRSSROUT.DDAITX, EDGARQRY_GRSSROUT.RUNLB, EDGARQRY_GRSSROUT.DDDPVA, EDGARQRY_GRSSROUT.VALUC, EDGARQRY_GRSSROUT.OPSEQ, EDGARQRY_GRSSROUT.WKCTR, EDGARQRY_GRSSROUT.DPTNO, EDGARQRY_GRSSROUT.TBCOD, EDGARQRY_GRSSROUT.FEGGNB
FROM EDGARQRY_GRSSROUT
WHERE ((([HOURS])=IIf("TBCOD"="3","DDARQT*RUNLB/1000",IIf("TBCOD"="4","DDARQT*RUNLB/10000","DDARQT/RUNLB"))));

 

[dhookom]

I think this is what you want (assuming fields in EDGARQRY_GRSSROUT of TBCOD, DDARQT, and RUNLB):

SELECT EDGARQRY_GRSSROUT.FEAAC3, 
EDGARQRY_GRSSROUT.DDARQT, EDGARQRY_GRSSROUT.DDAITX, 
EDGARQRY_GRSSROUT.RUNLB, EDGARQRY_GRSSROUT.DDDPVA, EDGARQRY_GRSSROUT.VALUC, EDGARQRY_GRSSROUT.OPSEQ, EDGARQRY_GRSSROUT.WKCTR, EDGARQRY_GRSSROUT.DPTNO, EDGARQRY_GRSSROUT.TBCOD, EDGARQRY_GRSSROUT.FEGGNB,
IIf([TBCOD]="3",[DDARQT]*[RUNLB]/1000,IIf([TBCOD]="4",[DDARQT]*[RUNLB]/10000,[DDARQT]/[RUNLB])))) As Hours
FROM EDGARQRY_GRSSROUT;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[aarellano]

Thank you so much dhookom It worked!!!!!!! I really apreciate the help!!!! I got about 95k records and I discovered that I have more TBCOD so this is what I did and it worked.

IIf([TBCOD]="C","0",IIf([TBCOD]="H",0,IIf([TBCOD]="",0


[code]


Again tank you !!!!!!

 

[aarellano]

I know this might be pushing it, but could I do this directly into the table instead of the query?

 

[dhookom]

There is generally no good reason to perform this is a table. You should not be storing values that can be calculated. You also should not be allowing users to view tables.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[aarellano]

Got it.
Thank you dhookom.