• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 666
  • Last Modified:

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
0
PSIUnit
Asked:
PSIUnit
  • 5
  • 5
1 Solution
 
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
 
Rey Obrero (Capricorn1)Commented:
try this then

tmpFolID = Nz(DMax("FollowupId","dbo_ct1035Followup","[Policykey] = '" & Pst!policykey & "'"),0) + 1
0
Technology Partners: 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!

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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now