Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Field Formatting (A2K)

    This is just a small thing that deals with field formatting.

    I have a SubForm called: subFormReturned
    which contains a field in the footer called; txtQReturned
    with the datasource of txtQReturned being; =Sum(NZ([Inv_Returned_Qty]))

    The field is then displayed on the main form as =([SubFormReturned].[Form]![txtQReturned])

    If there is data in this field in the subform it displays correctly on the main form.
    If not, I get the #Error indicator.

    It does however display correctly on the subform if there is no data, so the NZ is being lost in the translation.

    I thought that the NZ in the original field would have made it a happy camper, but not so.

    I'd appreciate any suggestions,
    Cheers,
    Andy

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

    Re: Field Formatting (A2K)

    What happens if you change the control source of txtQReturned to

    =Nz(Sum([Inv_Returned_Qty]),0)

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Formatting (A2K)

    'Morning Hans,

    unfortunately nothing.
    Cheers,
    Andy

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Formatting (A2K)

    I can't reproduce your error as you can see in the little attached demo.
    Just an idea : have you something in the format property of the textbox on the main form ?
    Francois

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Formatting (A2K)

    Francois,

    Very bizarre! I compared both yours and mine and found no differences, almost.
    So, I renamed all of your fields to match mine and mine still crashes.
    The only difference, including formatting, is that in yours it looks like this =[SubFormReturned]!txtQReturned
    and in mine when I tried to use the exact format, it comes back as =[SubFormReturned]![txtQReturned] with the extra square brackets.
    I'm obviously missing something obvious, obviously!
    Cheers,
    Andy

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Formatting (A2K)

    Francois,

    Just to double check my stuff I put together the attached document. Still baffled!
    Cheers,
    Andy

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

    Re: Field Formatting (A2K)

    You're not getting any further in this thread or in the one about Inventory Calculation. If I were you, I'd try very hard to create a stripped down copy so that Loungers can look at the problem directly. See <post#=401925>post 401925</post#> for instructions (converting to Access 97 usually reduces the size significantly)

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Formatting (A2K)

    The only manner I can get the error is when in the subform, the textbox containing the quantity has not the same name as the underlying field.
    If in the summing box in the footer of the subform, I use the name of the textbox, I get the error. If I use the name of the field, I don't get the error.
    Francois

  9. #9
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Formatting (A2K)

    Francois,

    This sounds weird, but I'm glad you got the error as well. I don't feel so out in left field. I'll try your manipulations to see if I can bail myself out. Out of desperation I tried first, importing your tables and forms into my database and vice versa and still got my original errors. Thanks much for looing at this,
    Cheers,
    Andy

  10. #10
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Formatting (A2K)

    Hans,

    Thanks for the suggestions. I did try to reduce that which I have into a sendable fashion but was not aware of the Access 97 item. I'll have to give that a go. I'm sure there must be a good reason that the Forum won't accept anything larger than a 100K. Quick question while in the neighbourhood and being aware that you are also a Word Expert, is there anyway that a Word document can be "Compacted" and not just zipped?
    Cheers,
    Andy

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Formatting (A2K)

    I had to precise : I get the error in all cases. With or without quantity.
    Can you attach a stripped version of your db like Hans propose ?
    Francois

  12. #12
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Formatting (A2K)

    OK, I did try changing the name of the text box to that of the underlying data and got the same error. Correct me if I'm wrong, but I thought that naming your text box the same as your data name was not advisable? Anyway, I'll try and create a cut down version of my system.
    Cheers,
    Andy

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

    Re: Field Formatting (A2K)

    The Lounge server is a standard PC (with Linux) with standard broadband connection. If we allowed large attachments, the Lounge would bog down completely.

    Word does not have a Compact and Repair option the way Access does (neither does Excel), but then it doesn't need it as much.

  14. #14
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Formatting (A2K)

    Hans,

    Thanks for the feedback on both counts.
    Cheers,
    Andy

  15. #15
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field Formatting (A2K)

    Francois & Hans,

    OK, this will be one of those believe it or not type scenarious.

    The premise of this whole issue was that I wanted to display a subform which contained a continuous form displaying all past transactions in reverse date order so that the last one in was displayed at the top of the list. I placed an =Sum field in the footer of this subform so that I could display a running calculation on the main form. The calculation would show on the main form when sitting on the first record, but as soon as I moved on to the rest of the records, I would get the Error Message.

    I've been able to get this to work by, and are you ready for this, placing another subform on the mainform using the same data file, putting the same =Sum field in the footer of this field, having the calculation on the main form refer to this form and made this subform "not visible".

    So, I get my continuous form showing past history, my main form calculation showing running totals, no screen space being wasted with a single form subform and all is well.

    I have no idea why this works.

    Now all I have to do is go and deal with the Average Cost.
    Cheers,
    Andy

Posting Permissions

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