473,320 Members | 1,876 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

Hi

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
  1. Dim strFirstThreeCharacters as String
  2. Dim strLastAccountRefUsed as String
  3. strFirstThreeCharacters= '---Iv'e written some code to find the first 3 alpha characters of new customers name--'
  4.  
  5. Dim rs As ADODB.Recordset
  6. Set rs = New ADODB.Recordset
  7.  
  8. rs.Open "SELECT MAX(CUSTOMER_ACCOUNT_REF) FROM tblCustomerDetails WHERE LEFT(CUSTOMER_ACCOUNT_REF,3)='strFirstThreeCharacters');"
  9.  
  10.  
  11. strLastAccountRefUsed = rs.MoveFirst
Any help would be much appreciated
Aug 27 '09 #1

✓ answered by ajalwaysus

Try this:
Expand|Select|Wrap|Line Numbers
  1. Dim rs As DAO.Recordset
  2. Dim strFirstThreeCharacters As String
  3. Dim strSQL As String
  4.  
  5. strSQL = "SELECT MAX(CUSTOMER_ACCOUNT_REF) AS MAX_CUSTOMER_ACCOUNT_REF FROM tblCustomerDetails WHERE LEFT(CUSTOMER_ACCOUNT_REF,3)='" & strFirstThreeCharacters & "';"
  6. Set rs = CurrentDb.OpenRecordset(strSQL)
  7.  
  8. strLastAccountRefUsed = rs!MAX_CUSTOMER_ACCOUNT_REF
  9.  
This is with the assumption that these tables are local or linked in your DB.

Also, the DMax need to be assigned to strLastAccountRefUsed

i.e.
Expand|Select|Wrap|Line Numbers
  1. strLastAccountRefUsed  = DMax(CUSTOMER_ACCOUNT_REF, tblCustomerDetails , WHERE LEFT(CUSTOMER_ACCOUNT_REF,3)='" & strFirstThreeCharacters & "'")
  2.  
Let me know if this works.
-AJ

5 7370
ajalwaysus
266 Expert 100+
Could you just use a simple DMax function? Are these tables local or linked in your DB?

Example of using DMax

Expand|Select|Wrap|Line Numbers
  1. DMax(CUSTOMER_ACCOUNT_REF, tblCustomerDetails , WHERE LEFT(CUSTOMER_ACCOUNT_REF,3)='" & strFirstThreeCharacters & "'")
  2.  
what is wrong with your code, is that you need to handle "strFirstThreeCharacters " 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

Expand|Select|Wrap|Line Numbers
  1. LEFT(CUSTOMER_ACCOUNT_REF,3)='" & strFirstThreeCharacters & "'"
  2.  
The way you had it, was handling "strFirstThreeCharacters" as a literal value.

Let me know if you have any other questions.

-AJ
Aug 27 '09 #2
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 'strLastAccountRefUsed=rs.MoveFirst'

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 (strLastAccountRefUsed)?

Thank you
Expand|Select|Wrap|Line Numbers
  1. Dim rs As ADODB.Recordset
  2. Set rs = New ADODB.Recordset
  3.  
  4. rs.Open "SELECT MAX(CUSTOMER_ACCOUNT_REF) FROM tblCustomerDetails WHERE LEFT(CUSTOMER_ACCOUNT_REF,3)='" & strFirstThreeCharacters & "';"
  5.  
  6. strLastAccountRefUsed = rs.MoveFirst
Aug 27 '09 #3
ajalwaysus
266 Expert 100+
Try this:
Expand|Select|Wrap|Line Numbers
  1. Dim rs As DAO.Recordset
  2. Dim strFirstThreeCharacters As String
  3. Dim strSQL As String
  4.  
  5. strSQL = "SELECT MAX(CUSTOMER_ACCOUNT_REF) AS MAX_CUSTOMER_ACCOUNT_REF FROM tblCustomerDetails WHERE LEFT(CUSTOMER_ACCOUNT_REF,3)='" & strFirstThreeCharacters & "';"
  6. Set rs = CurrentDb.OpenRecordset(strSQL)
  7.  
  8. strLastAccountRefUsed = rs!MAX_CUSTOMER_ACCOUNT_REF
  9.  
This is with the assumption that these tables are local or linked in your DB.

Also, the DMax need to be assigned to strLastAccountRefUsed

i.e.
Expand|Select|Wrap|Line Numbers
  1. strLastAccountRefUsed  = DMax(CUSTOMER_ACCOUNT_REF, tblCustomerDetails , WHERE LEFT(CUSTOMER_ACCOUNT_REF,3)='" & strFirstThreeCharacters & "'")
  2.  
Let me know if this works.
-AJ
Aug 27 '09 #4
Thanks AJ

I have tried your Dmax solution with a few ammendments and it works perfectly
Expand|Select|Wrap|Line Numbers
  1. 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.
:)
Aug 27 '09 #5
ajalwaysus
266 Expert 100+
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
Aug 27 '09 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Christopher Brandsdal | last post by:
Hi! I have a delicatg problem.... I have made a registration form for adding my friends information in a database. The problem is that I want to connect the persons with companies in the same...
6
by: DLP22192 | last post by:
I have the following single-line if statement that is evaluating true even though it shouldn't. I have never seen this before and I am concerned that this can happen in other areas of my code. ...
5
by: Joel | last post by:
Hi, I incorporated a function in my code that whenever I use a string variable in an sql statement if the string contains a single quote it will encase it in double quotes else single quotes. ...
3
by: Jason | last post by:
I have several tables with quite a few fields and I'm getting errors when trying to insert records with single quotes in the data like: name = John O'Henry or a city name of O'Fallen So I went...
3
by: Solution Seeker | last post by:
I want to Store the String value with Single Quotes in the Field of Database where if i try to Store the String value with Single Quotes (as it is) then it is throwing the error as SQL String...
1
by: TARUN | last post by:
Hello All I get stuck in one problem , Please help me and excuse me on the poor Knowledge in SQL Server Store procedure Q1...I want to return a string type value from store procedure , Is it...
11
by: Elmo | last post by:
Hi all! I am not very proud to ask this but here is my problem: string code = "\'13\'" The string code will have to contain following info: '51','52','63','other'... to get certain info...
23
by: dkirkdrei | last post by:
I am having a bit of trouble trying to double up on slashes in a file path. What I am trying to do is very similar to the code below: <? $var =...
0
by: dhascuba | last post by:
The @name field can contain a single quote in it such as: Mike O'Grady. Since this is creating and SQL statement on the server side, it will not process the name if it has a single quote in it. Id'...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.