We help IT Professionals succeed at work.

Adding total dates in a text box

gigifarrow
gigifarrow asked
on
255 Views
Last Modified: 2013-01-04
I have a table called "tblTempleFuelCellOnly".
When a vehicle is put in shop to be worked on it is given a date called "InshopDate" and vehicle unit and a serial number is also Entered.

If the vehicle is in the parkinglot and not getting worked on it is not given "InShopdate" but a unit name and a serial number.

(I need to find out how many of a certain unit are in shop being worked on.

My strategy is to add the total of a certain unit with a "inshopdate" minus the total of all of a  certain unit. So this is what I have But I keep getting a error. Which just says #error

=DCount("[InShopDate]"," tblTempleFuelCellOnly","[Unit] = ('FY10')")-Nz(DCount("[Unit] IN ('FY10' ]"," tblTempleFuelCellOnly"))

Now if there is a better way to this by all means expert please let me know. By the way this is hard coded in a text box.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Try this:
=DCount("*"," tblTempleFuelCellOnly","[Unit] = 'FY10' And Not IsNull([InShopDate])")
CERTIFIED EXPERT

Commented:
But what if they finished working on the vehicle? The above will include those in the count.
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Then we also need to know the datatypes of each fields, as this will effect the syntax (Double quotes versus single quotes)

=Dcount("InshopDate"," tblTempleFuelCellOnly","[Unit] ='FY10'")

This should only count Records where Inshop has a value and Unit=FY10

Is this what you wanted?
    "I need to find out how many of a certain unit are in shop being worked on."


JeffCoachman
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Please note IrogSintas post as well, as there may be somethings here that need clarification.

Remember that we are trying to address your question as posted.

If it were me, I would simply add a "Inshop" yes/no field to the table instead of relying on a Date/No Date field to determine if it is in the shop or not.

Then the syntax would simply be:
=Dcount("Inshop","tblTempleFuelCellOnly","Inshop=True")

But again, there may still be somethings that need clarification...

JeffCoachman
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
oops...

I mean like this to see inshop (Yes) for a certain unit (FY10)
=Dcount("Inshop","tblTempleFuelCellOnly","Inshop=True" & " AND " & "Unit='FY10'")

Author

Commented:
good question! I didnt think of that. "What if they finish working on the vehicle?". All the answers they want are based on dates.


InShopDate=Date working on(date data type)
DateCompleted=when they finished with the vehicle(date data Type)
DateReturned= when the vehicle is picked up.(date Data type)
Units= FY10, FY9, 278Th (Text Fields)

From this information they want to know the following:

1. Total Onsite (meaning both ones not in shop and the ones are in the parking lot)
2. Total Awaiting shop(meaning  there in parking lot  but  have no date yet that they are in shop)
3. Inshopdate(meaning being worked on but not completed yet)

So they can have a inshop date and date completed which would mean it wouldnt be counted as inshop. Sorry guys wasnt thinking about that.

Again thanks for all your help experts!!!
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Sorry for the rapid fire posts...

Actually you could keep this in a date field (because you need to track the date it went into the shop...)
So my original syntax still might be an option...

And also consider IrogSintas observation that a "DateOutOfShop" (or "DateCompleted") field may also be needed.
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Then I see a need for 4 dates:

DateDroppedOff (ex: 1/1/2013)
DateInShop (ex: 1/3/2013)
DateOutShop (ex: 1/5/2013)
DatePickedUp (ex: 1/10/2013)

From these you can get any statistics you want.

So what is your one direct question here?

Author

Commented:
So then to find the total in shop I need to find out if complete is null also? Because it can have a in shop date and also a complete date. So I would need to find out if there is a date for complete and if there is not then count the inshop date?

=DCount("*"," tblTempleFuelCellOnly","[Unit] = 'FY10' And Not IsNull([InShopDate])")- DCount("*"," tblTempleFuelCellOnly","[Unit] = 'FY10' And Not IsNull([isComplete])")
MIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
@gigifarrow,
Correction to your query.
=DCount("*"," tblTempleFuelCellOnly","[Unit] = 'FY10' And Not IsNull([InShopDate]) And IsNull([isComplete])")

Author

Commented:
Thanks jeff, but I dont have a Id number so I put this

=DCount("tblTempleFuelCellOnly","IsNull(DatePickedUp)" & " AND " & "Unit='FY10'")

and I get     ?error?



And fyed when i try your code
=DCount("*"," tblTempleFuelCellOnly","[Unit] = 'FY10' And Not IsNull([InShopDate])")

I get an #error also
MODKitProduction.accdb
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Check youir syntax:
=dcount("NameOfField","NameOfTable","YourCriteria")
Your syntax is:
=DCount("tblTempleFuelCellOnly","IsNull(DatePickedUp)" & " AND " & "Unit='FY10'")
...Not sure where you got that syntax from.

...But the sample file I posted seems to do what you were asking...

Author

Commented:
Jeff thanks for taking the time to go in depth ! you are so thorough.  Great job!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.