Assign the result of this simple query to a variable

  • Follow


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




0
Reply Utf 10/1/2007 8:56:04 PM

On Mon, 1 Oct 2007 13:56:04 -0700, Apex_RTX wrote:

> 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".
-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
Reply fredg 10/1/2007 10:57:33 PM



"fredg" wrote:

> On Mon, 1 Oct 2007 13:56:04 -0700, Apex_RTX wrote:
> 
> > 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".
> -- 
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail



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
0
Reply Utf 10/2/2007 12:43:03 PM

2 Replies
1665 Views

(page loaded in 0.063 seconds)


Reply: