Look up Form (Access 97)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

>>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?

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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..

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

try using
NZ[field name]

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Thanks but it still doesn’t change…. the format window on the form/report comes up empty…. if I paste the format it goes in but… doesn’t change it. Also tried writing –currency— also didn’t work.

Tried it on your database…. the window let me choose –currency— but didn’t change it.

Thanks
Carol

Reply | Quote

what about using

Amount: Format(nz([SumOfAmount],0),”””A$””0.00″)

where A$ is for Aussie Dollars. I’m sorry I don’t know what the symbol is for Israeli Shekels.

Reply | Quote

hi,
> Amount: Format(nz([SumOfAmount],0),”””A$””0.00″)>
> where A$ is for Aussie Dollars. I’m sorry I don’t know what the symbol is
for Israeli Shekels.

Nothing seems to work. I even tried pasting the shekel format on your
expression..no dice.
I got the shekel expression by using that option on the regional settings.
When I try to use the special character it comes out a question mark….
attached shekel image.

On the help file they write:
7 If the character looks different from the one you selected, select
the character and apply the same font you selected in Character Map. I
have no idea what this means…..

best I could do was
> Amount: Format(nz([SumOfAmount],0),”””NS-“”0.00”) NS=new shekel.

I doubt if you would find this character on any of your special character
charts…. I found it in all the Hebrew charts….

Thanks
Carol

Reply | Quote

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.

Reply | Quote

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

Reply | Quote