Assign the result of this simple query to a variable

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
Utf
10/1/2007 8:56:04 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
2135 Views

Similar Articles

[PageSpeed] 51

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

Similar Artilces:

assign a shortcut key in access
I want to assign a shortcut key in a form like when a user presses CTRL+S a code should run like Go To Control combo1 and me.combo1.dropdwon any way to do this? You need to yous the AutoKeys macro. Here's a very good explanation of how to do that! http://www22.brinkster.com/accessory/macros/002.shtml -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200707/1 missinglinq via AccessMonster.com wrote: > You need to yous the AutoKeys macro. Here&...

NTDSNoMatch Query
I'm starting to prep our 5.5 directory for migration to Exchange 2003. We already have a Windows 2003 domain in place. I've just run the Exdeploy tool with the ntdsnomatch test and it has picked up on three mailboxes that it says need altering - they all relate to Trend Scanmail as they all use the Exchange Service Account as their Primary NT account. For things like Resource Mailboxes, where we have no Primary account but multiple accounts are given access via the Permissions tab I would expect the ADC to create a disabled user in the Active directory. What I'm not clear ...

Assigning values from a sheet to an array
Can anyone explain why I get a runtime error with the code below.MANY THANKS! Option Explicit Option Base 1 Sub testing3() Dim Vt() As Double Dim j As Long Dim i As Long ReDim Preserve Vt(1 To 40) For i = 1 To 40 j = Int(Rnd() * 1100) Vt(i) = Worksheets("data").Cells(1, j) Next i End Sub ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! http://www.ozgrid.com/Services/excel-software-categories.htm ** Because j is greater than 256, the maximum number of columns in Excel. Alan Beban Ann...

General Query
Gidday all, I was just wondering, we have a large product database that I have designated mostly into kits (of the installations we do). To clean it up and make it easier to view what is required, I have set many of the single items as 'Inactive'. Besides the fact that this probably isn't what 'inactive' status was intended for, will there be any affects to the kits, and quoting using the kits while these Items are 'inactive' or is it purley cosmetic. ...

No "Assign Macro"
In the Excel help files it indicates that you can execute a pre-recorded macro from a command button. It states to first record the macro, then create the button, then right click on the button to get the shortcut menu and choose Assign Macro. I have tried this in both Excel 97 and Excel 2002 and in both cases I do not have any such item as "Assign Macro". Am I missing something If not by this elusive shortcut menu item, how can I assign the pre recorded macro through code? Aaron, It may be that your working with a command button from the "Control Toolbox" instead of...

Simple way to count by month?
Access2000 Hi I have a table that is just a list of serial numbers and dates that they were sold. Is there a simple way to build a query that will just list the total serials numbers by month, i.e. Dec-07 - 566 Nov-07- 453 Oct-07- 343 Each serial is on its own row. Getting the number sold by day is simple, but grouping and suming these to months has me stumped. Thanks - Steve Solved it. I used this if anybody else is looking SELECT Format([SQL_DELIVERY_DATE],"yyyy mm") AS MonthSold, Count(*) AS Total FROM tbl_sold GROUP BY Format([SQL_DELIVERY_DATE],"yyyy mm") ...

Using variables in activities generated by workflow
Hi Is is possible to use a variable (taken from the calling object) in the title of a workflow activity? For example, if I write a workflow rule to create a follow up call, I have no way of knowing to whom that call is until I drill down into the activity. It would be nice to have the title of the activity something like Follow up call to &contact.fullname to show in the activity list as Follow up call to John Smith. -- Thanks Gill Opsis www.opsis.com.au Gill, You actually have most of the syntax. Try the following: &contact.fullname; These slugs are unsupported, but the...

Serial Numbers auto assigned
S/N are being auto assigned when the SOP Setup Options DO NOT have "auto-assign serial numbers" marked. We do NOT want to use a separate fulfillment process. Up until a few months ago, everything was fine. Now, when a PO item, assigned to a sales line item comes in, the s/n is autoassigned. How can I get this to go back the way it was? Below is the way we have had our SOP set up for eight years - why has it changed? Set-up>SOP>ORDER Allocate by: Doc/Batch X Transfer Order to BO / Transfer Order to Invoice Options: X Allow repeating documents _ Use Separate Fulfil...

No Assigned Licenses
We have a total of 5 licenses. However only three users can access the CRM applications. If any other user tries to access the application we get an error message stating No Assigned Licenses Any suggestions? Thanks. Matthew You have to actually go assign the users licenses with the CRM, Log in to CRM as administrator, Click Settings from the Left menu, Click Business Unit Settings, Click users, Click the user you want to assign a license to, then from the popup click license from the left menu, Click Manage Licenses and add a license to that user. Just to warn you do not remove the l...

How to customize the numbering in a query?
I want to customize the numbering in a query. In other word, I want to make customized numbering in a column in query but as formatted below: 001 002 003 … … etc any help please? Here's an example using a Transactions table which numbers the transactions sorted by date: SELECT FORMAT(COUNT(*),"000") AS RowCounter, T1.TransactionDate, T1.TransactionAmount FROM Transactions AS T1 INNER JOIN Transactions AS T2 ON ( T2.TransactionID <= T1.TransactionID OR T2.TransactionDate <> T1.TransactionDate) AND (T2.TransactionDate <= T1.TransactionDate) ...

help designing query to get results
In my database of employees I have three date/time fields - Agency Start date, Rehire date (a lot of our employees only work part of the year) and Separation Date. If I wanted to show only the employees who have either started, were rehired or separated in a certain time frame, how would I do that? Example: Which employees started or were rehired or separted between july 1 and july 30? -- JoAnn In query design view, enter the date range in the Criteria row under the first date field. Below the Criteria row, you'll see another one marked Or. Enter the same date range...

Need result not formula in cells
Hi everyone! Using E02 on XP. Having fits over this, can't figure out what is happening. Have a spreadsheet and want all of the records in Column AG to 'read' the contract number contained in cell G1. First record is in row 4, so in AG4 I typed =G1. Worked fine. Did a Fill;Down, realized I wanted =G1 in each cell so I tried to key that into AG5 and all I get is the formula (=G1) rather than the contract number. I've had that same problem with other formulas. My column is set to text, and I've tried changing the cell formats but to no avail. It has GOT to be ...

Trouble with autonumber field in a query
I am running Access 2003 and am trying to create a query with an autonumber field. I have two tables in the query each with 7 fields. I have 3 of the fields linked because I only want to see the values in the query if these 3 fields are equal between the two tables. I was trying to utilize an example I saw on: http://www.lebans.com/rownumber.htm To help me create an autonumber field in the query, but I get an error message that tells me "Undefined function 'Serialize' in expression" Is this because this orignal database from www.lebans.com uses an older version of Acces...

Assigned task
Hi, we are receiving emails from our bosses with attachment of our schedule task which we manually enter into our outlook task folder, is there a way or how can we automatically accept assigned task to integrate to our task folder. We are using outlook2000 under windows98 the outlook 2000 version is part of the office 2000 package install on out PC's. TIA Is your boss sending the tasks to you using Outlook Rich Text Format? If not, have him start. Also, how is he assigning tasks? Using the Assign Task feature or forwarding the task to you? -- Milly Staples [MVP - Outlook] ...

get data from a table not in a query
Hi all. I have the following issue: I have a query with the following tables: WCust, WFees, and RateType. The WCust contains customer name, address, etc., WFees contains vaious fees paid, and RateType contains 3 different rate types and the rates specific to each type. The RateType table stores rates for retail customers. If type "B" then 20rate = .75 and if type "T" then 20rate = 1.25. My issue is that the wholesale customer sells to both types "B" and "T". So when using a form called WCustFees and entering total cartons sold to type "...

College Assignment Woes
Hello, I would GREATLY appreciate it if someone could guide me in some kind of direction. I have been given the following data: On a new sheet, named Salary Table, starting at cell A1, build the following table: Name Current Salary Percent Increase Effective Month 2004 Compensation. My exact problem is that I must createa formula that can be used for all to calculate the amount to each employee for the year 2004. The increases take place on the first day of the month specified. This means that if increase comes in July, the pay is six months at the old rate and six months at the new rat...

dupliates name in a query
Hello, I have created a query from a table for my employee data base, the query is dupilicating the names. The name only exits once in the table but i can see a name that exits 3 or 4 times in the query. Please help Tia On Sat, 28 Nov 2009 11:45:48 -0800 (PST), Tia <tia.abdelkarim@gmail.com> wrote: >Hello, >I have created a query from a table for my employee data base, the >query is dupilicating the names. >The name only exits once in the table but i can see a name that exits >3 or 4 times in the query. > >Please help > >Tia Please he...

Simple sum won't work
I am creating a report that will list the cost of a number of items sorted only by the date of the request. At the end I want the total cost of all requests. I am using: (=sum[cost]) in a text box in the footer. It keeps giving me errors. What am I missing? There is not even a full page on the report. I am placing the sum in the report footer. I have made it larger and am putting it above the default page and date fields. Ralph On Tue, 3 Jul 2007 11:40:54 -0500, "Michael" <michael@kisielrudnik.com> wrote: >Which footer are you placing the sum[cost] in? ><...

Change lookup range depending on result of 2nd lookup
Hi, am trying to vary the name of the lookup range depending o the result of a 2nd lookup -- NYC =vlookup(A1,range1,4,0) LA =vlookup(B1,range2,4,0) .. .. .. SFO =vlookup(C1,rangeXXX,4,0) Rather than having to manually edit the ranges I would prefer excel to lookup the ranges by something like NYC =vlookup(A1,vlookup(A1,RangeTable,2,0),4,0) RangeTable: NYC range1 LA range2 SFO rangeXXX Have tried to nest two vlookups as above but am having no luck Any suggestions? Thanks, sab_ya Of course not. You're saying lookup a value in ...

Some pre-sales queries....
Hi, Have a few queries..... Keywords - Is there any way to associate keywords with a customer and then do a mailshot based on a keyword criteria? Calendar availabilty - We have a customer who, when assigning a task to a colleague, would like to be warned if that task clashes with another item in their calendar. Also, the would like to be able to block a day - meaning, a user can "block" themselves out for a day and no tasks etc can be assigned to them on that day. Many Thanks Matt Matt: Keywords: You can create a custom field for the account or contact and populate it...

Assigning Records
When a Microsoft CRM Record is assigned from one Microsoft CRM User to another, the Microsoft CRM User who assigned the record will maintain share privileges on that record once it has been assigned. The ownership of that record will be changed to the Microsoft CRM User the record was assigned to, but the User who assigned the record (the previous owner) will still maintain share privileges on that record. (KB861702) We have a situation where we our system reassigns accounts to different users quite often. We would not like the previous owner to have share privileges on that record. It...

assign
How do I make every thin in a column that is not >4000 assigned the "0" and every cell >4000 assigned the "1" (and ignoring the letters)? column might look like 4002 AB009 4001ab 2004 0078 45 -- Message posted via http://www.officekb.com Are there always just 2 letters (or none)? On Tue, 07 Jun 2005 17:32:03 GMT, "jeremy via OfficeKB.com" <forum@nospam.OfficeKB.com> wrote: >How do I make every thin in a column that is not >4000 assigned the "0" and >every cell >4000 assigned the "1" (and ignoring the letters)? &g...

Recovering emails lost as a result of removing user account in Win 98
A few weeks ago, I have made some changes to my friend's Win98 OS to d away with the login popup (where u press enter or cancel when u have t key in your username. Both will bring you to the Desktop of win98) Can't remember the details exactly, but it does include changing th Windows Family Logon or something similar, and then changing som passwords for the computer, including the screensaver, renaming th extension to the existing password for the user to .old extension. ( searched online and followed the instructions there) I didn't realise that after that, some data the user wa...

Union query garbles ID field
I can't figure out why this happens. In some of my union queries, an ID field gets garbled and displayed as a non-western character, or a box shape of some sort. It's like it's encoded? Does anyone know why this might happen? I can post back with table & query descriptions. Thank you!! Matthew Here's my structure, simplified: tblContacts *ContactID LastName .... tblAffiliations *AffiliationID ContactID Title .... qryA: SELECT tblAffiliations.AffiliationID, tblAffiliations.Title, Null AS ContactID, Null AS LastName FROM tblAffiliations; qryC: SELECT tblCont...

Queries in BP
Hello: Is it possible to create queries in Business Portal that pull columns from views that users create in SQL Server 2005? I have created a few SQL views based on data from payroll historical tables, and I have created some SmartLists in SmartList Builder based on these views. I was hoping to be able to pull in some columns from these views into BP queries. Is it possible to do this? Thanks! childofthe1980s ...