ACCESS help required please

hello, it's only me

It's been about 3 years since I last did some access developing.

On an access report it's easy to group records together based on a field
and then total up each group. So group on the date field and then total
a numeric field.

However I would like to do this on a form. As simple as possible please
(although I do know some VBA).

I had hoped the form had same grouping capabilities but it appears not.

Any suggestions (I'll leave that open cos you'll make you comments
anyway, - otherwise this wouldn't be UKRM would it)

Thanks

 

You might be able to force Access to do this, but I wouldn't recommend
it[1]. It might be better to make your form look at one group at a
time, rather than all of them at once. Create a recordsource for your
form (query, SQL) that gets all the items in all the groups, then use a
date picker control for your filter.

It might be best to use the me.filter property of the form rather than
resetting the recordsource each time you pick a new date, but this is
up to you. If you use me.filter, don't forget to set me.FilterOn =
true to activate it.

This way you can bring in your fields (e.g. [myID], [myDate],
[myValue]), then set the datasource for a textbox to be nz([myValue],
=SUM([myValue]), 0) - the nz should prevent you getting a #Err! or
similar if something ends up null.

If you want anything else, email me.

[1] IME, trying to do anything in Access that involves using it not
quite as intended seems to make things very complex, very quickly.

 

This was/is my alternative. although a slightly different method (looks
simpler). I was simply going to have a dropdown list of all available
dates (and add on the current date as well) then use some code to select
the group based on that.

nz is something I have not used before (or at least cant remember yet)


Thanks for that I will give it a go.
Nice to talk to you again.

Oh yes I know this only too well....

 

Hi,

Not much different from the last time I appeared.

Bought a couple of new toys to keep me amused in the house (iDTV and iD
dvd/HDD recorder). Saved for a year to buy them.

I've just had the 748 serviced (got a good offer of a service at 1/3 of
the proper price). So no more cam belt worries.

MOT/Insurance is next.

Oh and I've still got to find some work as well.

 

That's what I'd do if it wasn't a date. As it was I thought I'd be
"creative".

It can be quite handy if things aren't coping with nulls, but if
something shouldn't be null it might be best to know about it. I think
the syntax is: nz(<expression to test for null>, <value to return if
not null>,<value to return if null>)

 

...
[Get a list and a sum of a column onto a form]

Good advice, yet, one comment: You want a list of records, and a date/time
picker to choose a group and a text field for the sum. That cannot be placed
together on one form.

I mean if you make the form to an endless-form to list all entries, you
cannot have to sum and date filter on it (or it would appear once for every
entry). So, you need to make a "regular" form containing the controls (date
and sum) and embed either a list control or ActiveX ListView or another form
as subform (set to endless) on it to display the detail lines.

 

In the past I've done what I suggested by putting the totals in
textboxes in the form footer, and the combobox (or date picker) in the
form header. This removes the need for a subform.

 

If you put it all in the detail section then yes it would be a problem.

Put the date picker field in the header and the sum field on the footer
and it works fine.

A problem I have now is if you pick a date that there are no matching
records then the combobox (date picker) does not even display the date
you are trying show records for.

This WILL occur when the date you put in there is the current date
(today's) and there are no records. All other dates will of course be
taken from the records so will exist.

So how do I get it to display the current date in the combobox even if
there are no records?

I vaguely remember this happening on another db I was working on but
cant remember how I solved it or got round it.

 

If you put it all in the detail section then yes it would be a problem.

Put the date picker field in the header and the sum field on the footer
and it works fine.

A problem I have now is if you pick a date that there are no matching
records then the combobox (date picker) does not even display the date
you are trying show records for.

This WILL occur when the date you put in there is the current date
(today's) and there are no records. All other dates will of course be
taken from the records so will exist.

So how do I get it to display the current date in the combobox even if
there are no records?

I vaguely remember this happening on another db I was working on but
cant remember how I solved it or got round it.