summing in a form..

[stephendeloach via AccessMonster.com]

In my form i have Beginning Mileage, Ending Mileage, Total Daily Mileage. As
of now I have to enter Beginning Mileage, my Ending Mileage is =Nz([AR],0)+Nz
([LA],0)+Nz([TX],0)+Nz([MS],0)+Nz([OK],0)+Nz([Other])+[Beginning Mileage].
my Total Daily Mileage is =Nz([AR],0)+Nz([LA],0)+Nz([TX],0)+Nz([MS],0)+Nz([OK]
,0)+Nz([Other]). Is there a way that i can make my Beginning Mileage carry
over from the previous record (so i wouldnt have to manually enter it)?
Thanks

 

[Guest]

Hi

Something along the following lines should work...

=DLOOKUP("EndingMileage","YourTable","DateField = #" &
format(DMAX("DateField","YourTable"),'mm/dd/yyyy') & "#")

Note: This takes the end mileage from the entry with the maximum date so if
an entry exists with todays date already you may need to tweak the above to
not select today. Also, if there are a number of people entering mileage the
above will need to be amended to pick the person currently entering their
mileage.

Post back with more info about your design if needed.

Regards

Andy Hull

 

[stephendeloach via AccessMonster.com]

Thanks for the reply.
This is what i have in the Control Source of the Ending Mileage field....
=DLookUp("EndingMileage","Daily Log","Date = #" & Format(DMax("Date","Daily
Log"),'mm/dd/yyyy') & "#")

It says #ERROR in the Ending Mileage and Beginning Mileage fields now..? Did
I not type it in correctly? There will be only one person entering infomation
in this database. Thanks

Hi

Something along the following lines should work...

=DLOOKUP("EndingMileage","YourTable","DateField = #" &
format(DMAX("DateField","YourTable"),'mm/dd/yyyy') & "#")

Note: This takes the end mileage from the entry with the maximum date so if
an entry exists with todays date already you may need to tweak the above to
not select today. Also, if there are a number of people entering mileage the
above will need to be amended to pick the person currently entering their
mileage.

Post back with more info about your design if needed.

Regards

Andy Hull

In my form i have Beginning Mileage, Ending Mileage, Total Daily Mileage. As
of now I have to enter Beginning Mileage, my Ending Mileage is =Nz([AR],0)+Nz

[quoted text clipped - 3 lines]

over from the previous record (so i wouldnt have to manually enter it)?
Thanks

 

[Guest]

Hi Stephen

The expression given is to calculate today's beginning mileage so should be
in the control of the Beginning Mileage Field.

However, it may be better to have it as vba code that runs once only when
the form opens because as soon as the new record is saved the expression will
change.

As regards the error, I will check the syntax on my system and get back to
you.

Andy Hull

Thanks for the reply.
This is what i have in the Control Source of the Ending Mileage field....
=DLookUp("EndingMileage","Daily Log","Date = #" & Format(DMax("Date","Daily
Log"),'mm/dd/yyyy') & "#")

It says #ERROR in the Ending Mileage and Beginning Mileage fields now..? Did
I not type it in correctly? There will be only one person entering infomation
in this database. Thanks

Hi

Something along the following lines should work...

=DLOOKUP("EndingMileage","YourTable","DateField = #" &
format(DMAX("DateField","YourTable"),'mm/dd/yyyy') & "#")

Note: This takes the end mileage from the entry with the maximum date so if
an entry exists with todays date already you may need to tweak the above to
not select today. Also, if there are a number of people entering mileage the
above will need to be amended to pick the person currently entering their
mileage.

Post back with more info about your design if needed.

Regards

Andy Hull

In my form i have Beginning Mileage, Ending Mileage, Total Daily Mileage. As
of now I have to enter Beginning Mileage, my Ending Mileage is =Nz([AR],0)+Nz

[quoted text clipped - 3 lines]

over from the previous record (so i wouldnt have to manually enter it)?
Thanks

 

[Guest]

Hi again

Forgot to say that the EndingMileage referred to in the expression is meant
to be a field in your table [Daily Log] (that holds the past ending mileages).

So, if necessary, alter the expression to match the actual field name.

Regards

Andy

Thanks for the reply.
This is what i have in the Control Source of the Ending Mileage field....
=DLookUp("EndingMileage","Daily Log","Date = #" & Format(DMax("Date","Daily
Log"),'mm/dd/yyyy') & "#")

It says #ERROR in the Ending Mileage and Beginning Mileage fields now..? Did
I not type it in correctly? There will be only one person entering infomation
in this database. Thanks

Hi

Something along the following lines should work...

=DLOOKUP("EndingMileage","YourTable","DateField = #" &
format(DMAX("DateField","YourTable"),'mm/dd/yyyy') & "#")

Note: This takes the end mileage from the entry with the maximum date so if
an entry exists with todays date already you may need to tweak the above to
not select today. Also, if there are a number of people entering mileage the
above will need to be amended to pick the person currently entering their
mileage.

Post back with more info about your design if needed.

Regards

Andy Hull

In my form i have Beginning Mileage, Ending Mileage, Total Daily Mileage. As
of now I have to enter Beginning Mileage, my Ending Mileage is =Nz([AR],0)+Nz

[quoted text clipped - 3 lines]

over from the previous record (so i wouldnt have to manually enter it)?
Thanks

 

[Guest]

Hi yet again

I have tested the expression and it works ok.

So, as per my previous posts, put the expression in the beginning mileage
box of your form and check the expression matches your field names.

Also, there must be at least one entry in your table else it will say error.

You can get around that by making the expression count the records so it
knows it can find a maximum but it is worth getting the simpler expression
working first.

When you can see it work & see how it works you will be able to experiment
with it to make it more specific.

Regards

Andy Hull

Hi again

Forgot to say that the EndingMileage referred to in the expression is meant
to be a field in your table [Daily Log] (that holds the past ending mileages).

So, if necessary, alter the expression to match the actual field name.

Regards

Andy

Thanks for the reply.
This is what i have in the Control Source of the Ending Mileage field....
=DLookUp("EndingMileage","Daily Log","Date = #" & Format(DMax("Date","Daily
Log"),'mm/dd/yyyy') & "#")

It says #ERROR in the Ending Mileage and Beginning Mileage fields now..? Did
I not type it in correctly? There will be only one person entering infomation
in this database. Thanks

Hi

Something along the following lines should work...

=DLOOKUP("EndingMileage","YourTable","DateField = #" &
format(DMAX("DateField","YourTable"),'mm/dd/yyyy') & "#")

Note: This takes the end mileage from the entry with the maximum date so if
an entry exists with todays date already you may need to tweak the above to
not select today. Also, if there are a number of people entering mileage the
above will need to be amended to pick the person currently entering their
mileage.

Post back with more info about your design if needed.

Regards

Andy Hull

In my form i have Beginning Mileage, Ending Mileage, Total Daily Mileage. As
of now I have to enter Beginning Mileage, my Ending Mileage is =Nz([AR],0)+Nz
[quoted text clipped - 3 lines]
over from the previous record (so i wouldnt have to manually enter it)?
Thanks

 

[stephendeloach via AccessMonster.com]

Still #ERROR...

This is what is in my "Ending Mileage" control source... =Nz([AR],0)+Nz([LA],
0)+Nz([TX],0)+Nz([MS],0)+Nz([OK],0)+Nz([Other])+[Beginning Mileage]

"Beginning Mileage" control source... =DLookUp("Ending Mileage","Daily Log",
"Date = #" & Format(DMax("Date","Daily Log"),'mm/dd/yyyy') & "#")

"Total Daily Mileage" control source... =Nz([AR],0)+Nz([LA],0)+Nz([TX],0)+Nz
([MS],0)+Nz([OK],0)+Nz([Other])

The values in the records are not stored into my Daily Log table for "Ending
Mileage" and "Total Daily Mileage" could that be the problem?

Hi yet again

I have tested the expression and it works ok.

So, as per my previous posts, put the expression in the beginning mileage
box of your form and check the expression matches your field names.

Also, there must be at least one entry in your table else it will say error.

You can get around that by making the expression count the records so it
knows it can find a maximum but it is worth getting the simpler expression
working first.

When you can see it work & see how it works you will be able to experiment
with it to make it more specific.

Regards

Andy Hull

Hi again

[quoted text clipped - 40 lines]

 

[Guest]

Hi Stephen

Yes. The fact that [Ending Mileage] isn't in the [Daily Log] table is
exactly the problem. Where can we get the last "Ending Mileage" from?

Do you store the last beginning mileage? and all the other mileages?

If so, then set the "Beginning Mileage" control source to...

=dlookup("Nz([Beginning
Mileage],0)+Nz([AR],0)+Nz([LA],0)+Nz([TX],0)+Nz([MS],0)+Nz([OK],0)+Nz([Other],0)","[Daily
Log]","Date = #" & Format(DMax("Date","Daily Log"),'mm/dd/yyyy') & "#")

Making sure the above uses the correct field names in the table.

Regards

Andy Hull

Still #ERROR...

This is what is in my "Ending Mileage" control source... =Nz([AR],0)+Nz([LA],
0)+Nz([TX],0)+Nz([MS],0)+Nz([OK],0)+Nz([Other])+[Beginning Mileage]

"Beginning Mileage" control source... =DLookUp("Ending Mileage","Daily Log",
"Date = #" & Format(DMax("Date","Daily Log"),'mm/dd/yyyy') & "#")

"Total Daily Mileage" control source... =Nz([AR],0)+Nz([LA],0)+Nz([TX],0)+Nz
([MS],0)+Nz([OK],0)+Nz([Other])

The values in the records are not stored into my Daily Log table for "Ending
Mileage" and "Total Daily Mileage" could that be the problem?

Hi yet again

I have tested the expression and it works ok.

So, as per my previous posts, put the expression in the beginning mileage
box of your form and check the expression matches your field names.

Also, there must be at least one entry in your table else it will say error.

You can get around that by making the expression count the records so it
knows it can find a maximum but it is worth getting the simpler expression
working first.

When you can see it work & see how it works you will be able to experiment
with it to make it more specific.

Regards

Andy Hull

Hi again

[quoted text clipped - 40 lines]

over from the previous record (so i wouldnt have to manually enter it)?
Thanks

 

[stephendeloach via AccessMonster.com]

Thanks for the fast reply... Were getting somewhere. Now I dont get the error
and the number comes up but it says "Circular Reference"??

Hi Stephen

Yes. The fact that [Ending Mileage] isn't in the [Daily Log] table is
exactly the problem. Where can we get the last "Ending Mileage" from?

Do you store the last beginning mileage? and all the other mileages?

If so, then set the "Beginning Mileage" control source to...

=dlookup("Nz([Beginning
Mileage],0)+Nz([AR],0)+Nz([LA],0)+Nz([TX],0)+Nz([MS],0)+Nz([OK],0)+Nz([Other],0)","[Daily
Log]","Date = #" & Format(DMax("Date","Daily Log"),'mm/dd/yyyy') & "#")

Making sure the above uses the correct field names in the table.

Regards

Andy Hull

Still #ERROR...

[quoted text clipped - 35 lines]

 

[Guest]

Glad we're getting somewhere!

The circular refereance means that we have something like...
Item A depends on Item B which itself depends on Item A

Let's Recap the controls...

Beginning Mileage
=dlookup("Nz([Beginning
Mileage],0)+Nz([AR],0)+Nz([LA],0)+Nz([TX],0)+Nz([MS],0)+Nz([OK],0)+Nz([Other],0)","[Daily
Log]","Date = #" & Format(DMax("Date","Daily Log"),'mm/dd/yyyy') & "#")

You also have controls for the individual mileages (AR, LA etc) which should
have NO control source - they aren't dependent on anything and are for the
user to enter freely.

Then you have Total Daily Mileage...
=Nz([AR],0)+Nz([LA],0)+Nz([TX],0)+Nz([MS],0)+Nz([OK],0)+Nz([Other],0)

And Ending Mileage, which should be...
=Nz([AR],0)+Nz([LA],0)+Nz([TX],0)+Nz([MS],0)+Nz([OK],0)+Nz([Other],0)+Nz([Beginning Mileage],0)

And the controls with a source get their data from...
Beginning Mileage gets data from the TABLE
Total Daily Mileage gets its data from the FORM
Ending Mileage gets its data from the FORM

If all the above checks out and there is still a problem...
Is your form using the table [Daily Log] as its source?
It could be that our calculated beginning mileage is using itself in the
calculation so we will need to restrict it to the max(date) before itself. So
we need to make the "Beginning Mileage" control yet more complicated...

=dlookup("Nz([Beginning
Mileage],0)+Nz([AR],0)+Nz([LA],0)+Nz([TX],0)+Nz([MS],0)+Nz([OK],0)+Nz([Other],0)","[Daily
Log]","Date = #" & Format(DMax("Date","Daily Log","Date < #" &
Format(Me.Date,'mm/dd/yyyy') & "#"),'mm/dd/yyyy') & "#")

Hope this gets you another step forward - it's tricky from here because
there are always several ways of doing the same thing & I don't know your
particular design.

If you still have problems you could email me the database with your
questions on the understanding I can't guarantee my response times! If you do
this, save the db as Access 2003 or earlier.

My email address is andyhull_w3b@a1REMOVE_THIS_BITsites.co.uk
(Obviously, take out the REMOVE_THIS_BIT if you do email me)

Regards

Andy Hull

Thanks for the fast reply... Were getting somewhere. Now I dont get the error
and the number comes up but it says "Circular Reference"??

Hi Stephen

Yes. The fact that [Ending Mileage] isn't in the [Daily Log] table is
exactly the problem. Where can we get the last "Ending Mileage" from?

Do you store the last beginning mileage? and all the other mileages?

If so, then set the "Beginning Mileage" control source to...

=dlookup("Nz([Beginning
Mileage],0)+Nz([AR],0)+Nz([LA],0)+Nz([TX],0)+Nz([MS],0)+Nz([OK],0)+Nz([Other],0)","[Daily
Log]","Date = #" & Format(DMax("Date","Daily Log"),'mm/dd/yyyy') & "#")

Making sure the above uses the correct field names in the table.

Regards

Andy Hull

Still #ERROR...

[quoted text clipped - 35 lines]

over from the previous record (so i wouldnt have to manually enter it)?
Thanks

 

[stephendeloach via AccessMonster.com]

I think everything checked out. I put the formulas in and now I got an #ERROR.
.. anyother suggestions? I will send it to your email and see what you think.
Thanks so much.

Stephen

Glad we're getting somewhere!

The circular refereance means that we have something like...
Item A depends on Item B which itself depends on Item A

Let's Recap the controls...

Beginning Mileage
=dlookup("Nz([Beginning
Mileage],0)+Nz([AR],0)+Nz([LA],0)+Nz([TX],0)+Nz([MS],0)+Nz([OK],0)+Nz([Other],0)","[Daily
Log]","Date = #" & Format(DMax("Date","Daily Log"),'mm/dd/yyyy') & "#")

You also have controls for the individual mileages (AR, LA etc) which should
have NO control source - they aren't dependent on anything and are for the
user to enter freely.

Then you have Total Daily Mileage...
=Nz([AR],0)+Nz([LA],0)+Nz([TX],0)+Nz([MS],0)+Nz([OK],0)+Nz([Other],0)

And Ending Mileage, which should be...
=Nz([AR],0)+Nz([LA],0)+Nz([TX],0)+Nz([MS],0)+Nz([OK],0)+Nz([Other],0)+Nz([Beginning Mileage],0)

And the controls with a source get their data from...
Beginning Mileage gets data from the TABLE
Total Daily Mileage gets its data from the FORM
Ending Mileage gets its data from the FORM

If all the above checks out and there is still a problem...
Is your form using the table [Daily Log] as its source?
It could be that our calculated beginning mileage is using itself in the
calculation so we will need to restrict it to the max(date) before itself. So
we need to make the "Beginning Mileage" control yet more complicated...

=dlookup("Nz([Beginning
Mileage],0)+Nz([AR],0)+Nz([LA],0)+Nz([TX],0)+Nz([MS],0)+Nz([OK],0)+Nz([Other],0)","[Daily
Log]","Date = #" & Format(DMax("Date","Daily Log","Date < #" &
Format(Me.Date,'mm/dd/yyyy') & "#"),'mm/dd/yyyy') & "#")

Hope this gets you another step forward - it's tricky from here because
there are always several ways of doing the same thing & I don't know your
particular design.

If you still have problems you could email me the database with your
questions on the understanding I can't guarantee my response times! If you do
this, save the db as Access 2003 or earlier.

My email address is andyhull_w3b@a1REMOVE_THIS_BITsites.co.uk
(Obviously, take out the REMOVE_THIS_BIT if you do email me)

Regards

Andy Hull

Thanks for the fast reply... Were getting somewhere. Now I dont get the error
and the number comes up but it says "Circular Reference"??

[quoted text clipped - 23 lines]

 

[stephendeloach via AccessMonster.com]

Well it says that your email isnt valid? Anyways... The record source on the
subform is SELECT [Daily Log].Date, [Daily Log].[Truck #], [Daily Log].[Vin #]
, [Daily Log].Driver, [Daily Log].[Ending Mileage], [Daily Log].[Beginning
Mileage], [Daily Log].[Total Daily Mileage], [Daily Log].AR, [Daily Log].LA,
[Daily Log].TX, [Daily Log].MS, [Daily Log].OK, [Daily Log].Other, [Daily Log]
[Truck Type] FROM [Daily Log]; a query... does that make any
difference? (i think that is a stupid question...!)
Now what?!! Any suggestions? Thanks

I think everything checked out. I put the formulas in and now I got an #ERROR.
.. anyother suggestions? I will send it to your email and see what you think.
Thanks so much.

Stephen

Glad we're getting somewhere!

[quoted text clipped - 54 lines]

 

[Guest]

Hi Stephen

Don't know why the email isn't working. Try this one instead...

andrew.hull@REMOVE_THIScsplc.com

Regards

Andy Hull

Well it says that your email isnt valid? Anyways... The record source on the
subform is SELECT [Daily Log].Date, [Daily Log].[Truck #], [Daily Log].[Vin #]
, [Daily Log].Driver, [Daily Log].[Ending Mileage], [Daily Log].[Beginning
Mileage], [Daily Log].[Total Daily Mileage], [Daily Log].AR, [Daily Log].LA,
[Daily Log].TX, [Daily Log].MS, [Daily Log].OK, [Daily Log].Other, [Daily Log]
.[Truck Type] FROM [Daily Log]; a query... does that make any
difference? (i think that is a stupid question...!)
Now what?!! Any suggestions? Thanks

I think everything checked out. I put the formulas in and now I got an #ERROR.
.. anyother suggestions? I will send it to your email and see what you think.
Thanks so much.

Stephen

Glad we're getting somewhere!

[quoted text clipped - 54 lines]

over from the previous record (so i wouldnt have to manually enter it)?
Thanks

 

[stephendeloach via AccessMonster.com]

Seems that one worked.. it will be coming form deloach@REMOVE_THIStswsinc.com

Hi Stephen

Don't know why the email isn't working. Try this one instead...

andrew.hull@REMOVE_THIScsplc.com

Regards

Andy Hull

Well it says that your email isnt valid? Anyways... The record source on the
subform is SELECT [Daily Log].Date, [Daily Log].[Truck #], [Daily Log].[Vin #]

[quoted text clipped - 16 lines]

 

[stephendeloach via AccessMonster.com]

Thanks so much for the help!

Seems that one worked.. it will be coming form deloach@REMOVE_THIStswsinc.com

Hi Stephen

[quoted text clipped - 11 lines]

 

[Guest]

Andy,

Hope it's okay to enter this thread, I still am not quite sure what the
etiquette is about asking questions for my problem during another person's
post. So, if I have committed a breach, please tell me.

I edited your formula above to
=DLookUp("LCENDBAL","tbl_LoganCountyBank","Datefield= # " &
Format(DMax("Datefield","tbl_LoganCountyBank"),'mm/dd/yyyy') & "#")
I've two questions, 1)must my form be based on my table; my ending balance
is calculated in a query so that is what I used for the form and 2)I'm going
back in time to complete the information required so is the above code
written for today's date; if so, can it be edited to enter the last date in
the past? (For example, I must go back to October of 2006 and bring the
ending balance up to date)

Hope you can help me out with this; Thanks in advance. k

Hi yet again

I have tested the expression and it works ok.

So, as per my previous posts, put the expression in the beginning mileage
box of your form and check the expression matches your field names.

Also, there must be at least one entry in your table else it will say error.

You can get around that by making the expression count the records so it
knows it can find a maximum but it is worth getting the simpler expression
working first.

When you can see it work & see how it works you will be able to experiment
with it to make it more specific.

Regards

Andy Hull

Hi again

Forgot to say that the EndingMileage referred to in the expression is meant
to be a field in your table [Daily Log] (that holds the past ending mileages).

So, if necessary, alter the expression to match the actual field name.

Regards

Andy

Thanks for the reply.
This is what i have in the Control Source of the Ending Mileage field....
=DLookUp("EndingMileage","Daily Log","Date = #" & Format(DMax("Date","Daily
Log"),'mm/dd/yyyy') & "#")

It says #ERROR in the Ending Mileage and Beginning Mileage fields now..? Did
I not type it in correctly? There will be only one person entering infomation
in this database. Thanks

Andy Hull wrote:
Hi

Something along the following lines should work...

=DLOOKUP("EndingMileage","YourTable","DateField = #" &
format(DMAX("DateField","YourTable"),'mm/dd/yyyy') & "#")

Note: This takes the end mileage from the entry with the maximum date so if
an entry exists with todays date already you may need to tweak the above to
not select today. Also, if there are a number of people entering mileage the
above will need to be amended to pick the person currently entering their
mileage.

Post back with more info about your design if needed.

Regards

Andy Hull

In my form i have Beginning Mileage, Ending Mileage, Total Daily Mileage. As
of now I have to enter Beginning Mileage, my Ending Mileage is =Nz([AR],0)+Nz
[quoted text clipped - 3 lines]
over from the previous record (so i wouldnt have to manually enter it)?
Thanks