Time Import Problems
May 8, 2006
I have a series of times that I am importing from microsoft excell. When the data comes in, access is recognizing the data at a date, not a time. the format of the time is as follows 03:12:45. This represents 3 hours, 12 minutes, 45 seconds. I have mass amounts of information and need to calculate the total amount of hours worked. (basically I have times that are spent on various projects and I need access to do the math when I have several times in the database) any help would be great.
david
View Replies
ADVERTISEMENT
Jan 24, 2007
I have a default directory that will contain Excle spreadsheets. This spreadsheets have the name of the table i want them to create when they are imported in Access.
I want first to count and present to the user the number of the spreasheets. then import one name the table with the name of the spreadsheet and then process. When done move to the next one until there is no more in the directory...
How can i write this in VBA?
Can someone help me?
View 1 Replies
View Related
Dec 12, 2013
I am having trouble getting the following code to load data from my Excel file "Book.xlsx" into my table called "ExcelDataBook":
Sub btnExcelImport_Click()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "ExcelDataBook", _
"C:Usersdacampb1DesktopBook.xlsx", , "Sheet1!A1:B12000"
End Sub
The Excel file only has one sheet "Sheet1' and has only 10,504 rows but this can vary so I set it in the code for 12,000 rows. The sheet has only two columns "A" and "B" with the first row of the two columns labeled "Store" and "Amount" and my tabel has only two fields "Store" and "Amount".My error reads: Run-time error '2391': Field 'F1' doesn't exist in the destination table 'ExcelDataBook.'
View 3 Replies
View Related
May 21, 2007
For anyone that might be able to help me out, I'd very much appreciate it, as this is now number 2 stupid workaround that I'd like to resolve before I need counselling...;)
I have a Date/Time field that I'm importing into my database via .csv files. The field is setup like the example below in every .csv file:
4/2/2007 8:30:00 AM
Access keeps throwing errors and deleting all the field values in this field whenever I try to import. I know that Access does this when a Date/Time field includes data that is not delimited, but these field values seem to be perfectly formatted to me... what can I do to stop Access from chucking these on import?
Right now I'm just importing the data into a "text" field, and then changing it to "date/time" afterwards, but I'm worried that once the table gets too long I won't be able to re-index all those records anymore and my database will be useless since it depends on that field being "date/time" format...
All those better than I feel free to show me how and where I've been stupid:D
View 6 Replies
View Related
Jul 17, 2015
I have the following piece of code for importing the .csv files from a selected folder and then renaming them into 'imported & filename' if succesful and 'failed & filename' if import failes for whatever reason (bad formating, etc.).
The problem is that the first time it encounters a 'bad' file after another, instead of going again to the Error handler, the command
Code:
DoCmd.TransferText acImportDelim, , "Activitate", filename, True
gives me the default MS Access error, namely 2391. I would like it to go the the Error_handler again ang follow the course of actions and rename the files into 'failed' and 'imported'.
Here's the code:
Sub ImportActivitate()
Dim strFile As String 'Filename's
Dim strFileList() As String ' File Array
Dim intFile As Integer 'Number of files
Dim filename As String, sFullName As String, sFilename As String
DoCmd.SetWarnings False
[code]....
Also, how can I make the routine stop cycling through the files after I renamed them all?
View 3 Replies
View Related
Sep 17, 2013
I want to make a database of diseases (need to learn them for school and would like a serchable database on my smart phone for future reference).
Unfortunately spent a lot of time making hundreds of pages of word tables before i realized a database would be better. See attached image or pdf. Is there a way to import the tables to Access?
I need to preserve the hierarchic info in the nested bullet point lists. E.g. under treatment i might have a point called Acute treatment, with sub-levels, Step 1, Step 2, etc. with their own sub-levels. I need to maintain this relational hierarchic info.
View 3 Replies
View Related
May 23, 2006
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?
Any ideas?
This screen shot may help:
View 2 Replies
View Related
May 23, 2006
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 short time but the format is in AM/PM or medium time.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Any ideas?
View 1 Replies
View Related
Nov 8, 2013
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?
View 3 Replies
View Related
May 6, 2015
how to be able to enter time in access form the same way as if would be an access table (1p = 1:00 PM; 1.25 = 1:25 AM etc)
View 1 Replies
View Related
Apr 3, 2014
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.
View 4 Replies
View Related
Dec 3, 2013
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
[ShiftLength] =DateDiff("n", [PunchIn],[PunchOut]) = 572 minutes.
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?
View 6 Replies
View Related
Aug 29, 2013
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.
View 4 Replies
View Related
Apr 10, 2015
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,
View 5 Replies
View Related
Dec 1, 2013
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.
View 4 Replies
View Related
Aug 22, 2012
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.
View 8 Replies
View Related
Mar 6, 2007
Hi,
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.
Many thanks
Chris
View 2 Replies
View Related
Jan 9, 2006
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?
Thanks and Happy New Year to all.
-Jim
View 2 Replies
View Related
Jul 18, 2013
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
View 12 Replies
View Related
Nov 3, 2014
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'));
how i get end time due to CHECKTYPE]=O
View 1 Replies
View Related
Oct 9, 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.:
Printed MinutesFromPrint
2:35 60366554
5:07 60366402
View 11 Replies
View Related
Mar 19, 2014
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.
data looks like this:
8/22/13 4:23 PM
1/29/14 12:21 PM
1/28/14 3:27 PM
8/26/13 4:27 PM
[code]....
this is what I have come up with to extract the time component of data set so that I can then later, sort it by the time in a query.
JustTime: TimeValue([YourField])
JustTime: ("hh:mm",([DateTaken])) and or ("hh:mm",[DateTaken])
I get either invalid operator or invalid syntax errors trying both of these.
View 5 Replies
View Related
Aug 17, 2006
Hello buddies :D, do you have any idea how to make this work?
To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.
This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only.
([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)
How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif
View 10 Replies
View Related
Jun 7, 2006
I have a link table with a field in GMT time. I want to convert the times to local time in a query. I know that I should use DateAdd, but I am just wondering how to know summer and winter time. On the summer our local time is +2 GMT and on winter +1.
View 14 Replies
View Related
Apr 10, 2014
Any sample database where its about Time In and Time Out Attendance. It will just calculate the difference of Time In Time Out with employees Card Number.
The Time In and Time Out attendance is provided (no data entry), it is generated from other tool. I will just load it from an excel sheet to the access database and it will calculate and generate report. Default should be 9 hours per day, if they wont render 9 hours it will show in Difference column. Form will be like this:
ID Num: | Name: | Time In: | Time Out: | Total |Difference (missing hours)
12345 | Al Smith | 8.00am | 5:00pm | 9 | 0
54321 | Joy Pitt | 8.00am | 4.30pm | 8.30 | -0.30
View 4 Replies
View Related
Dec 9, 2004
Hello again,
I think my subject heading explains it. How would you add or subtract two short time formatted values and produce a short time value? Also, how would you change a short time value into number minutes. (i.e. 5:30 into 330) Thanks!
G
View 1 Replies
View Related
