Assign the result of this simple query to a variable

[Guest]

I have a query that will return the count of evente corresponding to a
specific employee based on their employee ID number.

This is the SQL from the query:

SELECT Count(*) AS Expr1
FROM tblCME_Data
WHERE
(((tblCME_Data.EmpIDNo)=[Forms]![frmEmployeeBudget]![txtEmployeeID_No]));


If I run the query it works just fine. Instead of "running the query", I
want to place this SQL into my form's load event and have the result be
assigned to a variable.

I can't seem to find the syntax. I've even tried a DCount() funstion but I
can't seem to get that to work either.

Any help is appreciated. Thanks...

 

[fredg]

I have a query that will return the count of evente corresponding to a
specific employee based on their employee ID number.

This is the SQL from the query:

SELECT Count(*) AS Expr1
FROM tblCME_Data
WHERE
(((tblCME_Data.EmpIDNo)=[Forms]![frmEmployeeBudget]![txtEmployeeID_No]));

If I run the query it works just fine. Instead of "running the query", I
want to place this SQL into my form's load event and have the result be
assigned to a variable.

I can't seem to find the syntax. I've even tried a DCount() funstion but I
can't seem to get that to work either.

Any help is appreciated. Thanks...

The query is already doing the counting so code the form's Load event:
Dim intMyCount as Integer
inMyCount = DLookUp("[Expr1]","QueryName")

The value returned will only be available in the Load event. If you
need it available elsewhere, place Dim intMyCount up in the
Declarations section of the code window, instead of the Load event.

You might want to name this column with a more meaningful name than
"Expr1".

 

[Guest]

I have a query that will return the count of evente corresponding to a
specific employee based on their employee ID number.

This is the SQL from the query:

SELECT Count(*) AS Expr1
FROM tblCME_Data
WHERE
(((tblCME_Data.EmpIDNo)=[Forms]![frmEmployeeBudget]![txtEmployeeID_No]));

If I run the query it works just fine. Instead of "running the query", I
want to place this SQL into my form's load event and have the result be
assigned to a variable.

I can't seem to find the syntax. I've even tried a DCount() funstion but I
can't seem to get that to work either.

Any help is appreciated. Thanks...

The query is already doing the counting so code the form's Load event:
Dim intMyCount as Integer
inMyCount = DLookUp("[Expr1]","QueryName")

The value returned will only be available in the Load event. If you
need it available elsewhere, place Dim intMyCount up in the
Declarations section of the code window, instead of the Load event.

You might want to name this column with a more meaningful name than
"Expr1".

Thanks a bunch. It worked just fine. I was just making that WAY more
complicated than it needed to be.

The column "Expr1" was generated by Access when the query was created. What
I need it for, the name "Expr1" will work just fine.
Thanks again for the help