Forms :: Combining Date And Time Values Using DateDiff

Jul 8, 2014

I'm trying to create my own database dealing with hiring bikes on a daily basis.

I have created a form for the basic input, including StartDate, StartTime, EndDate and EndTime, followed by TotalDays. I added the time field so that if a client brings a bike back an hour or two after the pickup time on a later date (sounds complicated - e.g he takes it on Monday at 12pm until Thursday 14pm) then he would be charged for an extra day. It is similar to the car rental system used worldwide.

The TotalDays is currently using a simple expression =EndDate-StartDate.

Is there any way to make the form incorporate the time difference so that if EndTime-StartTime>1 then it would add an extra day to the "=EndDate-StartDate" calculation.

View Replies


ADVERTISEMENT

Forms :: Returning Difference Between Date / Time Values In HH.MM Format

Dec 11, 2013

I want to be able to display the result of a difference between Date/Time values in "HH.MM" format (i.e. yes I want the result in decimals and I don't want Access to round up or down just because it feels like doing it!). I have used the following:

Example 1:

Dim ActualManHours as Long
ActualManHours = (txtEndTime.Value - txtStartTime.Value) * 86400
txtActualManHours.Value = ActualManHours

Example 2:

Dim ActualManHours as Long
ActualManHours = DateDiff("h",txtStartTime.Value, txtEndTime.Value) & "." & Format(DateDiff("n",txtStartTime.value,txtEndTime. value) Mod 60, "00")

Example 3:

Dim StartTime as Double
Dim EndTime as Double
Dim ActualManHours as Long
StartTime = CDbl(txtStartTime.Value)
EndTime = CDbl(txtEndTime.Value)
ActualManHours = EndTime - StartTime
txtActualManHours.Value = ActualManHours

View 7 Replies View Related

Forms :: Combining Values In Expression Using IIF?

Apr 30, 2014

I'm trying to create a text box on a form which displays a value based on numerous criteria.

Basically I manage a database of employees. Some are external employees, some are internal. Some have left the organisation, and some are still employees.

I have two fields which need to be pulled into this expression:

is_leaver - text box, with value either "Yes" or NULL

organisation - name of organisation (text)... lets say my organisation is called Happy People Ltd

The text box I have is called employee_status

I can see that there are 4 possible combinations:

1. Internal Employee - Is an internal employee and still works here
2. Internal Ex-Employee - Is an internal employee but has left
3. External Employee - Is an external employee who still works here
4. External Ex-Employee - Is an external employee who has left

So far I've managed this:

Code:
=IIf(IsNull([is_leaver]),"Employee","Ex-Employee")

This displays whether they're an employee or ex-employee. How would I form the expression to combine the two fields, and calcuate whether they're internal or external?

Due to the way the form is set up I don't want to have two separate text boxes and would prefer to combine it.

View 5 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

DateDiff In A Query - Calculate Time Difference Between Two Columns

Jan 22, 2012

DateDiff in a query

I am trying to calculate time difference between two time columns as follows in a query which has a few inner joins

Diff: DateDiff("h",[Table1.TimeStrat],[Table1.TimeStrat])

This gives problem, it is asking [Table1.TimeStrat] to be entered, where is the problem?

View 4 Replies View Related

Combining Values

Sep 9, 2005

I am pretty new to access and struggling with a query problem. I currently have a table containing data like:

Name_____________Color Chosen
Bill..............................Red
Bill..............................Blue
Tom............................Red
Mark...........................Green
Bill..............................Green
Tom............................Blue

And I would like to be able to get to

Name______________Colors Chosen
Bill..............................Red, Blue, Green
Tom............................Red, Blue
Mark...........................Green

Can anyone suggest a query to get me this second table. Theoretically the maximum number of Colors that one person can choose is 4.

Many thanks in advance

Chris

View 5 Replies View Related

Combining Values Of Comboboxes

Dec 20, 2006

Hi! I have a problem combining values of comboboxes, let me explain me better...

I have a text box and two comboboxes on a form
First, you introduce a data on the text box, then you select something of one of the comboboxes

the third combo box have to have the value of the text box and the value of the combo box separated by a comma or a space. Please help me with this. I need to finish this database soon:confused:

View 3 Replies View Related

Combining Two Boolean Values?

Feb 11, 2012

I basically have two yes/no fields in an access table. I want field one to have the value yes if field two has value no, and the other way around.

I had a look at the Boolean operators, but im not sure where to go from there.

View 6 Replies View Related

DateDiff To Include Start Date

May 7, 2013

I have a Continuous form where I am trying to add a calculated feild that allows us to see how many days a client was in hospital. There is a start and end date. I want to see the following: If start date and end date are the same, this should show client in hospital for 1 day, not the default of 0. If start date 5/1/13 and end date is 5/3/13 it should show 3 days in hospital. If start date is 5/1/13 and there is no end date, this should show 7 dates (from start date to today's date or current date). Currently I have

=DateDiff("d",[EStartDate],[EEndDate])-([EStartDate]=[EEndDate])

but it only shows 1 if the start date and end date is the same. I need it to include the start and end dates typed in its equation.

View 1 Replies View Related

Hard Code TIME To Selected Date On Form (to Make It Date&time) For My Query Criteria

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

Combining Calculated Values From Two Queries.

Aug 24, 2006

I have created two queries which calculates a total. I want to add the values of the two queries in a third query to give me a grand total.

When I try to create this third query, it gives me an error saying that the fields cannot be used from two different queries. So I'm just wondering whether this is possible or is there any other way of doing this.

View 1 Replies View Related

Reports :: Combining Same Values From Two Fields

Aug 21, 2013

I would like to make a report to show how many employees and which employees are attending to which colleges/universities.

In my data (800 records), I have two fields which is "College 1" and "College 2" for each employee.

There are values that are enter in college 1 for some employees, other employees have the same value that is enter in in college 2. How do I get to show a report that has all the employees who attended the same college in either college 1 or 2?

For instance, this is my raw data:

Name - College 1 - College 2
Bob - University of HI - Honolulu CC
Sandy - Honolulu CC - University of HI
Clare - Kapiolani CC - University of HI
John - University of HI - Windardward CC

I want my report to show:

Colleges -
University of HI
Bob
Sandy
Clare
John

Honolulu CC
Bob
Sandy

Kapiolani CC
Clare

Windard CC
John

View 4 Replies View Related

Combining Rows With Duplicate Values

Jun 17, 2013

I am working with an Excel file of raw data aggregated from an annual customer relationship survey that has been sent out since 2010. The file has approximately 20,000 rows, meaning the survey has been taken around 20,000 times. Unfortunately, each time a customer takes the survey, it is included in the raw data as a separate entry. Therefore the file has numerous duplicate email addresses, corresponding with unique data for each time the survey was taken. Another issue regarding the data, is that in the first year the survey was sent out, the distribution mechanism "broke" and the survey was sent out multiple times (and completed multiple times) in the same year by the same customer, so the surveys are not necessarily uniformly distributed, if that makes sense.

I have been interested in isolating the common respondents (those who have taken the survey across multiple years, albeit not necessarily consecutive years). Up to this point, these respondents have been isolated manually using a pivot table, however I am now looking to enter the raw data into SPSS (a statistical analysis package), in order to view the drivers of these common respondents.

Therefore, I would like to be able to isolate these common respondents and the data corresponding with their surveys from the raw data in a separate worksheet or file. I have tried various formulas to do so in Excel to no avail. Is there anyway to accomplish this in Access or would a more complex database be needed?

Ideally the final product would have the common respondents' information from multiple surveys in one row and would be able to be filtered by feedback date (found in the raw data), so that hypothetically one could select a month and be able to tell how many customers considered to be common respondents completed a survey within that month.

View 8 Replies View Related

Combining Two Text Box Values Into One Label

Jun 12, 2013

I have two text boxes, and i want to make there value be the label for the form. Here is what i have in the Label on the form:

=Nz([Last Name] And [First Game],"Untitled")

Where am i going wrong?

View 5 Replies View Related

DateDiff Value If Same Date/field Predates/is Null

Sep 24, 2004

I'm really lost on this one...
How do I find the # of days it takes to complete a task between two date fields.
For example:
[DateRec] field is the field the app was first input to the database. [DateNumb] field is the date the first task was completed. So I understand,
=DateDiff("dd", [DateRec], [DateNumb]))
But I'm getting an #Error because often these two dates are the same date/equal, Or/ the DateNumb field may pre-date the DateRec date. Or/ one or both fields might be Null (blank). How do I set the formula on this?

Bottomline,I need to find the average # of days it takes to complete a task between two date fields for all tracking records. How do I do That???? HELP!

View 2 Replies View Related

Queries :: Combining Fields - Spacing Between Values

Mar 6, 2014

I'm creating a query which pulls together the numerical values of 32 separate fields. Each field has the potential to have a value in it ranging between 1 and 9, but most fields will be blank.

I want to prefix the value (if the value is not null) with a 2 or 3 character-long code relating to the field name.I then want to combine them all together in a query so I can easily copy and paste the output to a spreadsheet.Here is what I have so far, with just 9 of the field names, and it's already looking a bit clunky.

Code:

IIf(IsNull([zam_adm]),"","ADM") & [zam_adm] & " " &
IIf(IsNull([zam_cts]),"","CTS") & [zam_cts] & " " &
IIf(IsNull([zam_crs]),"","CRS") & [zam_crs]& " " &
IIf(IsNull([zam_IPB]),"","IPB") & [zam_IPB]& " " &
IIf(IsNull([zam_opr]),"","OPR") & [zam_opr]& " " &
IIf(IsNull([zam_owl]),"","OWL") & [zam_owl]& " " &
IIf(IsNull([zam_pmi]),"","PMI") & [zam_pmi]& " " &
IIf(IsNull([zam_wl]),"","WL") & [zam_wl]& " " &
IIf(IsNull([zam_sgt]),"","SGT") & [zam_sgt]

The output, unfortunately, is putting spaces between empty fields and I'm not sure how to prevent this in the syntax?

View 3 Replies View Related

Queries :: Combining Columns And Replacing Certain Values

Sep 29, 2014

I need to create a query that combines two columns (lets say Column A and Column B for example) however the problem is that whatever non-null values that are in Column B must replace any value in Column A. If Column B has a value that is null then Column A's value is shown.

I have an example of what I'm working with (access file) and what result I want (excel file).

View 2 Replies View Related

Queries :: Getting Date / Time Range - Date And Time Are Separate Fields

Mar 13, 2014

I have a database with date and time each stored in a separate field. Now I want to query the database based on a start date/time and an end date/time. I started with the code below but it only returns events within the same time range on each day when what I really need is every event from a specified date and time through a specified date and time.

SELECT myTable.ID AS myTable_ID, myDate, myTime, FirstName, LastName
FROM Staff INNER JOIN myTable ON
Staff.ID = myTable.StaffID
WHERE myTable.myDate >= #3/2/2014#
AND myTable.myDate <= #3/3/2014#
AND myTable.myTime >= #8:00PM#
AND myTable.myTime <= #11:00PM#
ORDER BY myDate desc

In the above example what I want is every event from 3/2/2014 8:00PM until 3/3/2014 11:00PM. But what I get instead is every event between 8:00PM and 11:00PM on 3/2/2014 and every event between 8:00PM and 11:00PM on 3/3/2014.

View 4 Replies View Related

Queries :: Drop Down - Query Not Combining And Filtering Values Based On A Form

Jul 22, 2014

I have a form (DropDown form) that has 3 drop down fields, you select your values from the drop downs and you would push a command button that runs an event procedure which runs a query (DropDown qry test). The user should have the option of picking any combination of fields to filter by. Or no combination, which would return all values in all fields. So I am basically using the form as parameter's for the query.

The problem I'm having is that my query is returning values for one field AND values for another field. Even if the other values selected are not in the same record. It's not combining the fields together to filter. For example: you pick a Project name and Supplier name, the query will return records that have the project name you selected but it will also return records with the supplier name you selected that have a different project name.

I've attached screen shots of the form and the design view of the query (the screen shot cut off the last column name. It is meant to say "Expr3: [Forms]![DropDown form]![Combo7]").

Using Windows 7,
Access 2010

Is there a way to select multiple values from the drop downs?

View 14 Replies View Related

Forms :: Date / Time Formatting

Apr 1, 2014

I have a time/date field. The format for the field is short time hh:mm I created a Private Sub for when the field is double clicked the time is populated.On Double Click:

Private Sub Time_of_Call__DblClick(Cancel As Integer)
Me.[Time of Call:] = Time()
End Sub

It works great. I double click the field and "poof" the time appears. My problem is when the time appears it is in hh:mm:ss..I need it to be in 24 hour/ military time.

View 14 Replies View Related

Forms :: Date And Time In Same Field?

Jun 19, 2014

i want the user to write any date and any time in same field in my form but i dont want to choose function now()

View 1 Replies View Related

Problem With Date & Time Fields In Forms

Oct 11, 2006

Hi,

Im having a problem adding the date and time to a new record using a form. The 1st method i used was by inserting the default value set to Date() & Time() functions, this worked fine for over a year, but now on some computers on the network the following error pops up "function is not available in expressions in table-level validation expression".

The 2nd method I tried was to add a macro with 'setvalue' commands to the After Update event of one of the first field's that was entered, this entered the same information into the Time & Date field once the first field was updated. This now brings up the macro fault screen and the 'Halt' button has to be pressed to clear the error.

This only happens on the older computers that access the database, the new computers don't bring up any errors.

Can anyone help me with this problem??!

PTDAccess

View 3 Replies View Related

Forms :: Date And Time Product Will Be Finished

Aug 19, 2013

If I had a textbox [txtTotalDay] which its days/Time was in decimals and a start textbox [txtStartDate] and a start time and number of shifts the product can run how can I accomplish this?

Example:

[txtTotalDay] = 3.50
[txtStartDate] = 8/19/13
[txtStartTime] = 6am
[txtNumOfShiftDay] = 2

ANSWER : The time/date it would finish would be 8/21/13 @ 4:00pm

View 6 Replies View Related

Forms :: Date And Time Not Displaying If No Data

Jun 9, 2015

I have a series of forms in my shiny new database and I have added the auto time and auto date fields to the form headers. I have added the code to keep the time ticking over and it all works fine.

Unless the is no data. If for any reason there is nothing in the Details part of the Form, the date and time do not display. As soon as any data shows up, so do the date and time.

The VBA code is running in either case so I am a little baffled. Usually Google is my friend but not in this case.

I am using Access 2013 on a win 7 machine.

View 5 Replies View Related

Forms :: Date Change Based On Time

Sep 16, 2014

I have a form and I want the dates to be displayed based on predefined time. In this case, I want my form to show present date from present day's 7:00 AM to next day 7:00 AM.

View 7 Replies View Related

Forms :: Changing Auto-date Rollover Time?

Mar 31, 2014

I have a date/time field on a form that has the default set to "=now()" which puts in the system date and time. I need to change it so that the date changes to the next day any time after 11:00 PM (instead of midnight). This is because our third shift starts their day at 11:00. If they start entering data right away, alot of their work shows up on the wrong (to them anyway) workday date. I'm struggling with the expression builder to get this to work.

One way is changing the time to be an hour ahead but that will affect the entire day when I just want to change the date for one hour per day.

View 3 Replies View Related







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