I have a query that pulls data for the current month and the last two months. It had been working fine until 2015. The problem is that it's looking for the last two months but for 2015. How do I edit my formula to pick up the two months and for it to know it's a different year??
Code:
CMSPM2: IIf([LOB]="CMS" And Month([Month])=Month([Forms]![Main Menu]![EndDate])-2,Count([Question]),0)
I am building a database with Access 2013. The information contains data built from a workplace violence report form. I have to build a query to pick the data but must fall between two different years.The data range must be from 09/01 previous year (ex. 2012) and 08/31 current year (ex. 2013).
As the database collects more information, the year range will change but the other information will stay the same (ex 09/01/2013 to 08/31/2014).I do not want to change the query annually, just let it change the year automatically.
I currently have a form where users can enter an "End Date", click a button, and it queries the data from 12 months prior to "End Date" entered.
Is there a way to force the query to show all 12 months, even if there are no records for a particular month? For example: if the user enters February 2013 in the date field, I would like the query to return:
March 2012
April 2012
May 2012
June 2012
July 2012
August 2012
September 2012
October 2012
November 2012
December 2012
January 2013
February 2013
...so even if June 2012 has no records, it is included in the query with a value of zero.
This is what I have so far:
WHERE (((Qry_Tbl_Assets.Dte) Between DateAdd("m",-12,[Forms]![Main_screen]![End_Date]) And [Forms]![Main_screen]![End_Date]))
I can see which records are missing from the table I want to import to but this query picks up no records:
SELECT [Field1] FROM Table_Linked WHERE [Field1] NOT IN (SELECT [Field1] FROM TableA);
Yet, when I run them separately, all fields are displayed.
Am I doing something wrong with the NOT IN part of the sttement that access doesn't like? If you run this in SQL server it works fine.
I am trying to write a query from a main table which will show records which have a date of less than or equal to a Variable (tempvar), however the variable (selected from a form) may be left blank in which case all records should be shown.
I have successfully used the following but the records returned are only the specific date choosen not that date and all prior
Like Nz(([TempVars]![tvcldate]),"*")
So I have tried the following but it doesn't work.
Like Nz(<=([TempVars]![tvcldate]),"*")
I'm using the following code to allow the user to pick a record from a continuous form and edit it in a new window. For some reason my where condition isn't working properly as the edit form always opens to the first record instead of the record associated with the "Edit" button that was pressed.
Code:
Private Sub lblEdit_Click()
DoCmd.OpenForm "frmEditPlants", acNormal, , "[PlantID] = " & Me.PlantID, acFormEdit, acDialog
End Sub
My computer has been updated to 2010 whilst I've been off sick (was 2003 before my accident).
I've created a main table, for devises across the company, and a combo box/selection box based on another table which holds a list of all the "Responsible" employee's aswell as another combo box/selection box for the device location.
So the person entering the information, can enter all the information for a device (torq wrench, socket set etc), who is responsible for it and which department they belong to (where to find the device).
Which all works fine
However, I'd like to create 2 queries, one to enable the user to run a report of all device's allocated with an employee or to be able to run a query for all device's stored in a particular department.
But I have been unable to set the correct query criteria, to enable to query user to be able to selection from a drop down list, which responsible person or location to pull back the correct list.
I was getting an error asking me to set the parenthesis, I have now deleted criteria for both queries, as even if I put [Enter] and type a Responsible person's name exactly as its held on the table, the report comes back blank.
1) I am trying to use the same set of data for different months. I will try to explain...For each month I want to mark the data as "Statement Received" and "Statement Reconciled". I don't want to create a brand new database every month with the same data. The database will become massive too quickly. Is there any way that I can set the date by using the filter date combo box I have created on the frm_SearchMulti form and having a fresh set of data for just April. When May comes around I can create a new date and have a fresh set of data, but still reference April data... I hope I am explaining this okay
2) Would it be possible to add a column into the "SearchResults" ListBox for Statement Received and Statement reconciled? I would want to update it through the text boxes I made directly above it. The buttons right now do not do anything... they are just there for formatting purposes. I want want to be able to select multiple record in the listbox and update them all simultaneously
3) How can I make the "New Account" button actually pull up a new account?
Hi,
Firstly I rather unsucessfly tried the search function,
I want to automatically delete any enteries older than 6 months, I do NOT want to archieve them, simply delete them. I only have three entries;
Date
Address Line 1
Post Code
I was unsure how i would go about this or even where to begin so some help would be much appreciated.
Kind regards
Hey Guys,
I've been racking my brains on this one and need your help. I was asked this question by a friend who has just set up a database for a non-profit organisation in the UK. I have attached an simple example database to illustrate the problem.
There is a table called "tblMembers" which contains data on members of clubs and the dates their membership expires.
There is a query called "qryExpiryCrossCheck" and it needs to display people whose membership has expired in the previous month (i.e. May 2005).
Sounds easy? Well the query needs to know that 'now' is June 2005 and that the previous month is May 2005 and then as I said display people whose membership has expired in the previous month.
Any ideas/solutions would be greatly appreciated. As I said this is a favour for a friend who is carrying out some free work for a non-profit organisation.
Cheers,
Rusty
:D
Im looking for something that filters information for 6 months prior to any current, non-specific date. Like it will automatically get the data dated from 6 months ago from june, july, or august, without anyone having to go in an change the date criteria.
Below is a non working example of what I am trying to come up with. If I could get something like this, it would save me a ton of time going back and updating all the date criteria for my databases.
Forgot to post it:
>=Now()-Day(180)
I am pulling sales results per customer and month. For presenting the numbers in a diagram I would like to show 0 sales for months in which no sales were generated. At the moment the query results only the months in which there are sales > 0. Is there a trick that I don't know?
SELECT Year([InvoiceDate]) & ' ' & Month([InvoiceDate]) AS [Month], totalsales_per_invoice([invoices]![InvoiceID],[invoices]![tax]) AS Ausdr1
FROM Invoices INNER JOIN customers ON Invoices.CustomerID = customers.CustomerID
WHERE (((Invoices.InvoiceDate) Between #1/1/2013# And #7/1/2013#) AND ((customers.CustomerID)=145))
GROUP BY totalsales_per_invoice([invoices]![InvoiceID],[invoices]![tax]), Year([InvoiceDate]), Month([InvoiceDate])
ORDER BY Year([InvoiceDate]) DESC , Month([InvoiceDate]) DESC;
As a Access newbie I am looking into a problem that I hope to solve structurally and "transparently" via Access in stead of Excel.
I would like to compare two similar tables for different months. I am looking for:
+ loan id's that are new in table(t), i.e. not exist in table(t-1)
+ loan id's that exited: that is exist in table(t-1) and not in table(t)
+ loan id's that stayed in both
How do I solve this in the easiest way?
We have a field (tbl_Enrolment.CourseCompletedOn) which holds the date a colleague completed a course. I want to bring through all courses completed within 12 months of todays date ( Date() ) from our database. I checked google and it suggested YEAR but that would only bring through dates with the same year, not within a 12 month period.
View 4 Replies View RelatedI'm trying to breakdown a date into years, months, and days. Simple right?
The code I've been using for months returns a negative month if it happens to be the next month. Instead of returning 1 Year, 1 Month, 23 Days, it will instead return 1 Year, -1 Month, 23 Days.
Code is:
Code:
Months: IIf(IsNull([TIR]),0,IIf(Day([TIR])<=Day(Date()),DateDiff("m",[TIR],Date())-[Years]*12,DateDiff("m",[TIR],Date())-[Years]*12-1))
I have a query that is supposed to be showing todays data (as it is labeled) but it keeps pulling today's number date (i.e. the 1st, or the 17th) for every month, when i only want June 18th not including jan/feb/march etc. 18th.
I started running this query in may, that's why it only shows may and june's data.
I'm thinking it's definitely a problem with the criteria but check out the attachments and let me know
I want to create a cosstab query with dates for the next 12 months accross the top as columns and employee names down the side as rows. I then want to populate with data showing what each person is programed to do under the dates. For instance trainer 1 is delivering training from the 23/06-30/06 then designing a course from 02/07-10/07. that type of thing.
I could represent an activity using a colour i.e. yellow for delivery, green for design etc.
Also do I need to create a table with all the dates? Is there a quick way of doing this?
I have a report (based on a query)which informs staff of students who withdrew from our school, and the month and date they withdrew (sorted ascending). I am grouping by month and it looks like the attachment I've included.
It works well enough, but what I would really like is for the month column (Month([withdrawldate]) to show January for 1, February for 2, and so on, but I find that when I do that, the months sort alphabetically, and not logically.
I'm having problems figuring this one out -- I'm fairly new to access. I have included a JPG attachment that shows the information I currently have in use and what I would like. I need a SQL statement that will generate the rolling 12 months by period.
The end results will be a table that is populated with the rolling 12 month values so I can qry a sharepoint infopath form to look up the rolling value (look up against lng_PERIOD and chr_EE_RACF) to populate the YTD values.
I have a small clinic database. I've got tblAppointments to show AppointmentID and AppointmentDate among various other data, but only the dates matter for what I'm trying to achieve.
I'm trying to show a chart on a report that shows the number of appointments by months for this year and previous year. How can I do this...without using SQL, hopefully?
Here's what I tried: I made a cross-tab query to successfully show the years 2013 and 2014 in the rows, months in the columns, and number of appointments as values. But then, I didn't know how to graph it to compare the number of appointments for the two years by months.
i have to process 3 upcoming months bills .. say i want to show in query like this bill for Nov, Dec, Jan based on the issue date i mentioned earlier and the months to be find from the numeric month field like 3 i mentioned which mean 3 months ahead from issue date.
View 8 Replies View RelatedI have to make program for a school
Some students pays fee for one month, some for two months and some 3 months.
School administration requires me to print notices for every student according their payment mode after comparing with next due months and already paid months.
Some notices will show their fee for the months as
(Id= 1 Jan-14, Feb-14),
(Id=2 Jan-14, Feb-14, Mar-14),
(Id =3 Dec-13, Jan-14) Considering as Id 3 has not paid due month i.e. Dec-13 Fee and his payment mode is 1 month only.
Here Id means Id number alloted to Students.
Trying to get a formula or function for calculating profit for each month between selected dates. Partial month calculations are required.
Source Data (record) examples:
Contract1: Startdate = 6/30/12; EndDate = 9/22/12; DailyProfit = $500; MonthlyProfit = $9500
Contract2: Startdate = 7/7/15; EndDate = 12/17/12; DailyProfit = $600; MonthlyProfit = $11400
Contract3: StartDate = 8/1/12; EndDate = 6/30/13; DailyProfit = $350; MonthlyProfit = $6650
User Query #1:
Notes:
ReportStartDate will always be entered as the first day of the month;
ReportEndDate will always be entered as the last day of the month
The report will always show 6 consecutive months
User selects
ReportStartDate = 7/1/12;
ReportEndDate = 12/31/12
Notable conditions:
-The calculation for Contract 1 will have a partial month profit calculated for September 2012. The other months can show the MonthlyProfit value, with October and December being $0.
-The calculation for Contract 2 will have a partial month profit calculated for July 2012 and December 2012. The other months can show the MonthlyProfit value.
-The calculation for Contract 3 will have no partial months since the StartDate is on a month beginning, and the ReportEndDate is prior to the EndDate. July 2012 will show $0.
Request: I either need a super complicated IIF statement for 6 fixed columns in a query (one column for each month) or a smart function, neither of which I can do.
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.
I'm trying to get a form to display a pick list so I can choose a staff name from the whole database and the form will go to that record. In the after update of my combobox I have:
Private Sub STAFF_ID_AfterUpdate()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "LNAME='" & STAFF_ID.Column(1) & "'"
If rst.NoMatch Then
MsgBox "The selected record can not be displayed because it is filtered out. " _
& "To display this record, you must first turn off record filtering.", _
vbInformation
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End Sub
and this works OK except when I try to make it pick on 2 columns I can't get it to work. E.g. if I select Joe Smith it just goes to the first Smith. I thought if I used AND on the findfirst line and put in the first name it would work. What am I doing wrong?
I also need to make it so if I type a letter the list will go to that part of the list but I'm not sure how to do this.
Hi all
I have a form which is used for entering holiday requests.
It has fields such as name, type of holiday, date from and date to but it also has a list box value. This list box contains the number of days between the date from and date to and exludes weekends and bankholidays using data from a table. The data from the form is inserted into a table using an sql string when the submit button is pressed. The problem I have is that the sql does not pick up the number of days from the list box unless I click on the list box before I click on submit. It is almost as if the value is in the list box but it is simply not recognised by the sql unless I select the list box using the mouse. I have tried to select the list box using code when the submit button is pressed but to no avail. Does anyone have any ideas how I can over come this or perhaps how to physically select the list box so it is highlighted when the submit button is pressed??
Thanks all
Gareth