Below is the code I have for a query that shows me the number of WorkUnits via a date range that is put in via calendars. This works great. It gives me one total for the date range. What I would like to be able to do is see the WorkUnit totals by month via a date range. If I put in the date range: Jan 1-April 30 I would like to get four WorkUnit totals instead of one. I would like to see the WorkUnit totals for each month. Can anyone assist with this? I asked a similar question like this yesterday but he SQL for this code is different and I have not been able to figure out how to apply yesterday's answer to this one. I have tried everything I can think of to no avail.
SELECT 'Total Work Units' AS FaultCategory, Count([WorkUnit]) AS [WU Totals]
FROM [Select Distinct [WorkUnit]
FROM WorkUnitsFaultsMainTBL
WHERE BuildID IN ("G004","E818","N005","F813","D024","C879") AND
PossibleCause NOT IN ("Out of Stock") AND
[TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] AND
[Forms]![Queries_ReportsFRM]![EndDateTxt]]. AS vTbl;
Can someone tell me how to get year to date totals, month to date totals, week to dates in a query? I need to get all three for three different fields.
I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.
I am trying to make a query that finds all records by the "Month Number" and have problems.
What I did: Month(DateField) I got month numbers like I wanted but when I typed in the criteria below, I got February thru December Records, but when I search on anything "up to" 9 it works fine.
I want: Between{month1] and [month2] Month1= 9 (example) Month2= 12 (example) Need results to be from the requested months but any year
I have to imagine it's how the date is converted to an integer, but not sure.
I'm trying to create a combo list box in a form that has all 12 months (January, February......etc,) listed in rows, and depending on the selection will bring up only that particular month within a range of dates from a table. I don't want it to look at the day or the year, but only the month.
Example: I select January from the drop down list in my combo box and my form will display all records with dates that are in January regardless of month or year.
My dates are stored as numbers (long integers), because they need to be in the form YYYYMMDD, and I couldn't figure out how to enter them and keep them in that format as Date/Time.
Anyhow, I have a form with a textbox and a button. When the user enters in a date range, I need totals to show up for only dates that are in-between and/or equal to the date(s) typed in the textbox.
For example when the user types in 20050904-20051004, and pushes the button, a display of totals will be displayed for only those dates and those in-between. If the user leaves the textbox blank, and pushes the button, I want totals from all dates to be shown.
As you may know from my previous question, I am a relative newbie to Access. Suggestions about how to do this maybe even how to derive and present the totals, and especially specific code will be very helpful and VERY much appreciated.
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:
a request came accross my desk to find holidays sales in relation to the same days of the week for other weeks. For example, Thanksgivings sales compared to each of the 2 thursdays prior and the 2 thursdays after. Anyone know of a way of pulling this data so I have it all in one query - Thanksgiving, Thanksgiving+1, +2 and -1 and -2 (the numbers representing weeks from holiday
I currently have a form that creates a report based on a query that takes in a start date and an end date. What I would like to do is to create comboboxes that will let the user choose a month and year for the report. Is there a simple way to choose an entire month in access? I've tried using the Month() function to no avail.
The SQL statement for where I select the start and end date is below:
WHERE ((([tblData].[EntryDate]) Between [Forms]![frmByRange]![BeginningDate] And [Forms]![frmByRange]![EndingDate])
I have a list of customers with multiple rent start and end dates. I need to create a query where each customer will have a record for every month/year between their respective rent start and end dates. I generally use macros but can't figure out a way to do it without VBA. My field names are Rent StDt and Rent EndDt. My query name is QCalcREndDt. I use version 2010.
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code: Between [StartDate:] And [EndDate:]
And
Code: Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
Is there a way to show the earliest and latest dates of a report generated by a non-date field?
E.g. I generate a report based on Food, and it'll list the days that this food is associated with. Is there a way to show the first and last day that appears in this report (i.e. the range of dates that the report shows based on the food selected)
I have some code that filters job raised I have 2 text box's txtdatestart and txtenddate after entering. date range between the too text boxs it shows me all job raised with in the period.i have entered what I would like is filter it again by client field using combo box cboclient so if the user enter's client name in cboclient combo box and date range in txtdatestart and txtenddate it will only show jobs raised with in the date range of the client enter in the combo box but if the combo box is empty show.
Code:
Private Sub cmdPreview_Click() 'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working. Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "#mm/dd/yyyy#" 'Do NOT change it to match your local settings.
Using access 2010; i have a form that includes a date field. Is there a way to force the user to only choose a month end date? When the user clicks the date from the popup, they may use 9/1/2013 when the mgr. want them to use only 8/31/2013. I am thinking validation field to put a msg but want to be able to force it not the option.
I am creating a repayment schedule (as a report) and I want to display a series of fields as a column which return (show) a date one month after the date in the field above.
The first repayment date field (Line 1) will show a date one month after the loan was paid out. the Next field below will show the date one month later.
I can't simply insert the "Date + 30" because that would get out of since over the year. If the loan was issued on say the 15th of January, I need the first field to display 15th February and the next would be .... 15th March.... Yes - You've got it!
Now I could do that in Excel, but I don't know how to get Access to do it.
I am trying to filter out information for an ODBC query I have written, to obviously make it more concise. Because our data is not set up very logically, a lot of my queries are based on text...but that is another story. My current situation starts with a simple make-table query finding certain data within a date range. This is followed up by another simple make-table query to find other specific data within another specified date range. Then I have a cross-tab query between the two tables to locate all the data that shares the same unique identifiers. My problem lies with trying to only pull information from a certain date range. For example, I want information to be included if the data has the same unique identifier as well as falls within +/- 7 days of the received date (dd/mm/yyyy) also listed in one of the tables. Each record will likely have different received dates so I cannot set it as a constant.
Make sense to anyone? I am not confident enough to play around with some expressions...well it is more I haven't been successful at using them correctly.
I Have a table that I collect data for numerous ID's. One ID may have 5 dates that is was serviced. It is due to be serviced at set intervals which is calculated from the most recent service date. I'm calculating the next service date in a text box with the DateAdd func. =(DateAdd('m',[Cal Freq],[MaxCalDate])) with the MaxCalDate being a Max func in a different text box for selecting the most recent sevice date. I'm trying to update the [Next Due] field in the [Master Asset] table with the calculated next service date so I can do queries on items that are due service within a date range. If I do an Nex Due Expr1: in the queries to calc the next due service date then do a criteria on that Expr1: such as >=[Start Date] And <=[End Date] it states datatype mismatch? Is there a way to select a requested data range on a calculated type date/time? Or How can I update the [Next Due] filed in a different table?
What I want to do is to produce a Query which will total the Promise Amount column and The Actual Amount column using a Date Parameter.
For example I want to know how much has been brought in between 20/09/05 and 25/09/05 therefore producing the result that I'll get a total of £143 for the Promise Amount Column and £117 for the Actual Amount Column. I have tried using the "sum" in Totals, but I think the date parameter is affecting that, the date parameter query I'm using is :
Between [Enter the beginning date:] And [Enter the ending date:]
I have a query that will return a total number of hours between to specific dates. I would also like to have that same column return the total number of ours regardless of the dates. Can anyone please help me?
I'm trying to figure out how I can set a parameter on my query to search for any activity that occurs within a month, that falls within a date range. Specifically:
Criteria TextBox: 4/2006
Activity Start: 3/6/2006 Activity End: 5/5/2006
I want this activity to show in my query because the time between start and end occurs during the month of April. It's no problem setting this up for a specific date, ie 4/1/2006, but I don't know how to do it for an activity that occurs all days between 4/1/2006 and 4/30/2006..or for one that starts 4/12/2006 and ends 4/18/2006, or starts 4/12/2006 and ends 5/9/2006. etc.
I have a table of data, one of the fields is a date.
What i want to do is be able to have a query that can check if the date falls within a certain range - ie fiscal year and output in another column the fiscal year "code".
Ie: dates between 01/06/05 and 31/05/06 is fiscal year 0506 dates between 01/06/06 and 31/05/07 is fiscal year 0607
Could this query be dynamic so if a new fiscal year begins it would know to make the output the next fiscal year code???
I have two tables: tblClasses & tblSchedule. There are joined by ClassID. For each class in the tblClasses there are several records with date field in the tblSchedule. (So each class stored in tblClasses happens on multiple dates stored in tblSchedule).
I want to create a query (SQL view) that would take two date inputs from a form(date range): datefrom & dateto, and return any class of which FIRST day of classes falls in between those dates.
(Or: how can I add a field to my query called [First day of class] that would basically have the value of the first date from the tblSchedule for the joined classID?)
I need to get records between two dates. Here is my query:
SELECT WGMAHIST_TIMEDTY.TDYPT, WGMAHIST_TIMEDTY.TDYCO, WGMAHIST_TIMEDTY.TDYSSN, WGMAHIST_TIMEDTY.TDYFND FROM WGMAHIST_TIMEDTY WHERE WGMAHIST_TIMEDTY.TDYSSN = 464299266 AND WGMAHIST_TIMEDTY.TDYEDT >= #06/29/2007# AND WGMAHIST_TIMEDTY.TDYEDT <= #09/21/2007# AND WGMAHIST_TIMEDTY.TDYPT = 1 AND WGMAHIST_TIMEDTY.TDYCO = 8
I am obviously doing it wrong because I keep getting an error that complains about the key work BETWEEN.
How can I make this work? I am using MS Access 2007.
I have a form with a combo box containing the names of the Months. What I want to do is then pass the Month name to a query as a date range. For example, select "May" from cboMonth and the query will check the date field for "between 05/01/2008 And 05/31/2008"
Here is what I have in my query but it does not show any records when I run it:
IIf([Forms]![Form1]![cboMonth]="May",Between #5/1/2008# And #5/31/2008#)
Is it even possible to use this type of an expression in the query?
Hello - I am trying to create a FOrm that will allow the user to type in two dates. From these dates a table will be created and displayed in a List Box.
Does anyone have a very simple example of the code
1. I am trying to write two input boxes to variables. 2. On click I am displaying a message box that repeates the values of these two variables 3. When they hit OK I want the records to appear in the ListBox. 4. From this point I will want to get the data to a report of some kind.
I am very new to this and am trying to see if someone has any code examples....
This is the code I have so far.....Actually I cant get passed #2 above. It shows the message box and the text but not the values of the variables...
I dont think I am using the variables right......I dont understand the syntax needed to write an input box value to a variable......
Please Help....
THanks
CODE:
Option Compare Database
Private Sub BeginingDate_BeforeUpdate(Cancel As Integer)
Dim BD As String
BD = BeginingDate.txt
End Sub
Private Sub EndDate_BeforeUpdate(Cancel As Integer)
Dim ED As String
ED = EndDate.txt
End Sub
Private Sub Command5_Click()
Dim var_BeginingDate Dim var_EndDate
var_BeginingDate = BD var_EndDate = ED
MsgBox "The Variable is " & var_BeginingDate & " and " & var_EndDate, vbInformation + vbOKOnly
