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?????
B
Bob Barrows
Brodie said:
Write a query to return the count of completed work orders for today and
display the result in a textbox.
B
Brodie Farrar
I did try the query, I just could not figure out how to get the info i wanted....
B
Bob Barrows
Brodie said:
Ummm ... I'm not looking over your shoulder at your monitor ... just sayin'
B
Brodie Farrar
@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
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
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?
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
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?
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?
Could this be due to the fact that [Stop Code] is a look up field?
B
Brodie Farrar
Do i need to reference [Work Order Tracking] again for the second condition?
J
John W. Vinson
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.
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.
=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
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.
J
John W. Vinson
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
Ummm... "the others"?
You're asking a lot of our telepathy.
--
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
A week will be Monday to Sunday--No conditions apply(ie: holidays)
a month will be the calendar month
a month will be the calendar month
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.