1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Expression refers to non-existent field

Discussion in 'Business Applications' started by Zirpman, Feb 14, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. Zirpman

    Zirpman Thread Starter

    Joined:
    Feb 14, 2013
    Messages:
    4
    I have an Access 2003 database with project information. I'm trying to show the value of change orders (which may or may not exist) on the project info form. I've used a hidden subform to total the appropriate approved change orders and referenced the total in an unbound TextBox. This works fine, but if there are no change orders for the project, there is nothing to reference in the subform and I get an #Error message. I want it to read "0" instead, but I haven't been able to get any Iif statements to execute properly. Here is my reference:

    =[CO-Approved].[Form]![TotalCO]

    CO-Approved is my subform and TotalCO is the sum in the subform footer.
     
  2. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
    maybe-(I do not do Access any longer)
    =iif([CO-Approved].[Form]![TotalCO]>"",[CO-Approved].[Form]![TotalCO],0)
     
  3. Zirpman

    Zirpman Thread Starter

    Joined:
    Feb 14, 2013
    Messages:
    4
    Tried that one already. It gives me a #Type error.

    I also tried =iif([CO-Approved].[Form]![TotalCO]=null,0,[CO-Approved].[Form]![TotalCO])
     
  4. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
    So sorry it did not help, but happy to see the Error returned. The added info, I am certain will trigger a response from at least one of the very knowledgeable Access skilled members here in short order.
     
  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Can I suggest that you move the control back up the chain and ensure that the form always has some value from the query, even if it is only Zero.
     
  6. Zirpman

    Zirpman Thread Starter

    Joined:
    Feb 14, 2013
    Messages:
    4
    Well, my backup plan is to start the first change order with no info and a $0 value, but I don't know if I can automate that for each new project. I would prefer not to take this route since a $0 change order is possible and it could cause confusion.
     
  7. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    How about:
    =iif(ISNull([CO-Approved].[Form]![TotalCO]),0,[CO-Approved].[Form]![TotalCO],)
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Draceplace, I don't think it can work, especially if their are no records as the Form does not deploy properly.
    Which is why I suggested ensuring that the Query does produce at least one record, even if it is has a Zero value for that field. The problem of "No Records" in a subtable is very difficult to overcome.
     
  9. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
  10. Zirpman

    Zirpman Thread Starter

    Joined:
    Feb 14, 2013
    Messages:
    4
    Creating the FormHasData Public Function as shown on the Allen Browne site worked great. Thanks 20_2_Many!
     
  11. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
    Awesome! Glad it helped you, I know it helped me as well.
    Please remember to mark this solved if it really is!
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved Expression refers
  1. KayakGirl2018
    Replies:
    4
    Views:
    429
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1089540

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice