I've now decide to release this project as it now does All I want and thanks to all those from hear who helped with some of the solutions to my problems I'm happy with it.
There are some items that have been disabled this is only because I don't need them at the min but when added I'll update the file, The tables are as complete as can be although there will be addition for later versions
This version is for 2003 should you require 2002 please post a request for it.
There will be additional tables as this project develops plus as I don't really use a lot of reports I've only added what I need but if you have a suggestion for a report let me know I will be adding a few as things develop.
Thanks to Kent Telephones (http://www.k8t.co.uk) for hosting this project.
IMPORTANT READ THE GETTING STARTED FOR SETTING UP AND INSTALLING THE SMTP DLL WHICH IS REQUIRED FOR THIS PROJECT TO RUN.
I am able to make a form that correctly works lets the user enter records for 1-6, with the user pressing crtl+shft+; to give the correct starttime, and then at the end of the task, the user can press the same and register the end of the task.
My problem is that I can't work out how to calculate 7) as one needs to input some VBA code that converts date/time to numeric values, allowing you to calculate time difference. However, I simply don't know; a) the proper 'code' that will calculate the time different between 5) and 6). I saw that it was perhaps the dateDiff command? And b) where I would even put that code into, i.e. the exact location where one goes to and types the code into... Is it a query? Do you set it to a Calculated value in the table editor?
Not sure if this has been posted yet. Microsoft has released beta of Office 2007, this includes a new UI to many of the old office programs. For the time being you can get it here: http://www.microsoft.com/office/preview/beta/getthebeta.mspx
Beta 2 of Internet Explorer 7 has also been released: http://www.microsoft.com/windows/ie/default.mspx
And I know I've posted on this before, but just so it is in the same place, Visual Studio 2005 can be downloaded here: http://msdn.microsoft.com/vstudio/products/trial/
There are also many business-oriented programs in beta for testing. If you own a small business, or think your company may have a need ~ test it out, get a feel, for free.
I need to start using the database I've created (desktop database). However, I don't want my current version to be the final version.
Should I create another file to work in and use the old version for data entry? When I am ready to start using my new version, is it easy enough to transfer all the data from the old?
I am talking to a potential customer about doing some contract work using access. This would be my first contract using access and I was just curious what some of you are charging as your rate. I'm a strong developer but my experience has been in a full-time position so I'm not sure what contractors would get paid.
I have a form asking for a billing address and a service address. Many times these will be the same. I added a check box to check to populate the following fields; Service Address, Service City, Service State, Service Zip.
Fields that it will be coming from are the same, just named Billing instead of service. Tried expression builder and macro builder with no luck.
I have created a table for customer bills, and a table for customer profiles which contains rates associated with them specifically. The problem is that after a new rate change has been imposed I want to be able to look at an old bill, via a report, and have it reflect the "old" rate within proper historical context. I'm looking for suggestions on how to best manage ongoing rate changes while maintaining a billing rate history in my reports?
I am woking on a project full of nightmares. My latest, and the one I am currently unable to solve, involves trying to calculate the number of days between Billing Dates.
I have a table named "Date" - I didn't design this! It contains a field named "Date"
If I query for just that Field I get the following: Date 9/8/2005 8/8/2005 7/8/2005 6/8/2005 5/9/2005 ...
I need to figure out a way to determine the number of days between the bills so I can calculate the average cost per day.
It seems like it should be an easy thing using like the datediff function or something, but since they are in separate records (rows), I can't figure it out. I have tried and tried and tried.
I developed a finance tracker database for tracking project revenues and costs (forecast and actual amounts). I have a form where we enter a new Work Order in the DB. This Work Order form has a continous subform where we create all the billing periods needed for the life of the Work Order. Once the billing periods are created (opened) we can then add our revenue and cost forecasts for each period. We have a Billing Period lookup table that has our billing periods with their respective start and end dates (which usually begin around the last week of a month and end about 3 weeks into the next month).
For the purpose of this question, lets say we only enter the required WO_Number and WBS_Code (Composite key) and the Work Order Start_Date and End_Date. I want to click a button to runs some code to automatically create all the billing periods for which their start and end dates fall into the Work Order start and end dates.
For example, say a Work Order starts on 5/1/2013 and ends on 7/31/2013
The billing period dates in the Billing Periods lookup table are as follows: May-13 ---> 4/22/2013 - 5/19/2013 Jun-13 ---> 5/20/2013 - 6/23/2013 Jul-13 ---> 6/24/2013 - 7/21/2013 Aug-13 ---> 7/22/2013 - 8/18/2013
Then we would need the following billing periods created in the Work Order subform: May-13 Jun-13 Jul-13 Aug-13
I don't even know where to start on this. Is it possible to automate this process with the setup I have? If so, how would I structure the VBA code/logic to use the billing period lookup table and create entries in the subform for all the billing periods that fall into the duration of the Work Order.
The attached DB is a stripped down version with only the tables and forms needed for this problem.
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still shorttime but when you read the time within the code it comes out in AM/PM format. Also, when the defaul tiem should be #20:00:00# but this changes to #8:00:00 PM#
Weirdly though, when you just open the table, the times are in the correct shorttime format.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
I have a database that stores information for lab testing. Each time a tech does a "step" in the test process he logs it in the table, using an input form. There are different categories, for example preparation, testing, analysis, etc, and each of those steps take time. I have the form autopopulate the date and time with NOW() evertime the form is updated. What i want to do is calculate the time it takes to do each in days. I can easily get how many days it was from now since they logged the test, =NOW()-TestDateTime. What I want to do is get the number of days it took to do each step, ie the number of days between each event. Is there a way to do this?
I have an attendance database and I connect the time attendance machine db to my access db, what i am trying to do is to generate a report that shows the time in and time out for specific date. the type of attendance db is date/time.
Please see the attached screenshot db from attendance machine.
an also some time there is duplicate entry, I need to get the first and the last entry only for specific date.
Formatting issue regarding elapsed time calculated using DateDiff().
I understand that you can specify the output value for DateDiff(). In my case I have chosen "n" for minutes. Each result in my query shows the correct calculation in terms of minutes.
[PunchIn] = 11/23/2013 8:11:28 AM [PunchOut] = 11/23/2013 5:43:30 PM
Now when I try to format the result in terms of H:MM (be it in a form or a report) I get varied results. I'll illustrate an example below:
=Format(([ShiftLength]/60),"0") & "." & Format(([ShiftLength] Mod 60),"00") Returns 10.32 Not correct
=Format(([ShiftLength]60),"0") & "." & Format(([ShiftLength] Mod 60),"00") Returns 9.32 This is correct but I need my result to be in the form of a decimal such as my next example
=([ShiftLength]/60) Returns 9.53333333. Getting there but how do I have this result only show two decimal points 9.53?
I have a date/time text field on a form with the General Date format and a combo box next to it that has sequencial times as the row source (IE. 12:45 AM, 1:00 AM, 1:15 AM, 1:30 AM, ETC.) When the user chooses a time in the combo box, I want the time portion of the text box to be updated with the chosen time in the combo. I have tried a few things but cant seem to get it right.
so i created a system to have events booked, and i am trying to check time availability of the event room available, but i dont know what wrong. it either my query or vba code. i have attached the attachment,
I have a working dB which can calculate a shift duration and sum total all shifts worked within a period for the purpose of producing a labor report for payroll. I have successfully used the DateDiff function and converted the minutes to HH:MM on my form and reports. Now I want to calculate elapsed time for a specific period within a shift, I'll call it OtherHours and I am aiming to calculate a portion of time that meet the following conditions below. I am using field names of [PunchIn] and [PunchOut] and both are of type General Date.
IF [PunchOut] ISNOT Saturday,Sunday EXIT FUNCTION ELSE IF [PunchOut] ISNOT Between Midnight and 0559 hours EXIT FUNCTION ELSE DATEDIFF ("n", <MIDNIGHT>, [PunchOut])
My thoughts are to solve the DateDiff portion and then figure out how to apply the conditions within the IF statements.
I have a database consisting of two tables. One is "articles" and the other is "tasks". To put it simply, I would like to find how much time the article spends in tasks, but one article can have many tasks, and they often (but not always) overlap. Tasks have a start and end date field.
Wonder if someone can help please. I'm quite new to Access so please bear with me.
I have a data field in my database consisting of both a date and time.
I then have a form containing two fields where the user can type a 'To' and 'From' date to extract the records that they are interested in. The query behind this uses the 'Between[Enter The Date] And [Enter The Date]' coding.
The problem is that because the field contains a time it doesn't return any records when I run the query.
Can anyone offer a bit of guidance on how I could ignore the time part of the field perhaps by adapting the above.
Hi all, I havent' been around in a while (toddler, work, getting my BS degree and new baby coming in a week and a half) so I am hopefully not asking a previously posted question.
I'm suspecting my new work isn't accurately paying for the hours worked. I made a quick simple DB where I input my punch times in short time format. There's the typical In, Out for Lunch, In from Lunch, and Out. I've got it correctly adding the times so that it reports the total hours worked per day, though, in order for it to display correctly, that total is in short time as well.
The problem is, when I group a week together, as soon as the sum of the totals reaches 24 or more, it goes back to 0, as it's on the idea that there's only 24 hours in a day.
Hopefully, it's just a formatting issue or is there a function to convert the times (or just the sum) to number of hours?
I am trying to create an Access database where I can let the employees enter their time-in and time-out, at the end of their shift. I already created this function working, but running into another issue. Everyday, employees need to enter a break. Sometimes they take a break and sometimes they don't, if they work shorter hours. Thus, the break is not a default 30min and has to be enterd by the employee.
Below is the code I have to calculate the total hour:
txtCalcTime has the below code in control source: =Int([CalcTime]/60) & ":" & Int([CalcTime] Mod 60)
Control source for this form "FrmTimes" property, in which Txtcalctime resides:
QryTimes "QryTimes" query has the code below: SELECT TimeID, TimeIn, TimeOut, Brake, DateID, DateDiff("n",[TimeIn],[TimeOut]) AS CalcTime FROM EmpTimes;
This shows the total number of hours worked. But how can I make it to substract the break time entered by the employee in "txtbreak" on FrmTimes
I have finger print machine and i already connect to it and get all log.then i tray to get data and here is the code that im using
SELECT Format(CHECKINOUT.CHECKTIME,"dd/mm/yyyy") AS CDate, IIf([CHECKINOUT.CHECKTYPE]=I,Format(CHECKINOUT.CHECKTIME,"hh:nn:ss ampm")) AS StartTime, Format(CHECKINOUT.CHECKTIME,"hh:nn:ss ampm") AS EndTime, USERINFO.USERID, USERINFO.Name, CHECKINOUT.CHECKTYPE FROM CHECKINOUT INNER JOIN USERINFO ON CHECKINOUT.USERID=USERINFO.USERID WHERE (((Format([CHECKINOUT].[CHECKTIME],"dd/mm/yyyy"))='10/04/2014'));
I need a Select Query to display data on a form. When an order is appended to my table the field named Printed is updated with the time it was appended. When my form opens it needs to display the field Printed and a field I call MinutesFromPrint. This field needs to display the total minutes elapsed from the time in the Printed field to the current time. My expression is not working.
MinutesFromPrint: DateDiff("n",[Printed],Now())
Here are a couple of samples of my results when I run the query at 7:49 A.M.:
I have a column/field named [DateTaken] which contains test dates and times in the same cell. I am needing to find those with a test time less than 2:30 pm or <14:30pm.
