Desparate to solve Type Mismatch problem

[Duck]

I have a bit of code that is connected to the click event of a button
control. The purpose of is to create a sales order number and then
check to see if it already exists before applying. The theory is that
if it does exists then the last digit would be incremented by one
before writing to the table. My problems seems to be in my SQL
statement and implementing the last "Like" portion of the statement.
It would normally read:
Like strPO & "*"
if it were in the design windows, but no matter what configuration I
use in the VBA code I get an error, ususally a Type Mismatch error.
My table has Sales order numbers both with and without the final "-
x" Here is my code:

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 & "-1"


Me.frmFrameLenses.Form!txtPONo = strPO

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

Any ideas would be appreciated

 

[pietlinden]

I have a bit of code that is connected to the click event of a button
control. The purpose of is to create a sales order number and then
check to see if it already exists before applying. The theory is that
if it does exists then the last digit would be incremented by one
before writing to the table. My problems seems to be in my SQL
statement and implementing the last "Like" portion of the statement.
It would normally read:
Like strPO & "*"
if it were in the design windows, but no matter what configuration I
use in the VBA code I get an error, ususally a Type Mismatch error.
My table has Sales order numbers both with and without the final "-
x" Here is my code:

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 & "-1"

Me.frmFrameLenses.Form!txtPONo = strPO

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

Any ideas would be appreciated

Seeing that I'm obscenely lazy, I would get the code from Access Web,
www.mvps.org/access "Incrementing the Numeric Portion of a String" and
tweak (if necessary) to suit. While figuring it out for yourself is
definitely good for learning how to (and maybe more importantly why
not to) do something, Dev's advice is very sound, and free for the
taking... IIRC, there's a nice example in the Developer's
Handbook.... very handy if you need to set/get this value in a multi-
user environment. (lock table, get new value, insert, return value,
unlock table).

okay, back to the crack pipe...
=0

 

[John W. Vinson]

I have a bit of code that is connected to the click event of a button
control. The purpose of is to create a sales order number and then
check to see if it already exists before applying. The theory is that
if it does exists then the last digit would be incremented by one
before writing to the table. My problems seems to be in my SQL
statement and implementing the last "Like" portion of the statement.
It would normally read:
Like strPO & "*"
if it were in the design windows, but no matter what configuration I
use in the VBA code I get an error, ususally a Type Mismatch error.
My table has Sales order numbers both with and without the final "-
x" Here is my code:

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 & "-1"

I've asked this before, I'll ask it again.

Do you have any LOOKUP FIELDS here?

I very strongly suspect that you're getting a type mismatch because CustID is
actually a Long Integer numeric ID concealed behind a Lookup.

Or possibly PO is a lookup field; what you *see* is the formatted ID but
what's stored is a number.

John W. Vinson [MVP]

 

[Duck]

I have a bit of code that is connected to the click event of a button
control. The purpose of is to create a sales order number and then
check to see if it already exists before applying. The theory is that
if it does exists then the last digit would be incremented by one
before writing to the table. My problems seems to be in my SQL
statement and implementing the last "Like" portion of the statement.
It would normally read:
Like strPO & "*"
if it were in the design windows, but no matter what configuration I
use in the VBA code I get an error, ususally a Type Mismatch error.
My table has Sales order numbers both with and without the final "-
x" Here is my code:

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 & "-1"

I've asked this before, I'll ask it again.

Do you have any LOOKUP FIELDS here?

I very strongly suspect that you're getting a type mismatch because CustID is
actually a Long Integer numeric ID concealed behind a Lookup.

Or possibly PO is a lookup field; what you *see* is the formatted ID but
what's stored is a number.

John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

You are right...CustID is a Long Integer. It is the autonumber,
Primary Key, from the Customer Table...but the SQL statement works
with the CustID portion of the Sales Order number included...it's not
until I try to add the &"*" portion that it fails. I'm soooo
confused....