Create a "Count" Window on a Form


B

Brodie Farrar

OK, So I use a database to track work orders, I want there to be a window on the "Work Order Form" that will display the number of completed work orders for today. How do I go about doing this?????
 
Ad

Advertisements

B

Bob Barrows

Brodie said:
OK, So I use a database to track work orders, I want there to be a
window on the "Work Order Form" that will display the number of
completed work orders for today. How do I go about doing this?????
Write a query to return the count of completed work orders for today and
display the result in a textbox.
 
B

Brodie Farrar

OK, So I use a database to track work orders, I want there to be a window on the "Work Order Form" that will display the number of completed work orders for today. How do I go about doing this?????
I did try the query, I just could not figure out how to get the info i wanted....
 
B

Bob Barrows

Brodie said:
I did try the query, I just could not figure out how to get the info
i wanted....
Ummm ... I'm not looking over your shoulder at your monitor ... just sayin'
 
B

Brodie Farrar

OK, So I use a database to track work orders, I want there to be a window on the "Work Order Form" that will display the number of completed work orders for today. How do I go about doing this?????
@bob, Thank you, I do know you are not looking over my shoulder, I thought maybe someone could provide some steps and info in order to get to where I wanted, But thank you for letting me know you are not standing over my shoulder, I feel safe now.
 
J

John W. Vinson

OK, So I use a database to track work orders, I want there to be a window on the "Work Order Form" that will display the number of completed work orders for today. How do I go about doing this?????
You can use =Count(*) as the control source of a textbox in the form header or
footer, or =DCount(<some appropriate expression for your database>), or use a
totals query.

Neither Bob nor I can give you the precise steps to follow, because you have
not posted enough details of your form or your tables to make it possible to
do so. If you would like a more specific answer, please feel free to post a
more specific question with enough details to enable us to help you.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Ad

Advertisements

B

Brodie Farrar

Thank you John, Please let me know what info you need,

I Have the a work orders table, which has a "Time Completed" Field with the date, I want to Count all completed work orders for the Current day, and display that total on the "work order tracking" Form.

The issue i run into is I can't figure out how to turn the Date/time into just date in the query, I am not sure how this process works?
 
J

John W. Vinson

Thank you John, Please let me know what info you need,

I Have the a work orders table, which has a "Time Completed" Field with the date, I want to Count all completed work orders for the Current day, and display that total on the "work order tracking" Form.

The issue i run into is I can't figure out how to turn the Date/time into just date in the query, I am not sure how this process works?
The way Access stores a date/time value is as a Double Float number - a count
of days and fractions of a day (tijmes) since midnight, December 30, 1899:

?Now(); CDbl(Now())
1/26/2014 5:06:16 PM 41665.7126851852

So to get all results from today you need all records between midnight last
night (which you can get from the builtin Date() function) and this coming
midnight. I presume you will not have any records where [Time Completed] is in
the future, so you could just put a text box on the form with a control source

=DCount("*", "[Work Orders]", "[Time Completed] >= Date()")

DCount counts records and takes three arguments: the first is what to count
(and * means to count all records that fits the criteria); the second is the
name of the table or query containing the records (change it to the actual
name of your work orders table, which I don't know); and the third is the
criteria, a valid SQL WHERE clause without the word WHERE.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
B

Brodie Farrar

GREAT! That worked, But i forgot one thing :(

I have "Stop Code" Where I want the count only when "Stop Code" = '00'

So your =DCount("*", "[Work Orders]", "[Time Completed] >= Date()"), But How do I have it just count with stop code 00?
 
J

John W. Vinson

GREAT! That worked, But i forgot one thing :(

I have "Stop Code" Where I want the count only when "Stop Code" = '00'

So your =DCount("*", "[Work Orders]", "[Time Completed] >= Date()"), But How do I have it just count with stop code 00?
Same way you would do it with a query: add another criterion. Assuming (again,
you don't say so I have to guess!!!!) that the datatype of Stop Code is TEXT
rather than NUMBER, you should be able to use

=DCount("*", "[Work Orders]", "[Time Completed] >= Date() AND [Stop Code] =
'00'")

A Text field criterion must be delimited with quotemarks, either singlequote '
or doublequote ". Since the third argument to Dcount already has doublequote
characters, it's easier to use singlequotes.

Side note: it's probably not a good idea to use blanks, and (especially)
punctuation if fieldnames. Blanks are meaningful delimiters in expressions, so
if you have a blank in a fieldname you MUST enclose the fieldname in [square
brackets], otherwise Access won't know that you intend Stop Code to be the
name of one thing - it'll think you're talking about Stop, and about Code, and
not know WHAT to do. I'd use single words with "camel case" - StopCode,
TimeCompleted - or use underscores instead of blanks - Stop_Code.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
B

Brodie Farrar

OK, so I tried that code, and I get and error, it just says #error in the text field,

Could this be due to the fact that [Stop Code] is a look up field?
 
Ad

Advertisements

B

Brodie Farrar

Do i need to reference [Work Order Tracking] again for the second condition?
 
J

John W. Vinson

OK, so I tried that code, and I get and error, it just says #error in the text field,

Could this be due to the fact that [Stop Code] is a look up field?
Yes.

See http://www.mvps.org/access/lookupfields.htm for a critique of this
misfeature. The Lookup Field APPEARS to contain a text string ("00" perhaps).
But it doesn't! It contains a numeric ID concealed from your view by the combo
box. I would suggest NEVER using the Lookup Wizard unless you fully understand
normalization, referential integrity and the workings of combo boxes; and if
you do understand all that, you won't NEED the lookup wizard.

If you're stuck with the lookup wizard, you'll need to either determine what
numeric ID Access assigned (under the hood, out of your view) to the "00" stop
code and use that number, without the singlequotes; or base your form on a
Query joining your Work Orders table to the stop code lookup table (and no, I
don't know the name of that table either).

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
B

Brodie Farrar

John,
Thank you so much, I got it, I had set up the ID's so that fixed it, again thank you, i really appreciate you working with me on this.
 
B

Brodie Farrar

Sorry to bother you one more time, But I would like to turn this:

=DCount("*","[Work Order Tracking]","[Time Complete] >= Date() AND [Stop Code] = 1")

into one for THIS MONTH, THIS WEEK, and THIS YEAR

Can you help???

Thank you, and again, sorry for bothering you.
 
B

Brodie Farrar

OK, So I use a database to track work orders, I want there to be a window on the "Work Order Form" that will display the number of completed work orders for today. How do I go about doing this?????
I am still keeping the original, but now thought it would be great to have the others, if you can assist, I have tried, can't seem to get the code quite right.
 
Ad

Advertisements

J

John W. Vinson

Sorry to bother you one more time, But I would like to turn this:

=DCount("*","[Work Order Tracking]","[Time Complete] >= Date() AND [Stop Code] = 1")

into one for THIS MONTH, THIS WEEK, and THIS YEAR

Can you help???

Thank you, and again, sorry for bothering you.
Sure; just change the criteria. But.. what do you mean by "this week"? Since
midnight last Sunday morning, Monday morning, some business week? Do you need
to be concerned with holidays? Do you need this as of now, or do you (or will
you someday) need to count the orders as of some point in the past?

You can use the DateSerial() function to get a point in time: e.g. the first
day of the current year is

DateSerial(Year(Date()), 1, 1)

and the current month

DateSerial(Year(Date()), Month(Date()), 1)

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
J

John W. Vinson

B

Brodie Farrar

A week will be Monday to Sunday--No conditions apply(ie: holidays)

a month will be the calendar month
 
Ad

Advertisements

B

Brodie Farrar

also, I see that you showed how to get current year, but can you show it in the existing formula, I can't seem to wrap my brain around where it goes.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top