Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
Access Q&A: DMax, DLookup, QueryDef
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   14 years ago

More Access Questions Answered:

Hi Richard,

I have a table with "Date" and "Rate" fields sorted by "Date" ascending. Not every record contains a value in the "Rate" field. Is there a query that would insert the value from previous record in case the "Rate" field is null? (In case there are succesive records with null values, all should contain the value from last record that does contain a value)

The sample table looks like this:


The result should look like this:


Thank you


First, you should never create a field called "Date". That's a reserved word in Access and you'll have problems with it once you start programming in VBA. I'll refer to it as DateField.

Now, there are a couple of ways you could do this. Personally, I'd use a VBA recordset, but here's how you could just do it with a query. First, make a new select query.

Create a RateDate field. This will hold the value of largest Date that has a Rate.

RateDate: IIF(IsNull(Rate),DMAX("*","TableName","DateField<" & DateField),Rate)

Of course, "TableName" is whatever table you're basing this query off of. Your query should now look like this:


Make a new field called Rate2. If Rate has a value, then Rate2 should be Rate. Otherwise, set Rate2 equal to the rate for the largest Date that's less than the current date (which is RateDate). Here's what the Rate2 field would look like:

Rate2: IIF(IsNull(Rate),DLOOKUP("Rate","TableName","DateField=#" & RateDate & "#"),Rate)

That should do it. Your new rate is in the Rate2 field, which you can use by itself, or use an Update Query to set into the Rate field.

I cover the IIF function in my Access 207 class:

I cover DLOOKUP in Access 302:

I cover DMAX in Access 308:

Thanks for being available!

Here's the background so that my code makes (some) sense, but feel free to skip it if you want: I have a set of Word files into which I'm trying to merge names and other information at the top. These Word documents were originally created to be filled out by hand, but now we're trying to save some time by filling out the tops of the documents with a mail-merge. There are at least 70 of these files: the tops of the files all have the same format, but the rest of each document is different. Each file also has a different set of names that needs to be merged into it.

So, I have a table that has all the names and other information that needs to be merged, along with a field that selects which file each record will need to be merged into. (The field is just an integer, but this table has a one-to-many relationship with a table that has the title of each file and an attachment field (new in Access 2007) to store the actual documents that will be merged into.)

Through dealing with other frustrations, I've come to the conclusion (perhaps wrong) that I can't have Access/VBA tell Word 2007 what its record source will be--I've got to go to the merge tools in Word and tell it to point to a particular table or query in my database. I'm trying to avoid creating 70+ tables or queries, one for each Word file, so I thought I'd have all the Word files point to the same query. Then I'd set up a form interface where someone could select the name of the file, hit a button, and I could get VBA to change the query's SQL (so that it pulled up the right data for that particular file) before opening the Word file and executing the mail merge.

And it works, kind of. But here's the problem: it only works the first time you run it. After that, the query remains the same as the first time you ran it, and the data is always the data for that first file that you merged. Closing the form and reopening doesn't help, nor does closing the database (without closing Access) and reopening. But if you completely close Access and then go back in, it'll update the query again, but only the first time...unless you close Access, and so forth. I've verified that the right SQL string is getting passed; it just won't change the querydef's SQL property after the first time. Maybe it's not supposed to?

One clue (perhaps): I've noticed that Access's record-locking file for the database, a .laccdb file, sticks around even when you close the database but don't close Access. Only when you close Access completely does that file go away.

And here's the code, with some of the table/query names changed to protect the innocent. There are a couple lines in there that I think might not be necessary, but I've tested it both with and without those lines--and the problem remains.

Private Sub Merge_Click()
Dim recCurrent As Long
Dim db As Database
Dim rsAssgn As Recordset
Dim rsAttach As Recordset
Dim objWord As Word.Document
Dim strSQL As String
Dim qdfGeneric As QueryDef

Const docLocation As String = [omitted]
recCurrent = Application.Forms("Merge").Controls("Files subform").Form!ID
Set db = CurrentDb
strSQL = "SELECT * FROM FullTable " & _
"WHERE (((FullTable.AssignmentID)=" & recCurrent & _
")) " & [some ORDER BY statements]
MsgBox strSQL
db.QueryDefs("ChangingQuery").SQL = strSQL
Set rsAssgn = db.OpenRecordset("Files")
rsAssgn.Index = "ID"
rsAssgn.Seek "=", recCurrent
If Dir(docLocation) <> "" Then Kill docLocation
Set rsAttach = rsAssgn.Fields("RubricFile").Value
rsAttach.Fields("FileData").SaveToFile docLocation
Set objWord = GetObject(docLocation, "Word.Document")
objWord.Application.Visible = True
For Each qdfGeneric In db.QueryDefs
Set qdfGeneric = Nothing
Next qdfGeneric
End Sub


Max, here are a couple of ideas:

First, what about using Access by itself to generate your documents as reports? Do the Word docs change often? If not, you might want to skip mail merge and just generate Access reports.

Second, you're not DESTROYING your objects. Make sure to kill each object that you're allocating memory for. That might fix your problem. At the end of your sub say:

Set rsAttach = Nothing
Set rsAssgn = Nothing
Set db = Nothing

Third, instead of a querydef, you could always use a MakeTable query to create a table on the fly, run your mail merge off of that, and then re-create the table. It gets around the problem of the query being the same.

Just some ideas. Of course, I haven't TESTED any of these, but hopefully this points you in the right direction.

I cover using Access to generate letters and other documents in my Access 204 class:

I cover MakeTable queries in Access 222:

I'm embarrassed to say I haven't covered Recordsets yet, but I'll be getting to them soon.

Start a NEW Conversation
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.

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

11/30/2021Import Multiline Cells
11/30/2021Adam's Access Games
11/29/2021Prevent Close
11/22/2021Currency Symbols
11/13/2021Access Developer 36
11/13/2021Access Developer 36 Lessons
11/13/2021Access Developer 36
11/13/2021Control Tip Text
11/11/2021Link to Excel

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Account Login
Online Theater
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
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: DMax, DLookup, QueryDef, tips  PermaLink