Subtracting 12 Hours From A Date-time Field

Nov 13, 2007

I am trying to subtract 12 hours from a time-date field when the payperiod does not equal 01.

in a query I have:

work date: iif([payperiod]="01",[StartTime],[starttime]-#12:00:00#) - Access added a PM before the second hash so it looks like this:

iif([payperiod]="01",[StartTime],[starttime]-#12:00:00 PM#)

When I run the query I get a message box: "syntax error (missing operator) in query expression '00:00:PM#,2))". I am unable to open the query to correct the error. I can cop the unaltered query from a back up database.

My question is how do I subtract the 12 hours.

Thanks

Steve

View Replies


ADVERTISEMENT

Subtracting Time Over 24 Hours?

Mar 27, 2006

Any Help!

I have fields that are time formated 99/99/99" "00:00;0;_ and I want to subtract them.

I need to get the results in two forms; first in Days and second in Hours. I have used the obvious subtract one from the other and it gives me days in a rounded numbeer, but the hours is not working out.:confused:

DB

View 4 Replies View Related

Subtracting Date/time Field From Integer Field

Jan 2, 2007

Hello everyone, I'm encounter a problem trying to write a code.

EX:
Date Months_to_credit Final_result
01/31/06 4 10/01/05
02/28/06 6 09/01/05
03/31/06 8 08/01/05


Does anyone have any ideas how to to substract Months_to_credit from Date and to return a date in the Final_result field?

View 1 Replies View Related

Subtracting Time Without Date Diff

Jan 23, 2008

Hi I have two fields one which represents the time participants went to sleep, one when they woke up however I dont have any dates - it refers to what they do usually. Is it possible to minus sleeptime from waketime to determine duration of time in bed without dates? Or do I assign dummy dates to use date diff function?

View 4 Replies View Related

Time Keeping DB - Best Field Format For Working Out Hours Worked

Jun 18, 2006

Hi, I am creating a time keeping db.

Before I jump in and start creating a way to record times worked I thought it best to ask for advice.

The db is created and the last part is to capture by member:
Basically I have the following fields:

MemberID
Job Type
Date of Job
Time Started
Time Finished

From the data captured I need to create a report to show month and ytd hours worked.

It would be easy to ask the user to calculate the hours worked and input the number of hours, however I would like to capture start and finish times.

Can anyone give me any pointers on the best field types and field formats for capturing times which would then make it easier to work out hours worked.

Thanks in advance

Darrell.....

View 1 Replies View Related

Subtracting Time

Jun 20, 2006

I have two times I need to subtract in a query. When I subtract these in the query I get a decimal point. How can I convert this data. Thanks

View 1 Replies View Related

Subtracting Military Time

Oct 21, 2005

My new job requires me to use access so I'm learning as I go. :confused:
I have already used previous threads from this forum to answer another problem I encountered. Thanks for providing this forum to the public!

I am attemping to run a query that will subtract military time. (I am not allowed to change the format of the times in the original tables. )

Example:

Column A Column B
1111 6.5

I need 1111 - 6.5 in Column C

View 4 Replies View Related

Subtracting Time Fields

Mar 26, 2005

Before I start implementing this, I would like to know if it is possible to do in principle

I want two fields, say TimeStarted and TimeEnded. I then want to subtract the two from one another to see how long the task took.

Is this possible or would i be wasting my time trying it?

View 1 Replies View Related

Adding And Subtracting Short Time Values Together/changing Short Time Into Minutes

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

Forms :: Update Time Portion Of Date / Time Field

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

Ignore Time In Date And Time Field

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

Sort By Time Only In A Date / Time Field

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

Time In Excess Of 24 Hours

Apr 10, 2006

Looking to have a time field that a user can enter how long they worked an issue. Format should be ##:##, but I'm afraid that if I use text, calculations will be impossible.
How does someone do this if it's expected that the value will frequently go over 24 hours?

View 5 Replies View Related

Hours Time Problem

Jun 27, 2006

I want to create a query that will calculate the following:

When a user enters a start time and an end time in 2 seperate fields (eg. 06:00 and 08:00) I want to automatically return how many hours fall between 00:00 to 01:00, 01:00 to 02:00, 02:00 to 03:00 etc through the 24 hour clock.

To give you a background why I want this, I'm creating a labour planning database that calculates the nuber of people available each hour in a warehouse. So when a user inputs 06:00 to 08:00 for overtime work I need the database to tell me 1 hour falls between 06:00 to 07:00 and 1 hour between 07:00 to 08:00.

I'm really struggling to come up with a solution to this one, any help would be appreciated.

View 1 Replies View Related

Need Help With Time/ Hours Worked Calculation

Jan 22, 2006

Hi Guys

I need to create a method of calculating -week number,hours worked per week and a leiu hours total.

Explanation

Users enter their worked hours per day. I need to calculate the total hours worked per user per week and if this is greater or less than their contracted hours a record of this figure over a continual time period (or year).

Variables :- Time worked,Holiday hours,Leiu hours taken,Sick leave, Other authorised leave.

I realise that i'm asking on a lot but working for a charity has financial limits.

Attached the basic database-so far

Thanks in advance for your help

Row

View 4 Replies View Related

Representing Time In Hours And Tenths

Mar 13, 2006

I created a time clock module where you input the time in and time out and it takes the difference of the two to give total time worked. However the boss wants the time worked represented in hours and tenths of hours not minutes. How can I take the time, seperate the hours...find out how many tenths of an hour worked and then add the hours and tenths together.

View 2 Replies View Related

Total Time On Hours And Minutes

Jan 29, 2007

Hi,
I am trying to show the time difference between 2 times but the calculation is not working correctly.

First I work out the totaltime on mins between 2 dates using datediff, then I am trying to convert to hours and minutes like :
Total_Time: Format([Sumoftotaltime_mins]/60,"00") & ":" & Format([SumofTotaltime_mins] Mod 60,"00")

This is working until I have something like :

03:00
15:45
25:20

this should work out as 44 hours and 5 mins but for some reason it is showing as 43 hours and 5 mins.

why would this be ?

View 4 Replies View Related

Time Exceeding 23 Hours 59 Minutes

Nov 2, 2004

Hi,
I have a bit of a problem. I am making a program in access 2002. In my program you should punch in hours:minutes, this is for keeping track of how long a job has taken to perform. The problem is that it could exceed 23:59, it could take up to a week or even more. In my database we have already put in a lot of info where it doesn't exceed the 23:59, I would like to keep this posts and just to put in new ones that I could use further on when I am about to take out reports, where I have to calculate time. Is there someone who knows what to do ??

Regards

Greger

View 6 Replies View Related

Calculation For Time X Hours In Table

Nov 19, 2011

I need to calculate in the tbleJobs table, the amount of the jobs total amount and place the result in the JobsTotalAmount field.

This has to be a manual calculation. Not an on event calculation.

Data is coming from two table:

tblLimos has the hourly rate.

tblJobs has the start time, the finish time and the total amount field.

View 6 Replies View Related

Storing Time Greater Than 24 Hours

Nov 28, 2011

I am using a database and need a field capable of storing times that will be greater than 24 hours.

I understand there is no "ready made" way of dealing with this in Access, so what's the best way?

The catch is I am using VBA code in Excel to populate the table, the value comes from a cell (in Excel formatted as "[h]:mm").

View 2 Replies View Related

Queries :: Date Field With Time - Query For Date Only And Get All Records

Apr 26, 2013

I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.

View 11 Replies View Related

General :: COMPARE DATE Variable To DATE / TIME FIELD

Feb 18, 2014

In VBA, how to compare date variable with date/time field from table, as when defined the data type in table, date/time option is the only choice for date data type even though I don't want the time portion.

View 13 Replies View Related

Subtracting Months From A Date In Query

May 30, 2006

I have a field called DATE_END_DEERS which is in a format of YYYYMMDD (20060530). I need to run a query that shows 6 months subtracted from this date. I can never get an answer that is even close. Can someone please help? ACCESS2000.

View 3 Replies View Related

Date Query - Subtracting Months

Apr 10, 2008

Hello Everyone,I have a query where I am trying to pull records where the user took a class that is 2.5 years old or newer based on their license expiration date. Can someone tell me what I'm missing/doing wrong?SELECT tblCE_Details.LIC_NUMBER, tblCE_Details.Course_Code, tblCE_Details.Course_Date, sociwork1.expirat_dtFROM Sociwork1 INNER JOIN tblCE_Details ON Sociwork1.LIC_NUMBER = tblCE_Details.LIC_NUMBERWHERE (tblCE_Details.LIC_NUMBER = Forms![SW form]!SLIC_NUMBER) AND tblCE_Details.Course_Date >= (sociwork1.expirat_dt - Month(30))ORDER BY tblCE_Details.Course_Date;Thanks,Crhodus

View 3 Replies View Related

Queries :: Subtracting Numeric Value From Date Value

Jan 15, 2015

in my query to subtract [Days] (Numeric Value) from a date field [TravelDate]

View 4 Replies View Related

Queries :: Subtracting Date Fields

Jul 10, 2013

I have an Access query which has 2 fields 'Sent Date' and 'Return Date'. Sent date is input by the user when they return an item for repair, return date is input by the user when the item is sent back to us from repair. What I need is a 3rd calculated field which will work out the number of days the item was away for repair. I have included the 3rd column 'Repair Duration' but I don't know how to type the formula in the query to work this out.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved