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.
Help!
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
[BeginOrderDate], [EndOrderDate], [InterimOrderDate] & [PostOrderDate].
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.
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.
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 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"
Neither of which work ....
I need a query that will relate values to given date ranges. For Example:
Table: TaxTypeRange
Values:
TaxType; FromDate; ToDate; Contact
W2; 1/1/15; 3/31/15; Tom
1099; 4/1/15; 6/30/15; Tom
W2; 7/1/15; 12/31/15; Tom
Table: Bills
Values:
Contact; BillAmount; BillDate
Tom; 1000; 3/31/15
Tom; 1100; 4/30/15
Tom; 1200; 5/31/15
Tom; 1300; 6/30/15
Tom; 1400; 7/31/15
Requirement: For every period where the TaxType = W2 the query should multiply the Bill amount by 1.20, so the result should be something like this:
Query: BillsAndTax
Values:
Contact; BillAmount; BillDate; TotalBill
Tom; 1000; 3/31/15; 1200 (TaxType = W2 so 1000* 1.2)
Tom; 1100; 4/30/15; 1100 (TaxType = 1099 does not apply 1.20)
Tom; 1200; 5/31/15; 1200 (TaxType = 1099 does not apply 1.20)
Tom; 1300; 6/30/15; 1300 (TaxType = 1099 does not apply 1.20)
Tom; 1400; 7/31/15; 1680 (TaxType = W2 so 1400 * 1.2)
Not sure how to set this up the right way.
i've got a date range form (Beginning and End Date) for reports. Well i've just been asked if there is a way to filter the date's with individual? For example, i want to run a report from 1 Jan 05 to 1 Feb 05 and I want the reports for only Joey Smith I have 4 checkboxs representing each person how would i go about doing this? Thanks in advance.
View 6 Replies View RelatedI am trying to extract records within a certain date range.
My structure is as follows:
Query 1 = pulls data direct from a table. There is a date field which is in the format YYYYMMDD.
Query 2 = pulls data from Query 1 and amends the date format to:
dd/mm/yyyy
Has anyone any suggestions on how I pull data from query 2 from within a certain date range. i.e 01/01/2005 to 01/05/2005
Thanks
I have a query which requires date parameters, which the user enters into a form. The form enters the parameters into 4 different queries then runs them to produce a report.
This all works fine EXCEPT for one query.
If I enter my desired date range into the query (in this case it is between 01/11/2004 and 30/04/2005) it returns no results. As the only values in the date fields of the table are 01/03/2005 and 01/04/2005 it should return all the records.
However if I enter the date range between 01/01/2005 and 30/04/2005 it works fine. It also works if I enter 01/01/2000 and 31/05/2005 - it just doesn't seem to like the year 2004!!!
The problem occurs whether I enter the parameters from the form or simply type them into the criteria of the query. Any ideas, it's driving me nuts!!
Hello all
I was wondering if anybody could help me out with a query problem I've been having.
I've been trying to use a query to display a list of available cars for a given start and end date entered by the users. I have found some guidance to make an attempt but it isn't working. The text in the Input boxes isn't what I'd like. Also the query is displaying all the cars in my database even when I deliberately trying to exclude some.
I'd really appreciate any help As this is my first database and Im really struggling with the use of criteria.
I've included a screen grab including my formulas
http://img.photobucket.com/albums/v242/b3rnie/access_screen.jpg
Apologies if I've missed this advice in my searching
I am trying to design a report that uses a range of dates as column headers. The row headers are vehicles and the intersection between the columns and rows will display details about that day's vehicle usage.
To do this I need a query that will produce a row of date headers based on parameters from a form. Also, the report needs to display date headers even for days on which no events occur.
Any ideas?
See also:
http://www.access-programmers.co.uk/forums/showthread.php?t=130335
hello
I've set up a query which contains among others the following fields
ProjectID
WorkstreamID
MonHoursDate
MonHours
MonHoursDate and Monhours come from another query where the date is a calculated value.
If I now run the query, it works and lists the hours done for that project.
If I enter the following expression in the criteria for MonHoursDate
Between [Start] And [End]
and then run the query and enter start and end dates, I get nothing.
Any ideas what I'm doing wrong? The date formats are set to Medium Date and follow the following fomat DD/MM/YYYY
Thanks
sunil
Hello all, I would like to ask for some help. I am trying to make a select Query that will give information based upon a date range the user enters. I know how to make it so the info shows up for a particular date, but for some reason cannot figure out how to make it so it works for a date range. Can someone please help. Thanks
View 1 Replies View RelatedI am working on a database of investments. I have a purchase date and a matured date. I would like to create a query where I enter a month and any investment that is active will show. For example if I have an investment that is purchased 1/1 and matures 2/28 and I query February it would show. If the query is for March it wouldn't show. I can't seem to find the answer for this so any help would be appreciated!
View 4 Replies View RelatedI am trying to create a query where the user can enter a date range and receive a list of records filtered by that date range. The column header is "Date" and the criteria field I have as: Between [Enter Start Date] and [Enter End Date].
This normally works when the user enters 1/1/14 and 1/31/14 as start and end dates for example. however, the table I am linked to with an ODBC connection lists the dates in the following format: 12/26/2013 6:15:11 PM and it will only return records if I enter the full date like 1/1/14 12:00:00 AM and 1/31/14 11:59:00 PM. This is cumbersome for the user and if you type just 1/1/14 and 1/31/14 no records are returned.
Is there any way around typing the full long date? I do not know VB, I only know how to use query design.
Below is the expression I have in a query. I have a Combo Box on a form that will show all my query's so the one need at the time can be selected. My question is: is there anyway when I select this query to run from the Combo Box on the form that I can input the date range on the fly? The date range will change periodically and I would like to somehow input the date range when running the query from the Combo Box.
FORMS: DCount("[Date_of_Change]","all_trucks_table","[FORM #]=True AND [Date_of_Change] Between #06/30/05# and #07/31/05#")
Thanks for all help!
My question involves the table below
AutoNumber(primary key)
SerialNumber
Station
Defect Type
Date
Basically now I need to be able to get the result i've gotten by a date range, but the crosstab query won't let me do it the way you can in a regular select query...help..
------------------Station 1 ----------Station 2------------Station 3---------------Station4
RxFailure ------------52-----------------52
Bit Error Rate 10 -----0------------------15
I am creating a database for a workplace for staff management.
i want a query where the user can input a month or a date range and be able to view all of the staff on holiday during this period.
I originally had a working query however it ignored staff that were on holiday during this period for example searched 11/2006
showed holidays in november but not staff who are on holiday from before and still on holiday during this time.
At this moment in time the fields i have included for beginning the query is BookingID, StaffID, EventName,StartDate,EndDate
any help would be appreciated
Hi.
I want to set up a simple query criteria where it searches based upon a column called Date.
So I want a criteria that says when you run the query ...
Between which date range? And which date?
Now I know I need to use Between and And within the Citeria, however I would be grateful for someone to show me the exact wording for the criteria.
Thanks and kind regards.
Phil Drury.
Hi all, Please help me correct this criteria for a filter in my query.
I want to show all records between todays date and the past 14 days.
I've tried,
Between Date() and "Date - 14"
And it dont work. Thanks for any reply.
Please help
I have a cross tab query which is based on a simple query which gives me a summary of sales (sales by sales rep and sales by job type) is there any way i can put a date range into this? everything I've tried doesn't work. It works in a simple query but i cant summarize the simple query. Thank you in advance.
Hi all, Can someone please help me with a date range query. Basically I have a query that shows delivery dates, from 01/01/2006 to 01/01/2009.
I just want to modify the query so it shows all deliveries within the past 21 days and all outstanding deliveries until 2009. Please note that in some cases, the delivery date may have been left blank in the table (as this it maybe unknown). If that is the case, the query criterion needs to pick that up as an outstanding delivery. Can someone help me with this? So far I’ve figured out
Between Date() And Date()-"21"
Thanks
I'm trying to produce a query that shows all records of patients that have a 'non-active' status (stored in the 'Patient Details' table) and haven't had any deliveries after 31/10/2011 (date stored in the 'Deliveries' table). I've tried a few different ways including using NOT IN (which access didn't like!) but I'm still no closer to getting the correct records.
View 2 Replies View RelatedI am trying to create a simple database to keep track of employee Car Insurances and MOT information.I am trying to create a query that will show me the following:
When the field "motexpiry" is Empty OR has a date within 30 days from todays date (including if today's date is in the field) OR the date is in the past.It also needs to show records with the same criteria for the field "insuranceexpiry".
And needs to show records where the field "cowensform" is blank.These are all OR queries, so that as long as ONE of all of those criteria is met, the record shows up.Once that query works, I need a very similar query but only showing records where one or more of those criteria is met, but only if the record also has "Oldham" in the "area" field.
I can then copy that query and edit the "Oldham" bit to have a query for each of our area offices.I tried putting "Oldham" in the criteria line of the area field in the query design, but it seemed to have no affect.