Conditional Processing based on Record Count

  • Thread starter Thread starter Dave F
  • Start date Start date
D

Dave F

In my module I need to perform some action if records exist in a table.

I'm guessing I would use an SQL statement, but what is the syntax to
determine the record count for a table and test the variable for a value?

Thanks,
Dave F.
 
Check out the DCount function (in Help file). It's a quick way to see if you
have any records in a table or query:

If DCount("*", "TableOrQueryName") = 0 Then
MsgBox "There are no records in the table or query."
Else
' do something in your code
End If

You also can filter the function using an SQL "WHERE" statement without the
"WHERE". For example, if you wanted to know if any records exist that have a
value of 12 in the FieldName field:

If DCount("*", "TableOrQueryName", "[FieldName] = 12") = 0 Then
MsgBox "There are no records in the table or query that match your
criteria."
Else
' do something in your code
End If
 
Thanks. This worked.

Dave F.


Ken Snell said:
Check out the DCount function (in Help file). It's a quick way to see if you
have any records in a table or query:

If DCount("*", "TableOrQueryName") = 0 Then
MsgBox "There are no records in the table or query."
Else
' do something in your code
End If

You also can filter the function using an SQL "WHERE" statement without the
"WHERE". For example, if you wanted to know if any records exist that have a
value of 12 in the FieldName field:

If DCount("*", "TableOrQueryName", "[FieldName] = 12") = 0 Then
MsgBox "There are no records in the table or query that match your
criteria."
Else
' do something in your code
End If

--
Ken Snell
<MS ACCESS MVP>

Dave F said:
In my module I need to perform some action if records exist in a table.

I'm guessing I would use an SQL statement, but what is the syntax to
determine the record count for a table and test the variable for a value?

Thanks,
Dave F.
Click to expand...
Click to expand...
 
Is it possible to use a varible in the criteria evaluation?

For example, can
If DCount("*", "TableOrQueryName", "[FieldName] = 12") = 0
be coded as:

FldNmValue = "12"
If DCount("*", "TableOrQueryName", "[FieldName] = FldNmValue") = 0

This didn't work for me so I don't know if this is possible or my syntax is
incorrrect.

Thanks,
Dave



Ken Snell said:
Check out the DCount function (in Help file). It's a quick way to see if you
have any records in a table or query:

If DCount("*", "TableOrQueryName") = 0 Then
MsgBox "There are no records in the table or query."
Else
' do something in your code
End If

You also can filter the function using an SQL "WHERE" statement without the
"WHERE". For example, if you wanted to know if any records exist that have a
value of 12 in the FieldName field:

If DCount("*", "TableOrQueryName", "[FieldName] = 12") = 0 Then
MsgBox "There are no records in the table or query that match your
criteria."
Else
' do something in your code
End If

--
Ken Snell
<MS ACCESS MVP>

Dave F said:
In my module I need to perform some action if records exist in a table.

I'm guessing I would use an SQL statement, but what is the syntax to
determine the record count for a table and test the variable for a value?

Thanks,
Dave F.
Click to expand...
Click to expand...
 
If DCount("*", "TableOrQueryName", "[FieldName] =" & FldNmValue) = 0

If Fieldname is not a number field, but is a text field.

If DCount("*", "TableOrQueryName", "[FieldName] =" & chr(34) & FldNmValue &
chr(34)) = 0



Dave said:
Is it possible to use a varible in the criteria evaluation?

For example, can
If DCount("*", "TableOrQueryName", "[FieldName] = 12") = 0
be coded as:

FldNmValue = "12"
If DCount("*", "TableOrQueryName", "[FieldName] = FldNmValue") = 0

This didn't work for me so I don't know if this is possible or my syntax is
incorrrect.

Thanks,
Dave

Ken Snell said:
Check out the DCount function (in Help file). It's a quick way to see if you
have any records in a table or query:

If DCount("*", "TableOrQueryName") = 0 Then
MsgBox "There are no records in the table or query."
Else
' do something in your code
End If

You also can filter the function using an SQL "WHERE" statement without the
"WHERE". For example, if you wanted to know if any records exist that have a
value of 12 in the FieldName field:

If DCount("*", "TableOrQueryName", "[FieldName] = 12") = 0 Then
MsgBox "There are no records in the table or query that match your
criteria."
Else
' do something in your code
End If

--
Ken Snell
<MS ACCESS MVP>

Dave F said:
In my module I need to perform some action if records exist in a table.

I'm guessing I would use an SQL statement, but what is the syntax to
determine the record count for a table and test the variable for a value?

Thanks,
Dave F.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top