Dear all,
in my table I have the following fields:
- fldstartdate
- fldstarttime
- fldenddate
- fldendtime
Can I calculate the time that is between those variables using an expression?
fldenddate.fldendtime - fldstartdate.fldstarttime = "x days and x minutes"
Kind regards,
Hans B.
I've got to do the following query in Access:
"This query should prompt the user to enter two dates and than calculate the total amount paid for all invoices in the Amount field between those two dates (inclusive)."
Query is based on table Invoices.
I have created the query and dates part works fine. However,when I run Sum for the Amount field, instead of total amount query displays amount of each invoice between those dates. How to get just Total (amount of each invoice added up)?
I'm asked to do a specific task, a database concerning of:Tables: 25Forms: 25Reports: 40-45Build from ground up, multi-user, accounts, account-restrictions, has to be linked to an already existing database, will need a fair amount of VBA coding.I would like to know specifically two things:--> what is your common hourly rate --> what would your common total-price-estimate for a project like this.
View 1 Replies View RelatedHow do I put an upper limit on the amount of time allowed in a date/time field?
For example - during a heart procedure a person is exposed to radiation, it will never be more than a total time of 10 minutes. I want to know how much, but I want data integrity so I don't want them to be able to enter more than 10 minutes.
I have made a basic form and use it a log on screen. This requires the user to in put a user name and a password before gaining access to the main database.
The background to the form is a table where the users details are entered including the i enter the user name and password.
Is there any way to add a counter that is only visible in the table, so I can report on the different users.
I have One table(and designed form from it) in which i have recording the daily transactions(it is like a daily book). Daily transactions took place like Purchase of items of Amount 45, sale=70, sold on account=100 etc.
What problem i am facing is: I would like to Add Cash In Hand amount and would like to show it on my form. When day end I would like to move remaining cash In Hand on separate column of that date. The next day i would like to take the previous Remaining Cash in Hand as Cash In Hand at Start for next date and so on.
I am trying to make a report in which I can see the amount of money that I have to pay weekly to an employee. To explain myself clearer, I have different rates for each employee. For example:
NameNormalPayRateVacationPayRateSickPayRateOvertimePayRateStandbyPayRate
John Smith$15$10$15$30$8
Ken Jonson$10$8$10$20$8
I am doing this in a query. So what I want to be able to do is, for example, take 4 hours that the employee worked at NormalPayRate and multiply it. But if tomorrow the employee worked at a different rate, for example 3 hours overtime, I want those 3 hours multiplied by OvertimePayRate.
The way I saw it on my Google researches, is that they have only one rate and the amount will be [SumOfHours]*[Rate]. But how can I tell my query to skip, for example, one rate because the employee didn't work at that rate that day. So I can have different amount for the different rates.
By the way, entering the information I can divide at what rate the employee worked x day. So if the employee work 6 hours at regular, and later on 4 hours at standby, I have those information separated in my table.
Results that I am getting: I can easily get the amount in money multiplying the whole hours by a rate, so my result in my report will be multiplied by the same rate.
I am using Access 2013 ...
How can I make a query that will take a field that I already have set to record the last contact date and check it against today's date to give me the span between the two?
View 1 Replies View RelatedI have attached a really simple db where in a query I'm trying to determine the elapsed time between A sent date and a received date. Can some tell me what I'm doing wrong? Thanks..
View 1 Replies View RelatedHi,
This is my first post here and I seem to like the activity on this forums. Now to my issue.
I have a table with schedules: Event and it's schedule in actual date:
Ex.
Event1 1/1/2007
Event2 5/1/2007
Event3 7/1/2007 (MM/DD/YYYY)
and I have another table which has the event name, time steps and numbers:
Ex.
Event1 1 1000
Event1 2 1500
Event1 3 1500
Event1 4 2000
Event1 5 1500
Event2 1 1000
Event2 2 1500
Event2 3 1500
Event2 4 2000
Event2 5 1500
Event3 1 1000
Event3 2 1500
Event3 3 1500
Event3 4 2000
Event3 5 1500 (I kept the numbers same for all, time steps are monthly)
Now, I can't think of away to write a query to link the dates and the time steps, eventually reaching to some output as this:
Event1 1/1/2007 1000
Event1 2/1/2007 1500
Event1 3/1/2007 1500
Event1 4/1/2007 2000
Event1 5/1/2007 1500
Event2 5/1/2007 1000
Event2 6/1/2007 1500
Event2 7/1/2007 1500
Event2 8/1/2007 2000
Event2 9/1/2007 1500
Event3 7/1/2007 1000
Event3 8/1/2007 1500
Event3 9/1/2007 1500
Event3 10/1/2007 2000
Event3 11/1/2007 1500
Thanks, let me know if there is anything not clear. Pointing me to the right direction to do this helps a lot since I am kinda lost.
The below function returns correct time difference between workdays. However, it is excluding Saturday as per the code.
It is calculating 06:30 am to 22:00 pm time for weekdays but I also want it to calculate the time from 10:00 to 13:30 on a Saturday.
I am trying to use the NetworkMinutes function to achieve this. However, there is a problem getting the time for Saturday.
Code:
Option Compare Database
Option Explicit
'---------------------------------------------------------------------------------------
' Procedure : NetWorkMinutes
' Author : Rod
' Date : 13/12/2012
' Purpose : Returns the number of work minutes between two date-time arguments.
[code]...
I have searched the forum for this answer but no luck. :(
I'm trying to calculate the amount of WORKING time between two dates in an Access database. At the moment i am just subtracting one date from another but this gives me all of the time in between including weekends and evenings.
I need this time to be calculated in hours.
In Excel i know there is a NETWORKINGDAYS function which does something similar but with days rather than hours.
At least if i could get the working days i could then convert it into hours.
Please help!
The Database is coming along nicely now, but in need of guidice with this one:
On the main table of the contacts I have what you could call a date of 01/01/06 and a combo box next or below that that I select what risk levels to asign the user.
Each user would have a no of year review based on the risk level:
High = 1 Year
Med = 2 Years
Low = 3 Years.
There is another date box that is blank at the moment, what I am looking for query wise or another way is on change/update the risk level is to add the start start in this case 01/01/06 (if user is Med Risk - then add 2 years) and adding risk level giving me a new date of 01/01/08 in the blank date box (this option will make the main table update.
Can anyone point me in the right direction for this one?
Thanks
Mike
We are collecting data from client files that have multiple dates (start_date, event1_date, event2_date, end_date.) For client protection purposes, we are absolutely not allowed to leave the facility with the actual dates. However, we want to know how many days have passed between each event. I thought about having the data collectors enter each date, have a calculated field store the time between dates, and then have the actual dates cleared when the record is saved or when the person moves to the next record.
View 14 Replies View RelatedThe project I am currently working on requires me to build a database for checking off if a certain person has submitted a time sheet for a certain date. I have a table for the dates that has the fields "date" and "Submitted time sheet?". The time sheet field is a checkbox.
I also have a table for employees with fields "employee last name", "employee first name" and "location".What I want to know is how to link each employee with the dates and whether or not they submitted a time sheet.
Hi
I have a table with
"starttime", "endtime", "agent", "phonenumb" and a datediff calculation that calculates the diff between "starttime" and "endtime"
the "starttime" and "endtime" columns are in "10/05/2006 12:57:55" format, i.e. the column holds both the date and time
The data is stored in an sql DB and is accessed by an odbc link using Access 2003
I want to be able to run a query within access that lets me chose "agent" and a specific date or a range of dates.
Any help would be greatly appreciated
Thanks
Ben
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:
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?
I have a script file of FoxPro 2.6 which is use to read amount field and write it in words.
Can any body convert in VBA Script.
I manualy write amount in words.
Thankyou.
Saira
Any way to have a form with Dates as column headers to update a table where the dates are stored in rows???
The table set up is like this:
tblOpHdr
DiaryID (PK) - OpDate (Date)
tblOpDetail
DiaryID (FK) - CostCode - MachineNumber - MachineHours - etc
I'm just wondering if there's any way I can do this with a datasheet or a crosstab type setup?
It's Access 2010.
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
View 1 Replies View RelatedHow many users can an access database support? I'm setting up an app accross 3 workstations but each has multiple users. There may be as many as 1000 records a day entered. Can an access database handle this or do I need to switch to sql server?
View 6 Replies View RelatedHi
Is there any way i can increase the amount of columns in a table before importing data? I am importing data from a notepad doc that needs abt 300 columns but i can only get abt 100 columns.
Thanks for any help
Hi All.
I need to generate a query which will show me in Field1 Cost for the current year only and in Field2 Cost from a start date to the current. If is it possible how to do it?
Thanks.
I'm trying to produce a query that will bring up a list of all employees with their current salary amount. The table however, has all changes for an employee and reads as follows:
Table Name(NBRJOBS)
Employee ID-- Date of Change -- Salary Amt
1234 ----- 01/01/2003 -------- 500.00
1234 ----- 05/01/2003 ----- --- 600.00
1234 ----- 11/01/2003 ---- --- 700.00
2222 ----- 03/05/2004 ------- 200.00
2222 ----- 12/05/2004 ---- --- 300.00
2222 ----- 02/01/2005 ---- --- 300.00
It will be necessary to create a report to list out all employees and their current salaries. Any suggestions?
I have a table containing the following two fields, one with monthly dates (end of month plus year) and one with profits (per month). However, for some dates the records are missing. For example, for the 31-1-1994 there is no record (not in the date field, nor in the profits field).
How can i create a query that will only show me the records if 10 or more monthly subsequent profits are known, so meaning that in those 10 months no records are missing? So that only the timespans without the gaps (missing records) are shown.
So if the 31-1-1994 and the 30/6/1994 record are missing, then the 4 subsequent records in between those two dates should not be shown,, since the amount of records is not 10 or more. However, if the next missing date would be 30/6/1995, then all the 11 subsequent records between 30/6/1994 and 30/6/1995 should be shown. Since the number of records is bigger than the required 10.
Thanks for helping me out!
Luuk