Count records in SQL, if... then ... else do nothing end if

[bulbisi]

I thought it was easier...
I'm trying to:
1/ Count the number of records in the Query "FR_LOAN_TYPE_1" (field "REFPMT")
2/ use that count value into a condition
3/ if countvalue > 0 then ...**do the stuff ** else ** do nothing ** end if
It must be something wrong, maybe a Dim statement, maybe my SQL string. Don't know.
Anyone has a clue?
Here is an extract of my code below

Thanks a million in advance... end of the day, I'm insanely tired.

Chris

PS: stAppFolder in a constant (Const) and is working well

    Dim QueryCount As Integer
    Dim CountRecords As Integer
QueryCount = "SELECT Count(FR_Loan_type_1.REFPMT) AS CountOfREFPMT FROM FR_Loan_type_1 "
CountRecords = QueryCount
    Dim stReportName As String
    Dim stPath As String
If CountRecords > 0 Then
    Dim stFileName As String 'name of the letters PDF file
    stReportName = "FR_Loan_DomOK" 
    stPath = stAppFolder
    stFileName = Format(stBatchTime, "yyyy-mm-dd-hhmmss") & "_" & stBatchNum & "_" & stReportName & ".pdf"
    DoCmd.OpenReport stReportName, acPreview
    DoCmd.OutputTo acOutputReport, , acFormatPDF, stPath & stFileName ', True
    DoCmd.Close acReport, stReportName
Else
End If

 

[CBrighton]

You have not used the SQL for anything, you have just assigned it to an int variable.

Here's some code for you:

Dim db as database
set db = currentdb
dim rst as recordset
set rst = db.openrecordset("SELECT Count(FR_Loan_type_1.REFPMT) AS CountOfREFPMT FROM FR_Loan_type_1")
dim intRecordCount as int
 
if rst.eof then
   '0 records
else
   rst.movelast
   rst.movefirst
   intRecordCount = rst.recordcount
   '1 or more records
end if

Note that I believe rst.recordcount needs to have gone to the end of the recordset to be accurate, hence the rst.movelast & rst.movefirst.

 

[bulbisi]

Interesting,
I didn't think I would need a use of BOF and EOF for a single Query.
Note I do have already my Connection set up (just being now at the top of my procedure)
I'm using ADO references and so will have a test on it.
zzzZZZzzzz wake brain. Need to finish it today.

 

[bulbisi]

i'm definitely not good working with recordset
here is what i was trying.

    Dim dbs As adodb.Connection
    Dim Db As String
Set dbs = New adodb.Connection
dbs = Application.CurrentProject.Connection
dbs.Open Db
    Dim QueryCount As adodb.Recordset
Set QueryCount = dbs.Recordset("SELECT Count(FR_Loan_type_1.REFPMT) AS CountOfREFPMT FROM FR_Loan_type_1 ")
    Dim CountRecords As Integer
    Dim stReportName As String
    Dim stPath As String
If QueryCount.EOF Then
    Dim stFileName As String 'name of the letters PDF file
    stReportName = "FR_Loan_DomOK" 
    stPath = stAppFolder
    stFileName = Format(stBatchTime, "yyyy-mm-dd-hhmmss") & "_" & stBatchNum & "_" & stReportName & ".pdf"
    DoCmd.OpenReport stReportName, acPreview
    DoCmd.OutputTo acOutputReport, , acFormatPDF, stPath & stFileName ', True
    DoCmd.Close acReport, stReportName
Else
    rst.MoveLast
    rst.MoveFirst
    intRecordCount = rst.RecordCount
End If

what a mess ...

 

[vbaInet]

DCount():

If Nz(DCount("*", "FR_Loan_type_1.REFPMT"), 0) <> 0 Then
    Dim stReportName As String
    Dim stPath As String
    Dim stFileName As String 'name of the letters PDF file

    stReportName = "FR_Loan_DomOK" 
    stPath = stAppFolder
    stFileName = Format(stBatchTime, "yyyy-mm-dd-hhmmss") & "_" & stBatchNum & "_" & stReportName & ".pdf"

    DoCmd.OpenReport stReportName, acPreview
    DoCmd.OutputTo acOutputReport, , acFormatPDF, stPath & stFileName, True
    DoCmd.Close acReport, stReportName
End If

 

[bulbisi]

I had to slightly amend the line this way:

If Nz(DCount("*", "FR_Loan_type_1"), 0) <> 0 Then

all fine now thank you a lot
+1 point

 

[vbaInet]

Happy to hear bulbisi.