Using Dmax with a SQL Statement

I have a subset of data from a table and within that subset i want to find the highest value in a field...  Then i need  to add 1 to the highest number...  As you can tell i have never used the Dmax function  here is part of my code so far...

 'Gather Current Followups on that Policy
        strSql = "SELECT * FROM [dbo_ct1035Followup] WHERE [Policykey] = '" & Pst!policykey & "';"
        Set Fst = db.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)
       
        ' **** Code to figure out the last followup id number
       
        tmpFolID = DMax(Fst!FollowupId) + 1
PSIUnitAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
'Gather Current Followups on that Policy
        strSql = "SELECT * FROM [dbo_ct1035Followup] WHERE [Policykey] = '" & Pst!policykey & "';"
        Set Fst = db.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)

try ***

tmpFolID = Nz(DMax("FollowupId","dbo_ct1035Followup","[Policykey] = " & Fst!policykey & "),0) + 1

or

tmpFolID = Nz(DMax("FollowupId","dbo_ct1035Followup","[Policykey] = '" & Fst!policykey & "'"),0) + 1
0
 
Rey Obrero (Capricorn1)Commented:

try

tmpFolID = Nz(DMax("FollowupId","dbo_ct1035Followup"),0) + 1
0
 
PSIUnitAuthor Commented:
i only want the certain records that i selected in my SQl Statement...  The Table dbo_ct1035Followup is keyed by PolicyKey and Followupid. Everytime a followup is added the Followupid increases by one. I am trying to programatically add a followup so i need to find out what the last followupid is for this policy so when i add a new one i will have the next number...

The above code gave the highest number in the table...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rey Obrero (Capricorn1)Commented:
try this then

tmpFolID = Nz(DMax("FollowupId","dbo_ct1035Followup","[Policykey] = '" & Pst!policykey & "'"),0) + 1
0
 
PSIUnitAuthor Commented:
Now it says Data type mismatch in criteria expression..  Followupid & Policy Key are both numeric... the tmpfolid is a variant...
0
 
Rey Obrero (Capricorn1)Commented:
try

tmpFolID = Nz(DMax("FollowupId","dbo_ct1035Followup","[Policykey] = " & Pst!policykey & "),0) + 1

where do you get Pst!policykey?
0
 
PSIUnitAuthor Commented:

here i sthe code from th begginning...
'Gather Notes from NBA from Yesterday
strSql = "SELECT * FROM [dbo_Notes] WHERE [InsertedOn] > date()-2 and left([Note],3) = '" & "gre" & "';"
Set Rst = db.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)

Do While Not Rst.EOF

    'Gather the PolicyKey from the CT1035
    strSql = "SELECT * FROM [dbo_ct1035ReplacementData] WHERE [PolicyNumber] = '" & Rst!CaseID & "';"
    Set Pst = db.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)
0
 
PSIUnitAuthor Commented:
I think That is IT!!!!

 strSql = "SELECT * FROM [dbo_ct1035Followup] WHERE [Policykey] = " & Pst!policykey & ""
        Set Fst = db.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)

        tmpFolID = Nz(DMax("FollowupId", "dbo_ct1035Followup", "[Policykey] = " & Fst!policykey & ""), 0) + 1
0
 
PSIUnitAuthor Commented:
Thank you very much!!!!
0
 
Rey Obrero (Capricorn1)Commented:
u r welcome!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.