Results 1 to 11 of 11
  1. #1
    MrOhhmy is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    23

    Help with Query or Calculated Field


    I'm having an issue with a calculated field. I see a lot of threads suggesting not to use them, I've tried queries and need help with my file please. The issue is the grand total of gallons pumped. I can only get the field to update once I close and reopen form. Also, it will only add all fields if I default the value to 0. I would rather it be blank and the query/calculated expression know it's a 0 if Null. Thanks in advance.Impoundments.zip

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I have calculated fields in every query. Thats how you use them.
    use: NZ(field,0) to convert null to zeros.

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I wouldn't use calculated fields in tables. I would have the calculated fields in queries.
    I have no idea what your database is about but the names used for fields strongly suggest to me that it is not normalized. Also, it would be better not to use spaces when naming objects like tables, fields and forms etc
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you cant use calculated field in a table.

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by ranman256 View Post
    you cant use calculated field in a table.
    I think you are mistaken. See: https://support.office.com/en-us/art...2-6de54fafbde3
    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
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I agree with Bob about the table not being normalized. It looks like you just imported an Excel worksheet.
    This is known as "Commiting Spreadsheet". This is quite common for new Access users to do because they see a table and it "LOOKS" like a spreadsheet.

    As to your problem with the query..... you have based the form "frm Impoundement" on the table "tbl Impoundments". You need to use a query.



    The first thing you need to do is make a copy of the dB.
    I repeat - MAKE A COPY of the dB. Use the copy so you can throw it away without losing you dB.

    Using the copy:
    Now you need to remove any and all SPACES ( or replaces the spaces with the underscore) in object names - this means field names, table names, form names, query names.......

    Close, then open the dB and try and add data to ensure that everything still works correctly.

    All good??

    OK, since you have based the form "frm Impoundement" on a table, this needs to be changed.
    Create a query based on the table "tbl Impoundments". Name it "qryImpoundments" (NO spaces ).
    Open the form "frmImpoundement" and click on the DATA tab. Change the RECORD SOURCE property to "qryImpoundments".
    Save the form and close it. Now open the form. It should open with no errors. Try adding data to see if the calculations are correct.

    Still good??


    Next, Open the query "qryImpoundments" in design view.
    For each calculated FIELD in the table, edit the same column in the query. You will be adding the calculation.
    For example, the calculated field "45A S Gallons 1".
    In the QUERY, the column would have
    Code:
    45A_N_Gallons 1: [45A_N_Finish1]-[45A_N_Start1]
    (note I changes some of the spaces to the underscore and deleted the space after "Start")

    For the next calculated field, the query column would be
    Code:
    45A_N_Gallons2: [45A_N_Finish2]-[45A_N_Start2]
    Get the idea? Do this for every calculated FIELD in the table.
    Once you have completed changing the calculated columns in the query, test the changes by entering data into the form.

    Almost Last step is to delete the calculated fields in the table. EVERY calculated field. They should all be in the query now.

    Last step: TEST. Enter data into the dB.

  7. #7
    MrOhhmy is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    23
    Thanks Steve. Did the query, no spaces. Still have the issue with the zeros and the updating of the Total field. Is there no way around having to put default value 0? If not, that's fine. My gallons pumped updates when I click another field, but the Total gallons doesn't until I close and reopen the form. Any way around this?

  8. #8
    MrOhhmy is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    23
    Never mind on the total formula, got the answer using the Nz function. Just have the issue with it updating only on closing and reopening form.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by MrOhhmy View Post
    Just have the issue with it updating only on closing and reopening form.
    As a test, press the <F9> key. This should recalculate the form/subforms without closing/opening the form.



    How many people will be using this dB at the same time?
    Will it be accessed from multiple offices/locations?


    Would you post your current dB so I can try a couple of things?

  10. #10
    MrOhhmy is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    23
    Impoundments (2).zip Updated DB.
    Yes, this will be shared. Each user has their own station but the db is on a shared network drive.

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by MrOhhmy View Post
    Yes, this will be shared. Each user has their own station but the db is on a shared network drive.
    This is a known and sure way to corrupt your dB.
    The dB should be split, with the BE holding the tables and the FE with everything else.
    Each user would have a copy of the FE on their local computer. The BE would be on the shared server.

    This is a common topic. Search the forum......



    (still looking into the other problem)

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

Similar Threads

  1. Replies: 1
    Last Post: 02-03-2016, 06:54 PM
  2. Replies: 2
    Last Post: 12-30-2014, 01:32 PM
  3. Replies: 1
    Last Post: 08-25-2014, 01:23 PM
  4. Replies: 4
    Last Post: 03-20-2014, 03:52 PM
  5. Replies: 3
    Last Post: 02-13-2013, 10:15 AM

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