Results 1 to 9 of 9
  1. #1
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110

    Field Iff giving an error

    I have a very simple Query that is not behaving in a way I expect.

    There is a table that has
    tblSalary
    SalaryID (PK)
    Name
    PayFrequency (FK)

    tblStudentLoan
    StudentLoanID (PK)
    SalaryID (FK)
    YearlySalary
    StudenLoan
    WeeklyThreshold
    MonthlyThreshold
    Repayment

    And then

    PayFrequency

    from tblPayFrequency
    with:
    PayFrequencyID (PK)
    PayFrequency

    The relationship is one tblPayFrequency to many with a (FK) in tblSalary

    In my Query I enter the following code into the Field box at the end of the query and the test is: (which works)

    LoanRepay: Iff(([tblSalary].[PayFrequency]=2,"True","False") and this works, as the 2 is the ID for PayFrequencyID (PK)

    However I would like to be able to have the query work on the actual word in the PayFrequency field, eg:
    LoanRepay: Iff(([tblSalary].[PayFrequency]="Monthly","True","False")

    Yet I cant find a way to do this.

    What am I missing that is so obvious?

    p.s
    I'll expand True and False into complex formula, which I know works, but kept this query simple.



    Regards

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Is the field PayFrequency (FK) in table tblSalary a "Lookup" field?
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110
    Hi Bob, yes it is.

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    They are generally considered to be a bad idea. See: http://access.mvps.org/access/lookupfields.htm
    Can you post a copy of your db
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Add tblPayFrequency to the query and add PayFrequency
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110
    Hi Bob

    Ignore the password protect thing at the beginning, just put steven.jones password 123 if it doesnt close.

    Its the query, there is only one that I am working with.

    Regards
    Attached Files Attached Files

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by Steven19 View Post
    Hi Bob

    Ignore the password protect thing at the beginning, just put steven.jones password 123 if it doesnt close.

    Its the query, there is only one that I am working with.

    Regards
    Is there still a problem with the query?
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    @Steven19

    In the attached db I have changed the lookup field and the query.
    I have also removed the "Full Name" from the table. This data should not be stored as you already have First and Last names stored separately.
    The Full Name can be returned in a query using a calculated field as I have done in the query.

    Post back if you have any questions.
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  9. #9
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110
    Hi Bob

    Thanks for the help you have provided. I have learnt something new and am looking forward to not repeating this error.

    Regards

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dlookup giving error
    By shylock in forum Programming
    Replies: 5
    Last Post: 11-02-2018, 08:20 AM
  2. Iif statement giving error
    By Tazmaniac in forum Queries
    Replies: 5
    Last Post: 03-25-2018, 01:29 PM
  3. Replies: 4
    Last Post: 05-22-2015, 02:29 AM
  4. DCount on a Query giving #Name? error
    By Huddle in forum Access
    Replies: 9
    Last Post: 06-20-2012, 11:40 AM
  5. NoData() still giving me an error.
    By cowboy in forum Programming
    Replies: 3
    Last Post: 04-08-2010, 12:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums