summing in a form..

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

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200705/1

0
stephendeloach
5/8/2007 8:58:57 PM
access.forms 6864 articles. 2 followers. Follow

13 Replies
1036 Views

Similar Articles

[PageSpeed] 38

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

-- 
Message posted via http://www.accessmonster.com

0
stephendeloach
5/9/2007 5:54:55 PM
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


"stephendeloach via AccessMonster.com" wrote:

> 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
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> 
0
Utf
5/10/2007 8:05:00 AM
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



"stephendeloach via AccessMonster.com" wrote:

> 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
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> 
0
Utf
5/10/2007 8:08:01 AM
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

"Andy Hull" wrote:

> 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
> 
> 
> 
> "stephendeloach via AccessMonster.com" wrote:
> 
> > 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
> > 
> > -- 
> > Message posted via http://www.accessmonster.com
> > 
> > 
0
Utf
5/10/2007 8:46:00 AM
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? 


Andy Hull wrote:
>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

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200705/1

0
stephendeloach
5/11/2007 2:32:41 PM
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


"stephendeloach via AccessMonster.com" wrote:

> 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? 
> 
> 
> Andy Hull wrote:
> >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
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200705/1
> 
> 
0
Utf
5/11/2007 2:54:03 PM
Thanks for the fast reply... Were getting somewhere. Now I dont get the error
and the number comes up but it says "Circular Reference"??





Andy Hull wrote:
>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

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200705/1

0
stephendeloach
5/11/2007 3:19:16 PM
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


"stephendeloach via AccessMonster.com" wrote:

> Thanks for the fast reply... Were getting somewhere. Now I dont get the error
> and the number comes up but it says "Circular Reference"??
> 
> 
> 
> 
> 
> Andy Hull wrote:
> >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
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200705/1
> 
> 
0
Utf
5/11/2007 8:34:00 PM
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

Andy Hull wrote:
>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]
>> >> >> > >> over from the previous record (so i wouldnt have to manually enter it)?
>> >> >> > >> Thanks

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200705/1

0
stephendeloach
5/14/2007 2:10:56 PM
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

stephendeloach wrote:
>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

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200705/1

0
stephendeloach
5/14/2007 2:24:08 PM
Seems that one worked.. it will be coming form deloach@REMOVE_THIStswsinc.com

Andy Hull wrote:
>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]
>> >>> >> >> > >> over from the previous record (so i wouldnt have to manually enter it)?
>> >>> >> >> > >> Thanks

-- 
Message posted via http://www.accessmonster.com

0
stephendeloach
5/15/2007 2:18:23 PM
Thanks so much for the help!

stephendeloach wrote:
>Seems that one worked.. it will be coming form deloach@REMOVE_THIStswsinc.com
>
>>Hi Stephen
>>
>[quoted text clipped - 11 lines]
>>> >>> >> >> > >> over from the previous record (so i wouldnt have to manually enter it)?
>>> >>> >> >> > >> Thanks

-- 
Message posted via http://www.accessmonster.com

0
stephendeloach
5/15/2007 2:18:44 PM
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


"Andy Hull" wrote:

> 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
> 
> "Andy Hull" wrote:
> 
> > 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
> > 
> > 
> > 
> > "stephendeloach via AccessMonster.com" wrote:
> > 
> > > 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
> > > 
> > > -- 
> > > Message posted via http://www.accessmonster.com
> > > 
> > > 
0
Utf
8/1/2007 9:04:00 PM
Reply:

Similar Artilces:

Minimizing form
Hi How can I minimize the current form? Thanks Regards DoCmd.Minimize in a command button code or other event. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "John" <John@nospam.infovis.co.uk> wrote in message news:uBcRcE73HHA.3600@TK2MSFTNGP02.phx.gbl... > Hi > > > > How can I minimize the current form? > > > > Thanks > > > > Regards > > ...

Form Dropdown Combo Box
My dropdown combo box currently displays one column. What I would lik to do is have the box dispay column A and B but only extract the I from B. How would I be able to do this -- Message posted from http://www.ExcelForum.com I'm not sure if I understand the problem correctly, but have you trie using a CONCATENATE function to join the values in Columns A & B? Say you have first names in Column A and surnames in Column B. Us something like =CONCATENATE(A1," ",B1) in Column C for the dropdow list. You could also use =A1&" "&B1. The apostrophe marks are just...

How would I add "shipping charge" to an Invoice (form/subform) ?
Hi I have an "Invoice" form/subform for entering customer invoice data. I need to add a "shipping charge" field. Sounds easy but I have a problem with the concept. I enter a customer number in the main form and based on the relationships, all the customer data is automatically displayed. I then enter each line item of the invoice in the subform and calculate a running subtotal for the invoice. After I enter all of the invoice line items, I need to enter the shipping amount and capture it so I can calculate the total invoice amount later. I guess my question is: Where...

Form data entry
I have a form which employees enter data answering questions using a scoring system (0-3). The form has 5 sections. What i would for it to do is after they have complete sections 1 - 4 and begin to score section five is to limit their scoring entries base on the previous enteries. For example, section # 1 has a total of 6 questions, when all 6 questions are scored the section is then given an average score, lets say it is 2. When they begin to score section 5 i want this section be limited to 2 or less. Same as if the average is 1, then they would be limited to 1 or 0. Reason being, is...

writing XML from string makes not well formed xml due to too many spaces
Hi, I've got an asp.net page here that someone else wrote and obviously expected that it would work because it has been distributed with quite a large application. This page allows one to look up some various XML schemas by their names, not sure where these schemas are actually found at because I can't seem to find them anywhere other than through this application. The schemas are encoded as UTF-16, initially the page did not set any response encoding so it failed because of encoding mismatch. I added in the ability to set the encoding (and the charset) Response.ContentEncoding= En...

Summing multiple fields
I need to SUM multiple field values in a row. I know how to set it up in the Query design using a calculated field and I realize Excel does this. The reason I want to do it in Access because it seems to have more automation, whereas if I build the query, I can run it for the result, opposed to Excel where more steps might need to be done for the same result. Are there any query wizards in 2003 that automatically calculate multiple field values, or am I stuck by doing it long-hand by setting up the query design calculated field by adding each column/field heading to the field n...

To change font appearance in rich text as form is filled out?
I am creating a form to be filled out and I want what people type to be in a different font and bold. How do I do that? I can change the way "Click here to enter text" but not the text they enter. I guess from your description you are using Word 2007 content controls. That being the case you can set the style to format the content from the control's properties.. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site...

How do i create a Web form to collect demographic data?
...

Calculations in a form 08-15-07
I am making a database that is meant to calculate the commissions for our salesreps from several sources of income. I have a master form with subforms for the recording and calculation of sales and maintenance contract commissions, and would like to add the commission from rentals (in another table) and bonus and incentives (from another table and entered manually) to the sales and maintenance contract commissions; then I want to record that monthly value in a subform that will compare amounts to their goal for the month and accumulate the ytd data and the goal for that (they get bonus ...

Linked images (backgrounds for forms and buttons) don't display, A
System: Win7/64; MS Access/Office 2007; SQL Server 2005 on networked server with shared folders on same server. ADP Project. Project menus and forms contain linked images (< 30) as eye-candy. No problem for months of development. (Make minor change to arbitrary SQL view and add rich text box to menu form. Probably no relationship to problem). Compact, reload. All images no longer display. Able to create new form in project and load linked image. Able to re-browse and link image to button. All image paths are absolute paths. Previous version, absent minor changes l...

Query by Form-TJ
I need to do a Query by Form based on multiple selections from combo boxes on a form using Access 2000 I need to bring back the appropriate records in a subform. On Mon, 31 May 2010 07:08:01 -0700, ambushsinger <ambushsinger@discussions.microsoft.com> wrote: Query by Form can't do that. It can only select records in the current form view. After that, you can potentially open a new form with that subset of records in a subform. -Tom. Microsoft Access MVP >I need to do a Query by Form based on multiple selections from combo boxes on >a form using Access 200...

How to filter listbox data in a form?
A Form contains Filter dropdown for Year selection and a list box. How to filter listbox data based on selected year. Any helps or thoughts could be appreciated. Advnaced Thanks ~Vins Check your other post. Vinod wrote: > > A Form contains Filter dropdown for Year selection and a list box. How to > filter listbox data based on selected year. > > Any helps or thoughts could be appreciated. > > Advnaced Thanks > ~Vins -- Dave Peterson ...

form base authentication #2
This is killing me. I'm implementing form base authentication. I have SSL and everything working but for one problem when use won't to get to owa they type mail.domain.com which then defaults to http://mail.domain.com. the problem I'm having is that FBA need it to default to https not http. How can I get it to default to HTTPS with out telling the uses to type https? I am implementing this on a FE/BE environment wayne .. What I do is change the http port on the main site to something like 8080, and then create a new site that listens on 80. For that site, instead of...

Print Forms
Does anyone know if there is a way to modify the print layouts or forms in CRM 3.0? For example, when we go on site we like to take a copy of the Case with us for customer approval, I';d liek to add our Logo and a signature line to the Case Print Form. The easiest (and supported) way to do this is to create a custom report that is launched via an ISV button from the case form. There are a lot of good posts on how to do this on this newsgroup, and the SDK will give you insight into creating custom reports. -- Matt Wittemann http://icu-mscrm.blogspot.com "Jclark" wrote...

Search Sub Form Help :)
Hi there, I know this question has probably been asked a million times, but for a n00b it's kinda hard to find the time at work to search similar threads, so apologies in advance! :) I take my issue is quite basic, so here's the jist: I currently have: 1 Table (data), 1 Add Form (fields are bound to the table), 1 Search Form (4 unbound fields) where by populating the fields with similar data captured in the existing table and clicking the "Search" cmd button, a query is activated and a new table pops up with the search results. I require: a SubForm in th...

form fields not saving to table
I have created a database with a form and three of the fields are not saving the information to the table. I have checked the row and control sources and now at a loss and thoughts? As you scroll through existing records is the data of the table displayed? Post the SQL of the query or form source. -- Build a little, test a little. "Kim" wrote: > I have created a database with a form and three of the fields are not saving > the information to the table. I have checked the row and control sources and > now at a loss and thoughts? > > ...

Word, Form Fields, and the Enter Key
I've posted once on this subject, with no reply. I thought I'd try again in a different format. I'm using the code below to act like the Tab Key to go between Form Fields. The problem is that when using the Enter Key, the Form Fields are not Formatted, as they are with the Tab Key. Please, someone copy this code into a blank worksheet and try it yourself, format some Form Fields, and tell me why the code is not formatting the Form Fields. By the way, after using the Enter Key, if I go back to the previous Form Field by using the left arrow or mouse, for example and...

save form
Access 2k. I haven't had this happen in quite a while but it happened just today and I don't know why. Occasionally when I edit code in a form module and then click save, the code window caption bar flickers for a few minutes as if it's running and saving, running and saving or something over and over and over. I've even closed Access down and is still does it. Nothing is running in Access. Just have the form and its code open in design. This seems to occur on a particular form but I cannot see any reason for this. Anyone else experienced this and know of a solution? It's ...

addition on a form
i need to add totals in text boxes to give me one total for all on the form Carl, Umm, not alot to go on... In the Form Footer put a Text Box with... =[Field]+[Field]+[Field] -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Carl" <Carl@discussions.microsoft.com> wrote in message news:851E5E98-63B0-4145-953D-B34E21EB097D@microsoft.com... i need to add totals in text boxes to give me one total for all on the form O...

Using Input Form
I am using an Input Form to create a large number of similar records. Even though each key field is unique, the bulk of the fields in each database record are duplicate. Is there a simple way to "duplicate the previous record" using an Input Form in Access? I hope this makes sense. George L. Are you using a custom Add Record command button, or Access' native New Record buutton? -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000 Message posted via http://www.accessmonster.com A simple solution might be to assign the default valu...

Using text and formatting sum function in formula
I am attempting to show text in a cell concatenated with the sum function and it works fine. The problem is the result has 10 decimal places in the resulting number. I have tried to use the format function with the sum function without success. Here is the formula I am working with: ="Total is: " & Format(SUM(B96:B104),"0.00") What am I doing wrong? Thank you. Try it like this... ="Total is: "&ROUND(SUM(B96:B104),2) -- Biff Microsoft Excel MVP "Billy B" <BillyB@discussions.microsoft.com> wrote in message news:...

Counting & Summing based on criteria on another column
I have a spreadsheet of estimate date that is incomplete (some cells have data, some do not yet). I need to be able to count the number of estimates (col A in the simple example below) that are for completed phases (col C)...in other words, the date in col C is in the past. I'll also need to be able sum col A for all completed phases (but this can be done in another cell). In the example below, I would expect the estimate count to be 2 and the estimate sum to be 4500. A B C 1 Estimate Actual ...

locate a column, sum specific columns to the right of that column
Tricky one here (I think): I have a drop down list of Labor Periods on a cover sheet. On a separate data sheet I have: Period 1 Period 2 ... Units Cost Hours Units Costs Hours The oldest Labor Period is the farthest to the right. I need a formula that will find the Labor Period selected from a drop down (DV) and sum the values to the right of that Labor Period. So if Labor Period 5 is selected, I want to add up the hours for Labor Periods 5,4,3,2,1. Any help would be lovely. Beric I think we need to be told about how you data is s...

CRM Custom aspx Form
I figured out how to pass the GUID ID to custom aspx pages. Now, I have a different problem that how I can make the Save and Save As button to call a function from other aspx forms? So when the user save the form, it will not only save the fields from the MS CRM (lets say) Opportunity form, but also it will save the fields from the side tab form (custom aspx page) fields. Thanks, Zaw your addin needs to save its own data. The built in save functions will not be aware of your application and therefore will not save your data as well. -- John O'Donnell Microsoft CRM MVP http://www.micro...

Change an underlying query paramaters by button on a form
Hi, I have a form that is bound to a query. The form displays a list of servers and a count of errors that have occured on each server. I also have two buttons on my form, one which is labelled 'All' and the other is labelled 'Errors Only'. I have modified the query (via the QBE grid) to allow for both situations, and copied the SQL as follows: When I click the 'All' button, I would like the following SQL to run: SELECT Full_Compare_Q.BTSContactGroup, Full_Compare_Q.Server, Full_Compare_Q.RecID, Abs(Sum(Services.Valid)) AS ErrorCount, IIf([Ping...