Hello peeps,
I have been working on a hours checking application, in which the user can enter their worked hours for the week and the application calculates how many hours at normal rate , time and half rate or double time rate have been worked for the week. All has been going swimmingly, with some help from here of course :)
What I would like to achieve now however, is, testing for a Sunday. This is because all hours worked on a Sunday, is paid at double time, regardless of the previous hours already worked for that week.
Example if a person worked 60 hours in a given week, there would be 47.5 hours of normal rate, 7.5 hours of time and a half rate and the remaining 5 hours would be at double time rate. But, if a person worked 60 hours in a week and 10 of those hours are worked on a Sunday, this would mean 47.5 at normal rate, only 2.5 hours at time and half rate and the remaining 10 hours at double time.
Therefore, is there a way I can test for a Sunday and perhaps automatically set a flag for the overtime calculation? Or is it possible with DateDiff() function, and if so could someone please assist me with the syntax or at least point me in a direction where I could solve this?
All replies gratefully recieved. Thanks in advance to anyone taking the time to read this post.
I would like to know if MS Access can handle functions that Excel has, for example, "weekday" function, that returns a number representing the day of the week (sunday=0, monday=1, ...), given a date value.
If so, I have a conditional like this in excel: =IF(weekday(DATE-3)=0;DATE-4;DATE-3)
If DATE is sunday, then move to monday. Is there a way to make something like this in Access?
I'm trying to group and display data on a weekly basis starting on Sunday. I'm using the following in a totals query with "Group By" Week Number: DatePart("ww",[Date])
My problem is that the results are returning some unexpected things.. March seems to have 6 weeks and the query is returning two months containing week number 14.. (as shown below).
2013 March 9 2013 March 10 2013 March 11 2013 March 12 2013 March 13 2013 March 14 2013 April 14
I have problem with my code here. I am using this code to determine if the LogDate with a weekday of Sunday can be declare as Sunday Work or Regular Work. The 1st week of my entry can determine if the LogDate is Sunday then compute if it reaches a 7 days working, if he is absent even 1 (one) day in his previous working days within the week. His Sunday LogDate is considered as Regular Days instead of Sunday Work. Can anyone tell me what is wrong in this code?
The problem here is the computation of 7 days after 1 week. There should be a 7 days computation every week per month in able to declare Sunday Work or Regular Work. I tried this code but it did not work on 2nd week, 3rd week and 4th week of the month.
How come on 2nd week, Sunday (LogDate) is not considered as Sunday Work even he completed the 7 days (working days) per week? I need help on this ASAP Thanks
Tim
Below is the code i'm using for defining Regular days and Sunday Working Days:
Private Sub LogDate_AfterUpdate() Dim rst As DAO.Recordset, rs As DAO.Recordset Dim db As DAO.Database Dim dteToday As Date, dtePrior As Date Dim i As Integer
Set db = CurrentDb Set rst = db.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
rst.FindFirst "[HolidayDate] = #" & dteToday & "#" 'See if the date entered is a holiday If rst.NoMatch Then Holiday.Value = 0 Else Holiday.Value = 1 End If If Weekday(dteToday) = vbSunday Then 'If dated entered is a Sunday determine if Dim strSQL As String 'it will be a regular day or a Sunday
I hope someone can help me. Thanks ahead for taking the time to read through my post.
I am new to Access (I know the basics,) and will ask my question by hand of describing what I want to do with my database.
I work as a laboratory technician, where I need to test UTP cores (cores are what's called one of the 8 cables inside a UTP/Network cable.) I want to create a database where I can store these test results.
To make it easier to envision my database, I'll explain a bit about the actual testing. Cores are grouped together, and tested, according to batches. There are 40 cores in a batch. Each core has a unique job number. Further, the cores are divided into 8 different colors (Green, W/Green, Orange, W/Orange, Brown, W/Brown, Blue, W/Blue.)
A standard test procedure is done on EACH core: dimensions on the insulation on a X and Y axis; dimensions on the copper on a X and Y axis; a test to determine if the copper sits centre. There are thus 5 values I want to record.
My problem is: I want all 40 cores, each with their 5 values, on one form. I want to save this as one batch, or one record. How would I go about doing this? . It’s a bit more complex than your standard table-query-form-report database.
As I said, I am acquainted with the basics of Access. I can do tables, queries, forms and reports.
I have a table where some of the values of MyField are null.
I am creating a function that depends on the value of Myfield which I pass to a variable varMyField and am testing it with If IsNull(varMyVariable) but it errors. Have tried setting the variable as a variant but no difference.
Function Test(varMyField as variant)
If isnull(varMyField) then Dosomething, Else Dosomething Else End if
I created a table and a form. One of my tables fields is AutoNum. Each time I test the form layout, the AutoNum field increase and yet I am only testing at this stage!How can I bring back this field to zero? Where is my data stored (extension)?
This code below works, but alot of what it does is unnecessary. I have a directory structure named 1000-2000, 2001-3000, 3001 - 4000 etc and within that is the individual folders 1000, 1001, 1002 etc
How do I test to search top level folder thats no larger than correctNum to then test the subfolder for correctNum? I've looked at instr and a few other funtions, but can't find a solution. Below is what I have now, it works but I know it searches everything and that's not correct
Dim filesys Dim Subdir Dim folder Set filesys = CreateObject("Scripting.FileSystemObject") Dim oDir Dim bDir CorrectNum = Serial.Value
I need some help with code. I can do this in Excel, but I am not sure how to do it in Access.
Here are the fields:
PoundsPerBox PoundsPerOrder BoxesToOrder
Here is the scenario: If a customer orders custom paint for their equipment, we need to total how many pounds of Powder Coat paint it takes to paint all the units (PoundsPerOrder). When we order this from the vendor we have to order it by the box which is measured in pounds. In this example, the vendor will sell us a box of white Poweder Coat paint in increments of 55 pounds. (55, 110, 165,...)
Verbally, the logic reads like this: If PoundsPerOrder is less than or equal to PoundsPerBox, then BoxesToOrder equals 1. This logic needs to be tested for each increment value (indefinately) of PoundsPerBox.
Am I making sense? How do I accomplish this in Access? And, as a side note, I need to capture and store this value in a table for future reference.
I know this is really silly. How do I display data that I am working with in a report? I want to do the equivalent of alert or response.write. I am using msgbox, but it's getting old. What is the syntax for just displaying it ? Can I do that on a form? I tried using document.write, but it didnt like it. thanks-
I do not want to allow the main form record to be submitted unless there is at least one subform entry.Initially I thought it would be as simple as a "Before Update" event which would test the record count of the subform. This works... kind of... not really. It does check successfully, however the conundrum is that if the user has completed the main form and tries clicking INTO the subform to add records, this fires the Before Update event and Cancels the update event.
How can I force a user to complete the subform without running into this issue? I dunno how easy this is to do considering you kind of HAVE to exit the main form to enter the subform, at which point the record HAS to be saved...
When the input value for m is empty/Null, the response returned in the query is #Error. Problem is, any way I try to test for this in the function, it still returns #Error.The Watch for m reads "<Out of Context>" for these errors. Anyway to test/trap for this within the function itself?
Code: Public Function getAbsoluteMagnitude(m As Double, d As Double, Optional dUnit As String = "Mpc") As Double Dim absoluteMag As Double ' IsError(m) returns False ' IsNull(m) returns False ' IsEmpty(m) returns False
I actually have about 13 separate fields in a table I'm trying to summarize in a single report field. Each field corresponds to a particular action taken. If the action was taken the field value is 1, if the action was NOT taken the field value is 0. The report needs to contain a single field that lists all actions taken. I tried doing this with an If statement formula on the report, but it does not work.
=(IIf([ACTION_VQ_SCAN]=1,"VQ Scan")) And (IIf([ACTION_OTHER]=1,[ACTION_OTHER_SPEC])) And (IIf([ACTION_CULTURE]=1,"Culture(s)")) And (IIf([ACTION_DRUG_THERAPY]=1,"Drug Theraphy"))........ and so on
It looks like it expects all if statements to be true, thus the formula does not return anything. Usually only 1 or 2 actions are actually taken, but I need to account for the possibility of any and all combination of actions.
I'm starting to automatically save PDF copies of certain reports in predetermined locations. I need to copy a file to a particular directory (based upon various criteria), but if the directory does not exist, I want to create the directory on the fly first. Otherwise I will just proceed with the copy.
I'm sure there is a DOS command to do this, but I can't find it.
I need to start using the database I've created (desktop database). However, I don't want my current version to be the final version.
Should I create another file to work in and use the old version for data entry? When I am ready to start using my new version, is it easy enough to transfer all the data from the old?
A while ago I was tasked with creating a compliance testing database for my company's internal processes. For example: Payments, Hiring, Project Management, etc. Right now it's all done through Excel and is structured like this:
In short, a Test has many Items (based on company regulations, like "Has the project been approved by all superiors?"). For each Item you choose a few Samples ("Project A", "Project B" and so on), and for each Sample the user attributes a Grade ("In Compliance", "Not In Compliance", "N/A", etc).
In the Tests mainform, the user fills in the Test details (Tab #1), chooses which available Items he wants to include in his test (Tab #2), details the Samples which will be tested (Tab #3) and finally assigns a Grade to each Item/Sample combination (Tab #4).
Here's what I can't figure out:
In the attached DB, if we go into the "CT - Projects 2013" and check the Samples in Tab#3, we can see they match perfectly with the ones at Tab#4.
Now if we go into "CT - Projects 2014" and do the same thing, we can see that the Samples assigned to the Items in Tab#4 don't match up with the ones in Tab#3 at all. This is because the DB is checking only if the Samples' ItemIDs match with the ItemIDs in the test. But it also needs to make sure that the TestID, the Item's TestID *and* the Sample's TestID match, or it just won't work!
I attached a 2003 version of the database as the first upload used Access 2010.
I have a form with two list boxes on it: listAllBANANA and listActiveBANANA
The listboxes are populated from tblBANANA. Active is defined by the tblBANANA field "ResponseDate" being Null.
Also on the form is a CloseBANANA command button. When pressed with no selection, it opens frmCloseBANANA to the first record. If a BANANA is selected in listActiveBANANA, the form opens to the selected record.
Now comes the tricky part: if a BANANA is selected in listAllBANANA, I first need to verify they have an Active BANANA selected, before opening the form. I've poked and tested and searched and cut and pasted my little fingers off with no joy. What I've deduced is that I'm either messing up the syntax of the DLookup or I'm incorrectly referencing the selected record. There's also the third option that I'm going about this in entirely the wrong way.
All other code below works. If I delete or comment out the line in red, I have no issues other than I may open up a blank form because I'm trying to close a BANANA that is already closed.
Code: 'If a BANANA is selected in Active BANANA or All BANANA, open Close BANANA to that record. If not, open form to most recent record Private Sub cmdCloseBANANA_Click() If Not IsNull(Forms!frmGUI!listActiveBANANA) Then
I would like to know, how do i reset the count of my auto number fields after testing? is it also possible to specify which number the auto number should start counting from?
I've created and tested my database. What step or steps do I take now to make it so it's a program that only shows the forms and menu's I created, not all the Tables, Queries etc and the formatting options? Is there a link that explains what to do step by step?