how to store SQL result in a variable

[wasim_sono]

Dear All

I need to store SQL result in a field to display it on form or store in table.

I used following coding but didn't find result.

Dim No As Variant
Dim sql As Variant
DoCmd.Maximize
sql = "select (max(feasb_no)+1) as no from feasblty"
CurrentDb.Execute "" & sql



I want to store Maximum no + 1 in "NO" variable.

Thanks.

Regards

Wasim

[orange]

Dear All

I need to store SQL result in a field to display it on form or store in table.

I used following coding but didn't find result.

Dim No As Variant
Dim sql As Variant
DoCmd.Maximize
sql = "select (max(feasb_no)+1) as no from feasblty"
CurrentDb.Execute "" & sql


I want to store Maximum no + 1 in "NO" variable.

Thanks.

Regards

Wasim A few points:
Why dim your variables as Variant?
Why not declare them what they are number /text?

Do not use a variable name that can be confused or is a reserved word? NO is a value for a boolean.
currentdb.execute requires an action query not a Select.

I have mocked up a solution based on my data.
My table Cust has an id field.

Cust contains:

Cust Cust
id hauliercode
1 100
2 101
3 217

'Dim No As Variant
'Dim sql As Variant
'DoCmd.Maximize
'sql = "select (max(feasb_no)+1) as no from feasblty"
'CurrentDb.Execute "" & sql

Dim Num As Long
Dim sql As String
sql = "select (max(id)+1) as num from cust"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(sql)
Debug.Print rs!Num

rs!Num is the value in the Num column of recordset rs.
The value printed is 4.

Hope this is helpful.

[geekgirlau]

As you are only grabbing the value of one field, here's another alternative that doesn't require a recordset:


Num = DMax("[id]", "cust") + 1


The only thing you have to watch when using any of the domain aggregate functions (DMax, DLookup, DCount etc.) is for null values. Although in this case it is highly unlikely that you'll get a null result, you can make it more robust with:


Num = nz(DMax("[id]", "cust"),0) + 1