Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
Access Q&A: Left, Running Total, Register
By Richard Rost   Richard Rost on LinkedIn Email Richard Rost   17 years ago

More Access questions I've answered recently:

I can't remember what to type in to get the last three numbers from one field containing 9 numbers. I thought you typed X:(Right,6,[file name]), but when I do this it acts as the right is a field name as well. What do I need to do??

Try this:

NewField: Right(FieldName,3)

I cover Left(), Right(), and Mid() in my Access 309 class:

copying data a field in one record to a different field in the next record: In my database I have a form Named account info. I have a subform for transactions. (Each transaction is a record) In the subform I would like a running balance. How do I copy the data in a field called new balance to a field (balance) in the next record.

Well, running balances are so much easier in reports than in forms. Do you really need to see a running balance on each line of your subform, or is a single SUM at the bottom of the subform OK? If you can deal with just a sum at the bottom, just put an unbound text box in the footer of your subform with the control source of =SUM([FieldName]) in it. I cover how to do this in my Access 301 class (

Now, forms don't behave the same way that reports do. It's easy to create a running total in a report. But you'd need some pretty complex coding to get a form to behave that way. Now, to do what you want to do - to simply copy a value from one record to the next, wouldn't be that hard.

What I would do would be to use DMAX and DLOOKUP and get the value from the previous record. Say you're dealing with an order table, and you want to get the LineItemTotal from the last record (which would be the record with the greatest OrderDetailID less than the current record's OrderDetailID)... you would say:

LastOrderID = DMAX("OrderDetailID","OrderDetailT","OrderDetailID < " & OrderDetailID)
LineItemTotal = DLOOKUP("LineItemTotal","OrderDetailT","OrderDetailID=" & LastOrderID)

In English, this basically says, "look up the largest OrderDetailID from the OrderDetail table where the OrderDetailID is less than the ID from the current record, and put that in a variable called LastOrderID." That will get you the ID for the previous record.

Then, "look up the LineItemTotal from the OrderDetail table where the ID equals the ID we just got from DMAX. Put that value in the LineItemTotal for THIS field."

Now, put that code in an event for your new record - probably in the BeforeInsert field (but you might need to play with it to figure out which event best runs it) and that should do it.

I cover DLOOKUP in detail in my Access 302 class ( DMAX is covered in Access 306 (

It's not easy - which is why I suggest just use a TOTAL on the bottom of the form and save the running total for the report.

Hi Richard. I would like to produce a trial version of an Access application that I'm currently developing and after a period of time, say two weeks, I want the program to require the user to input an "access key" once only in order for him/her to continue use. Whilst I'm fairly competent with Access and can produce professional looking applicaitons, my eperience of using code is very limited, so any help that you can provide would be greatly appreciated. Many thanks for your time

There are lots of different ways you could do this - depending on how secure it needs to be, and how tech-savvy your end users are. The simplest solution would be to make a table that has two fields in it: InstallDate and IsRegistered. When the program is run, read the value of InstallDate from the table. If it's blank, set it. If it's not blank, check to see if the program has been registered. If not, and it's after two weeks, pop up a registration form.

You can read a value from a table in code using the DLOOKUP function. I cover it in detail in my Access 302 class ( Since you only have the one record in your Settings table, your line would be like this:

MyInstallDate = DLOOKUP("InstallDate","SettingsT")

And that's it. You can look up IsRegistered the same way. Once you know those two things, setting up the logic for your VBA code is really quite easy...

If IsNull(MyInstallDate) Then
If Not IsRegistered AND Date() - MyInstallDate > 14 then 'more than 2 weeks
end if
end if

Now, to SET the registration, you could use a RecordSet or a simple UPDATE query. I would recommend the update query - it's easier. Just build an update query that sets the value of InstallDate to TRUE, save it, and then run that query from your code:

Docmd.OpenQuery "NameOfMyQuery"

If you don't know how to build an update query, see my Access 222 lesson on action queries ( You can also just grab the SQL of the query and run that from code to...

Docmd.RunSQL "---sql statment here---"

That will handle setting your dates. GetRegistration is up to you... make a form with a password field on it, and check that somehow when the user submits it. Then set the values accordingly.

Geez... I could probably make a whole tutorial JUST on how to do this - and I could probably spend a whole hour on this topic. And of course, the method I'm suggesting is ONLY if your end users don't really know how to use Access... because an experienced Access developer could simply attach to your tables and then set the values themselves. In this case, you'd need to use some kind of encrypted external file, registry keys, or something like that (or I guess you could do it with encrypted values IN your tables... but that's another story).

I hope this points you in the right direction. If you'd like more detailed help on this, see my TechHelp page (

Start a NEW Conversation
Only students may post on this page. Click here for more information on how you can set up an account. If you are a student, please Log On first. Non-students may only post in the Visitor Forum.
Subscribe to Access Q&A: Left, Running Total, Register
Get notifications when this page is updated

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 6/18/2024 9:31:16 AM. PLT: 0s
Keywords: access tips password left right mid dlookup dmax registration  PermaLink  Access Q&A: Left, Running Total, Register