I'm using the Date()-1 to populate a form with yesterday's date for easier data entry. However, on Mondays I always have to change the date to Friday's date because it is populating yesterday's date (which in this case is Sunday). Is there an access expression that calculate yesterday's date but ignores weekends? So that on Monday yesterday would mean Friday?
Is there a way, in a query or via the use of a module, to calculate the workdays between two dates excluding holidays without needing to maintain a separate table with the holidays listed out?
For example, I would like it to calculate 21 work days for the month of May. However, I don't want to have to go in the first of each year and manually list out all the holidays for the year. Is there a way for the Holidays table to just contain the number of holidays in a given month (i.e. in December we get 3 holidays (Christmas Eve, Christmas Day, and New Years Eve).
I am trying to limit the results of a SQL query by date. I would like to take the current date from the system clock, move back one day, check to make sure it's not a weekend or holiday, then use the result date to limit the results in my Query. If the current date minus one day happens to land on a weekend, the date picked should be the Friday before the weekend. If the current date minus one day lands on a holiday, it should be the date before the holiday as long as it is not a Saturday or Sunday (on another holiday) .
Here is my SQL query right now.
SELECT DISTINCT (Mid(ClientDiv.Client_Division,1,3)) AS ABC, RTIClientTracker.EMB_OOB, RTIClientTracker.OOB_Fixed FROM ClientDiv INNER JOIN RTIClientTracker ON ClientDiv.ID = RTIClientTracker.Client_Division WHERE (((RTIClientTracker.Division_Region)='RTI') AND ((RTIClientTracker.Cut)>=Date()-1)) ORDER BY (Mid(ClientDiv.Client_Division,1,3));
It limits what it selects by using the current Date minus one day.
I need to skip over Saturdays, Sundays and Holidays.
I have written this query to get the last 20 records of a commodity:
SELECT TOP 20 CurrentMonthData.[Symbol], CurrentMonthData.[DeliveryMonth], CurrentMonthData.[DDate], CurrentMonthData.[Close] FROM CurrentMonthData WHERE (((CurrentMonthData.[Symbol])="AD") AND ((CurrentMonthData.[DeliveryMonth])=#12/1/2007#)) ORDER BY CurrentMonthData.[DDate] DESC;
But I need to compare today's close to the last 20 records from yesterday. Is there a way to say the Top 20 records from Yesterday? Then I can compare today's close to the maximum of the last 20 day's close. If anybody can help with this query I would greatly appreciate it.:)
I am trying to filter a form to show the entire weekend's activity on Monday but only yesterday's activity Tuesday through Friday. Using this code I can return Friday's results on Monday and yesterday's for the rest. How do I get the range Friday to Sunday?
Hello I am new to this site as well as MS Access and SQL. So any help would be appreciated. Sorry if this is too drawn out. I have this query that returns the previous business day's(Monday through Friday) number of docs sent from my department to another department. I would manually go into the SQL view in Access and change the date to yesterday. The easy part was having the code do the previous day. The problem comes Monday morning when the code does the previous day. It will do Sunday which will return nothing since I really want Friday's numbers. I am trying to figure out how to take into consideration if the query is run on Monday morning and to automatically choose Friday. I checked out DayOfWeek() and Case When condition statements but really unsure about syntax and whatnot. Here is the SQL view of the query...
I have a combo box that when selected autofills a due date box. I forgot that I needed to skip weekends and only count business day. For example if you choose standard it adds 2 days to the date but I need it to skip weekends. Any help would be greatly appreciated.
Private Sub YourComboName_AfterUpdate() Dim intDays As Integer Select Case Me.YourComboName Case "Standard" intDays = 2 Case "Sensitive" intDays = 5 Case Else intDays = 10 End Select Me.YourDateTxtbox = DateAdd("d", intDays, Date) End Sub
I am not a programmer so need to be led by the hand, but can anyone see a way of achieving this which I could copy? My simple mind tells me that I have somehow to suppress the × and + during the sort. But how?
SELECT [qry_1].Month, B_Division_Group.Grouping_Name, Sum([qry_1].Month_Client_Count_from_B) AS Monthly_Count, Sum([qry_1].Month_Assets_from_B) AS Monthly_Total, Sum([qry_1].YTD_Client_Count_from_B) AS Yearly_Count, Sum([qry_1].YTD_Assets_from_B) AS Yearly_Total FROM ([qry_1] INNER JOIN tbl_branch ON [qry_1].BranchCode = tbl_branch.BranchID) INNER JOIN B_Division_Group ON tbl_branch.BranchName = BDivision_Group.N_Br GROUP BY [qry_1].Month, B_Division_Group.Grouping_Name;
While all monthly and yearly values were positive, the query produced exactly the results expected (i.e. one row of data for each Grouping Name/Month combination, containing the overall totals for each field).
Now, however, some Grouping Names have minus values and the query is showing an extra row (one for positive values, one for negative). It's as if having one or more minus values is being treated as a new Grouping Name/Month combination.
Is Sum() the correct method to use, when dealing with negative values, or should I be using some other function?
:confused: I have a Text box on a Form that copies data entered into other Text boxes on the same form. Sometimes there may be the odd text box that does not have any data in it. Is there anyway that I can put in code so that the blank text boxes are not copied into the main text box i.e. Only the populated text boxes are to be copied. Thankyou for any assistance that you can provide.:eek:
Let me just start out by saying that when it comes to Access, I ride on the "little bus", so I apologize in adavance if this is a lame question.
I have a table with two date/time fields. One for reporting the date and time of an issue start, the other for the date and time of an issue ending. I'm trying to find a way to get the number of hours between the two without including weekends. I found a module on mvps.org that excludes workdays. However it doesn't seem to work in this case. For instance, if I put in an issue start as 02/10/2006 7:30 am, and the issue end as 02/13/2006 6:45 am, it only counts it as one workday. It won't count the 13th as a workday until the time is >= 7:30 am.:confused:
I'm simply stumped. Any help would be greatly appreciated.
I am currently importing data using the the "TransferText" method in Visual Basic; e.g., expression.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)
Per the above description, I can either include the first line as a field name, or as data. Is there any way to simply ignore this data row and start the import from row 2 onwards?
I am currently trying to import a text file into an Access (2000) Table. My text file has a header line (first record) and a footer line (last record) which I want to ignore when importing. I am sure someone out there would have had a need to do something similar to this in the past. I am wondering if someone can point me in the right direction.
I have tried a few different google searches and nothing promising seems to come back. Mostly just references to check the first line for field headings box.
I have a db with two tables linked by a Set_ID field. One table characterizes set information (date, time, location etc) and the other table has records for groups of fish caught in the set. The fish_table has (amongt others) a field for species (text), clip-status (yes/no), coded wire tag status (yes/no), and 'count' (number). The count field is necessary to allow input of groups of fish en-masse, or individually, depending on the amount of accessory information obtained.
Obviously, some sets catch no fish and so no record is entered into the fish table for those sets.
When I design a query, I want to produce a table that sums up the count field for each set, and produces subtotals for each species (and for the 4 variations of the clip/tag status fields).
My efforts so far are only partially succesful in that I can produce the correct subtotals, but only for sets where something was caught. Sets with no corresponding fish_table record are ignored instead of treated as zeroes.
Is it necessary to manually enter a 'zero' count for each species of interest for each set that we do? (Massively time consuming) Or is there some other way to query the db that forces the query to equate no fish record with a zero value?
Any thoughts? I've searched the forum, and googled, but haven't come across anything I could recognise as analogous to my dilema.
In a query, I'm using concatenation to join multiple address fields (organisation, building number, street address, etc) into one field. I'm also inserting line breaks to create a formatted address and then creating a form on the query so the formatted address appears in a text box and can be copied and pasted straight into my label-writer software to print address labels.
However, one problem with which I am now presented is where a field is blank. E.g. if one of my contacts is retired, then they don't have an organisation listed in the "Organisation" field. This means that a blank line is left between their name and the rest of the address.
When running a mailmerge there is the option of skipping blank fields so that a blank line is not inserted in such cases.
I have 2 tables “MDW” and “LeadList” Each table contains the same type of data (Contact information) Some duplicate records MAY be in both tables.. (I would like to ignore the dupes). Each table has a unique ID field but are in no way related
I need to create a 3rd table “NewTable” WHERE… MDW!FirstName is not = to LeadList!FirstName And MDW!LastName is not equal to LeadList!LName And …….. I need to look at 10 fields
My thought was to create a query with both tables. Create Joins (where both are equal) for all 10 fields and moving on from there. The fields may or may not have values. I don't think I could add records if only of the indexed fields are null... Can I?
Does anybody see me walking in front of a freight train? Or, any other ideas?
I have a number of fields that hold values (scores - either 0,1 or 2) that need to be summed but in some cases the value is 99 (which is recognised by our stats program as a N/A type of anwer- This occurs when a question is optional and the user hasnt answered it. I did not want to value to be null (since its not like the user didnt answer it because they missed out on it by accident but rather they could not) and it couldnt be 0 either since 0 actually means something different in the context of the questionnaire.Lets say I have Q1 which has a value of 0, Q2 has a value of 1, Q3 has a value of 99 and Q4 has a value of 2I want to get the total of Q1+Q2+Q3+Q4 for a particular entry but if the value of any or all of the questions are 99 I want access to ignore it and just sum the rest of the values i.e. the sum should read 0+1+2+(99)=3 not 102I want to be able to include this sum function in the expression builder if possible since I am using it for a report. Can someone please help?
I have a Text box on a Form that copies data entered into other Text boxes on the same form. I have used the formula =[Text2] &" "& [Combo26] etc in the main text box that I want all the information in. Sometimes there may be the odd text box that does not have any data in it. Is there anyway that I can put in code so that the blank text boxes are not copied into the main text box i.e. Only the populated text boxes are to be copied. Thankyou for any assistance that you can provide.
I'm using access 2007 and have some date fields in my forms, i'd like to be able to blank out weekends and public holidays from the calender/date selector in these fields so theres no way those dates can be selected. Is this possible? Thanks.