Forms :: Filtering Records To Return Appointments For Month
Aug 4, 2015
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 have a query that selects usage records from a table where a data field falls within a rolling 12-month period that ends on a month and year selected by the user on a form.
PARAMETERS [Forms]![frmReport]![cboMonth] Short, [Forms]![frmReport]![txtYear] Long; SELECT UsageID, dtUsage, dblUsage FROM tblUsage WHERE (((DateDiff("m",[dtUsage],DateSerial([Forms]![frmReport]![txtYear],[Forms]![frmReport]![cboMonth],1))) Between 0 And 11));
I also have a query that pulls data between two dates that the user specifies using two textboxes on a form.
SELECT UsageID, dtUsage, dblUsage FROM tblUsage WHERE (((tblUsage.dtUsage) Between [Forms]![frmReport]![txtStartDate] And [Forms]![frmReport]![txtEndDate]));
On the form I have an Option Group control that sets the visible properties of the 4 controls; 12-Month Rolling cboMonth - Month Combobox txtYear - User entered year Date Range txtStartDate - User entered Start Date txtEndDate - User entered End Date
What I was planning on doing was adding two new textboxes (txtQryStart & txtQryEnd) and depending on what option is selected, change the dates accordingly
If blnDateRange Then Me.txtQryStart = Me.txtStartDate Me.txtQryEnd = Me.txtEndDate End If
If blnMonth Then If Me.cboMonth = 12 Then EndMonth = 1 EndYear = Me.txtYear + 1 StartMonth = EndMonth StartYear = Me.txtYear Else EndMonth = Me.cboMonth + 1 EndYear = Me.txtYear StartMonth = EndMonth + 1 StartYear = Me.txtYear - 1 End If Me.txtQryStart = DateSerial(StartYear, StartMonth, 1) - 1 Me.txtQryEnd = DateSerial(EndYear, EndMonth, 1) End If
Now I can use one query to accomplish both types of query; SELECT UsageID, dtUsage, dblUsage FROM tblUsage WHERE (((tblUsage.dtUsage) Between [Forms]![frmReport]![txtQryStart] And [Forms]![frmReport]![txtQryEnd]));
This approach works. It saves me from having to duplicate the queries and some future headache if anything needs to be changed in the queries.
What I was wondering is there an better/simpler way to do this? :confused:
I'm using a datasheet that can be filtered for certain fields. I have a macro that opens a form to the record that was selected (aka "current") in the datasheet. It filters this opened form to "1 of 1" which makes sense. How would I go about filtering that form for all of the records left (after filtering) in the datasheet?
I hate macros, but I'm doing client-specific work. The primary key is a field (autonumber) called "ListingNumber".
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 a form that is bound to a query that returns all records matching a set criteria (namely, the field ApprovalDate is null); the idea is that the user clicks on a command button which gives information, and if the user chooses to continue (vbYes), then the field ApprovalDate has its value set as the current date, and the record is saved.
Next, I have put in DoCmd.Requery to requery the form and effectively go to the next record wanting approval - this appears to work fine, however I need to add something to the code that handles a null return from the requery - preferably a message box and then have the form close.
trying to enable database users to filter records based on column names which i have in a combobox. They enter the required value in a textbox and click the "Find" button.The code (linked to button click event) is not throwing any errors but the records are not being filtered.
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:
When I add a record to table A (using a form) the first field I enter is a date. That date will then determine which records I see in my drop down list from table B (via a query).
Some records in table B have EffectiveFrom and EffectiveTo dates recorded - because they are now obsolete, other records have no dates recorded in these fields because they active.
How can I view and then select from the drop down only those records in table B which are effective/valid to the date entered in table A?
I have a header/detail form working well. I use a Dsum to update a TOTAL field in the header - based on all the detail records for that Header.
However, I want to 'filter' or condition the records which the form will initially display from the Header file. I have a conditioning field (Invoice date) in the header which should 'block' it from display on the form. I may also desire to SORT the selected Headers record into a different sequence before display...
Looks like any "filtering" I try on the header table makes it difficult to go back and update the TOTAL in the Header?
I'm creating a database for a sales team. They need a calendar where they can enter the reminder like "call peter at 10:30 on 11th july" and reminder like " call frederik on july12 at 12:30am,etc. And they need a popup when the reminder is due. Is this possible in access.
All using access 2010. Here's the situation. I built a searchform according to datapigtechnologies video. Used a query with criterias on fields i want to search on the form: ex.
Code: Like [frm]![frmMyform]![MyField]
It worked fine i thought until i ran the same query outside the form without the criteria field. I filtered the query for the same result and the one used on the search form did not return all data records for that particular result set. Ex. on the searchform; I select the fields I wanted to filter then ran the query. I come up with only 9 records when it should be 18 I get with running the exact same query without the criteria and manually filter the results.
I'm constructing a database to record appointments. I want my users to input their appointments on one half of a form (I can manage that bit) but on the other half I want them to be able to see a list of the appointments they already have booked that day (with any patient) or what the patients already have booked that day (with themselves and any other therapists).
The appointment fields they will need to see are:
start time end time patient/therapist (depending on if they are viewing their own or the chosen patients' appointments type of appointment (physiotherapy etc) location
The date won't need to be seen but will dictate which day's appointments are listed.
At it's simplest I could get away with a list of the day's appointments for the patient and date the therapist has typed into the form to start the record.
Ideally I'd like them to be able to choose whether to see a list of the patient's appointments for that day or their own. If there's room I would display both.
Here's the final, 'moon-on-a-stick' bit... it would be great if I could show the appointments in time slots rather than just as a list. From what I can tell that's quite tricky to pull off but i thought I would ask anyway...
At the moment I don't even know what keywords to look up - is this 'embedding a report in a form' or 'inserting a filtered list' and so on.
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.
Working in Access 2007 - I would like to have an updatable calendar in the header section of a form, and when this is changed by the user I would like the subforms (there are several) in the details section to be updated with various appointments with dates corresponding to the date selected in the header.
I am creating a dentist booking system and i need some way to display appointments today for a certain dentist and then from their to click the customer and view their teeth details that the dentist will use to update...
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?
The aim of what I am doing is to create a monthly statement to give to our intermediaries that shows the commission they will receive each month for the deals they have referred. I have managed to create this report, HOWEVER I can't figure out how to filter out which month I need, so I a report for Jan, Feb Mar etc... The idea is that at the end of each month I need to run the report so only the latest month shows...
I wonder if anyone can help. I attach a table that is a material list (27000 records). When I add a new material I want to be able to see the last part code I created for a particular supplier.
For example C001, C002 and C003 are for a supplier company called Chapters so I would scroll through the combo box search list in the form for the last one and create a new record called C004. The problem is that E00001, E00002 for a company called Edwards supplies 20,000 items so it's a long way to scroll through to create a new part code called E20001. How can I show in the form what the last part code is for each Supplier?
I attach the form and table as I'm not very good at explaining this, although I have had to delete approx 20000 records as the database was too big to be posted. Any advice/help would be greatly appreciated, many thanks
I have a database to record drawings received. On the form there is a combo box to select discipline (Civil, Electrical, Mechanical) and a combol box to select the volume the drawings are in (Volume 1 - foundations, Volume 2 etc). The disciplines are stored in tblDisciplines with a relationship between this and the main tbldocument. I also have a table , tblLocation which has the volume ref and description. However when I click on the location field it shows me all volumes for all disciplines. What I want is if the first field selected is Civil only the civil volumes are displayed. Can any one point me in the reight direction
I have a rather simple problem that I do not have the time to solve. I have a time series taken at 30 second intervals over 5 months and the records total over 250,000. I only want data from every 15 minutes, so only one out of 30 records. How do I filter the table to extract only the records I need?