Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Feb 2002
    Location
    Summerville, South Carolina, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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).

  3. #3
    Lounger
    Join Date
    Feb 2002
    Location
    Summerville, South Carolina, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.[img]/forums/images/smilies/smile.gif[/img]

    Thanks again for your input.

    Bill Blazer

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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).
    Francois

  5. #5
    Lounger
    Join Date
    Feb 2002
    Location
    Summerville, South Carolina, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Francois

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •