Access Q&A: DMax, DLookup, QueryDef
By Richard Rost
19 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.
|
