Counting Records

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

I want to run a query from code to count matching records in a table.

How can I achieve this?

I then want to use this same code again within an IF statement, so IF the
count generated by the query is > than xyz then do something else do
something else.

That kind of thing.

I have no experience of running queries in code so don't know what to do.

Thanks
 
Look up help on the DCount function

If DCount("Field","TableorQuery","Criteria") > xyz then
Do something
else
Do Something Else
end if
 
Thanks

I might be being a bit thick here, but what's the significance of the
"Field" in the function? What field is it?

Thanks
 
None, you simply have to specifiy any one of the fields in the table or query
as the first parameter of the DCount function.
 
What have I done wrong here - it keeps failing saying error 2465 Access
can't find the field "|" refered to in your expression.

If (DCount([ID], [SYS_Software], SFW_LicenseID = ID) >= LIC_Quantity) Then
(something)
Else
(something else)
End If

ID is a field in the table I want to perform the count on (SYS_Software),
SFW_LicenseID is also a field in this same table. I am trying to count all
records where the SFW_LicenseID field matched the ID field of the current
record on the form I am working on. Then if this count is >= LIC_Quantity
(in the table the form I am working on draws its data from).

Thanks
 
The function needs the quotes as each parameter is a string parameter.
Assuming this code is on your form and the ID field is on your form and is
numeric then it should read
If DCount("[ID]","[SYS_Software]","SFW_LicenseID = " & ID) >= LIC_Quantity
Then

Keith said:
What have I done wrong here - it keeps failing saying error 2465 Access
can't find the field "|" refered to in your expression.

If (DCount([ID], [SYS_Software], SFW_LicenseID = ID) >= LIC_Quantity) Then
(something)
Else
(something else)
End If

ID is a field in the table I want to perform the count on (SYS_Software),
SFW_LicenseID is also a field in this same table. I am trying to count all
records where the SFW_LicenseID field matched the ID field of the current
record on the form I am working on. Then if this count is >= LIC_Quantity
(in the table the form I am working on draws its data from).

Thanks


Dennis said:
None, you simply have to specifiy any one of the fields in the table or
query
as the first parameter of the DCount function.
Click to expand...
Click to expand...
 
Thank you for your help

Working now.

Dennis said:
The function needs the quotes as each parameter is a string parameter.
Assuming this code is on your form and the ID field is on your form and is
numeric then it should read
If DCount("[ID]","[SYS_Software]","SFW_LicenseID = " & ID) >= LIC_Quantity
Then

Keith said:
What have I done wrong here - it keeps failing saying error 2465 Access
can't find the field "|" refered to in your expression.

If (DCount([ID], [SYS_Software], SFW_LicenseID = ID) >= LIC_Quantity)
Then
(something)
Else
(something else)
End If

ID is a field in the table I want to perform the count on (SYS_Software),
SFW_LicenseID is also a field in this same table. I am trying to count
all
records where the SFW_LicenseID field matched the ID field of the current
record on the form I am working on. Then if this count is >=
LIC_Quantity
(in the table the form I am working on draws its data from).

Thanks


Dennis said:
None, you simply have to specifiy any one of the fields in the table or
query
as the first parameter of the DCount function.

:

Thanks

I might be being a bit thick here, but what's the significance of the
"Field" in the function? What field is it?

Thanks

Look up help on the DCount function

If DCount("Field","TableorQuery","Criteria") > xyz then
Do something
else
Do Something Else
end if

:

I want to run a query from code to count matching records in a
table.

How can I achieve this?

I then want to use this same code again within an IF statement, so
IF
the
count generated by the query is > than xyz then do something else
do
something else.

That kind of thing.

I have no experience of running queries in code so don't know what
to
do.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top