Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    #Name? Nulls (A2k)

    I had some help on this a while back, and have to re-visit the issue.
    I'm using Texts to lookup values direct from the table, if data is there, all is ok, if data is not, I get #Name?.
    I guess it's because of the various labour descriptions I have to work with.

    I can not get the expressions to handle the Null value.
    I've attached a screen dump.
    My guess is I may have to use the texts to pull the data from a query instead of table, in which I could possibly use something like:

    TGIIF([TG],[TG],0))

    I'm not sure really.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: #Name? Nulls (A2k)

    Does it help if you use

    =DSum("Nz([TG],0)","tblLabourBooking","EstimateNo=" & [EstimateNo] & " And Operation='Valet'")

    or

    =Nz(DSum("Nz([TG],0)","tblLabourBooking","EstimateNo=" & [EstimateNo] & " And Operation='Valet'"), 0)

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #Name? Nulls (A2k)

    Option 2, in a nutshell
    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #Name? Nulls (A2k)

    Hans
    Adding an extra field [Supp], Would that be:

    =Nz(DSum("Nz([TG],0)","tblLabourBooking","EstimateNo=" & [EstimateNo] & ","Supp=" & [Supp] &" And Operation='Valet'"), 0)

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #Name? Nulls (A2k)

    I think I've got it

    =Nz(DSum("Nz([TG],0)","tblLabourBooking","ESTIMATENO=" & [EstimateNo] & " and SUPP=" & [SUPP] & " And Operation='Mechanical'"),0)

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #Name? Nulls (A2k)

    Hans
    Although this expression now works as it should, the pulling if data is some what very slow, it's not instantanious obviously for the calculation direct from the table.
    Would this improve if I do the summing in the subform and then reference it on the main form.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: #Name? Nulls (A2k)

    You'll have to try it on a copy of your database to find out. It might help, but you'll still need DSum as far as I can see, and the statistical domain functions DCount, DSum etc. are notoriously slow.

    Another possibility would be:
    <UL><LI>Create a group by query that totals TG etc. by operation
    <LI>Create a new subform based on this query.
    <LI>Link the subform to the main form by Estimate number.[/list]This new subform would take the place of the "Work in progress" block on the main form.

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #Name? Nulls (A2k)

    I see what you mean Hans.
    I'll have a play with it, I feel it may not look as neat as my current set up, but, I may have to sacrifice something to improve the efficiency.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: #Name? Nulls (A2k)

    Hi Dave,

    If you make the new subform a continuous form instead of a datasheet, you should be able to make it look almost exactly like what you have now:
    <UL><LI>Set "Scroll Bars" to Neither, "Record Selectors" to No, "Navigation Buttons" to No and "Dividing Lines" to No.
    <LI>Format the text box bound to Operation as if it were a label.
    <LI>Format the other text boxes the way you have them in your main form.
    <LI>Give the subform control on the main form a transparent border.[/list]

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #Name? Nulls (A2k)

    <P ID="edit" class=small>(Edited by D Willett on 25-Jun-03 09:00. )</P>Like so :--------------

    Thanks again.
    I tried the subform method, which works very efficiently (Instantanious and no lag).
    The continuous method could be my answer.

    I think you should change your post signature to:

    Regards Hans
    [b]There's more than one way to skin a cat !![b] <img src=/S/catty.gif border=0 alt=catty width=19 height=18>

    Regards
    Dave <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Attached Files Attached Files

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #Name? Nulls (A2k)

    I'm going round the twist here.
    There's one subform on each tab(4), they all have the same record source.
    When I enter data in any of these, I need to requery or refresh the new subform which is not in the tab pages,ie, the form I've just created.
    Main:frmLabourOperations
    Sub In Tab Page:sbfLabourByJob (Data entry from here - Only one of the four)
    Sub Continuous Just Created:SbfQryLabourTotals

    As you see below, I'm cracking up:
    'Forms!frmLabouroperations.sbfqryLabourTotals.Requ ery
    'Me.Parent.sbfqryLabourTotals.Requery
    'DoCmd.Requery "sbfqryLabourtotals"

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: #Name? Nulls (A2k)

    I assume the code runs in the subform(s). If that is correct, try

    Me.Parent.sbfqryLabourTotals.Form.Requery

    (sbfqryLabourTotals must be the name of the subform as a control on the main form frmLabourOperations; this might be different from the name of the subform in the database window)

  13. #13
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #Name? Nulls (A2k)

    Hans
    Yes the code runs in the after update of [TG] and [TT] from any of the subforms within the tab pages.
    With your expression, as with mine, when the [TG] or [TT] is updated, the running total does not update.
    I added a calculated text in the continuous subform, which flickers when the after update occurs.
    The calc text doesn't update either.

    If I place the cursor in the cont-sub and Shift F9 it updates.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: #Name? Nulls (A2k)

    OK, then try

    Me.Parent.sbfqryLabourTotals.Form.Recalc

    instead of Requery.

  15. #15
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #Name? Nulls (A2k)

    <P ID="edit" class=small>(Edited by D Willett on 25-Jun-03 14:43. )</P>Hans
    Still not the way it should, the recalc works only after the 2nd update.
    See what you think. I originally wanted to go with a Refresh, still giving me control over the sbfLabourByJob.

    I noticed, by changing the continuous sub back to datasheet view, the re-calc works fine.
    Its only when it's in continuous, it doesn't work ??
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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