Problems Filtering By Date

Aug 11, 2005

i have created a new form based on a database called RptSmp97 from microsoft knowledge DB.
My form, as in the sample, calls a report when it is opened and at the same time shows you a multicombo form for filtering the report. In the sample the form uses only text fields, but i need to use numeric and date fields too. I could solve the problem with the numeric combos adding a condition for them, however, when i add another condition for the date it doesnt work.
Also, at the end of the code i print the sql and it looks perfect.

here is the code when the apply filter button is pressed....

Private Sub Command18_Click()
Dim strSQL As String
Dim intCounter As Integer

'Build SQL String
For intCounter = 1 To 15
If Me("Filter" & intCounter) <> "" Then
MsgBox Me("Filter" & intCounter).Tag
If Me("Filter" & intCounter).Tag = "effective_dt" Or Me("Filter" &
intCounter).Tag = "issue_eff" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & "
>= (" & Me("Filter" & intCounter) & ") And "
End If
If Me("Filter" & intCounter).Tag = "change_id" Or Me("Filter" & intCounter).Tag = "priority" Or Me("Filter" & intCounter).Tag = "Dom" Or Me("Filter" & intCounter).Tag = "Intl" Or Me("Filter" & intCounter).Tag = "Tasman" Or Me("Filter" & intCounter).Tag = "Regional" Or Me("Filter" & intCounter).Tag = "AA" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Me("Filter" & intCounter) & " And "
End If
If Me("Filter" & intCounter).Tag = "name" Or Me("Filter" & intCounter).Tag = "status" Or Me("Filter" & intCounter).Tag = "assignee" Or Me("Filter" & intCounter).Tag = "app_status" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
MsgBox strSQL
Reports![RepFilter].Filter = strSQL
Reports![RepFilter].FilterOn = True
End If

End Sub

thank you in advance, max.

View Replies


ADVERTISEMENT

Query Help - Filtering For A Date...

May 17, 2007

This query is to show aging. I have a field for processed date. I have a query that allows a date range to be selected using the Between function.

The query runs and pulls these variables from a form. It shows only those accounts which were processed between those dates.

However, it is also showing all of the accounts that have no date in this field. I need to set it up so that if the field has no date then it doesn't show those accounts.

I've tried Not (is null) for the criteria but that isn't working. I've tried a lot of stuff but nothing seems to work.

Thanks for the help :)

View 1 Replies View Related

Filtering Date Input

Dec 1, 2005

i am trying to build a marine turbine and parts log and have a problem in satisfying a working rule.

i have 2 tables which i have attached,

table 1 shows the on / off dates that a generator was fitted to a turbine

table 2 shows a general date history of the generator, when it had checks done, fitted, etc

what i would like to do is fit the generator again to another turbine

using a continous form to show the available dates in table 2, i would NOT like to see the dates during the period when the generator was fitted previously as shown in the on / off the dates in table 1.

the working rule being that a generator can not be fitted to 2 different turbines at the same time

can anybody point me in the right direction or assist in how i can overcome this problem

many thanks in advance for help provided

View 3 Replies View Related

Filtering By Date In A Report?

Oct 28, 2013

I've created a Form where I enter all my data - then I created a Report to output this data, it includes two buttons to filter based on one category. That's working fantastically well for me.

But now I would like to filter on dates. For example, last 7 days, last 14 days etc - and also give the user the option of filtering between x and y dates. I've discovered this is easily done by right-clicking the date field in the report, but I'd prefer to embed these commands into buttons etc so it's easier to navigate.

View 2 Replies View Related

Filtering Specific Date Periods

Oct 29, 2006

Hi all!

I want to add a function to a form that filters records from a specific date period.

Each record has a field containing the date the record was added.
Example: I want to show the records added between 15.09.2006 and 10.10.2006.

Furthermore, the “from” and “to date” must be defined in textboxes, and the filter initiated with a button.

I need this functionality on a continuous form and in a report. How do I achieve this?

View 14 Replies View Related

Modules & VBA :: Date Filtering Through Excel

Jan 27, 2014

I have an Excel workbook which I use to extract data from an Access database using VBA. A requirement has popped up to show things from the last week. Unfortunately, I can't just use a Date()-7 query in Access as the start point might be different, so it needs to be based on seven days prior to a date field in Excel.

So, I have a cell in Excel with a date, in this case "16/01/2014". I want to then use that date in my query to say "greater than 8 days prior to to this date". The field is formatted as a date and the variable (DatafileDate) taken from this field and passed to the function is a Date. In my Select query I then have this line to select the top 5 marked as UK accounts in this case:

Code:
SearchText = "SELECT TOP 5 * FROM `" & TableName & "` WHERE `" & TableName & "`.`Account`='UK' AND `" AND `" & TableName & "`.`Close Date` > #" & DatafileDate & "#"

Code:
#" & DatafileDate & "#" -8

Should mention that the TableName stuff is so the name of the table can be set elsewhere. "Close Date" is the column in my Access query I need to filter on.

View 2 Replies View Related

Filtering For Lowest Date Out Of Duplicate Records

Mar 13, 2008

I have a table that has a list of order information, there are multipule records per order that have information about when the order was processed. There is a day tied to the process time of each record, I need to filter out the duplicate records and get one record for an order, but that one record must be the first process time entry for that order. Anyone know how to make this query? Let me know if this doesnt make sense.

View 4 Replies View Related

Queries :: Filtering On Value Of Formatted Date Field

Feb 22, 2014

I have a database with a table of employees, and that has a column named "DaysOff", into which I type a couple of days like this: "SUNDAY AND MONDAY"

Next, I have a form onto which I will display a query of those employees. The form contains a field called DATE in which I display the mm/dd/yyyy date. What I want to do is this:

I only want those employees that have a DaysOff field that DOES NOT CONTAIN the WEEKDAY NAME of the date in the DATE field. So, for instance, if the DATE field read 2/22/2014, and I have three employees as such:

Employee 1 DaysOff "Monday and Tuesday"
Employee 2 DaysOff "Friday and Saturday"
Employee 3 DaysOff "Sunday and Monday"

In that case, ONLY EMPLOYEES 1 and 3 should show up. Employee 2 will NOT show up, because his DaysOff field contains the word "Saturday", and the WEEKDAY FORMATTED value of the DATE field is "Saturday"

I hope that's not too confusing. Again, this needs to be the filter criteria for the query. That query will only return a list of employees that are NOT off on the day in question. If one of their off days is the day in question, the query won't return that record.

I've tried a few things, but I can't get it to work. If you want, I can list the various things I've tried, but I imagine one of you knows exactly how to do what I need to do, and you won't need my feeble attempts as a springboard.

View 2 Replies View Related

Filtering Data With Date And Check Boxes

Aug 25, 2014

I am new in MS Access programming and is trying to create a search form for all records based on selected dates and check box. I was able to filter the records by dates but can't find ways to filter the records that meet the dates to be filtered again based on the selected check box/s. Basically what I wanted to is display all the records within the selected dates and check box/s.

View 9 Replies View Related

Reports :: Report Not Filtering Correctly With Date Inputs?

Mar 13, 2013

My report generates base on 2 date inputs(from textboxes) namely, ProcStartDate & ProcEndDate & a combobox(cmbRptSupplier) to select the supplier:

Code:
Private Sub btnReport_Click()
Dim strDocName, strWHERECondition As String
strWHERECondition = ""
If Me.cmbRptSupplier = "" Or IsNull(Me.cmbRptSupplier) = True Then

[Code].....

When I leave my cmbRptSupplier blank (so that I can leave supplier out of my criteria), & fill in 1/2/2013 for ProcStartDate, 14/3/2013 for ProcEndDate, I get ALL the invoices displayed in return for my report. Even with those before 1/2/2013.

View 10 Replies View Related

General :: Date Format In Table - Filtering Data

Jan 19, 2014

I have a table having column as date, format as mm/dd/yyyy and i update the column as now() using VBA.

However some places the date is in mm/dd/yyyy and in some mm/dd/yyyy nn:mm:ss are appearing which is making the filtering of the data ambiuge.

View 2 Replies View Related

Queries :: Access 2013 - Filtering Report By Date?

Jan 14, 2014

Access version: 2013 / The user can click a button on the report that opens a form where they can select various filters. Once the user has selected the filters and clicks done, I have some VB code that fires which builds an SQL string with all of the filters selected. It then reopens the report with the new filter.

What I am running into issues with is one specific filter that the user can select. They can select a "from date" and "to date". Sometimes this filter returns what I want and sometimes it doesn't.

For example. I set 01/01/14 as the start date and end date as 15/01/14. <-- this works as desired.But if I put start date as 10/12/13 and end date 15/01/14 for example then it returns all previous records up to the specified end date. I.E dates such as 10/10/13 will show.

This is the VB code used to build the SQL for this filter:

Code:

newWhere = newWhere & "[FollowUpDate] > #" & Me.txtFromDateSelect & "# AND [FollowUpDate] < #" & Me.txtToDateSelect & "#" & " AND "

note: I have also tried using the between SQL syntax for querying the date to no avail and the same results.

This is the SQL that the above code creates:

Code:

[FollowUpDate] > #1/01/2014# AND [FollowUpDate] < #15/01/2014#

This only happens when I try select a start date prior to 12/12/13 (so the year is not the issue)

This happens both when I apply just the from date and to date filter and also when I mix it in with other filters. I.E Company name, product etc..

View 6 Replies View Related

Modules & VBA :: Filtering Subform (datasheet) By Fields (Date)

Mar 27, 2014

I filter my subform (datasheet) by fields, everything works great except "date" column. MS Access shows that record does not exist but it isn't true.

Code:
Me![frmAktPD].Form.Filter = "[Data przyjęcia] = " & Me.DataPrzyjecia
Me![frmAktPD].Form.FilterOn = True

Where is the problem?

View 1 Replies View Related

Forms :: Filtering List Box Using A Date Rage And Combo Selection

Jan 5, 2014

I am using this code to filter a List Box based on a Date range and a Combo Box selection:

Code:
Private Sub Combo139_AfterUpdate()
Dim StrgSQL As String
StrgSQL = "SELECT [User Name], [Date Of Request], [Description of Problem], Status, Sub_Job FROM QRY_SearchAll " & _
"WHERE [Date of request] BETWEEN #" & CDate(Me.txtStartDate) & _
"# AND #" & CDate(Me.txtEndDate) & "#;"
StrgSQL = StrgSQL & " WHERE Sub_Job = Combo139"
Me.SearchResults.RowSource = StrgSQL
Me.SearchResults.Requery
End Sub

However, It is not working. when I click the Combo box the List Box comes up blank.

View 13 Replies View Related

Modules & VBA :: Filtering Subform By Mainform Date Text Boxes?

Jul 25, 2013

I'm having issues with this one.

Table name is CompDates
Subform is ViewByMarket

Start date is Text13 and End Date is Text15.

I want it to filter out any matches between the 7 fields. Here is my code:

Code:
Private Sub Command19_Click()
Dim strFilter As Variant, _
strSDate As String, _
strEDate As String
'check Text13
Text13.SetFocus 'set focus to Text13 to be checked

[code].....

It will only filter for Build Date 1 and not for any of the other fields.

View 1 Replies View Related

Queries :: MS Access Cross Tab Query - Date Range Filtering

Aug 14, 2013

I just wanted to know how can i set a parameter on the following cross tab query to filter dates. Date field is [pdate By Day].

TRANSFORM Sum([PettyMaster Query2].Amount) AS SumOfAmount
SELECT [PettyMaster Query2].[Petty Cat].Field2, [PettyMaster Query2].[pdate By Day], [PettyMaster Query2].ProjLoc, [PettyMaster Query2].descriptionofpay, [PettyMaster Query2].projno
FROM PettyMaster, [PettyMaster Query2]
GROUP BY [PettyMaster Query2].[Petty Cat].Field2, [PettyMaster Query2].[pdate By Day], [PettyMaster Query2].ProjLoc, [PettyMaster Query2].descriptionofpay, [PettyMaster Query2].projno
PIVOT [PettyMaster Query2].PettyCOA.Field2;

View 11 Replies View Related

Filtering

Jun 14, 2005

I have a text box (txtInput), is there a way that I can filter results on a form based on txtInput.text?

View 1 Replies View Related

Filtering

Jun 2, 2007

Hey guys im a bit stuck and would really appreciate any suggestions...

I have 3 tables:

Table 1: Products
Product_Name

Table 2: Suppliers
Supplier_Name

Table 3: Prices
Product_Name
Supplier_Name
Supplier_Price

Table 1 & 2 have a relationship to table 3, with a many to many relationship. i.e. many products can have many suppliers.

This part works well and i have a form that shows each product with a subtable that shows that products suppliers.

However, i want to be able to filter to prices table to show each individual supplier and what products they supply.

I appreciate they time anyone took to read this and hope i made it clear, and if any one can help like i siad i would be very gratefully :)

View 1 Replies View Related

Filtering

May 19, 2006

When using the MS Works database the "Find" command makes a new table of all records that include the chosen words.
The "Find" command on Access 2002 does not do this, nor does any filter command. Is there an easy way to do this in Access?

Thanks

View 3 Replies View Related

Help With Filtering

Aug 10, 2005

I want to filter the information displayed on a form using the values I select in text or combo boxes.

I want to filter the form using one or more of the following options.
Call Ref - text box
Location No - Combo box
Area - combo box
Status - combo box

The combo boxes are taking their values from other tables within the database.

Can anyone help me develop the code needed to filter the form.

Thanks

View 4 Replies View Related

Filtering!!

Jun 26, 2006

hello

i have a data sheet form which I need some help with!!
I have a lot of data that I need filtering.

The first filter is for day and the second is for time of day.
I.e. i want to be able to click on a date (using the calender from MS datepicker) and have the datasheet return the records for that selected day.

But I would also like another field that can filter it down to a specific time of day. e.g. 27/06, 13:00 returning all the records for this specific time slot.

At the moment there is a filter for date AND time but I would like date AND/OR time.

Also I would like to have it setup so it automatically gets rid of data over 2 months old, is there a way to do that, if not automatically maybe with a macro/command button??

Thanks for you help

View 1 Replies View Related

Filtering

Jan 14, 2008

i have a form bounded to a table called street.
this form has 2 bounded text boxes:
country: lookup field to the country table
city: luukup field to the city table

i want to do the following: when GetFocus on the city text box, i want to filter in thi stext box for all the cities that belong to this country (value in country text box)
how?

View 5 Replies View Related

Form Filtering?

Nov 1, 2005

Hey all I am having a problem with my form filter. I was wondering if you can just direct a filter from form 1 to form 3 without referencing form 2. I am thinking not cause I am having a problem getting the filter to work. Here is the code I have in my load form.

framfiltval = "CONSTR_LOCATION = " + "'" + Form_PID.ADDRESS3.Value + "'"
Form.Filter = framfiltval
Form.FilterOn = True

Thank for the help in advance

View 2 Replies View Related

Question On Filtering??

Jun 20, 2007

Hi

I have this database that list companies and what type of loan they have. I am having a hard time thinking of the right type of filter to use. I am looking to select multiply types of loans and have it filter out the companies that have that service. I was thinking of a list box or options but I am having trouble with the table. My question is how would you go about it? Thanks

View 1 Replies View Related

Filtering, I Guess That's It

Jul 23, 2007

hi
hope someone can help.
I'm making a form to select country/state/city for members
i made a table with 3 columns country,state,city

i wrote down the states for 2 countries (the ones i'm gonna use)
the city is left blank (since i don't want to write down all the cities just yet)

i'm looking to do:

When i try to display the Country as a drop down using a query that selects the column for country, i get a list of the same country the amount of times it is in the table for each state.
I only want to see each country once. even though in the table it is written several times.

View 2 Replies View Related

Filtering Nightmare

Feb 1, 2008

Is there any way that I can import a text file and split the data into three columns without having to go through 65000 lines of text.

It is basically a print off of a directory structure. I have a very large text file that lists the files in folders for each of hundreds of users. I need to divide the information up into three columns so that the below data:

BOBSongs

2003-07-15 12:10 PM 1,474,564 blue.mp3
2003-07-15 12:09 PM 858,204 orange.mp3
2003-07-15 12:10 PM 1,665,028 yellow.mp3

BOBsongshappy

2005-08-23 03:01 PM 917,504 jolly.mp3
2003-07-15 12:09 PM 331,005 joker.mp3

BOBfiles

2005-08-23 03:01 PM 917,504 writing.doc

ends up looking like this:

BOBsongs1,474,564 blue.mp3
BOBsongs858,204 orange.mp3
BOBsongs1,665,028 yellow.mp3
BOBsongshappy 917,504 jolly.mp3
BOBsongshappy 331,005 joker.mp3
BOBfiles917,504 writing.doc

I easily imported the file into Excel, but it is still all in a column. Any advice would be appreciated.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved