I am trying to write a query that looks at groups of records for averages and maximums but the records are only from Mon-Fri (Trading Days). I have a query that runs everyday and compares today's close to see if it is greater than the maximum close of the last 20 trading days. I want to use between date()-21 and date()-1. BUT this will include weekends where there is no data.
QUESTION: Is there a way to say between the last 20 trading days(Mon-Fri)?
I researched dateadd() and found one place were it says that "w" is used for weekday. http://www.database-design-2000.com/dateadd.htm BUT when I did a simple query to see if it would exlude the weekends it doesn't. I will have no data for the weekends but if I am doing an average of the last three days and this is run on Monday, it will only include Monday and not the previous Thursday and Friday. Thanks for your help:)
I am using Access 2010. I've calculated the age of clients by creating a new field with Age: Year(Now())-Year([D O B]) but I cannot figure out how to use a parameter query to return the age of the clients between age 20 and 30, 30 and 40, 40 and 50 etc.
[Forms]![FormName]![Field] or [Forms]![FormName]![Field] Is Null
Ordinarily this works fine in returning either the selected value or all values if left null.
I need to pull in data from a Crosstab query, which means setting my parameter labels to [Forms]![FormName]![Field].
The problem is that setting the parameter labels is conflicting with pulling all records if the form dropdown is left null.
If I keep my parameters simple and just say [Forms]![FormName]![Field] then the query works with the crosstab data, but I can't do that. I need to show any records if the dropdown is left null.
The crosstab data isn't specifically the problem but needing to set the parameter names seems to be
I think I may have found a workaround by labeling the column headings in the crosstab, which means I don't have to assign parameter labels
It would still be good to know if there's a way of making it work with the parameter labels but this will do for now...
I am trying to create a parameter query to return dates that have 2 years remaining.
For example I have dates for when mortgages expire, and I want to recognise the dates that have two years remaining using a parameter query but I can't figure out if I use DateAdd or DateDiff.
I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.
So if my input table looks like this
[strField] "kick the ball" "return the pass"
my query result should looks like this
[Orig] [new] "kick the ball" "kick" "kick the ball" "the" "kick the ball" "ball" "return the pass" "return" "return the pass" "the" "return the pass" "pass"
Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.
With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.
I m new 2 access, i have a query which i simply do not know how to handle. Please if anyone can help me:
I have a form having two dates on it, First is Invoice Date and the second is delivery date. What i wish to see is that automatically the invoice date takes date from system (this part is done), and the delivery date field automatically adds 2 more days to it and excludes weekend days, saturday and sunday. for example: a date of 6 July 2006 was picked by invoice date field from system, the delivery date now should add 2 more days to it plus it must not include weekend days in it, so the resulting date in delivery date field must read 10-Jul-2006.
I have a database (big surprise :rolleyes: ) and I need it to calculate and filter information based on a process name, a number of days for that process and a schedule date.
For the process NC Batch I need it to calculate out 15 working days from today excluding weekends and holidays and then find the records in the orders table that has a schedule date less than or equal to the returned date. I have a calendar in excel that does it with a look up.
I have a table listing holiday dates and a table listing processes and the corresponding day count.
In the query I currently have it listed as
[ScheduleDate]<=Date()+20.
but this will not take into account holidays and it includes weekends.
I know there are several threads on work day functions but I could not get any of them to adapt to my needs. Sorry.
I live and work in middle east. Here the weekend starts on Thursday and the week starts on Saturday. Thursday and friday are Off Days. Saturday is the first day of the week. I have a problem related to date calculations.
I have a form having two date fields on it.
1) INVOICE DATE 2) DELIVERY DATE
i wish to see that the INVOICE DATE automatically takes system date from system and same applies for DELIVERY DATE but the only difference will be that the DELIVERY DATE field automatically adds next two more days from the system date. If either of the next two days that are added to system date is/are weekend (thursday or Friday) then it must show the first available working day after the weekend.
for example: a date of 5 July 2006 was picked by INVOICE DATE field from system, the DELIVERY DATE field now should add 2 more working days to system date (it must not include weekend days in it), so the resulting date in delivery date field must read 09-Jul-2006.
I am sure that there are people who can solve my query
I'm an Access novice! I have a table with a library loan date field defaulting with today's date and a due back field. I want the due back field to show a date 3 weekdays after the loan date. I've tried using DateAdd("w",3,Date()) but this just adds 3 days, not weekdays. Does anyone know how do I get it to skip weekends? If I need to use VBA, please supply instructions for including the code, as well as the code itself. :confused:
What I am trying to do is execute a function M-F every 2 hours between 4am and 6pm. The 2 hour part was easy, I just created a form with a timer, but the other part isn't working so well, here is my latest attempt.
Code: StrTime = Time() If Not ((Weekday(Now()) = vbSaturday) Or (Weekday(Now()) = vbSunday)) And (StrTime > "4:00" Or StrTime < "18:00") Then
I have a form whose data source is a select query, q3, that is built from 2 other select queries. I'll call them q1, q2, and q3. q1 is a parameter query where I enter a "Cutoff Date" that the 3 queries manipulte and generate the desired results that appear in the form. The problem is that I don't know how to capture the parameter "Cutoff Date" from q1 to display on the form.
I have a query that requires a Start-Date and an End-Date to be input by user for the Where clause. It is asking for both over and over. I've had it ask from 1 up to 4 times! :eek: Shouldn't it store the input and only ask for it once? I'm thinking that the way my query is arranged may be causing it to have to loop through that section more than once to find the data, but that's just my theory. Any help would be great!
Here is my code (abbreviated slightly):
SELECT DISTINCTROW C1.*, C2.* FROM Pen AS C1 INNER JOIN Jobs AS C2 ON C1.subno=C2.[Jobs Acct] WHERE ((C1.typ="SS" Or C1.typ="CC" Or C1.typ="PP" Or C1.typ="TT") And C1.stdate>=[Enter Start Date] And C1.stdate<=[Enter End Date] And C2.[Type]<>"EE" And C2.[Type]<>"QQ" And C1.entdate<=C2.[ChangeDate]+60);
I'm selecting rows from "Pen" and "Jobs" that have the same subno/Jobs Acct numbers (text), then there are criteria for "Pen" types, user inputs criteria for date range (Start Date and End Date) and there are criteria for "Jobs" types. Finally, there's a cross-table criteria based on a date field ("Pen" entdate should not be more than 60 days past the "Jobs" ChangeDate). Tables are in quotes in my explanation here.
So running the above, it asks for user input "Enter Start Date", then again for "Enter End Date"...but then it asks for each again...and again...and sometimes again!
Help! :confused:
P.S..I didn't notice this repeating until I made it user input (parameter query) because it was using whatever dates I hard-coded in there before.
HI, i have a field in a query called [cost_type], these typically contains the values "principle works" or "additional works" how can i set the query up so that it doesn't return the "works" part of the record? Could i just return the first 10 characters of the result? thanks
Each record in my table has six possible sale dates as a result of cancellations. My problem results when a record is sold twice during the queried time frame (SaleDate1 and SaleDate2). Currently my query uses an if statement and if they are in the same time period, it will only return SaleDate1. I would like to see the record returned twice (once for SaleDate1 and once for SaleDate2). Any suggestions?
Hi Everyone, I have a database that I enter time for in the form of military time, I would like to make a query to count the number of entrys in the PM,AM, And NIGHT. For instance if a record has a time of anywhere between 700 or 2299 I would like it to be in the AM catagory, then I would like to total up the number of AM records. Any help would be great. Thanks.
Is there a SQL function in Access that will allow it to return (as a field value) the name of the table where a particular record was found?
The reason that I want to do this is because I have a single table (Table A) with building address records counted and broken down by state and by month - but some of the records belong to smaller groups (e.g. Zone 1, Zone 2, etc.) that aren't states, but that I want to count in the crosstab as if they were. There are building addresses on Table A that belong to each of these four groups, so I need to somehow match the records on Table A with the appropriate Zone, and place the name of that Zone in the "State" column of the query results instead of the actual state so that I can then count the results in the same crosstab.
I had the intention of resolving this by creating three tables (with the listings of address information for each Zone #), then matching records in Table A to each Zone table in a query by the address field to get each record's appropriate Zone no.(if available), then using the results of this query in my crosstab - :confused: but I can't figure out how to get Access to create a calculated field that lists from which table a resulting query record came from, so I can't continue.:confused:
I placed ="G*" in a text field to get all names that begin with G and a blank return was issued. Maybe the manual I have has the wrong info. I have Access 2003 and a Access 2000 manual. Please help.
I have a table listing employee names and their birthdates. I have to create a single query that returns the names of the oldest employee and youngest employee. it's easy to return only their birthdays, but i can't figure out how to return the names (with or without their birthday). I can only use a single query.
I enter values into a table with 2 decimal places.
I have a query that applies percents to the values - I have set the results to have 2 decimal places.
I have another query that summarises data from the previous query, however the results I get are not right, the decimals differ from the actual result, if I make a sum of the values by hand.
It seems as if the query I have made to apply percents only DISPLAYS data with 2 decimals instead of actually rounding it, so that the other query summarises data with more than 2 decimals, and thats where the difference int the end result comes from. I don't know how to fix this however.
May I know some to construct any functions or query string that can track the no. of records that mean certain criteria in the run time.
Basically I want to track the records in a subform (in datasheet view). Should the no. of records is 0, then I can disallow user from saving it onto the table for better record keeping.
I have searched for an answer and spent some hours but still not getting it.
I have played about with the query joins and still nothing?
I have never had this problem with my database in 5 years (well not that i have noticed). Some records are fine others are triplicated and quite a lot are blank?
This is the SQL for the query.
SELECT Employee.EmployeeID, Employee.FirstName, Employee.Surname, Employee.Address, Employee.[Town/City], Employee.Region, Employee.DateofBirth, Employee.Nationality, Employee.MaritalStatus, Employee.PassportNo, Employee.ExpiryDate, Employee.Nextofkin, Employee.Relationship, Employee.NOKAddress, Employee.ContactNo, Certificate.CertName, EmployeeCert.Level, Trade.TradeName FROM Certificate INNER JOIN (Trade INNER JOIN ((Employee INNER JOIN EmployeeCert ON Employee.EmployeeID = EmployeeCert.EmployeeID) INNER JOIN EmpTrade ON Employee.EmployeeID = EmpTrade.EmployeeID) ON Trade.TradeID = EmpTrade.TradeID) ON Certificate.CertID = EmployeeCert.CertID WHERE (((Employee.EmployeeID)=[Forms]![Employee]![EmployeeID]) AND ((Certificate.CertName)="IRATA"));
Basically i have to solve this problem.
I have a report that is based on this query and if the employee is not displayed correctly instead of the employees Name and Surname i am getting "#Error" ?
I have a query that uses a linked table from an external database and joins it with a table from my database to return records that contain the same values.
Is there a way to return only the records that are present in the external database and not in my db?
