[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

dmax query help

I have a table with a document name field. The document name is constructed based on the supplier's code_year_month_(incrementing number sarting at 1).pdf

eg
DogShop_2011_01_1.pdf, DogShop_2011_01_2.pdf, DogShop_2011_01_3.pdf, etc.

code below is what I've managed so far but need a little help.

Any ideas?
   
Dim strDocName As String
    strDocName = Me!SupplierCode & "_" & Year(rst![CashFlowDate]) & "_" & Month(rst![CashFlowDate]) & "_"
    
    Dim rs As DAO.Recordset
    Dim strSQLSup As String
    strSQLSup = "SELECT * FROM [tblSupplierInvoices_1] WHERE [DocumentName] Like  " & strDocName & "* "
      
    Set rs = db.OpenRecordset(strSQLSup, dbOpenDynaset)

    tmpSupplierInvoiceCount = Nz(DMax("DocumentName", "tblSupplierInvoices_1", "[DocumentName] = " & rs!DocumentName & ""), 0) + 1
        
    strDocName = strDocName & tmpSupplierInvoiceCount & ".pdf"

Open in new window

0
Shawn
Asked:
Shawn
1 Solution
 
GRayLCommented:
This:

tmpSupplierInvoiceCount = Nz(DMax("DocumentName", "tblSupplierInvoices_1", "[DocumentName] = " & rs!DocumentName & ""), 0) + 1

to:

tmpSupplierInvoiceCount = Nz(DMax("DocumentName", "tblSupplierInvoices_1", "[DocumentName] = '" & rs!DocumentName & "'"), 0) + 1

Single quotes added
0
 
ShawnAuthor Commented:
I'm getting a syntax error here:
    strSQLSup = "SELECT * FROM [tblSupplierInvoices_1] WHERE [DocumentName] Like  " & strDocName & "* "
0
 
ShawnAuthor Commented:
seemed to fix that with the same logic.
now I am getting "no current record"

how would I go ...if no record...then?
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
ShawnAuthor Commented:
almost there.

here's what's working:
Dim strDocName As String
strDocName = Me!SupplierCode & "_" & Year(rst![CashFlowDate]) & "_" & Month(rst![CashFlowDate]) & "_"
   
Dim rs As DAO.Recordset
Dim strSQLSup As String
strSQLSup = "SELECT * FROM [tblSupplierInvoices_1] WHERE [DocumentName] Like  '" & strDocName & "*' "
     
Set rs = db.OpenRecordset(strSQLSup, dbOpenDynaset)

If rs.RecordCount = 0 Then
tmpSupplierInvoiceCount = strDocName & "1"
Else

here's what's NOT working:
tmpSupplierInvoiceCount = Nz(DMax("DocumentName", "tblSupplierInvoices_1", "[DocumentName] = " & rs!DocumentName & ""), 0) + 1
End If

strDocName = tmpSupplierInvoiceCount & ".pdf"
0
 
ShawnAuthor Commented:
got to this but still no luck. says type mismatch.
any ideas? anybody?

tmpSupplierInvoiceCount = Right(DMax("DocumentName", "tblSupplierInvoices_1", "DocumentName like '" & rs![DocumentName] & "'"), 1) + 1
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
How about this:

tmpSupplierInvoiceCount = Nz(DMax("DocumentName", "tblSupplierInvoices_1", "[DocumentName] = " & Chr(34) & rs!DocumentName & Chr(34)), 0) + 1

mx
0
 
ShawnAuthor Commented:
strange. I'm still getting a type mismatch
0
 
Gustav BrockCIOCommented:
Use DCount:

tmpSupplierInvoiceCount = DCount("*", "tblSupplierInvoices_1", "[DocumentName] = '" & rs!DocumentName & "'") + 1
       
/gustav
0
 
ShawnAuthor Commented:
getting closer. Right now it will give me

F3WCOMM00_2010_11_1.pdf for the first record,
F3WCOMM00_2010_11_2.pdf for the second record,
BUT...F3WCOMM00_2010_11_2.pdf for the third record,


strSQLSup = "SELECT * FROM [tblSupplierInvoices_1] WHERE [DocumentName] Like  '" & strDocName & "*' "
     
Set rs = db.OpenRecordset(strSQLSup, dbOpenDynaset)

Dim tmpSupplierInvoiceCount As String

If rs.RecordCount = 0 Then
tmpSupplierInvoiceCount = strDocName & "1"
Else
tmpSupplierInvoiceCount = DCount("*", "tblSupplierInvoices_1", "[DocumentName] = '" & rs!DocumentName & "'") + 1

tmpSupplierInvoiceCount = strDocName & tmpSupplierInvoiceCount

End If

strDocName = tmpSupplierInvoiceCount & ".pdf"

Open in new window

0
 
ShawnAuthor Commented:
tried this
tmpSupplierInvoiceCount = DCount("DocumentName", "tblSupplierInvoices_1", "[DocumentName] like '" & Replace(rs!DocumentName, ".pdf", "") & "'") + 1

but it just gives me F3WCOMM00_2010_11_1.pdf for each record
0
 
Gustav BrockCIOCommented:
You probably have to do something like this:

strDocName = Left(rs!DocumentName, Len(rs!DocumentName) - Len("_x.pdf"))

/gustav
0
 
ShawnAuthor Commented:
sorry I don't think I follow. I think I already have this at the top.

full code below with a couple extra ideas. this is driving me crazy :-}
Dim strDocName As String
strDocName = rstM![SupplierCode] & "_" & Year(rst![CashFlowDate]) & "_" & Month(rst![CashFlowDate]) & "_"
    
Dim rs As DAO.Recordset
Dim strSQLSup As String
strSQLSup = "SELECT * FROM [tblSupplierInvoices_1] WHERE [DocumentName] Like  '" & strDocName & "*' "
'strSQLSup = "SELECT Max(DocumentName) AS MaxOfDocumentName FROM [tblSupplierInvoices_1] WHERE [DocumentName] Like  '" & strDocName & "*' "


Set rs = db.OpenRecordset(strSQLSup, dbOpenDynaset)

Dim tmpSupplierInvoiceCount As String

If rs.RecordCount = 0 Then
tmpSupplierInvoiceCount = strDocName & "001"
Else
'tmpSupplierInvoiceCount = DMax("DocumentName", rs) + 1
'tmpSupplierInvoiceCount = Nz(DMax("DocumentName", "tblSupplierInvoices_1", "[DocumentName] = " & Chr(34) & rs!DocumentName & Chr(34)), 0)

'Dim tmpName As String
'tmpName = Replace(rs![DocumentName], ".pdf", "")
'tmpSupplierInvoiceCount = DMax("DocumentName", "tblSupplierInvoices_1", "DocumentName like '" & tmpName & "'")
tmpSupplierInvoiceCount = DMax("DocumentName", "tblSupplierInvoices_1", "[DocumentName] Like  '" & strDocName & "*' ")
tmpSupplierInvoiceCount = Replace(tmpSupplierInvoiceCount, ".pdf", "")

tmpSupplierInvoiceCount = Right(tmpSupplierInvoiceCount, 3) + 1
If tmpSupplierInvoiceCount < 10 Then
tmpSupplierInvoiceCount = "00" & tmpSupplierInvoiceCount
ElseIf tmpSupplierInvoiceCount < 100 Then
tmpSupplierInvoiceCount = "0" & tmpSupplierInvoiceCount
End If

tmpSupplierInvoiceCount = strDocName & tmpSupplierInvoiceCount

End If

strDocName = tmpSupplierInvoiceCount & ".pdf"

Open in new window

0
 
Gustav BrockCIOCommented:
I think you are making it too complicated:
Dim rs As DAO.Recordset
Dim strDocName As String
Dim strSQLSup As String
Dim intSupplierInvoiceCount As Integer

strDocName = rstM![SupplierCode] & "_" & Year(rst![CashFlowDate]) & "_" & Month(rst![CashFlowDate]) & "_"
    
strSQLSup = "SELECT * FROM [tblSupplierInvoices_1] WHERE [DocumentName] Like  '" & strDocName & "*' "

Set rs = db.OpenRecordset(strSQLSup, dbOpenDynaset)
intSupplierInvoiceCount = rs.RecordCount
rs.Close
strDocName = strDocName & Format(intSupplierInvoiceCount + 1, "000") & ".pdf"

Set rs = Nothing

Open in new window

0
 
ShawnAuthor Commented:
this has simplified but I am still getting:

F3WCOMM00_2010_11_001.pdf
F3WCOMM00_2010_11_002.pdf
F3WCOMM00_2010_11_002.pdf

it seems strSQLSup is taking the first record rather than then highest one
0
 
Gustav BrockCIOCommented:
No, that's not it, but try this method:
strDocName = rstM![SupplierCode] & "_" & Year(rst![CashFlowDate]) & "_" & Month(rst![CashFlowDate]) & "_"
    
strSQLSup = "SELECT Count(*) FROM [tblSupplierInvoices_1] WHERE [DocumentName] Like  '" & strDocName & "*' "

Set rs = db.OpenRecordset(strSQLSup, dbOpenDynaset)
intSupplierInvoiceCount = rs.Fields(0).Value
rs.Close
strDocName = strDocName & Format(intSupplierInvoiceCount + 1, "000") & ".pdf"

Open in new window

/gustav
0
 
ShawnAuthor Commented:
perfect! thank you!
0
 
Gustav BrockCIOCommented:
You are welcome!

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

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