How can I store SQL result into variable?

[Busbait]

I need to know how can I store SQL result into variable, I have used the below code but it dose not work.

Is there an easier way to do this?

---------------------------------------------------------
Dim dbsCurrent As Database
Dim count As Recordset
Dim result As String

Set dbsCurrent = CurrentDb
Set count = dbsCurrent.OpenRecordset("SELECT Estate.ElectricMeterNumber FROM Estate WHERE ((Estate.EstateNumber)= 1)")

[ElectricMeterNumber] = result
count.Close
dbsCurrent.Close

[Scott Price]

Will you have more than one meter number for each estate?

If so you will need to store the result into an array.

This is an example code pulled from my test db, it's not intended to solve your particular problem, just provide you with one possible way to use recordsets and arrays to capture values...

Public Function EventCustomer() As String
 
Dim MyArray() As Variant
Dim evcust As String
Dim cust As String
Dim ev As String
Dim rs As DAO.Recordset
Dim ls As Integer
Dim intCounter As Integer
Dim list As Integer
DoCmd.OpenForm "frmEventCustomer", , , , , acHidden
 
Set rs = Forms!frmEventCustomer!lstEventCustomer.Recordset
Erase MyArray
            With rs
                .MoveFirst
                .MoveLast
                ls = .RecordCount
                .MoveFirst
                MyArray() = .GetRows(ls)
            End With
 
ev = MyArray(1, 0)
cust = MyArray(2, 0)
list = 0
evcust = ev & ": " & cust
For intCounter = 1 To (ls - 1)
        list = list + 1
        cust = MyArray(2, list)
        evcust = evcust & ", " & cust
Next
EventCustomer = evcust
DoCmd.Close acForm, "frmEventCustomer", acSaveNo
Erase MyArray
rs.Close
 
End Function

Regards,
Scott

[JConsulting]

I need to know how can I store SQL result into variable, I have used the below code but it dose not work.

Is there an easier way to do this?

---------------------------------------------------------
Dim dbsCurrent As Database
Dim count As Recordset
Dim result As String

Set dbsCurrent = CurrentDb
Set count = dbsCurrent.OpenRecordset("SELECT Estate.ElectricMeterNumber FROM Estate WHERE ((Estate.EstateNumber)= 1)")

[ElectricMeterNumber] = result
count.Close
dbsCurrent.Close

if it's just a one time deal

Dim Myval
myval = dlookup("myfield","mytable","Somefield=somevalue")

that will store the value of "myfield" into the variable myval

Hope this helps
J

[Busbait]

Thanks Scott and JConsulting for your replies.

I would appreciate if you can provide me with an example of How to store SQL result into variable assuming that the SQL statement will return only single value.

[Scott Price]

Thanks Scott and JConsulting for your replies.

I would appreciate if you can provide me with an example of How to store SQL result into variable assuming that the SQL statement will return only single value.

Like J said try this:

Dim MyVal As Integer

MyVal = DLookup("ElectricMeterNumber", "Estate", "[EstateNumber] = 1")

(This is assuming that your ElectricMeterNumber is an integer value... if it could use decimal points, change the variable declaration to Double)

Regards,
Scott

[JConsulting]

Thanks Scott and JConsulting for your replies.

I would appreciate if you can provide me with an example of How to store SQL result into variable assuming that the SQL statement will return only single value.

You had originally asked if there was an easier way...thta's what the Dlookup() function was...easier.
This works the way you originally designed it I suppose.
J

Dim dbsCurrent As Database
Dim rsCount As Recordset
Dim result As String
 
Set dbsCurrent = CurrentDb
Set rsCount = dbsCurrent.OpenRecordset("SELECT Estate.ElectricMeterNumber FROM Estate WHERE ((Estate.EstateNumber)= 1)")
 
result = rsCount![ElectricMeterNumber]
rsCount.Close
 

[Busbait]

Thanks Gentlemen for your support