Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    caroled
    Guest

    Look up Form (Access 97)

    I am a learner in Access, using Access 97.
    I wanted to make a form from which I could choose the month to show total expenses for allthe members for that particular month.
    I based the form on a query that had 2 fields: date which took only the the month from from the date,using datepart, and the amount sorted by --sum--. I also had the date look up the month by way of combo box that changed the date number to the name of the month. The form works with one problem... the months that don't have totals.... there were no expenses for that month.... remains the same. (The amount for a month that does show). Is there any way that this can come up 0.00.

    Thanks
    carol

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look up Form (Access 97)

    I'm not sure what you mean by "the months that don't have totals ... remains the same".
    If you are asking how you can have month totals show up as zero, instead of Null, look up the Nz function.
    The Nz function can be used to return zero, a zero-length string (" "), or another specified value when a Variant is Null.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Look up Form (Access 97)

    >>The form works with one problem... the months that don't have totals.... there were no expenses for that month.... remains the same. (The amount for a month that does show). <<

    I don't understand this. Do you mean if Jan had $25 in expense, but Feb had no expenses, that Feb is showing $25 on your form? If you just run the query, what does it show? Does it have a record for Feb at all?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    caroled
    Guest

    Re: Look up Form (Access 97)

    Thanks for your reply... what I mean is that so far there were expenses only for April and May.... no other months have expenses for any members.....in database form for query I see each month with its total....
    On the form I can choose these and get their totals but if I am looking at the total for April and press say January.... the total stays the same.
    Carol

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Look up Form (Access 97)

    I assume you mean that if you look at the query you see zero for January, but your form shows a different number; that is, the total for the month viewed prior to that.

    I don't understand how you are displaying your data; that is, I don't know what your form looks like. You say when you "press January", what do you mean by that? Is this a button or a combo box perhaps? And what code runs when you do that?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    caroled
    Guest

    Re: Look up Form (Access 97)

    When I look at the quiery I see only April and its sum/May and its sum.(dataview) When I make a form based on this query, I make a combo box to look up the months I get a list of all the months, May works, April works, but using other months the --sum-- remains the same, the sum for either April or may.. I am probably trying to do something that can't be done..[img]/forums/images/smilies/sad.gif[/img]

  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look up Form (Access 97)

    If the form has a combo box that lists the months, using the month number as the key, you are obviously populating this from a value list. Why not change the combo box to be populated from the query, this will show only the months where there is data. Add a second combo box bound to the date field from the query but have two columns, the second column being the sum of the amount. set the column width for column 1 to zero. This will show the Amount in the combo. When you change the selected month from the date combo, set the value of combo 2 to equal combo1 (after update event) the amount from the query will change automatically.

    Alternately you can add a parameter to the query under the date field eg forms!MySummaryForm!cboSelectMonth. Then when you have changed the date in the combo box you can requery the control/subform etc that is displaying the query output to obtain the correct value.

    There seems to be a problem with what you are doing. When you say you have based the form on the query, you get the records returned by the query. Changing data in a combo based on the date field attempts to change the data for that record if the combo box and the form are bound to a table or query.

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Look up Form (Access 97)

    I'd have to see the code you are using to populate the listbox. You obviously can't be basing it on the original query. It is hard to produce lists of things that haven't happened, which is really what you are trying to do. Depending on the situation, there are ways of getting this though. Again, I'd have to see more of your situation.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    caroled
    Guest

    Re: Look up Form (Access 97)

    Stewart

    > Alternately you can add a parameter to the query under the date field eg
    forms!MySummaryForm!cboSelectMonth. Then when you have changed the date in
    the combo box you can requery the control/subform etc that is displaying the
    query output to obtain the correct value.

    I didn't quite understand this... I am a real NEWBIE and need IDIOT
    language.

    This DID give me an idea. I made a new query based on first total query
    and added the months table to it . Joined the month(datepart) and the month
    number. Chose the option --Include all records from tb-months and only those
    from Qu-TotalExp where the joined fields are equal...

    The fields that I chose where : month name/frm tb-months; month from
    Qu-TotalExp; SumOfAmount from Qu-TotalExp.

    In the datasheet view I see all the months, some empty and some with their
    respective totals.

    Using this query I made a look up form making a unbound combo box for Month
    name. And placed the SumOfAmount on the form as a field.
    It now works.... months that don't have sums are empty.... it would be nice
    if they would come up as $0.00... but maybe I am asking to much.

    Thanks for all your help......

    Carol

  10. #10
    caroled
    Guest

    Re: Look up Form (Access 97)

    Mark

    I'd have to see the code you are using to populate the listbox. You
    obviously can't be basing it on the original query. It is hard to produce
    lists of things that haven't happened, which is really what you are trying
    to do. Depending on the situation, there are ways of getting this though.
    Again, I'd have to see more of your situation.

    I don't know exactly what you mean by -code- but I did solve part of the
    problem by making a new query based on my first total quiery and the table
    of months. By making a join between the month-number on the months tb with
    the month (datepart from original date) on the total query I was able to get
    the month-name and the SumOf Amount on the same query. By choosing option
    2-Include all records from tb-months and only those from Qu-TotalExp where
    the joined fields are equal...

    This gave me all the months and those that total sums. Of course the fields
    where there were no sums is empty.... would love that to come up 0.00....
    but when ever I tried some expression in either the criteria of month or
    sum I got an error saying that the join was wrong.

    Made a simple lookup form from the second query.. a combo box for the month
    to choose the month and placed the sum of the month as a field .
    It now works and at least doesn't give a number for months that have no
    sums... as yet.

    Thanks for you help......

    carol

  11. #11
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look up Form (Access 97)

    try using
    NZ[field name]

  12. #12
    caroled
    Guest

    Re: Look up Form (Access 97)

    I am not sure what you mean by NZ. Null to Zero?
    I have two fields in my query. Month-Name /Sum. If I put "NZ [Month-Name]" in the criteria
    of Month-Name.... I don't see any data... just the labels. If I put it in th sum field
    I get --Data type mismatch. I have tried " " also with no success.

    Thanks
    Carol

  13. #13
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look up Form (Access 97)

    Alternately you can add a parameter to the query under the date field eg
    forms!MySummaryForm!cboSelectMonth. Then when you have changed the date in
    the combo box you can requery the control/subform etc that is displaying the
    query output to obtain the correct value.

    I didn't quite understand this... I am a real NEWBIE and need IDIOT
    language.

    Have alook at query 3 in attached. Run it from the form using the command button, not Idiot language, just something that you can understand. Don't worry we were all beginners once.


    it would be nice
    if they would come up as $0.00... but maybe I am asking to much

    It is not to much to ask. Query 2 or 3 do this. Look at the Amount field. The NZ function is wonderful.
    Attached Files Attached Files

  14. #14
    caroled
    Guest

    Re: Look up Form (Access 97)

    Thank you for the zip which really helped a lot. We had looked at
    the -nz-function but tried using it in the criteria and not on the field
    name.

    I have succeeded in using it, as you showed, also made a form to choose the
    months.....

    Problem.... had a problem with your query3.....got error message
    concerning --MsJet35.dll that is probably because I am using Hebrew
    supported windows.(I always blame it when I have problems with DLL'S)

    Also when I make SumOf Amount with the -nz- function the special format that
    I had for the amount, that showed the sign for Israeli Shekels got lost. I
    tried pasting it in the format several times but it doesn't seem to want to
    register it. I just re-checked yours and the SumOfAmount also lost its $
    sign.
    I guess we can't have it both ways. I got around that by making an image of the sign and inserting.
    Liked the idea of viewing the query in datasheet form.

    Thanks again for taking the time and effort to send me the mini-database.

    Carol

  15. #15
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look up Form (Access 97)

    Your welcome.

    Also when I make SumOf Amount with the -nz- function the special format that
    I had for the amount, that showed the sign for Israeli Shekels got lost. I
    tried pasting it in the format several times but it doesn't seem to want to
    register it. I just re-checked yours and the SumOfAmount also lost its $
    sign.


    Change the format in the field properties to currency, or the format of the field in the form/report.

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
  •