Conditional Processing based on Record Count

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.
 
K

Ken Snell

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
 
D

Dave F

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...
 
D

Dave F

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...
 
J

John Spencer (MVP)

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...
 
Top