Assign a Variable a Value from SQL

[Guest]

Is possible to assign a variable a value from SQL? See strSQL.

strSQL = DoCmd.RunSQL "SELECT TOP 1 [Request ID Number].YearID" & _
"FROM [Request ID Number]" & _
"ORDER BY [Request ID Number].ID DESC;"

Thank you for any help.

 

[Dirk Goldgar]

Is possible to assign a variable a value from SQL? See strSQL.

strSQL = DoCmd.RunSQL "SELECT TOP 1 [Request ID Number].YearID" & _
"FROM [Request ID Number]" & _
"ORDER BY [Request ID Number].ID DESC;"

Thank you for any help.

RunSQL is for action queries. You need to open a recordset on that
query and get the value from the record it returns:

Dim strSQL As String
Dim rs As DAO.Recordset
Dim lngYearID As Long ' or whatever data type is appropriate

strSQL = ...

Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
If Not .EOF Then
lngYearID = !YearID
End If
.Close
End With
Set rs = Nothing

 

[Guest]

As Dirk points out, the RunSQL will not work for you. Since you are looking
the highest RequestID Number, you could use the DMax Domain Aggregate
function:

=DMax("[YearID]", "[Request ID Number]")

 

[Dirk Goldgar]

As Dirk points out, the RunSQL will not work for you. Since you are
looking the highest RequestID Number, you could use the DMax Domain
Aggregate function:

=DMax("[YearID]", "[Request ID Number]")

That's not quite the same as what Lamar's SQL statement would return:

strSQL = DoCmd.RunSQL "SELECT TOP 1 [Request ID Number].YearID" & _
"FROM [Request ID Number]" & _
"ORDER BY [Request ID Number].ID DESC;"

Note that the field begin selected is [YearID], while the field being
ordered by is [ID]. It could be that this is a typo, by I wouldn't want
to assume that.

 

[Guest]

You are correct. I misread the Order By.

As Dirk points out, the RunSQL will not work for you. Since you are
looking the highest RequestID Number, you could use the DMax Domain
Aggregate function:

=DMax("[YearID]", "[Request ID Number]")

That's not quite the same as what Lamar's SQL statement would return:

strSQL = DoCmd.RunSQL "SELECT TOP 1 [Request ID Number].YearID" & _
"FROM [Request ID Number]" & _
"ORDER BY [Request ID Number].ID DESC;"

Note that the field begin selected is [YearID], while the field being
ordered by is [ID]. It could be that this is a typo, by I wouldn't want
to assume that.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)