IIF Function (Access 2K (SR-1))

Topic

New Reply

I’m trying to use the IIF() function in the following statement: Void: IIf([Invoice]![FCO]=”No”,””,”Void”). Actually, that was a test statement to see if it would work. Nothing I’ve tried with that function works. I really wanted the iff() function to read like this in a query: Void: IIf([Invoice]![FCO]=”No”,[Tot_Inv],[Tot_Inv]*-1). The Tot_Inv value is a computed value in the query that sums up the individual elements of an invoice. The intent is that if FCO=”Yes”, the invoice has been voided and multiplying the total invoice value by -1 will result in a negative invoice value that will be subtracted from a listing of monthly reports in a report. Either express above results in “#Error” (sans quotes).

I really don’t see this as that difficult, but I’m missing something. I don’t think it is a reference since VBA routines in other parts of the application are working fine. Can anybody offer a suggestion as to what I or Access is doing/not doing right?

Thanks,

Bill Blazer

Reply | Quote

Replies

If the value in [FCO] is a boolean Yes/No value, then you don’t need the quotation marks when you test for it – that is confusing Access, since it is trying to comapare a boolean with a string.

Your expression should read:

Void: IIf([Invoice]![FCO]=No,[Tot_Inv],[Tot_Inv]*-1).

Reply | Quote

Dean, thanks for the imput. Not long after I posted that, I found that if I changed the “No” to -1 and adjusted the rest of the expression accordingly, it worked as I originally expected. Not sure if I ever tried the expression without the quotes but it is a Yes/No field. I tried so many different things my head was swimming. Now, having that little problem resolved, a new one has developed (related) in that once I have the negative value, it is being double subtracted resulting in an erroneous value in the report. I’m off to browse the lounge again to see if I can find help with that problem. If not watch for another post.

Thanks again for your input.

Bill Blazer

Reply | Quote

Use :

Void: IIf([Invoice]![FCO]=No,[Tot_Inv],0)

For yes/no field, if find it easier to use true and false

Void: IIf([Invoice]![FCO]=False,[Tot_Inv],0).

Reply | Quote

Thanks for the response Francios. You may have noticed, I finally got it resolved by just using “-1”. For experience, I’ll play around with your suggestions. Now, if you could point me to some sights or information that might provide instructions on exporting single records I’ll start exploring that as my next project.

Thanks again.
Bill Blazer

Reply | Quote

If you want to export one record to an external file, create a query that return that record and use the DoCmd.TransferDatabase, DoCmd.TransferSpreadsheet or DoCmd.TransferText (depending of the type of file you want to export to).
See the help file for the specifics of the transfer commands.

Reply | Quote