Continuation of problem generating Sales Order Number

[Duck]

-------This is my earlier post which goes as follows:

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

-------I got the following response:

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

----------I tried the advice given except that I had to change the
last line to read:

strSql = strSql & " WHERE ((tblInventory.SO)) Like '" & strPO & "' &
"*" & '"

but I still get a Type Mismatch message. As the last contributor
conjectured, my CustID field is, in the original table (tblCustomer),
and autonumber field. However even when I try to convert the CustID
field with Cstr() I still get the same error...Here is the code as it
currently stands:


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 strDate 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
strDate = CStr(Format(Now(), "yymmdd"))
Debug.Print "The Date part is " & strDate
strCustID = CStr(Me![CustID].Value)
strPO = strDate & "-" & strCustID

Me.frmFrameLenses.Form!txtPONo = strPO

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]

numInc = 1
strDate = CStr(Format(Now(), "yymmdd"))
Debug.Print "The Date part is " & strDate
strCustID = CStr(Me![CustID].Value)
strPO = strDate & "-" & strCustID

Me.frmFrameLenses.Form!txtPONo = strPO

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

What is the datatype of tblInventory.SO? Guessing here: is it a Lookup Field
which actually *CONTAINS* the numeric CustID but *DISPLAYS* the composite text
customerID? That would explain the problem, and provide yet another example
why so many of us despise Lookup Fields!

John W. Vinson [MVP]