Select Into Variable

[ninfan]

Hello. I frequently find myself needing to retrieve a value from a table that I will use as part of a SQL statement that I am building in code. For example, I might need to pull the max date from a certain field then use that in a Where clause to pull all records with that date.

In Oracle PL/SQL, I could use "SELECT MAX(RUNDATE) INTO Variable_Name" then use "Variable_Name" later in my code. I have not discovered a similar function in Access VBA. I'm sure this is easy but I haven't figured out a solution. I usually end up with a Make-Table query that I join to in order to accomplish this.

Also, I know that this example isn't the best because it could be handled in a single SQL statement. I seem to frequently need to be able to put a single result of a query into a variable though.

Thank you for any thoughts on this.

Troy

 

[jimmythegeek]

There are domain functions that are helpful: DMax, DMin, DCount, DLookup, DSum. Do a lookup in help for Domain Aggregate functions. In your example above it would be:

variablename = DMax("Fieldname", "tablename&quot

You can also add criteria:

variablename = DMax("Fieldname", "tablename", "Field1=" & Value)

Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[ninfan]

Thank you, Jim. I've used the domain functions to populate controls on forms but I hadn't thought about using them in my code. I suppose I could construct my domain criteria clause to produce about any result I need (since I don't always need MIN or MAX). It doesn't do any harm to get the max value of a field in a 1 record recordset!

 

[cmmrfrds]

You can retrieve data from the database as follows.

Dim cn As New ADODB.Connection, sql1 As String
Dim rs As ADODB.Recordset, connString As String

Set rs = New ADODB.Recordset
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=angelfish;" & _
"Persist Security Info=False"

sql1 = "select max(startdate) as sdate from dbo.Employees "
rs.Open sql1, connString, adOpenForwardOnly, adLockReadOnly
dim avar as date
avar = rs!sdate

 

[jimmythegeek]

When you don't need Max or Min, the DLookUp is very helpful. This will allow you to pull any field from any table matching certain criteria.

Let's say I wanted the LastName of a user with an ID of 55 from the user table:

DLookUp("LastName", "tblUser", "UserID=55&quot

Works great. You can even have multiple criteria. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?