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. )
I am currently working on a database where my source data has the times in military format which includes the seconds; 18:45:30. All my time fields are set to data/time and format is Short Time. The Short Time format gives me military time but only down to the minute. I need to include the seconds into my time fields. I tried creating an input mask but it converts it to standard time.
how I can show military time down to the seconds as in my example above?
We have a new contractual requirement to track all time spent on Military contracts and report it to our Prime Contractor annually.
I am trying to put into Access 2010, basically the same functionality that I have built into Excel time sheets for years; that is simple entry of time in military time using 3 or 4 keystrokes depending on the time of day.
I have a table which records an employee's employee number and job that he or she is working on. I have formatted fields to short for Start Time and Stop Time, and I have a Calculated field also formatted short for elapsed time, however I need to have it simplified to where there is no colon separator on key entry in the form to 700 for 0700 hrs military and 1317 for 1:17 PM.
I will be using this database on several computers, but only a couple that will be used for time entry.
The Excel spreadsheets work well, but I have found them cumbersome trying to dump all the information into a Master spreadsheet from several different individual spreadsheets and I am using Access as our ERP so it will save time if I just add a simple form to track time from there putting it in the hands of the Repairmen and save someone from data entry late.
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:
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!
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?
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.
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]
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.
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!!!
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.
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
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...
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!!!
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 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?
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.
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 order stock
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
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
