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:

Date;Rate
20070101;100
20070102;
20070103;
20070104;200
20070105;205
20070106;206
20070107;
20070108;195

The result should look like this:

Date;Rate
20070101;100
20070102;100
20070103;100
20070104;200
20070105;205
20070106;206
20070107;206
20070108;195

Thank you

Maros


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:

DateField;Rate;RateDate
20070101;100;20070101
20070102;;20070101
20070103;;20070101
20070104;200;20070104
20070105;205;20070105
20070106;206;20070106
20070107;;20070106
20070108;195;20070108


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: http://599cd.com?GOAC207

I cover DLOOKUP in Access 302: http://599cd.com?GOAC302

I cover DMAX in Access 308: http://599cd.com?GOAC308




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
rsAssgn.Edit
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
objWord.MailMerge.Execute
rsAttach.Close
rsAssgn.Close
For Each qdfGeneric In db.QueryDefs
qdfGeneric.Close
Set qdfGeneric = Nothing
Next qdfGeneric
db.Close
End Sub


>Max



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
etc.

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: http://www.599cd.com?GOAC204

I cover MakeTable queries in Access 222: http://599cd.com?GOAC222

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.
 
Subscribe
 

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/28/2021Random
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
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
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