I have been given a set of pricing tables in Excel that, based on the type of work done, show a dollar amount for time ranges. In other words, if WorkType = A, the appropriate pricing table is referenced and the amount of time in minutes is found within ranges ("0 - .3333", ".3333 - .6500", etc) shown in hour decimals (.3333 = 20 minutes), then an associated dollar amount is reported back. (I know... very awkward, but not my choice). The time ranges are not consistent in size either within a particular pricing table nor across tables. The person using these tables is doing this manually now.
What I need to be able to do is enter the WorkType and duration, and get the dollar amount from the table, even though the duration is not going to exactly match the value in the table. In Excel's VLOOKUP, you can put in amounts that not exact, and the function finds the highest value less than your entry. Can Access do that? I tried reading up on DLOOKUP, but I don't think that is what I need (or maybe I just don't understand it). I'm not sure of an efficient table setup or how to get the correct output.
I have a report that displays incidents, their details, consequences and a photo. Among the details is a severity rating high medium or low, I have been asked to make the report shorten the records which have been given a low severity (because it takes up as much space as the more important/severe ones).
The only method I can think of is to use the onformat event, to shrink and make invisible all the fields that I don't want to see if the severity field shows 'low'.
I am preparing a database to track the daily activities of my team. This is a real time database wherein the users will add their daily ongoing task and submit.
The action for me is, I need to capture the time required for each task.
Also, there is a possibility that the user will have to hold a task for a while/days due to some issue and will start another task. Once they have a feedback on the issue they will resume the pending/holded task.
Here, I need to capture only the time they have invested in performing the task and ignore the time spent to receive feedback on the issue. Hence the time should start when they start the activity and pause when they hold the activity. Again, the time should continue where they had paused, when they re-start the holded activvity and finally ends when the submit.
I'm sick of the lame excuse for error messages that Access gives out.. I'm talking about 'Overflow', 'invalid use of Null' and such like.
What about telling you what field/expression caused the error and on what record if applicable.
It's so frustrating - Access obviously knows what caused the error but refuses to tell you so you have to go through the annoying process of taking out fields one by one (when in query view).
Does anyone agree that they need to make the error handling in Access much better?
How do I conditionally change the Back Color and Alternate Back Color based on the value of notes.FollowUpDate? I have a continuous form using the following code:
Code: Private Sub b_all_past_Click() Me.resultsFrame.SourceObject = "FollowUp_bystaff" Me.resultsFrame.Form.RecordSource = "SELECT * From notes WHERE (((Notes.FollowUpDate) < Date()+1) And ((Notes.followup_person_id) = GetCurrentUserId()))ORDER BY notes.priority,(notes.FollowUpDate) DESC;" ' End Sub
hi i have a button that loads a report. If i then close the report the form, which is set with no border/no max/min/close/non movable and opens maximised, places itself off centre. I want it to stay maximised full screen. The only work around i currently have is when the use moves the mouse over the form it maximises itself. Will this then be calling this function everytime the mouse moves(cant be very efficient). is there a better way to maximise the form Immediately after closing the report.
I have two fields. A barcode field will have several instances of the same data, and the other field has different dates and times when it was scanned. I need to be able to query the barcode field and only return the record with the latest time. Can this be done in a query or do I need to do something else? Here is a sample of data I am working with. I attached a small sample of data as an example.
I'm making a dispatch call database that will be sorted at the end of the month based on call types and which shift that call was originated on. Our shifts are 6a-2p, 2p-10p, 10p-6a.
I am figuring that the time would be converted to minutes and then the display would be figured based on which times the minutes occured between. IE: the call origintaed at 0100hrs = 60 minutes. It would figure that 60minutes translates to "Night Shift".
How would I convert time into minutes and how would the if/then be established?
I'm trying to find a solution to a problem. Is there any way using VBA to generate a random number, based off the current system date and time? The theory is that a random number seeded by the system date/time will be truly unique and the possiblity of it ever occuring again will be nearly nil.
Or is there a better way to create unique ID's (such as member ID's, or something similiar) based off the system date/time that can be used as a primary key in a relational db.
How do you run a procedure that activates at a set time? I want to run a procedure that moves a log file to an archive directory at midnight (or at anytime after this when the PC is next turned on). Thanks for any help... Mat
TRANSFORM Count([Copy of qryDailyCountOfValidations].Staff_Number) AS CountOfStaff_Number SELECT [Copy of qryDailyCountOfValidations].PatternStartDate, [Copy of qryDailyCountOfValidations].[End Time] FROM [Copy of qryDailyCountOfValidations] GROUP BY [Copy of qryDailyCountOfValidations].PatternStartDate, [Copy of qryDailyCountOfValidations].[End Time] PIVOT Format([Start Time],"Short Time");
it shows the number of employees that start a shift at a particular time and the time the shift ends, on any day.What i need to show on a graph is the count number of employees starting at x time and plotting the number of hours the employees are on shift until they leave or another employee starts....
-the y axis is the count of employees -the x axis is the start time of their shift
10 9 8 7 xxxxxxxx 6 x x 5 x x 4 xxxxxxxx x 3 xxxxxxx x 2 xxxxxxxxxx 1 0 00:00 03:15 05:30.........................23:59
So the above mock up says 3 employees start at 00:00 and continue until 03:15 then another employee starts, then 4 employees continue 06:00, then 3 more employees start, 7 all together, then 5 employees leave later etc...
-the level of detail needed is to plot for every 15 minutes. -The [end time] is not strictly needed, just included to show what columns i have available
achieving this on a graph on an access 2010 report..i have updated a test database so you can see what the data looks like.
I will simplify the situation as I can , I have one table in my DB with the following fields :
ID , APP_DATE , APP_TIME , DETAILS , APP_STS
I want to have a pop-up form 5 minutes before the appointment time !!! (That is it)
- I know it's better to manage the appointments within outlook. - I do not want to connect my DB with outlook appointments. - I want the reminder form to have a "snooze for 5 minutes" button and "Done" Button to change the appointment status (APP_STS) to done. - I think we are going to use on timer event (which is totally new for me)
I am creating a timesheet database for work. We will be using TimeStation app which allows export of excel documents. I have linked the table with the same headings as the excel document and am importing the data based on that. What I want to be able to do is run a query that gives us deductions for breaks based on a few values.
I would need it to deduct 0.75 hours from any shift longer than 6 hours where the person clocked in between 8.30am and 12.50pm
I would need to deduct 0.5 hours from any shift longer than 6 hours where the person clocked in between 12.51pm and 5.31pm
and To deduct 1.25 hours from any shift over 12 hours which cancels out the above.
My table, TblTYPE, will contain two field. TYPE DURATION
There will only be 7 records in this table: FUS, 30 minutes POS, 30 minutes PRE, 30 minutes NOV, 90 minutes FUL, 90 minutes ANN, 90 minutes NPB, 60 minutes
1st question: How do I enter only minutes in the table for field 2?
------
I want the following to happen. The end-user enters the start time and the type. As soon as the type is entered, field three calculates an end-time based on the start time and the type. When the type is entered, the minutes listed in field two of the type table are added to the start time (giving you an end time).
Can anyone help me? 1.) How to properly format the minutes in the original table. 2.) How to add the minutes to the original time in a query.
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.
I have a query with a Start Time where the need to return a set integer in another field in my query. I am attempting to get this to work in my StripSecondsQry.
I am not getting any error messages and I am not getting any output, When I view this in the Locals window I can see that it should be returning 7, but instead I get nothing unless I change it to
Code: Function SortStart(StartTime As String) As Integer
then I get zero.
I had this working within the query, but I had to add one more time and then received a message that the expression was too complex.
I have a form where I am capturing StartTime and EndTime of certain events. I have set the input mask as 99:00 >LL;0;_
My users think it is too much to enter am or pm. What they want is the system to calculate the am pm based on the time entered.
If the time entered is between 8:00 to 11:59, the system should save it as 11:59 am and if the time entered is between 12:00 to 7.00, the system should save it as 7.00 pm.
I am trying to write an expression that will result in a date in the format (mm/dd/yyy). I want to display the curent day if it is before 11:00 am based on the system time, and the next day if it is after 11:00 am. Below is the expression that I currently am working with... but it is not working.
In this thread: http://www.access-programmers.co.uk/forums/showthread.php?p=549287#post549287
I had a problem. Now I decided to just make two tables, one with sales for a "passer by" and/or directly to a client without a case/labour costs. And a table for sales in relation to a case with labour costs etc. I guess I still m*ssed up on that part and have to make do with that decision (kind of like I depicted in the photoshopped image).
For invoicenumbers I want to use DDMMYYYYHHMM. When adding that field to the design of a table, how do I go about that? Do I just use a Text field and create an input mask for that? Or do I need to use a number format with a certain input mask? Or would it be wiser just to use the date/time type of field and give it a new inputmask? I guess 000000000000 would be the one for that, right? Although I read the help file I do not know if I should use ;0; behind those first group of zero's or not. Yes it is a mandatory field, but it will fill in itself as soon as a product is typed in in the product/invoice list, with a default value of the current date and time.
I need to brush up my knowledge on the formatting for that so that 28-05-1975 19:55 will be displayed and stored as 280519751955. Or is that also done automatically by using that input mask? Or will that give an error when Date or Time() etc. is used for that field in VBA?
Just a few questions I need some help with, I've been trying some stuff but it really doesn't "stick".
And if anyone has some time, I could really use some general advice/ideas for setting up the invoicing part differently for a new version of the db yet to come in the future.
Hello All... I'm very new to Access.. so please forgive my ignorance..
Here is the scenario..
I have a table with two fields named "item" & "description" that contains 10 records total.
I have another table with 100 records with the fields "invoice #","item", "price paid","date paid","time paid". The item fields are linked between the two, and the 100 records contain different invoices for these ten items.
I want to pull the price paid off of the latest invoice, based on the time and date... What is the easiest way to do this?
I have a query which is displaying a date/time field a record was input. in my query I'd like to only display the records where the date/time based on what today is i.e. Monday it will display <Friday or yesterday for Tue to Fri.
Suppose that you want to make a formula which will calculate time periods based on a given tariff. To be more precise.
Suppose you can "get in" somewhere, entrance is $2. You are free to stay there, without additional charge, for 5 days. Then, for the next 5 days, the charge is $1 per day The next 7 days, the charge is $1.5 per day Thereafter, $3 per day.
In this scenario, we can make a query, we use datediff function to see the number of days between "gate in" and "gate out".
The problem is that the tariff is not very simple and the query gets to big and difficult to control with lot's of "iif" and may be I need to make more than 4 queries and union select and so on.
I tried to find in the docmd if there is any suitable function that I could use.
I have a database that has 2 forms. After submitting the first form, the user should complete the second form within 24 hours. The first form stores the Date/Time the form was submitted. I want to be able to run a query and have a column in the query that is "Time Remaining". In non-technical terms, this column would be: Date/Time form submitted + 24 hours - Current date/time.
