Subtracting Two Times To Fill Out Textbox?

Jan 31, 2006

I have 2 time boxes with times in them. How do I get the 3rd textbox to display the difference between those times?

View Replies


Subtracting 2 Dates And 2 Times From Seperate Boxes

Jul 6, 2007

Jus thought I'd offer this code to the forum as spent a long time finding a suitable solution..

SELECT DischargeTime.ID, [Arrival Times].ID, [Arrival Times].[A&EArrivalDate], [Arrival Times].[A&EArrivalTime], DischargeTime.[Date of outcome], DischargeTime.[Time of outcome], DateDiff("d",[A&EArrivalDate],[Date of outcome]) AS DateDifference, [Time of outcome]-[A&EArrivalTime] AS TimeDifference, IIf([DateDifference]>=1,([DateDifference]*(1/24)*576)+[TimeDifference]*(1/24)*576,([DateDifference]*(1/24)*576)+[TimeDifference]*24) AS hours3
FROM DischargeTime INNER JOIN [Arrival Times] ON DischargeTime.ID = [Arrival Times].ID;

Basically I had an admission date and in a seperate box an admission time and then I had a discharge date and discharge time again in seperate boxes. The information was spread over two tables hence the join. How I solved the problem was by gaining both the days difference between and then the difference in hours and then I created an iff function and played around with it till it worked. Dont really know how i got it working in the end but it does so hopefully this code will save some1 alot of time!

(I know that if id shown my dates as date/time in 1 box the problem would have been much easier solved, however I dont personally like displaying dates and times that way especially when you have inexperienced people entering data)

Find below a result from the query

DischargeTime.IDArrival Times.IDA&EArrivalDateA&EArrivalTimeDate of outcomeTime of outcomeDateDifferenceTimeDifferencehours3

View 2 Replies View Related

Must Fill In Textbox

Jan 2, 2006


I have created a form with one unbound textbox and a command button.

How do I make sure that the query only runs if text is entered in the textbox?
I do not want the command button to run unless the textbox has data to query.

At the moment the Query runs and displays all records.

View 3 Replies View Related

Fill Textbox Based On Value In Another

Aug 3, 2005

I have a data entry form for Table-A. After I enter an account on the form, I use the account to get a value from a different table and put that value in another combobox. I tried using a SetValue to fill the combobox. I put the SELECT statement from a query in the Expression parameter. But Access doesn't accept that Expression and produces an error message about "Forms" not being a field. This has got to be a common action in forms, so there must be a better way to do this.

View 2 Replies View Related

Forms :: Fill Textbox With Text If It Is Null?

Jul 3, 2014

I am trying to use this expression in my textbox to show when the textbox value is null:


It gives me a circular reference error (#error in the textbox). If I change it to this:

=IIf(IsNull([ContractStatus]),"Null","ContractStatus"), it will display the text "Contract Status" (obviously).

What am I overlooking here? Does this need to be done in VBA?

View 9 Replies View Related

Auto Fill Textbox In Seperate Form, Help Needed

Apr 10, 2007

Is it possible to fill an unbound text box in a seperate form with some text "Warning.HIGH RISK" on the click event of a checkbox on a different form?

View 2 Replies View Related

Forms :: Auto Fill Textbox From Past Records

Aug 26, 2013

I have a form for creating new entries in a table. In it there is a textbox for a City field that I would like to have some kind of auto-complete based on past records in the database--similar to how Excel provides auto-complete options within a spreadsheet based on entries in previous cells.

In other words if "Binghamton" has been entered in a past record and the user starts typing "Bing" in the textbox, then "Binghamton" will become an autofill option. Is there a way to set this up?

View 12 Replies View Related

Forms :: Fill Value In Textbox When Item From Combobox Is Selected

May 19, 2013

I have a table, Registration, with 8 fields:

inschrijvingsid,name,gemeente,gsm,telefoon,email,a ankomst,gerecht,personen

I have another table, Login with 3 fields : Naamid,name,,status

Then I have a combox that is connected with fldname from the table Login.

In that form I have 8 textboxes:


And status.

Now what I can't. I want when I select a record from tblLogin Like for example Daan that I see in the textboxes his info from the tableRegistration.

Also when I change the textbox Status it and click "Verander status" it changes in the database.

View 3 Replies View Related

Modules & VBA :: DLookup Fill Textbox After Combo Selection

Apr 29, 2014

I'm a novice when it comes to Access 2010, and I'm having trouble with DLookup syntax, and am going nuts. I have tblLookup, tblFees and Costs, and frmFees and Costs. All data entry is done in frmFees and Costs.

The tblLookup lists plaintiffs and the matter related to them. In the form, I made a combo box [Combo13] for plaintiffs and a text box for Matter [Matter], but whenever I enter the plaintiff, I get an error.

The error is:
Run-time error '3075':
Syntax error (comma) in query expression '[PlaintiffName]=The Plaintiff I Typed in'

The code I'm trying to use:

Private Sub Combo13_AfterUpdate()
Me.Matter = DLookup ("[Matter]", "tblLookup", "[PlaintiffName] =" & Me.Combo13)
End Sub

[PlaintiffName] is the column name in the tblLookup.

I looked around the forums but couldn't find anything that matched my setup.

View 3 Replies View Related

Create Textbox To Fill In A Set Term For Multiple Variables?

Mar 18, 2014

I am building a database for a martial arts program for older/elderly adults. I have a section that measures their physical capabilities. I need to create a text box that will fill in if their abilities are below average, average, or above average on one of the tests. The problem is, the test measurements are based on age and a range of repetitions.

For example, men 60-64 need to do 14-19 reps of the exercise to be average and women of 60-64 need to do 12-17 reps of the exercise. Men from 65-69 need 12-17 reps and women of the same age need 11-16 reps of the exercise. I have a box for their age (on a separate table from the rest of the information) but I need to figure out how to allow someone to fill in the number of reps a person did and have if they were average, above average, or below average filled into the last box based on that number, their age and their gender.

I tried a combo box, but I can't seem to figure out how to create the table to get it to work.

View 5 Replies View Related

Modules & VBA :: Automatically Fill In A Textbox On A Subform On Button Click

May 4, 2014

I have a mainform with a combobox and a button. What i want is when i click on the combobox and then on the button I want that the textbox in the subform automatically fill in.

The mainform name is FrmTakenInvoerenEnToewijzenAanEenMonteur,

The combobox name is Keuzelijst1,

The subform name is SubTakenInvoeren,

And the textbox name is txtOpdrachtnr.

I tried this code in on button click:

Me.SubTakenInvoeren.txtOpdrachtnr = Me.Keuzelijst1

But that doesn't work.

View 7 Replies View Related

Subtracting In Access

Oct 10, 2005

I'm trying to do a calculation in access that will allow a user to receive a discount in one field and show the results in another. The form looks like this. Having a hard time figuring out the line total. It's giving me a the wrong amount. The employee discount is $.20 and the price is $2.27 for example.. Thanks

Bound text box [UnitPrice] - Bound text box [EmployeeDiscount] x Bound text box [Quantity] =
Bound text box [LineTotal]

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 One Query From Another?

Feb 23, 2008

Hi everyone,

I am familiar with databases and basic SQL, but never used Access, so your help is greatly appreciated!

What I *think* I need to do here is take the results from one query, subtract another query from that, then create a table of the results, so that I can make a chart in a report. I say that I *think* I need do that because Iím not confident that this approach is the best but thatís a whole other story...

Please look at the following tables below for an example. The Item table has ItemCode as a primary key. The Bids Table has multiple rows for each ItemCode showing bids for the item:

Item Table
ItemCode Date Address
JH231 2008-Feb-20 123 Test St.
KH831 2008-Feb-21 512 Test St.

Bids Table
ItemCode Amount
JH231 100.00
JH231 60.00
JH421 310.00
KH831 20.00
KH831 40.00
KH831 10.00
KH831 90.00

To make a pretty graph, I would like a table (so a query, or macro, or VBA or something?) that returns the minimum Amount for an ItemCode subtracted from the maximum Amount for an ItemCode based on the example above. What I would end up is something like this:

ItemCode Difference Between Maximum and Minimum
JH321 250
KH831 80.00

I hope this is clear. Any help on how to do this from you Access pros would be great, OR if Iím taking the wrong approach, suggestions on what I should try next. Thanks!!!


View 6 Replies View Related

Subtracting Stock Levels

Nov 9, 2005

Hi, I have a data base that is used to keep track of stock level. When i issue a stock item and move to the next record I would like the stock level to be reduced by 1, any ideas.

View 2 Replies View Related

Subtracting Results From Two Different Queries.

Jun 16, 2005

Hi, here's the situation:

I have a query A, that gives me the a set of numbers.
I have a query B, that gives me another set of numbers.

Numbers in B are always equal or larger than those in A. (B >= A)

I want to create a new query C, that does the subtraction. (C = B-A)

Here's an example to explain what I want.
Let's say I have A=4,7 and B=6,9

Now, when I create the query, I get the following result:
A | B | C=B-A
4 | 6 | 2
4 | 9 | 5
7 | 6 | -1
7 | 9 | 2

What I want is this:
A | B | C=B-A
4 | 6 | 2
7 | 9 | 2

How do I do that?


View 2 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. )


Column A Column B
1111 6.5

I need 1111 - 6.5 in Column C

View 4 Replies View Related

Subtracting Dates, Getting Whole Months

Dec 12, 2005

I found the following code on this site in a search for a method to subtract two dates ie [Startdate] and [EndDate] and display the result as a whole number. ie 2, 6 etc. This code finds the difference but displays the results as 1 year 5 months 2 days, where I need the equivalent as 17.
Can this code be modified to produce the desired result and if so how would I go about it. Thanks for the help.

Option Compare Database
Option Explicit

Public Function getTimeElapsed(StartDate, Optional EndDate As Date) As String
On Error GoTo Err_Handler

If IsNull(StartDate) Then
getTimeElapsed = "No startdate"
Exit Function
End If

Dim YY As Integer
Dim MM As Integer
Dim DD As Long

' use current day if EndDate is not supplied,
' + 1 makes both days inclusive.
EndDate = IIf(EndDate = 0, Date, EndDate) + 1

MM = DateDiff("m", StartDate, EndDate)
DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate)

' recalculate if DD is negative.
If DD < 0 Then
MM = MM - 1
DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate)
End If

YY = MM 12 ' integer division.
MM = MM Mod 12 ' remainder.

getTimeElapsed = YY & IIf(YY < 2, " year ", " years ") & _
MM & IIf(MM < 2, " month ", " months ") & _
DD & IIf(DD < 2, " day", " days")
Exit Function

MsgBox Err.Description
Exit Function

End Function

View 1 Replies View Related

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:


View 4 Replies View Related

Subtracting Query Values

Sep 7, 2007

How do i subtract one value, worked out using a query, from another value, worked out using a query, and display it in a text box?

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

Subtracting Inside Of A Field? CAN IT BE DONE!!!

Jun 2, 2006

I am looking to subtract number inside the same field. and then return the difference to a new field (column). This data is used to generate a graph and the this is currently done by hand. I know there has to be an
easier way to do this.

Below is an example of what I am looking for.

I have a table called Numbers, and fields or columns called...

Value(s) Result(s)
-70 0
-70 1.02
-68.98 1.09
-67.89 3.11
-71 .71
-69.29 1.52

So what I am doing is subtracting row two from row one. The difference is then put in another field(column) in row one. The process repeats, row three from two, four from three, five from four, etc, etc. This occurs for hundreds of rows.

Is there a SQL statement that can do this? Your help is much appreciated!!!

Thanks for your help.

View 3 Replies View Related

Subtracting Dates In Queries

Feb 1, 2013

My form consists of a textbox with a selected date. I have a combo box with integers 1-15. I have a table with 15 people and their birthdays.

I am trying to find the birthdays after a selected date by the integer selected in the combo box. For example, if I select 2/1/2013, and pick 7 in the combo box, the subform should show me people with birthdays 2/1 through 2/8.

I have a query with a datediff function. This is my query expression Expr1: DateDiff("d",[DateofBirth],[Forms]![Calendar]![Text1]) The criteria is <=[Forms]![Calendar]![Combo3].

It displays some data, but it's not consistent at all. I'm hoping to fix this without any SQL changes or form code. But if that's what I need, I'll do it.

I'm using access 2010, and I'm fairly new at it.

View 1 Replies View Related

Subtracting Values From Two Consecutive Records

Jan 17, 2006

I am having a problem, probably due to my inexperience with Access. Here is the seniero:

-I have a form/table set up for operators to enter numbers on a daily basis.
-I am setting up queries/reports to display not only the entered data, but also calculated values from the entered values
--I have no idea on how to subtract a previous day's value from today's value and report that value.
- Since Access gives no "ownership" to any value I do not know how to reference yesterday's value in today's calculation.Any Ideas?

Record #1
Date - 1/17/06
Value - 15,232

Record #2
Date - 1/16/05
Value - 14,111

Desired output
Date 1/17/06
Value - 1121

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

Subtracting Info Using Data From Another Table

Nov 18, 2007

hi, i've been trying to find a solution to this problem but i can't seem to get my head around it. i have searched through the forums but found all the threads to be confusing although it probably is simple.

im a doing a project for a2 for a trailer tent company

got 2 tables where the problem lies in

they only sell one trailer tent to a customer
when a trailer tent is sold, i want to update no in stock in stock table
i.e. trailer tent sold --> "no in stock"field for that particular trailer tent goes down by one. I would like the user to be able to change stock level any time as well so don't want it to be fully automated

sorry if i'm not making it clear, not really sure how to explain it

thanks for help, much appreciated

View 3 Replies View Related

Copyrights 2005-15, All rights reserved