Store single value from an SQL MAX Statement in a string variable

[atherstone]

Hi

Using Access VBA I am trying to store the resulting value of an SQL statement in a string variable (strLastAccount RefUsed) 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. strFirstThreeCh aracters 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

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

Any help would be much appreciated

[ajalwaysus]

Could you just use a simple DMax function? Are these tables local or linked in your DB?

Example of using DMax

DMax(CUSTOMER_ACCOUNT_REF, tblCustomerDetails , WHERE LEFT(CUSTOMER_ACCOUNT_REF,3)='" & strFirstThreeCharacters & "'")

what is wrong with your code, is that you need to handle "strFirstThreeC haracters " differently, you are correct you need to wrap it in quotes because it is a string but because it is an assigned value in VBA you need to maniuplate the code to look like this

LEFT(CUSTOMER_ACCOUNT_REF,3)='" & strFirstThreeCharacters & "'"

The way you had it, was handling "strFirstThreeC haracters" as a literal value.

Let me know if you have any other questions.

-AJ

[atherstone]

Thank you for your quick response.

I tried the DMax but kept getting errors, I may do more research on this.

I also tried editing my code to how you said and eddited out the last line to see if the SQL statement worked but it says

'The connection cannot be used to perform this type of opperation. It is either closed or invalid in this context'

However, if I do not edit out this line 'strLastAccount RefUsed=rs.Move First'

I do not get this error, instead I get an error saying

'Compile error. Expected Function or Variable'



How do I store the result from the SQL statement into a variable (strLastAccount RefUsed)?

Thank you

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

[ajalwaysus]

Try this:

Dim rs As DAO.Recordset
Dim strFirstThreeCharacters As String
Dim strSQL As String

strSQL = "SELECT MAX(CUSTOMER_ACCOUNT_REF) AS MAX_CUSTOMER_ACCOUNT_REF FROM tblCustomerDetails WHERE LEFT(CUSTOMER_ACCOUNT_REF,3)='" & strFirstThreeCharacters & "';"
Set rs = CurrentDb.OpenRecordset(strSQL)

strLastAccountRefUsed = rs!MAX_CUSTOMER_ACCOUNT_REF

This is with the assumption that these tables are local or linked in your DB.

Also, the DMax need to be assigned to strLastAccountR efUsed

i.e.

strLastAccountRefUsed  = DMax(CUSTOMER_ACCOUNT_REF, tblCustomerDetails , WHERE LEFT(CUSTOMER_ACCOUNT_REF,3)='" & strFirstThreeCharacters & "'")

Let me know if this works.
-AJ

[atherstone]

Thanks AJ

I have tried your Dmax solution with a few ammendments and it works perfectly

strLastAccountRef = DMax("[CUSTOMER ACCOUNT REF]", "tblCustomerDetails", "left([CUSTOMER ACCOUNT REF],3)='" & strFirstThreeCharacters & "'")

I will also try you last posted solution as I will no boubt need to use this in the future.

Thank you very much for your help, I have been at this for half the day now so am glad it is sorted.
:)

[ajalwaysus]

Wow my bad, I should have caught that, COMPLETE brain freeze. So sorry. Glad it is working for you now.

Comeback with any more questions.

-AJ