Using Access VBA I am trying to store the resulting value of an SQL statement in a string variable (strLastAccountRefUsed) and an unsure of how to do this. Also I am unsure if my SQL statement is correct. I am using a string variable as a criterea in the WHERE part of the statement. strFirstThreeCharacters is the first 3 alpha characters of a new customers name.
This is my incomplete and probably incorrect code, but I think Im going in the right direction
Expand|Select|Wrap|Line Numbers
- Dim strFirstThreeCharacters as String
- Dim strLastAccountRefUsed as String
- strFirstThreeCharacters= '---Iv'e written some code to find the first 3 alpha characters of new customers name--'
- Dim rs As ADODB.Recordset
- Set rs = New ADODB.Recordset
- rs.Open "SELECT MAX(CUSTOMER_ACCOUNT_REF) FROM tblCustomerDetails WHERE LEFT(CUSTOMER_ACCOUNT_REF,3)='strFirstThreeCharacters');"
- strLastAccountRefUsed = rs.MoveFirst