Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  

Access Q&A: Left, Running Total, Register
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   10/25/2007 4:53:24 PM
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 (

Permanent Link
Keywords: access tips password left right mid dlookup dmax registration

Add Your Comments or Subscribe
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
  Your Name:  Required
  Your Email:  NOT Public


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 7+4:
  Notify me when the News is updated.
  Remember Me for my next comments
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard



You may want to read these articles from the 599CD News:

3/30/2020Access DateDiff Function
3/23/2020Browsing and Selecting Files
3/14/2020New Access Group
3/6/2020NEW Access 2019 for Beginners
8/15/2019Last Chance for Access CDs
8/1/2019Customer List Form Template
8/1/2019New Template Access Customer Database
7/30/2019Microsoft Access Developer 13
6/20/2019TIP: Validation Rules in Access
11/20/2018Microsoft Access Developer 12

Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

YouTube Channel    LinkedIn

Blog RSS Feed   
Richard Rost Microsoft MVP