?
Solved

dmax for recordset

Posted on 2013-05-17
12
Medium Priority
?
335 Views
Last Modified: 2013-05-20
hey guys,

could yall help me see what's wrong with this code?

Dim dateDataStorage As Date
Dim dateRetentionWorkHours As Date

Dim dbWBRRetention As DAO.Database
Set dbWBRRetention = OpenDatabase(CurrentProject.Path & "\WBR BE - Retention.mdb")

Dim rsRetentiontblWorkHours As Recordset
Set rsRetentiontblWorkHours = dbWBRRetention.OpenRecordset("tblWorkHours")


dateDataStorage = Int(DMax("[Planned Start]", "tblDataStorage"))
'dateRetentionWorkHours = Int(DMax("[DateOfWork]", "tblWorkHours"))
dateRetentionWorkHours = Int(DMax("[DateOfWork]", "rsRetentiontblWorkHours"))

Open in new window


problem is on line 13. line 12 works ok though.

the error i get is thiserror message
0
Comment
Question by:developingprogrammer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39174058
It's very simple. Here:

DMax("[DateOfWork]", "rsRetentiontblWorkHours"))

rsRetentiontblWorkHours must be a table name or query name, not a recordset.

/gustav
0
 

Author Comment

by:developingprogrammer
ID: 39174063
whao thanks for the quick reply gustav! as it turns out rsRetentiontblWorkHours is a recordset ha, so how do i find the max for the recordset? thanks in advance!! = ))
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39174179
--- >>> Set rsRetentiontblWorkHours = dbWBRRetention.OpenRecordset("tblWorkHours")

Since rsRetentiontblWorkHours is nothing more than the records from tblWorkHours (no additional criteria), you dont need to call out the recordset name in your DMax.  Just use this:

dateRetentionWorkHours = Int(DMax("[DateOfWork]", "tblWorkHours"))

And use the recordset as needed elsewhere in your code.

It looked like you already tried that though.  what was the problem with it?

Also, why are you converting to Int?  Why not just this?

dateRetentionWorkHours = DMax("[DateOfWork]", "tblWorkHours")
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 1000 total points
ID: 39174195
Oh... your referring to a different database.  Try ordering your recordset by DateOfWork descending so that the first record in the recordset is the most recent like this:


Set rsRetentiontblWorkHours = dbWBRRetention.OpenRecordset("SELECT * FROM tblWorkHours ORDER BY DateOfWork DESC")
If rsRetentiontblWorkHours.Recordcount = 0 then 
       msgbox "No records"
       Exit Sub  '(or function)
End If
dateRetentionWorkHours = rsRetentiontblWorkHours("DateOfWork")
' the rest of your code follows

Open in new window


(There are other ways to do this too...)
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 1000 total points
ID: 39174245
Yes, I would do samewise, but you only need the top record:

Set rsRetentiontblWorkHours = dbWBRRetention.OpenRecordset("SELECT TOP 1 * FROM tblWorkHours ORDER BY DateOfWork DESC")

/gustav
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39174268
Definitely go with the TOP 1 as Gustav is suggesting if this is your only purpose for the recordset.  My own post (using the whole recordset) is assuming that you have additional code that you haven't posted,  using the full recordset in other ways.
0
 

Author Comment

by:developingprogrammer
ID: 39176779
Whao you guys, you guys are fantastic. What would I do without y'all? You are the wind beneath my wings. Haha = ) but yes it's really really reassuring and like the feeling of having a mobile phone vs not having a mobile phone having you guys = )

Will try this later on in the day. Thanks guys!! = ))
0
 

Author Closing Comment

by:developingprogrammer
ID: 39179889
thanks guys!!!! = ))
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39179917
Glad we could help out :-)
0
 

Author Comment

by:developingprogrammer
ID: 39179921
Yup, and a lot mbizup!! = ))
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39180260
You are welcome!

/gustav
0
 

Author Comment

by:developingprogrammer
ID: 39180541
Thanks gustav!! = ))
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question