 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / August 2007summing 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 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 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 > [quoted text clipped - 20 lines] >> over from the previous record (so i wouldnt have to manually enter it)? >> Thanks 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.... [quoted text clipped - 29 lines] > >> over from the previous record (so i wouldnt have to manually enter it)? > >> Thanks 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.... [quoted text clipped - 29 lines] > >> over from the previous record (so i wouldnt have to manually enter it)? > >> Thanks 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 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 > [quoted text clipped - 21 lines] >> > >> over from the previous record (so i wouldnt have to manually enter it)? >> > >> Thanks 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 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 > [quoted text clipped - 20 lines] >> >> > >> over from the previous record (so i wouldnt have to manually enter it)? >> >> > >> Thanks 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 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 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. [quoted text clipped - 7 lines] >>> >> >> > >> over from the previous record (so i wouldnt have to manually enter it)? >>> >> >> > >> Thanks 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 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 Thanks so much for the help! >Seems that one worked.. it will be coming form deloach@REMOVE_THIStswsinc.com > [quoted text clipped - 3 lines] >>> >>> >> >> > >> over from the previous record (so i wouldnt have to manually enter it)? >>> >>> >> >> > >> Thanks 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 > [quoted text clipped - 60 lines] > > > >> over from the previous record (so i wouldnt have to manually enter it)? > > > >> Thanks |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.