First time poster, I finally had a question that I couldn't find an answer to here.
I have an MS Access query that returns 1 result (which is a number) that I want to store as an integer variable (x) so I can use it later for a loop. The issue is that because I'm using it as a recordset and the variable is an integer, I'm receiving a "Type Mismatch" error.
Right now I'm just storing the result to a cell and setting the variable equal to the cell:
Ws.Range("A1") = Db.OpenRecordset("SELECT COUNT(Asset_Name) FROM Assets WHERE Active = True").GetRows(1)
x = Ws.Range("A1")
Ws.Range("A1").Delete
And then later I just have a loop that runs x times:
For i = 0 To x
Basically, I just want to have some code that looks like this:
x = Db.OpenRecordset("SELECT COUNT(Asset_Name) FROM Assets WHERE Active = True").GetRows(1)
Any help here would be huge. Thank you!
The following should give you the correct result:
Dim x As Integer
Dim db As DAO.Recordset
db.MoveFirst
If IsNumeric(db.OpenRecordset("SELECT COUNT(Asset_Name) FROM Assets WHERE Active = True").Fields(0).Value) Then
x = CInt(db.OpenRecordset("SELECT COUNT(Asset_Name) FROM Assets WHERE Active = True").Fields(0).Value)
Else
MsgBox "The query did not return a number." & Chr(10) & "Aborting..."
End If
Note, that you are using DAO and not ADO as your original tags on the post indicated. Still, they both behave rather similar and the cursor is normally on the first row (when the data is returned). So, MoveFirst should not be necessary. Still, Microsoft themselves keep using it in its own sample code all the time. The first column if for DAO and ADO alike .Fields(0).
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments