Counting Open Events By Month
Jan 11, 2007
I have a table, part of which includes a [start date] and [end date]. I need to be able to plot a graph that will show, for each month, how many projects were still open. they will at the start of the month have a [start date] but no [end date]. I have created another table that holds the date for the first of every month in the range that I am intrested in. I suspect I need to use a subquery in here somewehre but cant get my brain around it :(
Many thanks
Peter
View Replies
ADVERTISEMENT
Jan 7, 2005
I have this BIG Problem:
I have a form where i use 9 text fields named “Pallet” bound to 9 different tables (each table belongs to each terminal user, I create these tables due I have to perform differents actions and reports by person) i get the max value of each table by model and by user using the DMax function, the key point is to get the Max Value of this 9 fields just like this:
Field (Pallet)#1:108 Field (Pallet)#5:136 Field (Pallet)#9:159
Field (Pallet)#2:109 Field (Pallet)#6:138
Field (Pallet)#3:115 Field (Pallet)#7:132
Field (Pallet)#4:125 Field (Pallet)#8:138
Max Pallet: 159 I got this value creating the follow function...
Public Function fMax(ParamArray Variables())
Dim i As Integer
For i = 1 To UBound(Variables)
If Variables(i) > Nz(fMax) Then
fMax = Variables(i)
End If
Next i
End Function
Based on this i open a controlled field in my form and wrote this function:
= fmax ([Pallet#1]; [Pallet#2]; [Pallet#3]; ...[Pallet#9] )
The BIG QUESTION is how can i reset to zero the criteria in my Dmax function to get the max value of each table??? Due each month the counting needs to be reset to zero because the issue is to get every month the production quantity of cell phones pallets produced, and if I let this function alone, it will give me the max pallet number regardless the month the key is to get the max value according the month…
I try this function but doesn't work:
=DMáx("[Escaneo_Gabriela]![Tarima]","[ Escaneo_Gabriela]"," [Escaneo_Gabriela]![Modelo] = 'G1500S' And Month([Escaneo_Gabriela]![Fecha])=Month(Date()) And Year([Escaneo_Gabriela]![Fecha])=Year(Date())")
I was thinking in open a field in each table named "Month" and create a function or module to give me the value only of the current month based on this field, but i'm not quite sure about the sintax, PLEASE HELP ME>>>!!!!!!!
View 1 Replies
View Related
Mar 21, 2013
I have a table which has sentdate invoices and paiddat invoices. i want to be able to get a monthly count for the amount of invoices sent and paid to compare on a graph. e.g jan 2011 = 5 feb 2011 = 6 ect
View 1 Replies
View Related
Nov 4, 2007
Hiya
This is my first post here, but I've two questions so I'll divide it into two threads.
Normally I can google these to help find the answers but a bit of a loss at this one, probably beginners stuff to some of you.
Heres a link to the database below
http://casp.gamecommunity.co.uk/database.gif
Total count is easy: =Count(tblMain!ID1)
What I want to do is count the amount of records for each individual month of the current year so I don't have to change the year date everytime a new year comes round. As soon as the next year comes around they all reset to 0.
The clever way would be to count the records for the current month -1 current month -2 etc and automatically update the month labels but I think that would be getting a bit comlicated.
Thanks in advance for any help. :)
View 6 Replies
View Related
Oct 9, 2013
We have an ActiveX grid control, 10Tec iGrid, written in VB6. One of its method, Group, used to group rows, raises the AfterAutoGroupRowCreated event so the developer can adjust the look and contents of every group row created during the automatic process of grouping.
When our ActiveX grid is hosted on an MS Access form and we call the Group method while populating the grid in the form's Open event, the AfterAutoGroupRowCreated event isn't triggered. But this event definitely works as expected in other development environments, and even in MS Access if we call Group from the form's Load event.
Is it a well know issue of MS Access, when any events of ActiveX controls aren't triggered while "executing" the Open event
View 1 Replies
View Related
Dec 8, 2014
I have 10 forms named
FormA - FormB ------ FormJ
I want to choose the formA and from a combo I want to choose a day so when this day come the chosen form will open automatically in the specified date when I open the database...
For example i choose FormA and for the date i choose 9 or monday
So every 9 of any month the form will open or every monday....
View 3 Replies
View Related
Feb 25, 2015
I have vba code
PHP Code:
    DoCmd.OpenReport "MonthlyData2", acPreview, , " YEAR(CourseDate) = '" & Me.txtCourseDateMonth & "' "Â
this works sucessfuly how can I add the following to the code so it filters month and year?
PHP Code:
'" MONTH(CourseDate) = '" & Me.txtCourseDateYear & "' "Â
View 5 Replies
View Related
Jan 26, 2007
I have a report due the first of each week in which I need the cases open and cases closed for the previous week, the week two weeks prior and the 2007 and 2006 year to date on two different types of cases. I have a case management table with a field for Type of Case, date assigned and date closed that I uses in my queries. Presently I have two query, one that generates only Type 1 cases from the Case Management Table and another for Type 2. I then use the Type 1 Query in another query that limits the results for Type 1 cases to those opened last week, one for those open two weeks ago, one for 2006 YTD and one for 2007 YTD. In these 4 queries I have one field [Type of Cases] and I have the query count. I then do this for Type 2 cases and then go through the whole process to do Closed Cases. All my queries have criteria to automatically filter the dates to the time periods mentioned above. I then have one report query that I put all the number in for my report. This query has 16 fields with the numbers for each period, last week open and closed, 2 weeks open and closed, etc. I then generated a report that takes these numbers from my report query and puts it in a report format automatically. As you can imagine this takes some time to go through each query to generate these numbers, so I was wondering how I may do this differently. Also, I have experienced a problem when a field produces no records I get a blank sceen with nothing under the Count of column and get the same thing for my report. How can I fix this.
View 1 Replies
View Related
May 5, 2014
I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
View 2 Replies
View Related
Mar 19, 2012
I have a MS access table with 12 numerical columns for 12 Months payments for our customer base.
I need to calculate variance of payments month on month and identify set of customers who have made huge payments.
View 10 Replies
View Related
Oct 18, 2005
I have a table full of dates of meetings through out the year...
Example
tMeetingDates
16th August 2005
18th September 2005
19th October 2005
23rd November
-----
i also have a report that i print out each month that has the date of the meeting on it... i currently edit the date manually.
I was wondering if there was a way to automate this facility, so that the report looked to the table of dates and looked for the current Months Date that is stored i the table.
i then want this date to be displayed in the Report.
So in this instance if i am running a report for tomorrows meeting being the 19th October it would display that date in the report....regardless of when i run the report...
Obviously if i run the report on the first of November because the month has changed it would then display the date of the November Meeting...
we only ever have one meeting a month!!!!
Please help
Andy
View 11 Replies
View Related
Aug 9, 2013
Using Access 2010..I have form with a date on it. For this control I have show date picker set to "For dates" and lo and behold I get calendar! I can scroll through this calendar month by month. Great if I just want to go back or forward a month or three. What I'd like to be able to do is scroll through the calendar year by year. Can I do this with the method I'm using at the moment? If not is there a way round it that isn't over complicated?
View 6 Replies
View Related
Jul 10, 2005
Here is what I want to do:
I'm organising 15 training events, for 60 participants. These 15 training events fall in three themes: running, swimming, cycling. So there are 5 training events held on running, 5 on swimming, 5 on cycling. Each has on average 4 participants, but in reality, some have 6, some 2.
Everybody has to attend each of the three themes, so three of the 15 events.
I have a table called "events". each record contains data on a certain event (place, date, speaker, participants,...)
I have a table of 60 participants, with: their names, their age
Per event, I want to indicate who is participating, with a combobox. Now, the number of participants per event is not limited, so it could be 5 or 30.
Also, it could be nice if the combobox content is limited to the people that haven't been selected yet. So if John Doe is already taking the swimming training in event Swimming1, if I pull down the list of participants in event Swimming2, I do not want to see John Doe's name there.
Also, I want to calculate the average age of all the participants of a certain event.
I want to print out two reports:
1) per training event, an overview
2) per participant, an overview of all the events he has inscribed to
How do I go about this?
Currently I am using 30 comboboxes, and 30 fields in the table "events", called participant1, participant2, participant3,..., participant30
But it is becoming messy, and when I ask for a report per participant, he only gives me the details for the people in participant1.
View 9 Replies
View Related
Oct 7, 2005
Right this is a fundamental issue for me here and I cant get my head around the problem!!!
The database I am working on is a database which records the training courses (run by the NHS) which staff at GP surgeries in our area have attended and acts as a booking system for them.
So we have the obvious tables - one for courses (including the name, cost, etc) and then one for course dates (linked by courseID) and one called training_record which contains who went to what when.
It seems simple but it has come to my attention that for example, "Course A" may in fact take place over 2 days, which could be a week apart. The surgeries are billed in one invoice, i.e. Course A would be £100 for 2 days.
How would you recommend I have this data? I can see all sorts of confusion arising... :(
The only way I have thus far thought would be to put each one in like:
course name: cost date
Course A: day 1 £100 - 29/10/2005
Course A: day 2 n/a - 6/10/2005
Course A: day 1 £100 - 01/11/2005
Course A: day 2 n/a - 08/11/2005
However this seems long winded as some courses may be a few days long and this means adding each person to multiple ones!!!!
Any assistance with getting my head around this would be ace! :)
View 1 Replies
View Related
Feb 15, 2006
Is just me, or are all of the events on tab control tabs disabled?
View 3 Replies
View Related
Jun 23, 2006
Access front end, SQL Server backend.
----
I have a continuous subform (part of a tab control on a main form) whose name and sourceobject are both "sbfCannulae". There are about 10 fields on the subform, and all are bound to fields in a table. The primary key of this table must be generated by combining two other fields, which must happen just before the record is saved. (I know calculated data should not usually be saved in tables, but trust me. In this case it is necessary.)
Right now the user COULD enter data into the primary key-bound textbox, but since the user is me, I make sure not to do that.
When I enter data into all fields except the primary key, I get an error because the primary key cannot contains nulls...of course.
I use the following code to tell me when an event has been triggered.
Code associated with sbfCannulae:
Option Compare Database
Private Sub Form_Activate()
MsgBox "cann activate"
End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
MsgBox "cann before insert"
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
MsgBox "cann before update"
End Sub
Private Sub Form_Current()
MsgBox "cann current"
End Sub
Private Sub Form_Dirty(Cancel As Integer)
MsgBox "cann dirty"
End Sub
When I enter data into a new record, the Dirty event is triggered, but I cannot for the life of me figure out how to trigger any of the other events. No combination of values and nulls in any fields, clicking in other tabs, clicking to other records, or using the navigation buttons will ever trigger any events but Dirty.
The only msgbox I ever get is the one giving delivering the SQL error about the primary keys not taking null values.
What am I doing wrong?
-Matt
View 1 Replies
View Related
Sep 10, 2006
Dear friends
I request your comments please...
I have a form with bound and unbound fields and have linked the [Event Procedure] on these fields base on there respective action.
Now when I open or click a particular field with the event procedure, the procedure is names are like -
Private Sub Rate_Exit(Cancel As Integer) or
Private Sub Quantity_Exit(Cancel As Integer)
but others events are name like Private Sub Quantity_Enter() simple. Why is that ms access (Cancel As Integer) uses only for specific events only.
View 1 Replies
View Related
Oct 1, 2004
I have a main form that calls several other forms for the user to input data. When the user closes one of the sub forms I would like to know what event is fired when the main form gets the focus back. I have tried several different events and can not figure out which one will work. Hopefully someone knows the answer I'm going crazy tring to figure it out, oh wait I'm already there.
Thank you
View 3 Replies
View Related
Mar 16, 2007
I have a database in which people add little bits to different fields as they go. For instance, person 1 puts in appointment, person 2 verifies info for the customer, person 3 checks the customer in, person 4 takes their money. What I am trying to do in a form is track the time when each department accessed it, and which user. I can do this on a new record, but for some reason, I can't get the event properties to work correctly on my form. I have a field for each department set aside for when they update their part of the record. What I am trying to do is, when the record is updated, having the time autopopulate the field.
Example:
Appointments enters |DATE OF APPT| |TIME OF APPT| Appointment timestamp should = time()
Verification enters |PATIENT INFO| |VERIFIED y/n| Verification timestamp should = time()
Reception enters |PULL TAB#| |CHECKED IN y/n| Reception timestamp should = time()
Cashier enters |AMOUNT COLLECTED| Cashier timestamp should = time()
All of these fields are on the same database (I have 2 linked tables: Customer demographics and appointments linked one to many by account # for the customer), and obviously, there's a lot more to it, but this is just a short example of what each department will enter. Appointments starts the appointment, so their timestamp will work, as they are starting new record. However, all of the following departments will be adding to this appointment record. When the other departments open their form with the fields in which they will be filling out, I want it to timestamp when they update the record as well as inserting their login "currentuser()" into the "updated by?" field for each department. For some reason, when I put time() into the event portion the field, it doesn't work. I tried it on dirty, on exit, after update, and it just doesn't update. Am I missing something?
View 5 Replies
View Related
Oct 13, 2006
HiI have a field that is text which holds the month in form "oct" etc. I need to pass the numerical value of that month to a function so how do I create an expression that will change it (in this instance) to 09.kind regards in advance.peter
View 5 Replies
View Related
Jun 19, 2005
Hi guys, ..and girls ;)
i have a calendar that works like a charm, but i would like to add the possibility to add recurrent events in it.
for instance if a user has to perform a task at a choosen time schedule, it is shown in the agenda, like each week or each two weeks or each month.
Anybody any idea how i would aproach this problem or has a sollution?
Thanks
View 1 Replies
View Related
Aug 19, 2005
Hi everyone,
I try to implement a function that looks for broken references in my Access project, so that I won't need to modify the needed references for every workstation. Here's my problem: Where could I put the call of this function so that this code will run before trying to compile the forms and, as a result, avoiding the compile error that access pops-up when a reference is broken or missing.
Thanks in advance! :D
View 1 Replies
View Related
Jun 23, 2007
I have a series of events that have a start date and an end date. How do I write an append query to append an event to a table as an individual event for every day in the given range?
View 2 Replies
View Related
May 13, 2005
I have a start and end date fields plus a txt box called wDays.
I'm trying to make sure the user puts in both dates but end cant be earlier than start and vs versa....
If not provide a message as to which is missing....
If alls well do the calulation with the module "GetWorkDays"
I f everything goes bad, dont save the record at all!
I have this behind a cmdButton. Used to unhide the wDays field because it displays #error otherwise...and to do the calculation... with the BeforeUpdate
Private Sub Command8_Click()
If IsNull(Me.sDate) Then
MsgBox "Please enter a Start Date"
ElseIf IsNull(Me.eDate) Then
MsgBox "Please enter a End Date"
Else
If Me.eDate < Me.sDate Then
MsgBox "End Date cannot be earlier than the Start Date"
Exit Sub
End If
End If
Me.Requery
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim myDays As Integer
If IsNull(Me.eDate) Or IsNull(Me.sDate) Then
Me.wDays.Visible = False
Else
Me.wDays.Visible = True
myDays = GetWorkDays([sDate], [eDate])
Me.wDays.Value = myDays
End If
End Sub
Any help would be really appreaciated because I have tried many combonations of code placement to get it working correctly....
View 1 Replies
View Related
May 26, 2005
Hello all:
I have a split, shared database in which the Before and AfterUpdate events on my forms are not working at all. I tried setting a breakpoint to verify this and, sure enough, they are not firing.
Does anyone have any clue as to why this may be occurring?
Thanks so much for the help. I have tried searching the forum with no luck.
View 2 Replies
View Related
Aug 15, 2005
I have a form with a number of controls with code behind - this works as required.
However, recently I decided to use a tabbed control to hold the controls and simply cut/pasted these control onto one of the pages of the tab control. The events now no longer fire as they did and I have no functionality on the form - the code still exists in the design of the form though..... any ideas?
View 1 Replies
View Related
