Assigning SQL result to a variable

[Mark77]

Hello

I am trying to assign the result of a SQL statement (Count of recordset) to a integer variable. I am getting a type mismatch on the assignment. The SQL statement is good. What am I missing. Code below:

Dim intPMCount As Integer
Dim rcdPMCount As Recordset
Dim mySQL As String

mySQL = "SELECT Count(tblPMTemplate.PMTID) AS CountOfPMTID" & _
"FROM ((tblPMTemplate LEFT JOIN tblAsset ON tblPMTemplate.AssetID = tblAsset.AssetID) " & _
"LEFT JOIN tblAssetType ON tblAsset.AssetTypeID = tblAssetType.AssetTypeID) " & _
"LEFT JOIN tblAssetState ON tblAsset.AssetStateID = tblAssetState.AssetStateID " & _
"GROUP BY tblAssetType.AssetType, tblPMTemplate.Archive, tblAssetState.AssetState " & _
"HAVING (((tblAssetType.AssetType)=[Forms]![frmGenerateSchedule]![GetAssetType]) " & _
"AND ((tblPMTemplate.Archive)=False) " & _
"AND ((tblAssetState.AssetState)='In Use'));"

Set rcdPMCount = CurrentDb.OpenRecordset("mySQL")
intPMCount = rcdPMCount

[FishVal]

Hi, Mark.

I guess no way to assign result of SQL to variable.
Better look at DCount function.

[nico5038]

To get the number of records from a recordset you need to use:

 Set rcdPMCount = CurrentDb.OpenRecordset("mySQL")
  if  rcdPMCount.eof and rcdPMCount.BOF then
     ' no records found
     intPMCount = 0
   else
     rcdPMCount.movelast
     intPMCount = rcdPMCount.recordcount
   endif
 

When you only need the value from a field in the recordset use:

 Set rcdPMCount = CurrentDb.OpenRecordset("mySQL")
  if  rcdPMCount.eof and rcdPMCount.BOF then
     ' no records found
     intPMCount = 0
   else
     intPMCount = rcdPMCount!CountOfPMTID
   endif
 

Getting the idea ?

Nic;o)

[Mark77]

Thanks FishVal

I was really trying to generate the recordset with a coded recordset and then do a dLookup off the recordset. I am having trouble with the syntax on this. Can you help.

[FishVal]

Thanks FishVal

I was really trying to generate the recordset with a coded recordset and then do a dLookup off the recordset. I am having trouble with the syntax on this. Can you help.

Hi, Mark.

• if you want iterate through recordset you may do it using EOF and or BOF property
  

  'forward iteration
  With RS
      .MoveFirst
      While Not .EOF
          ........
          .MoveNext
      Wend
  End With
   
  'backward iteration
  With RS
      .MoveLast
      While Not .BOF
          ........
          .MovePrevious
      Wend
  End With
   

• if you want to retrieve recordcount for other purposes then look at Nico's suggestion

Regards,
Fish