Queries :: Selecting All Date Fields Between Two Dates?
Jul 31, 2013
I'm trying to select a range of relevant dates for an amortization calculation (see my earlier thread on this subject here), but I'm having a bit of trouble making the SQL work.
I have a table called "t_AllMonths" that has only one field: MonthStartDate which contains the first day of the month for a very wide range of months over something like a ten-year period. I'm calculating amort for assets which will be amortized for some subset of those months (defined by the asset's Amort Start Date and Amort End Date). Further complicating matters, the amortization may be suspended during certain "hiatus" periods when the asset it not planned to be in use, and may differ by which business units make use of the asset.
Table: t_AmortPeriods
Fields: AmortPeriodID, AssetID, Amt_Period_Num (which I don't expect to use in this), StartDate and EndDate
Right now, I'm just trying to pull the range of dates between the earliest amort start date and the latest amort end date. (Min of StartDate and Max of EndDate, respectively) for a given AssetID.
My sql looks like this:
SELECT t_AllMonths.MonthStartDate,
Min(t_AmortPeriods.StartDate) AS MinOfStartDate, Max(t_AmortPeriods.EndDate) AS MaxOfEndDate,
t_Assets.AssetID
FROM t_AllMonths,
t_Assets INNER JOIN t_AmortPeriods ON t_Assets.AssetID = t_AmortPeriods.AssetID
WHERE ((t_AllMonths.MonthStartDate) Between [MinOfStartDate] and [MaxOfEndDate]);
I keep getting an error message that reads "Run-Time Error 3122: You tried to execute a query that does not include the specified expression MonthStartDate as part of an aggregate function."
I have a table which includes a start date field and completion date field for housebuilding.
I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.
It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.
'WHERE ((OperationalRiskEventTable.DateReported)>=Forms!U pdateForm!UDateBegin And (OperationalRiskEventTable.DateReported)<=Forms!Up dateForm!UDateEnd)'
in a query by form.
The problem is that you have to enter a date in the between values for results to show. If I don't enter information into a different field such as Full Name but I enter in 40 into Age then everyone that is 40 years old will show. On the other hand if I enter 40 into the Age field but I leave the Date Reported fields empty then no results will show.
How can I change it so that I don't have to enter dates into the date reported fields for results to show?
I have a query that is used to capture all the fields that can be possibly used.When run & exported to excel the user then simply deletes the columns with the fields that they don't require.We do this so we don't have to keep creating queries over and over again when you just use one that does all but then delete what you don't need afterwards.
Any way of using a form with tick boxes for each of these fields so when they user ticks the fields they require reporting on then only those fields will be returned in the query.
In my Query I have a Date Received field and a Date Approved field.
I would like to calculate the number of work days, which excludes weekends, between Date Received and Data Approved fields.
It would be more desirable to calculate the number of work days, excluding weekends and governmnet obsereved holidays.. but I don't know if that can be done... If not I can stick with the number of work days excluding weekends.
How would I go about doing this? I would need to add a new column to my query?
What would I put for the field and criteria? What kind of expression do I use?
I have a table called Packages, in this table I have multiple fields with just short dates example of fields (ID), (Home_1), (Home_2), (Home_3) and (Home_4). What I was wondering is there a way to comine all of the Home fields into one for the purpose of counting the number of dates for all the records. Total number of dates of all the Home fields? I am using Access 2010
I have a DB that consists of Movie titles and the multiple dates ( as many as 10) on which they will be used in the coming year.I built a flat table with 10 date fields.Then tried to build a relational table with just movie title and dates linked to ID.I cannot work out an ability to SEARCH the Database for a SPECIFIC DATE and get returned a listing of ALL Movie titles that will air on that date,
I have a query that is based on three queries. 2 of those queries have the same fields but return data for different dates. The 3rd query returns the dates needed. In other words, I am trying to return all results from both queries. these are the results I want:
Report_ID Report_Date Contents Approvers Standard ops 5/1/13 daily operations rpt tom tomlin royer manuf 7/1/13 syndication rpt rob davis
etc.
These are for year-t0-date. there is a YTD query that returns all dates needed. The other two queries return records on different dates, on is for dates <5/5/13 and the other is for dates >5/4/13. I have listed the SQL below. It returns the correct records, but for the Record_ID it returns -1 in each field instead of the report_ID text. So, I need to get the other fields to show up correctly.
Code: SELECT [Daily_rpts_YTD_due_before_5_5_2013]![Report_ID] Or [Daily_rpts_YTD_due_after_5_4_2013]![Report_ID] AS [Report ID], due_dates_Daily_YTD.Due_dates FROM (due_dates_Daily_YTD LEFT JOIN Daily_rpts_YTD_due_before_5_5_2013 ON due_dates_Daily_YTD.Due_dates = Daily_rpts_YTD_due_before_5_5_2013.Due_dates) LEFT JOIN Daily_rpts_YTD_due_after_5_4_2013 ON due_dates_Daily_YTD.Due_dates = Daily_rpts_YTD_due_after_5_4_2013.Due_dates WHERE (((due_dates_Daily_YTD.Due_dates)=[Daily_rpts_YTD_due_after_5_4_2013]![Due_dates] Or (due_dates_Daily_YTD.Due_dates)=[Daily_rpts_YTD_due_before_5_5_2013]![Due_dates]));
I am trying to calculate a value based on comparing the current date to the dates in these fields. I am using the below formula. However, using 6/27/2013 as the current date, my formula keeps resulting in "Bronze" when it should result in "Standard" Am I doing something wrong?
=IIf(Date()<[Bronze],"Standard",IIf((Date()>=[Bronze]) And (Date()<[Silver]),"Bronze",IIf(Date()>=[Silver] And Date()<[Gold],"Silver",IIf(Date()>=[Gold] And Date()<[Platinum],"Gold","Platinum"))))
I am involved in centrally managing a project , a part of which involves employees of certain companies being given a weekly subsidy for study on academic courses. I have successfully set up a table for all the learners and a related table for companies.
Each learner will receive a fixed weekly rate subsidy in the first year, and a lower fixed weekly rate subsidy in the second year. Somebody (not me) will need to check their paychecks to ensure that employees have been paid and all is above board. The learners/employees can start on the programme at any time. I need a way to track this (total claimed amounts, where they're up to etc).
My theoretical solution, is to have a field for each week a learner is on the programme (52 first year, 52 second), and instead of entering a currency amount in the field, the check-person can enter the date they saw the evidence and are happy with it. I thought then if I create a query that searches any dates that fall within the current claim period (eg >01/01/2015, <=Date()), and multiplies field count that fall within those parameters by first/second year subsidy rate if they're in that table. I think my logic is sound, I am just not sure how to write the query/expression, or if it's possible, or if there is a much simpler way to do it.
I would like a command button to print records from a select query displaying dates for the past 2 days but not for todays date. e.g. today is the 29th, I want to print records for the 27th & 28th.
Data is being recorded hourly and kept for 2 months so there will be new and old data that I don't want printed.
I am totally new to Access and this is my first database.
I have a database for volunteers in which one table is basically a calendar. In this table I have fields for the month, day of the week (i.e Monday) and day of the month (i.e 21st). There is also a filed called "Special days"
Sometimes I may want to produce a report that selects only certain days such as Saturday, Sunday and other days marked as "special days". Another time I may also want to produce a report that selects other days.
At the moment I have a "test filed" in which I place a 1 for those days i want to select then I choose all those with a 1. This is obviously a very poor construction.
I have a database which records time spent on various projects and I would like to be able to calculate the time spent on any particular project between two selected dates. The report and the query behind it already works to display the time accrued on all the dates of a project, but I want to be able to break it down to small ranges of dates
I have a form on which the user selects the start date and the end date. How can I apply this calculated information as a criteria in a query so that I can produce a report with the relevant information on it.
I already have the report and the date selection form designed. The dates selected go into two unbound fields which have a date format.
I've built an an update query that updates a Yes/No field "Expired?" to "Yes" if the expiration date has passed i.e. if the current date is newer than the expiration date. I thought this would translate into the current date being greater than the expiration date?
UPDATE Alert SET Alert.[Expired?] = 'Yes' WHERE Alert.Current_Date>Alert.Expiration_Date;
When I run this, it identifies the records to be updated, but then stops updating the fields due to "a conversion failure."
I am trying to calculate the difference in dates between two fields. How do I find the difference in days between field one which contains the date 04/12/2011 and field two which contains the date 04/12/2013? I have tried to use the datediff function, but it keeps telling me it doesn't recognize the field name, even though the spellings correct.
I have created a database to keep track of transcripts that are requested. This database has their names, ID number, date requested(With a pop-up calender), transcript type requested(This is a combobox where the choices are "Official Copy" and "Student Copy") and address.
So far, it works great. What I wish to do is run a report that I can choose the begining date and ending date for a specified month to see the total "Official Copies" and "Student Copies" for a given month.
I have a db tracking vacation times for staff. One of the fields tracks a members vacation start and end dates. I need a way to pull a report to see who is on vacation based on Date() (today).
For example:
A record for John Doe has him start vacation 08/19/2014 and end vacation on 8/28/2014. If I wanted a report that shows who is on vacation today, 08/25/2014, using the date() function), how would I do this?
The input form of the database have one common table containing data for all parameters for row source. As all data was in one table had one ID. My problem is I am trying to run reports from a form after selecting dates and parameters.
For selecting parameters I created dynamic combo box. For dynamic combo box I needed ID and description of the parameters. So I created separate tables for each parameter with ID and Description column. Which ran me into the problem that when I run report based on main input form which has different row source table and my report form has different row source tables and reports doesn't run when I base my queries on the report form.
Is it possible to create a query to select all dates from a given reference date? I don't mean all dates in a table - I mean all dates generally?
(The idea being to fill the first field in the resultant dataset with the list of dates, then run subqueries off that to fill the remaining calculated fields)
I'm currently using a date field in one of my tables to populate this first field (the full SQL is in a separate thread here)
But that was just a convenient way of getting a list of dates; the dates in that table don't actually have any significance to the resulting dataset (other than they should roughly overlap with the dates I'm looking for)
The flaw in that method is that the table from which I get those dates can only ever have dates up to and including yesterday. I also need to get today's date in there (and calculate the subqueries based on that date as well).
It's also possible - although unlikely - that there could be random dates missing from that table as well - in which case I need to plug those gaps and calculate my fields for those missing dates as well.
For clarity; that first field (AsOfDate) should contain every weekday from the earliest date in that table (i.e. Min([tblBalances].[BalanceDate]) up to and including today. It doesn't matter if any of the dates inbetween are missing from tblBalances as the subqueries will just return zeroes for those dates (which is exactly what I want to see).
The query is there to query data, not to edit data or manipulate data in anyway. However, I am disgusted by the appearance of the date picker when I pick a field that is a date field.
Is there a way to hide/remove these date pickers from the query sheet?
Within the table itself I turned the "Show date picker" property to "Never" but that didn't make a difference.
I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)
I have a table "Assessment tracker" with the following structure
Name Type Candidate short text Unit short text EV1 Date Date EV2 Date Date
My Data:
Candidate Unit EV1Date EV2 Date TH1 10 07/05/2015 25/05/15 TH1 10 07/05/2015 07/06/15
I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.
It does this by using a function shamelessly copied from the web somewhere...
Function Maxdate(ParamArray FieldArray() As Variant) ' Declare the two local variables. Dim I As Integer Dim currentVal As Date' Set the variable currentVal equal to the array of values. currentVal = FieldArray(0) ' Cycle through each value from the row to find the largest.
[Code]....
This is working well (I think)
I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.
Query: SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate FROM [Candidate AC Dates] GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;
But this is returning
Candidate Unit MaxOfAchdate TH1 1025/05/2015
I expect it to return
Candidate UnitMaxOfAchdate TH1 10 07/06/2015
It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)
I am using Access 2007 and I have had a curious issue arise.
I like to use the Date Picker feature on all my forms to make it easier to enter dates. I have set up each table's date field property to "show date picker for dates", as per attached (.jpg)
When I use that particular field in a form, I also set up the field to allow for the Date Picker option (date picker form property sheet.jpg)
The curious part is that not every form that I set up actually shows the date picker when I select the date field. Some forms it works perfectly, others it doesn't.
