Help with code

[Duck]

I have a form on which there is a button that I want to be able to
generate a new Purchase Order number based on the date, customer ID,
and sequence number. I have code with which I am trying to identify
the latest PO number for a particular Cutomer on that day, so that I
can increment the sequence number. I believe my code is close but I
keep getting a "Type Mismatch" error,, and I didn't get the error
until I added the portion with the "*" wildcard!!

Here is the code:

Private Sub btnGeneratePO_Click()
On Error GoTo Err_btnGeneratePO_Click

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection

Dim strYr As String
Dim strMo As String
Dim strDy As String
Dim strPO As String
Dim strTmp As String
Dim numInc As Integer
Dim strCustID As String
Dim strSql As String
Dim numCnt As Integer



numInc = 1
strYr = Right(DatePart("yyyy", Now()), 2)

strTmp = DatePart("m", Now())
If strTmp < 10 Then
strMo = "0" & strTmp
Else
strMo = strTmp
End If


strTmp = DatePart("d", Now())
If strTmp < 10 Then
strDy = "0" & strTmp
Else
strDy = strTmp
End If

strCustID = Me![CustID].Value
strPO = strYr & strMo & strDy & "-" & strCustID

strSql = "SELECT Max(tblInventory.SO) AS MaxOfSO FROM
tblInventory"
strSql = strSql & " HAVING ((Max(tblInventory.SO))) Like " & strPO
& "' & " * " & '"

rs.Open strSql
numCnt = rs.RecordCount

Debug.Print "Count is " & numCnt



Debug.Print "The Purchase Order Number is " & strPO

Me!txtGeneratePO.Value = strPO


Exit_btnGeneratePO_Click:
Exit Sub
Err_btnGeneratePO_Click:
MsgBox Err.Description
GoTo Exit_btnGeneratePO_Click
End Sub

 

[John W. Vinson]

I have a form on which there is a button that I want to be able to
generate a new Purchase Order number based on the date, customer ID,
and sequence number. I have code with which I am trying to identify
the latest PO number for a particular Cutomer on that day, so that I
can increment the sequence number. I believe my code is close but I
keep getting a "Type Mismatch" error,, and I didn't get the error
until I added the portion with the "*" wildcard!!

It looks like you're assuming that a Date/Time value is a String and are using
a wildcard to search it. A date/time is NOT a string! It's stored internally
as a number, and can be searched using # as a delimiter. You can also use the
Format() function rather than picking apart Now() with functions:

Format(Date(), "yyyymmdd")

will return "20070626" as a string value.

strSql = "SELECT Max(tblInventory.SO) AS MaxOfSO FROM
tblInventory"
strSql = strSql & " WHERE ((tblInventory.SO)) Like '" & strPO
& "*'"

will then find the maximum SO starting with "20070626000123" if CustomerID is
000123 (I *hope* CustomerID is of Text type).

John W. Vinson [MVP]