I have form with two date text boxes. One is start date and the other is end date. I want to query a table and only pull records that the date field is between the start date and end date.
my code I am working on is:
Dim startdate2 As String
Dim enddate2 As String
Me.start_date = startdate2
Me.end_date = enddate2
Dim SQL As String
SQL = SELECT FROM 'project' WHERE Date between 'startdate2' and 'enddate2'
DoCmd.RunSQL SQL
I am getting a error on the sql = statement, I can't seem to get the statement right.
Hi everyone, I am hoping to learn something new about access today. What I need is to be able to run a query that gives me rows of data based on paramenters that I specify. For example, I will query this all just on one table and I want to come up with an output that is in a nice format as follows:
Basically each member has an effective date, like member A might have 01/15/04 effective date and member B might have 01/02/04 effective date. I need to capture the count of members by month based on their effective date range. I would like to do it all in one query like above instead of just running a query for January, then February, then March, so on.
Is there a way to do this? Right now I am just running a query and getting one member count at a time and copying that number to an excel spreadsheet. it works okay, but is time consuming. I would rather semi-automate things for me. Any ideas?
Oh and I am new to access, by no means an expert, so be gentle with me.
So I have a search form that I created that has also a subform (a continous form, that has a query as its source) where my records are listed. I started on making a Between dates filter where i would be able to pick dates and get the results of records that are in that range.
I put two text boxes in which I set format to Short Date so that I get the button to select the date from a calendar for each text box. Then I made a button that is only for this and added a Clickon code so that it filters my subform over the recordsource of the form. The problem is is that i get an error and it doesn't filter.
Code:
Private Sub btnDatumUpis_Click() Dim SQL As String SQL = "SELECT qrySearchV.VID, qrySearchV.MarkVoz, qrySearchV.ModelVoz, " _ & "qrySearchV.TipMot, qrySearchV.Regist, qrySearchV.VlaVoz, " _ & "qrySearchV.KorVoz, qrySearchV.KatV, qrySearchV.DatumUVoz, " _
I have a form that request information from the user (StartDate, StartTime, EndDate and EndTime) the problem is that it's not working. The only way I can get any data to show is when I remove the StartTime and EndTime. Only then will it pull the items from the StartDate and EndDate.
Here is what I have as my criteria: Between [Forms]![OpPROD_ALL]![StartTime] And [Forms]![ OpPROD_ALL]![EndTime] And Between [Forms]![ OpPROD_ALL]![StartDate] And [Forms]![ OpPROD_ALL]![EndDate]
The users will be able to request a report based on a start and end date along with a start time and end time.
Side note: this is to pull date for 3rd shift (Example) 4/14/2013 10:00PM - 4/15/2013 10PM
Actually, I don't think i've done this right, but here's the goal I wish to achieve.
I have 2 tables, one containing contract info and another containing client info. I have the 2 related using the client name field. I have a form which is based on the contract table which contains a drop down field of client names. When I select a client, I want it to update a read only client address field within the form, just for display purposes.
Is this easy to do and can anyone point me towards some examples on how to achieve this???
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 a simple form where a user selects a query from a drop down list. I need to have the query apply a date range that has also been selected in the form.
Would I call to the date form fields from the query as a variable somehow?? If so, could someone show me the syntax? I am extremely new to access.
I have a form [ReportForm] with two unbound text boxes to select a date range.
[BeginOrderDate] & [EndOrderDate]
These two boxes look up a query with the following expression:
between Forms![ReportForm]![BeginOrderDate] and Forms![ReportForm]![EndOrderDate]
However in my query I have several other columns with dates [InterimOrderDate] & [PostOrderDate].
How to modify the expression above to look up these dates as well so that when typing in a date range in the form the query will return records where the date range filters records for
I am working on a query form, in which user can enter three parameters: date range (datefrom field, and dateto field), 1st selection of category (a drop-down menu, multi-selection enhanced), 2nd selection of category (a drop-down menu, multi-selection enhanced. By clicking command button, user can choose whether the result show in query table, or in a report. It didn't work... the error has something to do with the date criteria. If I remove the date criteria, i.e. just allowing selection of two categories, it works perfectly fine.
I've checked out many postings in this forum, but it doesn't take me far... below is the code, please please help.... Note: the cmdOK is the name of the command button for presenting results from query table. ------------------------------------------- Private Sub cmdOK_Click() On Error GoTo cmdOK_Click_Err Dim blnQueryExists As Boolean Dim cat As New ADOX.Catalog Dim cmd As New ADODB.Command Dim qry As ADOX.View Dim varItem As Variant Dim strDate As String Dim str1MainCate As String Dim str2MainCate As String Dim str1MainCateCondition As String Dim str2MainCateCondition As String Dim strSQL As String ' Check for the existence of the stored query blnQueryExists = False Set cat.ActiveConnection = CurrentProject.Connection For Each qry In cat.Views If qry.Name = "QryCateDateForm" Then blnQueryExists = True Exit For End If Next qry ' Create the query if it does not already exist If blnQueryExists = False Then cmd.CommandText = "SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips" cat.Views.Append "QryCateDateForm", cmd End If Application.RefreshDatabaseWindow ' Turn off screen updating DoCmd.Echo False ' Close the query if it is already open If SysCmd(acSysCmdGetObjectState, acQuery, "QryCateDateForm") = acObjStateOpen Then DoCmd.Close acQuery, "QryCateDateForm" End If ' Build criteria string for Date strDate = "(((NewsClips.IssueDate) Between #" & datefrom & "# And #" & dateTo & "#))" ' Build criteria string for 1MainCate For Each varItem In Me.fstMainCate.ItemsSelected str1MainCate = str1MainCate & ",'" & Me.fstMainCate.ItemData(varItem) & "'" Next varItem If Len(str1MainCate) = 0 Then str1MainCate = "Like '*'" Else str1MainCate = Right(str1MainCate, Len(str1MainCate) - 1) str1MainCate = "IN(" & str1MainCate & ")" End If ' Build criteria string for 2MainCate For Each varItem In Me.SecMainCate.ItemsSelected str2MainCate = str2MainCate & ",'" & Me.SecMainCate.ItemData(varItem) & "'" Next varItem If Len(str2MainCate) = 0 Then str2MainCate = "Like '*'" Else str2MainCate = Right(str2MainCate, Len(str2MainCate) - 1) str2MainCate = "IN(" & str2MainCate & ")" End If ' Get 1MainCate condition If Me.optAnd1MainCate.Value = True Then str1MainCateCondition = " AND " Else str1MainCateCondition = " OR " End If ' Get 2MainCate condition If Me.optAnd2MainCate.Value = True Then str2MainCateCondition = " AND " Else str2MainCateCondition = " OR " End If ' Build SQL statement strSQL = " SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _ "WHERE NewsClips.[IssueDate] " & strDate & _ str1MainCateCondition & "NewsClips.[1CategoryMain] " & str1MainCate & _ str2MainCateCondition & "NewsClips.[2CategoryMain] " & str2MainCate & ";" ' Apply the SQL statement to the stored query cat.ActiveConnection = CurrentProject.Connection Set cmd = cat.Views("QryCateDateForm").Command cmd.CommandText = strSQL Set cat.Views("QryCateDateForm").Command = cmd Set cat = Nothing ' Open the Query DoCmd.OpenQuery "QryCateDateForm" ' If required the dialog can be closed at this point ' DoCmd.Close acForm, Me.Name ' Restore screen updating cmdOK_Click_Exit: DoCmd.Echo True Exit Sub cmdOK_Click_Err: MsgBox "An unexpected error has occurred." _ & vbCrLf & "Procedure: cmdOK_Click" _ & vbCrLf & "Error Number: " & Err.Number _ & vbCrLf & "Error Description:" & Err.Description _ , vbCritical, "Error" Resume cmdOK_Click_Exit
End Sub ------------------------------------------------
I have a query form that allows user to search by two criteria (in which, user can select "And" or "Or" clause for the two criteria). I also allow user to filter the results by date range. On the form, I have two command button, one will prompt the results in a query table, and the other will prompt a report.
Problem 1 I manage to prompt report with a date range (i.e. two unbound text boxes for start date and end date). But, I dont' manage to disable the filter if the date range is null. Below is the code for this report command button. Should I use a toggle button to make a select case?
Private Sub cmdReport_Click() Dim varItem As Variant Dim strDocName As String Dim str1MainCate As String Dim str2MainCate As String Dim str2MainCateCondition As String Dim strDate As String Dim strSQL As String Dim strFilter As String ' Build criteria string for 1st ComboBox For Each varItem In Me.fstMainCate.ItemsSelected str1MainCate = str1MainCate & ",'" & Me.fstMainCate.ItemData(varItem) & "'" Next varItem If Len(str1MainCate) = 0 Then str1MainCate = "Like '*'" Else str1MainCate = Right(str1MainCate, Len(str1MainCate) - 1) str1MainCate = "IN(" & str1MainCate & ")" End If ' Build criteria string for 2nd Combo Box For Each varItem In Me.SecMainCate.ItemsSelected str2MainCate = str2MainCate & ",'" & Me.SecMainCate.ItemData(varItem) & "'" Next varItem If Len(str2MainCate) = 0 Then str2MainCate = "Like '*'" Else str2MainCate = Right(str2MainCate, Len(str2MainCate) - 1) str2MainCate = "IN(" & str2MainCate & ")" End If ' Get 1toggle button condition If Me.optAnd2MainCate.Value = True Then str2MainCateCondition = " AND " Else str2MainCateCondition = " OR " End If ' Build SQL statement strSQL = " SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _ "WHERE NewsClips.[1CategoryMain] " & str1MainCate & _ str2MainCateCondition & "NewsClips.[2CategoryMain] " & str2MainCate & ";" ' Build criteria string for Date If Not IsNull(Me![dateTo]) Then strDate = strDate & " NewsClips.IssueDate Between #" + Format(Me![datefrom], "mm/dd/yyyy") + "# AND #" & Format(Me![dateTo], "mm/dd/yyyy") & "#" 'Format(Me.dateTo, "mm/dd/yy") Else strDate = strDate & " NewsClips.IssueDate >= #" + Format(Me![datefrom], "mm/dd/yyyy") + "#" End If ' filter string strFilter = strDate ' Open report strDocName = "RptCateDateQry" DoCmd.OpenReport strDocName, acViewDesign, , strFilter With Reports(strDocName) .RecordSource = strSQL .Filter = strFilter .FilterOn = True End With DoCmd.Save acReport, strDocName DoCmd.OpenReport strDocName, acViewPreview Exit_cmdReport_Click: End Sub
Problem 2 I have no idea how to filter the query results by date range in the query table. Below is the code of the query table button, which do not offer the filter feature. Would really appreciate it if you can give me some advice.
Private Sub cmdOK_Click() On Error GoTo cmdOK_Click_Err Dim blnQueryExists As Boolean Dim cat As New ADOX.Catalog Dim cmd As New ADODB.Command Dim qry As ADOX.View Dim varItem As Variant Dim strDate As String Dim str1MainCate As String Dim str2MainCate As String Dim str1MainCateCondition As String Dim str2MainCateCondition As String Dim strSQL As String ' Check for the existence of the stored query blnQueryExists = False Set cat.ActiveConnection = CurrentProject.Connection For Each qry In cat.Views If qry.Name = "QryCateDateForm" Then blnQueryExists = True Exit For End If Next qry ' Create the query if it does not already exist If blnQueryExists = False Then cmd.CommandText = "SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips" cat.Views.Append "QryCateDateForm", cmd End If Application.RefreshDatabaseWindow ' Turn off screen updating DoCmd.Echo False ' Close the query if it is already open If SysCmd(acSysCmdGetObjectState, acQuery, "QryCateDateForm") = acObjStateOpen Then DoCmd.Close acQuery, "QryCateDateForm" End If ' Build criteria string for Date If Not IsNull(Me![dateTo]) Then strDate = strDate & " NewsClips.IssueDate Between #" + Format(Me![datefrom], "mm/dd/yyyy") + "# AND #" & Format(Me![dateTo], "mm/dd/yyyy") & "#" 'Format(Me.textStartDate, "mm/dd/yy") Else strDate = strDate & " NewsClips.IssueDate >= #" + Format(Me![datefrom], "mm/dd/yyyy") + "#" End If
' Build criteria string for 1MainCate For Each varItem In Me.fstMainCate.ItemsSelected str1MainCate = str1MainCate & ",'" & Me.fstMainCate.ItemData(varItem) & "'" Next varItem If Len(str1MainCate) = 0 Then str1MainCate = "Like '*'" Else str1MainCate = Right(str1MainCate, Len(str1MainCate) - 1) str1MainCate = "IN(" & str1MainCate & ")" End If ' Build criteria string for 2MainCate For Each varItem In Me.SecMainCate.ItemsSelected str2MainCate = str2MainCate & ",'" & Me.SecMainCate.ItemData(varItem) & "'" Next varItem If Len(str2MainCate) = 0 Then str2MainCate = "Like '*'" Else str2MainCate = Right(str2MainCate, Len(str2MainCate) - 1) str2MainCate = "IN(" & str2MainCate & ")" End If ' Get 1MainCate condition If Me.optAnd1MainCate.Value = True Then str1MainCateCondition = " AND " Else str1MainCateCondition = " OR " End If ' Get 2MainCate condition If Me.optAnd2MainCate.Value = True Then str2MainCateCondition = " AND " Else str2MainCateCondition = " OR " End If ' Build SQL statement strSQL = " SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _ "WHERE NewsClips.[1CategoryMain] " & str1MainCate & _ str2MainCateCondition & "NewsClips.[2CategoryMain] " & str2MainCate & _ str1MainCateCondition & strDate & ";" ' Apply the SQL statement to the stored query cat.ActiveConnection = CurrentProject.Connection Set cmd = cat.Views("QryCateDateForm").Command cmd.CommandText = strSQL Set cat.Views("QryCateDateForm").Command = cmd Set cat = Nothing ' Open the Query DoCmd.OpenQuery "QryCateDateForm"
' If required the dialog can be closed at this point ' DoCmd.Close acForm, Me.Name ' Restore screen updating cmdOK_Click_Exit: DoCmd.Echo True Exit Sub cmdOK_Click_Err: MsgBox "An unexpected error has occurred." _ & vbCrLf & "Procedure: cmdOK_Click" _ & vbCrLf & "Error Number: " & Err.Number _ & vbCrLf & "Error Description:" & Err.Description _ , vbCritical, "Error" Resume cmdOK_Click_Exit End Sub
Sorry for posting this question again, as I thought it's better to make it a seperate posting, rather than a reply to my early post. Your advice will be greatly appreicated.
First, I am trying to get a query to return records between a certain date range. In the form I have DateFrom and DateTo unbound text boxes from which faculty select the date range. I know I have done this before, but I cant figure out how to create a field name and write the criteria for the source query in design mode!
I tried [Form]![FormName]![DateFrom]<[Form]![FormName]![DateTo] in the criteria but I can't seem to write a valid field name that doesn't alter the criteria in some way...
Second, in the same form, faculty enter the StudentID for the student they wish to get records for. How do I write the code to show a msgbox when there is no such ID in the event they enter an StudentID incorrectly.
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 database with several clients who have a series of appointments. I want to be able to create a report of all the clients to be seen during a range of dates to be inputted by the user. All help would be greatly appreciated.
I have developed a Debt Management program for work but I am stuck on a feature that we need to add to it. Basically I have a table which holds "planned future transactions". These are ad hoc transactions that need to be added from time to time to the clients budget. For example the client might have $500 a quarter for school fees for the next two years or he might receive a bonus every six months for the next 5 years. Currently we are putting these in manually but it is tedious and is causing complaints. The feature I require is to be able to add a record such as the school fees, nominate that this fee is paid every three months for the next 8 quarters and get the system, say through an append query? to create 8 records, each with the appropriate date 3 months after the previous one.
Any suggestions or assistance would be appreciated.
I have a query that I want to "filter" based on a date derived from a Build statement (Expression). The Build statement works I can even sort by "Ascending".. But when I try to add a "Between [Startdate] And [Enddate] statement in the Criteria box, returns nothing".
Is their a way to update the TotalBatches in TableA with the sum of NumBatches from tableB that have the same BatchType and falls between the FromDate and ToDate of TableA?
I have multiple buildings that I own. Each building earns a monetary amount each day. Some days they earn $0, some days they earn $1,000. This is all kept track in a data base in which someone manually enters the information each day.The three fields are:
Building Date Profit
I have narrowed a query down to one building, and I am interested in two things. How do I write a query that shows a 30 day peak (the most profitable 30 days). In other words. I want to see the following Building: GNB Tower Date: 02/14/13 - 03/15/13 Profit: $25,162
The next thing I am interested in is a 7 day peak from within these 30 days. This shouldn't be too difficult if I can figure out how to query these 30 days, I can query 7 days out of those 30 days. So this bigger mind stumper is the first one.
out of a date range of months and months (Let's say 10/12/13 - 6-14-13).Once I figure this part out, I am sure I can figure out how to compile all buildings into one large report with their 7 day peak and 30 day peak.
Context is I have a TABLE 1 that has a record for every package shipped with various details about it, include how it shipped, and the date. Table 2 is a key to go from the ship method to a better grouping we deem service level. Table 3 is a Fuel Surcharge (FSC) Table. It includes various FSC % that vary by different service levels and different date ranges. In the end I need to know the amount of pkgs shipped (by month and servicelevel) and the relevant FSC.I have several tables in my database but we should be able to focus on 3 to explain my situation and only the relevant fields:
Table 1 is Left join on Table 2 based on ShipVia Table 2 is Left join on Table 3 based on FSCType The issue is Table 3 is dependent on Table1.ShipDate being between the date ranges as well.
There are overlapping date ranges, however they are differentiated by the FSCType field.The only semi-feasible solution I currently have is to expand Table 3 to include every date, and not date ranges. This will create an issue due to size of date ranges etc.
First of all, I didn't know how to search for the precise info regarding my question, so, If it is repeated, feel free to close this thread or delete it.
So, here it is my question:
I have a table with values taken once monthly, that is, for each person on that table, I would have a maximun 12 records per year. In that table I have the Id of the person, the date field where I store a date when I get the record, a field where I store a value and a control number field. The values on the control number field go from 1 to 20 (or less).
What I want to achieve is, given a range of dates, show the last two controls of every person, something like: id, last control date, last control number, value of last control, previous control date, previous control number, value of previous control. As you could see, I want to show in the same row the last two controls for each person, only if the happend to have two controls, if they have only one, it wouldn't be necessary to show them, so, how can I do this?.
I am trying to calculate the working days Based on all web searches I am unable to find the specific scenario I am working in Auto industry, which means auto industry usually close twice a year for any reasons, let say in July for one or sometime two weeks and in December depends upon the Christmas date usually from December 20 till Jan 01
Now my question is when i am enter the holiday details in table do I have to enter line by line date e.g. july 01, 02, 03 (I am able to understand, how this works but still not yet tried) OR july 01 to July 07 (which make sense, but unable to find how to use date range to calculate working days)...
I have recently set up a button that exports multiple queries to one Excel file, using TransferSpreadsheet code. This works great and saves a lot of time, the only thing is on many of the queries I have a date range set, so you have to enter a date range to get the results.
Is there away that once I press the button I enter the date range once and than it exports the rest of the data based on this range, rather than entering the same dates multiple times.
I am looking to have entries made to the table based on a date range. I have a database of events and most repeat daily, weekly, etc. When I enter them into Access, they go in as a single entry with a start and end date. But I need to have them as individual entries for each day so that rather than one entry of 5 days, I need to create 5 entries of one day each.
I have a weekly list of transactions that come in the format DD/MM/YYYY HH:MM:SS, I need to tag these individual transactions with a week number. The problem is, I can't use the Datepart function etc. as the day is classed as running from 8am to 8am rather than midnight. I have a list of all of the weekly date ranges for a few years (with the time) so I was able to solve this problem in excel by using the Index and Match functions. However, I'm trying to automate this process as much as possible so I'd rather perform this function in Access.
I have the following project that works as a timesheet manager:
Users enter in a form the employee name/date/hours worked/role/overtime (yes or no)/rate... on a daily basis. At the end of every week, they must create a report depending on the dates given in a query.
I have only one table (tbl_EmployeeHours) and a query to calculate the number of hours between two dates.
At the moment a report gives me all the information above (employee name/date hours worked etc..) but what I am trying to is a bit different.
I need Access to create a report (based on a date range given by the user) that would give me: per unique name, the number of normal hours worked in total, next column, the number of hours worked as overtime (rate 1.5), then, the number of hours worked as overtime (rate 2).
This means there are 3 different totals. For example, in the date range given, someone might appear twice but in the report he will appear only once with 3 different totals...
My guess:
I would say I need to create a different query for the 3 different totals (normal hours, overtime 1.5 and overtime 2). For each query you would need to ask the user to input the date range (which would not be so user-friendly I agree...). Then, I am not sure how access would create that list of names and combine the 3 different results from the queries...?
Code: ' count records in query Dim rs As DAO.Recordset Dim db As Database Dim strSQL As String Dim beginDatum As String Dim eindDatum As String Set db = CurrentDb
