I would like a query to return dates based upon the input of just the day and month. At the moment I have a parameter query which asks for 'start date' and 'end date' and this works fine, but I want the query to return all the records for all the years in the database and not just the current one (date format is dd/mm/yy)
So if I type <start date> 01/01 and <end date> 02/01 the query will return:
I've been trying to solve this problem for the past month and at the verge of destroying my PC! I've trawled through numerous web sites but just cant seem to figure this one out.I'm trying to get access to tell me what the next service moth is based on the start date, end date, and the frequency of service.
For example:
Start date: 01/01/2014 End date: 31/12/2015
service frequency: Every 4 months
Using the above information the service months are: 1 - April 2014 2 - August 2014 3 - December 2014 4 - April 2015 5 - August 2015 6 - December 2015
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.
I have a table which has a column 'Date', which contains dates in the format dd/mm/yy. I wish to produce a quiery which will produce an extra column entitled 'month', which will match each date to the month of that date. I know there is a formula which does this (called 'month' i think), but i dont know how to use it to achieve what I want.
Hey all, I have a form that has two textboxes, one box is for month and the other is for the year. After I enter the month and year I want, I click on a command button that previews a report. Well, the report is not giving me the information for that particular month and year, actually it gives me nothing.
The report gets its information from a query. In that queries criteria field is the below expression that I have.
Like [forms]![frmPEL]![cboMonth] & "/*/" & Right([forms]![frmPEL]![txtYear],2)
Can anyone see anything wrong with this? I should be able to enter the month and year and click on the report button I want and preview that report, but no joy.
I have an appointment database with, of course a Date field.I would like to be able to filter the records to show appointments for a particular month.I want to see what appointments I have for September, for example.Ideally I would like to populate a combo box with month names and filter the form based on the month selected.
I cannot get my query to return result when I enter the month of "June" as search criteria. June is listed in the table and query, every other month is returned except June.
I need to calculate the next date[Due Date] in 6 months, which is simple
[Patient's file].[Last Appointment Date]+180 as [Due Date]
and I have to group the Due Date by the next month. Lets say the due date is 08/25/07, so it has to be grouped as September group at the top or the list. It should be displayed as "September, 2007"
How should I assing let say 08/01/07 - 08/31/07 dates to September groups. I mean the statement should be universal for any day and any month of the year.
I need a query that lists each day of the month (or quarter) that is selected from a form. Meaning, if a user selects June 2005 on the form, the query will return to show every date from June 1, 2005 to June 30, 2005. Is this simple, tiny little thing possible?
The reason behind this is that on a report, I need to show data for every date. If there is not data entered for that date, it still needs to appear with the date listed, but with no entries represented.
I have created a query to limit the list in a combo box to display the months and years of the date field in a table. Below is the code that I am using. This works fine except this displays the years within the months - for example:
January 2012 January 2013 January 2014 February 2012 March 2012 March 2014 April 2012 May 2012 May 2014
I would much prefer it to sort by month and year:
January 2012 February 2012 March 2012 April 2012 May 2012 January 2013 January 2014 March 2014 May 2014
Code: SELECT DISTINCT Month([QTDate]) AS MoNum, Format([QTDate],"mmmm yyyy") AS MoName FROM MainTBL ORDER BY Month([QTDate]);
How to change this code to make it sort on Month and Year, not just month?
I am trying to create a Totals Query which returns a data set between two dates. So far I have managed to select the data I want (Please see attached screenshot). However, I only want to select records between a date range working on my field [DueDate]. If I add the due date field to the current query then it removes the grouping and all records are displayed.
I am trying to figure out a logical WHERE condition. I have a Query that gives me data,
Code: Name | Start | End --------+-----------------+------------- Paul | 30-May-15 | Eugin | 21-May-15 | 28-May-15 Francis | 04-Mar-15 | 08-May-15 Samuel | 10-May-15 | 13-May-15
I want to have only Paul and Francis. As Eugin start is 21-05-2015 and End is the same month, so is Samuel's. So I want to eliminate those two records.I have checked pbaldy's web of overlapping records. But unlike that, I need something customised.
Begins and ends before range - we don't want - Yes Begins before, ends during - we want this one - Yes Begins and ends during - we want this one too - NO Begins during and ends after - we also want this one - Yes Begins and ends after - we don't want this one - Yes Begins before and ends after - we want this one -NO
ID Program Year Program Start Date Midpoint Date Internship Date End Date
I have successfully created queries to create reports showing all Start Dates by Month no matter what program and similar reports for all the other date queries. What I need it to do is list everything happening within a month and sort them by category. How do I do that? I've attached the database so you can see my queries as well as the main switchboard reports associated.
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 am wanting to get an expression that will return the difference in years and months between 2 dates. Specifically, I want to know peoples ages in years and months based on a person's D.O.B and todays date. I have managed to do it in years:
I want to create a simple query from a list of orders dating back over 12 months.
The fields I have is Ord_Date, Qty
I need to show the orders by month for the last 12 months.
The problem I am getting is that the orders for the month of April (as we are in April now) contain orders from 1st - 22nd April 2013 and orders from 23rd - 30th April 2012, therefore confusing the figures.
I would just like orders grouped by month with a total qty dating back 12 months, but without any old orders for the current month.
Thank you for all you help with the above title. Your suggestions were all implemented.
Code: Private Sub GPA_AfterUpdate() If DIVISION = GRADUATE Then 'Honors field is empty HONORS = "" End If If UNDERGRADUATE = True Then 'Apply appropriate honors End If If GPA < 3.2 Then HONORS = "" ElseIf GPA >= 3.2 And GPA < 3.5 Then HONORS = "CUM LAUDE" ElseIf GPA >= 3.5 And GPA < 3.8 Then HONORS = "MAGNA CUM LAUDE" ElseIf GPA >= 3.8 Then HONORS = "SUMMA CUM LAUDE" End If End Sub
Here is the issue: Form created with with a combo box called "DIVISION."The choices of the combo box are "GRADUATE" AND "UNDERGRADUATE." There is a text box called "GPA", where a gpa is entered. then, there is an "Honors" field. When a gpa is entered, the honors box returns the appropriate message.
When graduate is selected from the combobox, the honors box should be empty. (That part does not work.) When undergraduate is selected from the combo box, it works.
I have 2 formulas that work for me in excel. I hope to be able to replicate the result in Access.
1) I have the following in a field called PROBLEM STORE NAME: SALE (DAY 1 - HD) Mt DRUITT (WOWPOS) (E) THE MALL (WOWPOS) TOWN HALL (RF) LIVINGSTON
I want these to be grouped as WOWPOS or ISS460 (if not WOWPOS), so return in field STORE TYPE as: PROBLEM STORE NAME.............................STORE TYPE SALE (DAY 1 - HD)............................................... ..ISS Mt DRUITT (WOWPOS) (E)......................................WOWPOS THE MALL (WOWPOS).......................................... ...WOWPOS TOWN HALL (RF).............................................. ......ISS LIVINGSTON........................................ ..................ISS I use the following formula in Excel to achieve this when I export query results from Access: =IF(ISNUMBER(SEARCH("wowpos",K2)),"WOW","ISS460")
2) I have a field VENDOR ID that may or may not contain ANY detail. If the cell is empty I need it to return N/A. If populated I need to return VENDOR CALL. The following formula achieves that for me in Excel: =IF(ISBLANK(X2),"NO","VENDOR CALL")
I have created a form based on a query with students' gpa in the form. The gpa is in a specific field. There is another field for honors with is for summa cum laude, magna cum laude, cum laude.
Here is the issue: If the students' gpa is 3.8, return "summa cum laude" If the students' gpa is 3.5, return "magna cum laude" if the students' gpa is 3.2, return "cum laude"
This applies to undergraduate students only, which there is a field for.
I have a form with a field within it called ID number, another table contains the ID number with a person's name next to it. I was wondering how I can make it so that in the form, when the ID number is entered, the name of the person also shows next to it and does so automatically for each different record. I am pretty sure that a subform is needed however it doesn't seem to work for me so I must be doing it wrong. How would I do this?
