I'm trying to finish a query that calculates the time worked in the AM and PM, adds them together, then takes that away from the standard working day.
I've done everything else, but when I take the hours worked away from the standard day it always ends up with a positive number, regardless if they haven't worked enough hours.
I've looked through the forums and everyone is talking modules, but thats over my head.
My head's swimming right now so the solution to this problem may be right under my nose and I'm just blind to it.
I have a table with events, represented by an ID number, and the date/time the events occured. Each event is also attatched to a particular mission number. Looks kinda like this:
Mission EventID Time
Each Mission has different events like liftoff and patient contact, and I need to find the time between events for each mission. I'm trying to calculate the Time from inital call to mission end, from dispatch to liftoff, ect.
Do I need to put these times into my mission table as columns, Time1, Time2, Time3... or can I leave them normalized and still run calculations on them? :confused:
I am wondering why access does not follow its own code all of the time?
For example I have passworded forms following the tutorial on the Microsoft site. It mostly works when the participants fills in the wrong password we will get an error message box and when they cancel the password dialogue box it wont let the person enter the form - but not all the time. Sometimes if you press cancel it still opens the form anyway (without requiring the correct password).
This is similar with the module that I put in to prevent people from using the mousewheel to leave their record etc. Most times it works but sometimes it just isnt called on form load.
There is nothing wrong with any of these codes and when they work they work well but I cant seem to rely on access to do what is supposed to?
In my DB that we use and a workflow tool, some of our work has and due date and time.If we get the complete_package our work time starts and we have X amount of time to complete our work. This is something i worked on but set it aside, now i am coming back to try and fine tune this so it returns a more accurate value.
So if we receive and [Date_Complete_Package_Received] at 09/13/2014 09:00:00 AM and based on the work being done we have 5 hours to complete the work, then the [Date_and_Time_Complete_Package_DUE] would be 09/13/2014 02:00:00 AM. That part is simple and i have coding that does that just fine. [SLA_Time] is where it gets the amount of time allotted, we have 5,10,12,14,and 16 hours depending on what is being done.
This is done on AfterUpdate of a field on one of my forms and it works the way it is but what i need is to be able to run this through my Workhours Function so i am not getting values that our in off hours.The following doesn't work i know i can't use the Workhours function with the DateAdd but this is just to show what i am trying to do.
I have been tasked at my work to create a database for tracking time off from work. I have built several databases in Access 2003 and have now transitioned to 2010 and it is seems to be going well. My past databases have been rather simple data in data out not really that big of a deal. However on this database they need a report that will show them a week view that shows them all the employees regular days off and any additional time off they have schedule in that week.
I have an employee info table that has employee basic info, Emp name, Emp number, Work Week code (which identifies which days off the employee has) Hire Dates (for seniority sorting) and then I have 7 fields listed as D1, D2, D3 and so on until D7 (I will try to explain these fields later).
I have another table (Time Requests) that has 3 fields, Date, Emp number, and Time code (which identifies why they are off work or unavailable to be assigned).
The last table I will refer to has 7 fields, they are Date1, Date 2 – Date7
I now have a form. This form has the 7, fields Date1, Date2 – Date7.
On my form Date1 field is updated by a manager with a date that is a Sunday then Date2 = [Date1]+1, Date3 = [Date1]+2 and so on until I have all 7 fields showing date from Sunday – Saturday.
These fields are lined across the top in a vertical direction positioned above a subform of the employee info table. So directly below the 7 Date fields are the D1, D2, D3, D4, D5, D6, D7 fields then to the right is the Emp name and Emp number, This gives me a Week view of who is working.
I have been able to get the D1, D2….. fields to show their respective Work Week codes by writing very simple “If Then” statements . So here is where I AM STUCK…When I am showing this week view how do I get D1 –D7 to identify what date they are supposed to be in reference to Date1 – Date7 then compare themselves to the table “Time Requests” to see if they have a match and then set the value of D1, D2 …. to the “Time code” in that table. D1 – D7 need to auto populate and do this for about 50 employees.
Here is a screen shot of what I have so far and where I am stuck: [URL] ....
I have a linked table in my access accdb file to a view on a SQL Server DB.
One of the columns is a date column, where only the time value is important, so it's stored in the default format, like 12-30-1899 12:00 AM.
In SQL Server, I can query records that fall between a certain time frame. It works in Access when written as an ADO query (that's another story), and it also works when I convert the view to a local access table.
But when I query it as a linked server, it will return everything is greater than 12-29-1899 11:59 pm, but when I try to search any date/time ranges on 12-30-1899 nothing is returned. This seems to be a bug in Access.
The reason I no longer use ADO, is that the results would not print. For some reason, when I go to print preview, Access would evaluate the query I'm passing through to SQL Server and throw a syntax error. I miss ADP.
I 'm trying to create a database for tracking time off from work and print a weekly roster. I have built several databases in Access 2003 and have now transitioned to 2010 and it is seems to be going well. My past databases have been rather simple data in data out not really that big of a deal. However on this database they need a report that will show them a week view that shows them all the employees regular days off and any additional time off they have schedule in that week.
I have an employee info table that has employee basic info, Emp name, Emp number, Work Week code (which identifies which days off the employee has) Hire Dates (for seniority sorting) and then I have 7 fields listed as D1, D2, D3 and so on until D7 (I will try to explain these fields later).
I have another table (Time Requests) that has 3 fields, Date, Emp number, and Time code (which identifies why they are off work or unavailable to be assigned).
The last table I will refer to has 7 fields, they are Date1, Date2, Date3, Date4, Date,5 Date6, Date7
I now have a form. This form has the 7, fields Date1, Date2.... Date7.
On my form Date1 field is updated by a manager with a date that is a Sunday then Date2 = [Date1]+1,Date3 = [Date1]+2 and so on until I have all 7 fields showing dates from Sunday - Saturday.These fields are lined across the top in a vertical direction positioned above a subform of the employee info table. So directly below the 7 Date fields are the D1, D2, D3, D4, D5, D6, D7 fields then to the right is the Emp name and Emp number, This gives me a Week view of who is working.
I have been able to get the D1, D2.. fields to show their respective Work Week codes by writing very simple If Then statements . So here is where I AM STUCK, When I am showing this week view how do I get D1 - D7 to identify what date they are supposed to be in reference to Date1 - Date7 then compare themselves to the table Time Requests to see if they have a match and then set the value of D1, D2 . to the Time code in that table. D1 - D7 need to auto populate and do this for about 50 employees.
I have a calc'd percentage field in my query. This is a multi user app and the problem is that is can (and does often) happen that the numerator information for my calc gets entered before the denominator data and therefore the calc'd field shows and as an error.
I attempted to use a simple IIF statement to input a message rater than the error code but now the field will not format to Percent. It worked, I got the message to display and it calc'd the records that had data, it just will not display in percent format. It does let me set the format to percent, it just doesn't display and does not allow the decimal places to be set.
This was the line: CalcPercent:[Numerator]/[Denominator]
This is the new line: NewCalcPercent:IIf([Denominator] = 0, "Message", [Numerator]/[ Denominator])
I've tried to search for an answer and I'm sure it is going to be something simple that I've overlooked.
I've searched hours on the forum but with no luck. If there's anyone out there who can help me would be very much appreciated.
The calculation in Excel is very simple but I am unable to replicate in Access. Please don't confuse this with FIFO calculation as it is purely Weighted Average Cost method based on Buy unit prices i.e Sale unit prices are not required as it will sell at the Average Cost of the Buys upto the point of Sale.
i.e. Buy 100 @2 = 200 Buy 100 @3 = 300 Total 200@ 2.50 =500 Sell 100 leftover will be Total 100 @ 2.50
I am attaching an Excel S/sheet which shows the Calculation of Stock ABC and the Average Prices (which is what I am trying to achieve). I have also attached an Access Dbase with same details but without any calcs as whatever I have done I am unable to achieve my result.
subtracting field B from field A. Field A has data in all records, field B only some. The 'only some' I get a result but the ones that have nothing in B I get nothing even though A has data. Both are currency and default is 0. How do I take nothing from something and get nothing you ask ?
This is the second edited section: I got the update query to work properly. Is this the best way to solve my problem?
This is the original edited section: For the problem below would it be possible to use an update query to update the table? Criteria for the termination_date field would be "is not null" payment_to_date Update to: field would equal DateDiff("m",[payment_start_date],Now())*[monthly_payment] Is it possible to use other fields in the table as part of the UpdateTo: calculation? I get an error when I try this.
This is the original question: I have a field in a table called payment_to_date that I want to always be populated with the current total of how much a customer has paid to date based on their monthly payment rate. I have a form for data entry into this table but once their account info is entered the first time their won't be any changes to the account unless they terminate the service so calculating from an event procedure on the form doesn't make sense to me (I am new at this).
I entered the following in a text box control source on the form and it works but the textbox is not bound to the table so it's not the right way to do it.
=IIf([termination_date] Is Null,(DateDiff("m",[payment_start_date],Now())*[txtpayment]),(DateDiff("m",[payment_start_date],[termination_date])*[txtpayment]))
The calculation from above is basically: If there is no termination date then the payment to date = the number of months between todays date and the date payment was started multiplied by the monthly pay rate.
Can anyone tell me how to keep the information in the table current for the payment_to_date field?
Hello everyone. I am very new to databases, may have bitten off more than I can chew, but I was tried of working in 4 different spreadsheets and decided to pull them all into a database so that I could get what I wanted when I wanted. Here is what I need to be able to do: In a table I have 3 different industries entered 5 different times. They all cover a Monday to a Friday. Each day for each industry I record a reading from a meter. I make a calculation by subtracting Mondays number from Tuesadays, Tuesday from Wednesday, Wednesday from Thursday and Thursday from Friday. This calculation gives me how much water each industry used each day, over 24 hours. Doing this in a spreadsheet was simple. My table has the Industry name, date, previous day meter reading, present day reading. I want to have the calc show in my query. How do I set up this calculation. Any help would be greatly appreciated. Thank you. I attached a sample database that contains the table and query.:confused:
How do I use fields in related tables to create a calc field? The wizard only shows me the current table's fields. Can I do create me desired field by manually creating the calculation and bypassing the wizard? Or is this another restriction of Access?
I want to create a calculated field that If the employment type is FT Hourly or PT Hourly it will have a pop up screen for the Hourly Wage to be entered. If those criterion are not met it nulls the Hourly Wage field.
I have finger print machine and i already connect to it and get all log.then i tray to get data and here is the code that im using
SELECT Format(CHECKINOUT.CHECKTIME,"dd/mm/yyyy") AS CDate, IIf([CHECKINOUT.CHECKTYPE]=I,Format(CHECKINOUT.CHECKTIME,"hh:nn:ss ampm")) AS StartTime, Format(CHECKINOUT.CHECKTIME,"hh:nn:ss ampm") AS EndTime, USERINFO.USERID, USERINFO.Name, CHECKINOUT.CHECKTYPE FROM CHECKINOUT INNER JOIN USERINFO ON CHECKINOUT.USERID=USERINFO.USERID WHERE (((Format([CHECKINOUT].[CHECKTIME],"dd/mm/yyyy"))='10/04/2014'));
We are creating a simple database to maintain driver license information for faculty, staff, and students who use cars from the universitys motor pool.
To do this, I have created two tables: tblDRIVER and tblLICENSE.
The fields in tblDRIVER are: pkfDriverIndex strLastName strFirstName strInitial strAddress1 strAddress2 strCity strState strZIP datBirthDate
The fields in tblLICENSE are: pkfUpdateIndex fkfDriverIndex datDateUpdated strState strLicNumber datExpirationDate ynViolations ynActive
Information about the driver is stored in tblDRIVER and the drivers license information is stored in tblLICENSE. Periodically, we run a report that identifies drivers whose licenses are due to expire within a certain number of days. All this works fine.
My problem is that I am trying to create a lookup form that will load from a data entry form that will permit the Motor Pool Clerk to look up a driver by name and review the licensing information (which appears as a subform).
All this sort of works- I am using a combo box (based on a query) to look up a drivers last name (which it does) and to populate all the drivers information on the look-up form (including license information in a subform). Currently, the combo box locates the driver (including the unique index, last, and first names), and populates the form with first and last name but the rest of the information is not displayed on the look up form. Worse still, sometimes one drivers last name matches up with the first name of the next driver listed in the table! This seems to happen only if a look up is attempted more than once. What gives.
In a query I have placed functions that work fine. Now I needed to round a currency number to the nearest $10. ex. 224.49 would be 220.00. I used round(xxxxxxx,-2). this gives me an error. positive 2 works fine. What's the deal? thanks for anyone who can help me. :)
A few years ago, I wrote an application in Access 2000 that worked fine. Last year I upgraded to Access 2003. Everything works the same, but the UCase, Left and Right text functions give error messages when used in queries. Anyone have a way to fix this?::confused:
i have a DB which tracks order numbers, the main table is a SQL table linked in, and there are lots of local tables as well, one of which is called userDetails and contains a field useremail
i want to open a form and only show records from trackermaindata that contain my useremail -
SELECT dbo_TrackerMainData.userEmail FROM dbo_TrackerMainData, userDetails WHERE dbo_TrackerMainData.userEmail=userDetails.useremai l;
when i double click this query in access it works and it shows me only my records, but when i try and run this query from a button (using a macro) in my form, it asks me to Enter Parameter Value userDetails.useremail
i've tried lots and lots of things so far and nothing works. i have lots of other queries which do work, but they don't reference another table - they either contain a static value or take their data from an entry box.
can anyone give any reason why this should not work? it seems straight forward enough!!
I have set up a form to run a query, setting the following VBA to pick up values from the combo boxes in the form. The only problem is that I cant seem to get this working. When i press the command button it won't even get past the Dim db As Database line.
Private Sub Command10_Click() On Error GoTo Errorhandler
Dim db As Database Dim qd As QueryDef Dim vWhere As Variant
Set db = CurrentDb()
On Error Resume Next db.QueryDefs.Delete "qry_email2" On Error GoTo 0
vWhere = Null vWhere = vWhere & " AND [Status]=" + Me.cbostatustype vWhere = vWhere & " AND [Substatus]=" + Me.cboSubstatus vWhere = vWhere & " AND [PublicationName]=" + Me.cboPub1 vWhere = vWhere & " AND [PublicationName]=" + Me.cboPub2 vWhere = vWhere & " AND [PublicationName]=" + Me.cboPub3
If Nz(vWhere, "") = "" Then MsgBox "There are no search criteria selected." & vbCrLf & vbCrLf & _ "Search Cancelled.", vbInformation, "Search Canceled." Else Set qd = db.CreateQueryDef("qry_email2", "SELECT * FROM tblgeneralcontactdetails WHERE " & _ Mid(vWhere, 6))
Im trying to add fields together in a query (these fields are results churned out by an iif expression), however access just concatenates the fields instead of summing. Access will not allow me to change the format of the original fields either.
I'd be grateful if someone could offer some help with this to a frustrated Access novice : )
I have a query that filters records according to the value of an unbound text box in my form (basically search on last name)
I want to introduce wildcards into the filtering process but cannot get it to work.
Here's what I've done:
In the criteria field I had [Forms]![MAIN]![Text440] refering to the unbound text box on the form already mentioned.
This worked fine, but the following: Like "*[Forms]![MAIN]![Text440]*" does not.
Nor does various combinations of brackets such as: Like "*([Forms]![MAIN]![Text440])*"
In fact it now doesn't return anything, even if given a positive match. I have trawled help and the forums but to no avail. I have also tried the % character instead of *
If I copy paste the SQL from a query (View->SQL) to a macro it doesn't work. Why? I've tried removing quotes,Caps, adding quotes, parenthesis nothing worked. Any ideas?
