Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2008
    Posts
    9

    Unanswered: Comparing fields within table and printing

    Hi,
    I'm trying (unsuccessfully) to use an IF function in Access Query in the criteria to compare the fields HOMELOC with CURRLOC (in the same table). If the CURRLOC is different than the HOMELOC then i want to have the CURRLOC print what is in that field. If the CURRLOC matches the HOMELOC then i want the display to be blank.

    I tried to mimic the excel format and get undefined IF function errors etc. So can anyone point me in the right direcection? Also, At first i thought i should be putting in the criteria box for CURRLOC, but then i got to thinging like in excel, i should create a new CURRLOC2 box or something and put the IF in that criteria box but then i get a pop-up and get no results at all in a file with 210 records.

    What I'm using in Criteria =IIf([Quickcat].[CurrLoc]<>[Quickcat].[HomeLoc],"[Quickcat].[CurrLoc]",""))
    Trying to match in excel =IF(C10<>B10,C10,"")
    Last edited by Librarysystem; 07-03-08 at 11:16.

  2. #2
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151
    It looks like you have an extra ')'

    =IIf([Quickcat].[CurrLoc]<>[Quickcat].[HomeLoc],"[Quickcat].[CurrLoc]","")

    The above should work.

    When trying to debug an if statement, try using something other than "" if the value is false, that way you'll know for sure what the expression has evaluated.

    Good luck, Stu
    --If its free, take it for what its worth!

  3. #3
    Join Date
    Apr 2008
    Posts
    9
    I have also tried this in the CURRLOC with no results

    IIF([fieldname]<>[fieldname] ,PRINT[fieldname])

  4. #4
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    From scratch

    I'm not sure where you are trying to run the query. Is it from a form, when you a click a button? Or is it the record source of a field on a form?

    If you are running it from a form then the criteria has to reference the form and the control and should be something like this; I'm assuming your form name is Quickcat

    =IIf(Forms![Quickcat].[CurrLoc]<>Forms![Quickcat].[HomeLoc],Forms![Quickcat].[CurrLoc],"")

    Sorry didn't notice the "" around the true part of the statement. They shouldn't be there either.
    --If its free, take it for what its worth!

  5. #5
    Join Date
    Apr 2008
    Posts
    9
    after importing information into the table, i go to the create query wizard and then set up the query, then customize in design view. Running by the ! character. I'm attaching a screen shot of just where i am typing in.
    Attached Files Attached Files

  6. #6
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    I think....

    OK, I think this is what you are trying to do;

    tcn HomeLoc CurrLoc NewField
    1 Test1 Test1 ""
    2 Test1 Test2 Test2
    3 Test3 Test1 Test1

    You need to add another field in your query, after the CurrLoc

    NewField:iif(CurrLoc <> HomeLoc,CurrLoc,"")

    That should do it
    --If its free, take it for what its worth!

  7. #7
    Join Date
    Apr 2008
    Posts
    9
    It Worked!!!!!!!!!!!!!!!!!!!!!!

    Thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Posting Permissions

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