Solved

Adding total dates in a text box

Posted on 2013-01-03
15
227 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.
0
Comment
Question by:gigifarrow
  • 8
  • 4
  • 3
15 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38740246
Try this:
=DCount("*"," tblTempleFuelCellOnly","[Unit] = 'FY10' And Not IsNull([InShopDate])")
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38740275
But what if they finished working on the vehicle? The above will include those in the count.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38740292
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38740302
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38740326
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38740358
oops...

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

Author Comment

by:gigifarrow
ID: 38740441
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!!!
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38740446
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38740496
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?
0
 

Author Comment

by:gigifarrow
ID: 38740497
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])")
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 38740660
What the heck...

Try this.

As you can see doing this for *every* Unit will be a lot of work.
To my expressions, you would just add the criteria:
    " AND " & "Unit='FY10'"
Or
" AND " & "Unit='FY11'"
...or whatever...

Have fun...

Based on your experience here, I am sure you can see what I did here and adapt as needed.

;-)

JeffCoachman
Database19.mdb
0
 
LVL 29

Expert Comment

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

Author Comment

by:gigifarrow
ID: 38741179
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38741523
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...
0
 

Author Closing Comment

by:gigifarrow
ID: 38745999
Jeff thanks for taking the time to go in depth ! you are so thorough.  Great job!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now