Queries :: LIKE Filter Does Not Work From A Form Field

Jan 14, 2015

I am attempting to use a form field as the source for a query filter criteria. Everything works fine if I simply use an "if equal" filter condition. As soon as I try a "like" condition, nothing works.

I created a test table with just one column (fld1). The table contains three records with the following values: BRDODS, BRD, TLAODS.

The following hard coded query returns two records, as it should.

SELECT Table1.fld1
FROM Table1
WHERE (((Table1.fld1) Like 'BRD*'));

I also created a test form (Form1) with just one text field (Text0). My intent is to soft code a criteria value via the form field instead of hard coding the query, as above. When I populate the form field with BRDODS, the following soft coded query returns one record, as it should.

SELECT Table1.fld1
FROM Table1
WHERE (((Table1.fld1)=[Forms]![Form1]![Text0]));

When I enter LIKE "BRD*" in the form field, no records are returned. I should get two records, just like the hard coded query above.

I've tried all variations of the LIKE statement in the form field, but nothing works.

View Replies


ADVERTISEMENT

Queries :: Work Order Number - Criteria To Filter By Text?

Jun 30, 2015

Is it possible to do a criteria like the "Between" to pull data like a work order number?

Ex WO#: WO5551212

I would like to be prompted to enter a Work Order Number when I run a query, then have the query display all of the data for that particular work order number.

View 4 Replies View Related

Filter By Form Does Not Work

Apr 15, 2005

Hi,
I have a form which I can't seem to filter by form. When I click on "Filter by Form", the only combo option I get is "Is Null" or "Is Not Null", it does not give the full list in the table to choose. Can anyone tell me what's wrong with my form? The form also has a sub-form....does it matter?

I also tried tesing filtering the table with query but it also doesn't work.
My query,
SELECT DOCUMENT.TITLE
FROM DOCUMENT
WHERE (((DOCUMENT.TITLE) ALike "*work order*"));


Also, with Filter by form can I use wildcards such as entering in the search field of the form as "= "*work order*"

thanks,
Galantis

View 1 Replies View Related

Forms :: Query Doesn't Work To Filter In Form

Nov 4, 2013

I've got a single form ("Lead Data") that has Cascading Combo boxes that work perfectly, entering data into "tblLeadData":cboMatterTypeIDcboMatterIDcboAttyIDcboPlglID Attorney & Paralegal are the people assigned to the Matter. My problem is in finding a way to allow a specific Attorney or Paralegal to filter for only his or her records. I made a query of tblLeadData that works perfectly as a query, but when I use it as a filter in an "on click" macro event, it doesn't work. I suspect it's because of the cascading combos, because I've successfully used this kind of query based macro filter in the past.

Okay, more on how it is set up. The same people are always assigned to a specific matter, so when you pull down the Atty & Plgl combo boxes, there's only one person. So it isn't a true Parent/Child relationship, but it's working. And there were two advantages of this set up over an autopopulate set up (which I considered): 1) When I change something in the reference tables (refAtty and refPlgl), it also changes in tblLeadData & 2) in case there's an exception to the usual assignment pattern, we can just leave Atty & Plgl blank and put the correct assignment in a text box called "AssignmentNotes."

So my query of tblLeadData that works, qryLeadDataAssign, uses the following fields:

tblLeadData.AttyID
refAtty.Atty
tblLeadData.PlglID
refPlgl.Plgl
tblLeadData.AssignmentNote
Expr1: [Atty] & " " & [Plgl] & " " & [LeadAssignmentNotes]Criteria: Like "*" & [Who?] & "*"

The Join Properties in the query between tblLeadData and refAtty is set to "2: Include ALL records from 'tblLeadData' and only those records from refAtty" where the joined fields are equal." And the same for Plgl.

When I run the query, it asks me a single time, "Who?", I put in the name and it pulls up all instances of the name from any of the 3 fields. It acts as a "contains" filter, not an "equals" one.

As for my cascading combos, here are the settingsMatterTypeIDRow Source:

SELECT refMatterType.MatterTypeID, refMatterType.MatterType, refMatterType.[MatterType] FROM refMatterType ORDER BY refMatterType.[MatterType]; On Change Event:Me.cboMatter.Requery
MatterIDRow Source: SELECT tblMatter.MatterID, tblMatter.Matter FROM tblMatter WHERE (((tblMatter.MatterTypeID)=[Forms].[LeadData].[cboMattertype])) GROUP BY tblMatter.MatterID, tblMatter.Matter, tblMatter.Matter ORDER BY tblMatter.Matter;
On Change Event:Me.cboAtty.Requery

[Code] ....

I put a button on the form and put an embedded macro as an "On Click" event. The macro is an "ApplyFilter" and the filter name is qryLeadDataAssign. When I click on the button, I am asked to enter

data 3 times:Enter Parameter Value: Atty
Enter Parameter Value: Plgl
Enter Parameter Value: Who?

Clearly, the expression in the query doesn't function in the button. And the result, no matter what I put in, is that all of the records are still there, although the filtered button is activated.

I tried putting the expression from the query into the macro builder window, but I for sure don't know what I'm doing there and haven't been able to make it work.

View 4 Replies View Related

Forms :: Two Combo Boxes To Filter Form Won't Work Together

May 8, 2013

I have a database and a form that is based off of a simple table. The table has NO look ups and all of the formatting is text. The form is continuous and simply displays these fields. (Kind of like a company roster with name and department). I have two combo boxes at the top of this form in the header with the intent of filtering the form records by department and employee type (lets say A or B).

I have tried every way I know how to get these records to filter and they will not filter correctly. Utilizing methods I have used in multiple other databases, I set the two comboboxes to cascade based on a query in the recordsource. The vba I'm using in the after event of each combo is ....

Me.Filter = "[Brand] = '" & Me.cbobrand & "'"
Me.FilterOn - True
'Brand is the "department" and the other identical code is for Personel_Type

This filters the records but independently. So, cbobox1 filters the records to show all Brand 1. When cbobox2 selection is made it shows all the Personel_Type of the selection however the first filter is already disregarded. (i.e. When cbobox2 selection is made, it contains both brands instead of the one I've just selected and filtered in cbobox1. )

Outside of making two queries to account for each possible filter, I have tried setting a filter on the filterON, I have tried a SQL based VBA code, I have tried making two forms and attempting to set the filter on open but either way, nothing keeps the first filter selected (or the FilterON, or both SQL filters, etc). The ONLY way I have found to get it to filter both is with the filter function in Access, which is not useful as my users will not have access to the menu bars.

I have this EXACT same setup in another database and it works fine with 3 cbo boxes with an after even to set a filter for the recordsource for all three.

View 5 Replies View Related

Can't Get Filter To Work

Jun 30, 2005

I want to add a button (FindNew) to my form (frmDenial) that, when clicked, will filter for records where the field (MBRLast) has "xx" in it, or is Null. I have spent the last 5.5 hours searching through the forums and playing around with sample codes and still cannot make it work. Please help me with this, I'm sure it's very simple. Thank you so much, everyone in the forum is so helpful!

View 3 Replies View Related

Filter Does Not Work?

Nov 29, 2006

Okay, I have a datasheet form. The source is TableData.

I want it to be filtered by date. The date is found on a separate form in txtDate.

So here is what the "Filter" property looks like:

((TableData.Quarter=([Forms]![frmWhatever]![txtDate])))


Now here's the odd part. When I initially put this filter in, it works at first. However, once I save the datasheet form and reopen it, the filter no long works, even though the filter property still remains.

Any ideas?

View 9 Replies View Related

Queries :: Cannot Get Crosstab Query To Work In A Form

Dec 30, 2013

When I run this query

TRANSFORM Sum([rpt LEAD TYPE SUCCESS RATE2].rec_cnt) AS SumOfrec_cnt
SELECT [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date
FROM [rpt LEAD TYPE SUCCESS RATE2]
GROUP BY [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date
PIVOT [rpt LEAD TYPE SUCCESS RATE2].Results;

I get valid results.

When I run the form it prompts me 3 times for each start date and end date. Then I get the following error :The Microsoft Access database engine does not recognize '' as a valid field name or expression.

I am running MS Access 2010 on a Window 7 pc.

View 10 Replies View Related

Queries :: Wildcard Search Does Not Work With A Form Control

May 24, 2015

I am trying to get a wildcard search to work with a form.

I have a query, in which the criteria is:

Like "*" & [Enter a word] & "*"

That works fine. I enter a word, and I get the few records in which the word appears.But if I try to replace [Enter a word] with a word entered on a control on a form, it doesn't work - I get all the records. This is my code:

Like "*" & [Forms]![Myform]![Mycontrol]&"*"

What am I doing wrong?

View 8 Replies View Related

Filter Query Wildcards Won't Work-pls Hlp

Jun 6, 2005

I'd be grateful if someone could offer some help with this to a frustrated Access novice : )

I have a query that filters records according to the value of an unbound text box in my form (basically search on last name)

I want to introduce wildcards into the filtering process but cannot get it to work.

Here's what I've done:

In the criteria field I had
[Forms]![MAIN]![Text440]
refering to the unbound text box on the form already mentioned.

This worked fine, but the following:
Like "*[Forms]![MAIN]![Text440]*" does not.

Nor does various combinations of brackets such as:
Like "*([Forms]![MAIN]![Text440])*"

In fact it now doesn't return anything, even if given a positive match. I have trawled help and the forums but to no avail. I have also tried the % character instead of *

I am using Access2000...

View 2 Replies View Related

Forms :: Filter Between Two Instances To Work Without Values?

Mar 25, 2013

The thing I've been trying to do is make it so that my form filters my records, and I'm trying to make a between function for it. My form is shown below in the attachment.

What I need to do is make it so that my form filters my records Between the two year boxes AND between the two Length boxes. But I need it so that if nothing is in the boxes, it shows all records, and if something is in only the Year boxes, it only filters the years and not the lengths.

Code:
Field: Length
Criteria: Between [Forms]![SearchForm]![Length1] And [Forms]![SearchForm]![Length2]

Then in a separate column I had

Code:
Field: [Forms]![SearchForm]![Length1]
Or: Is Null

This works fine if it's only for Length, but if I try to do the same for the MovieYear boxes, it screws up and just shows me no records...?

View 9 Replies View Related

Queries :: How To Filter Checkbox Field

Jul 29, 2013

I have a query which is run from a form via a command button. On this form i have a couple control sources (not sure if this is the correct term, but i have text boxes and drop down lists which the query uses in its criteria).

Everything works fine except i can't figure out how to filter my checkbox field (aka MyDeliv). I'd like to have a drop down box with 3 options: "yes", "no", and "ignore". i would like "yes" to return only records with the MyDeliv box checked, "no" to return only records with the MyDeliv box unchecked, and "ignore" to return records regardless of the MyDeliv checkbox.

View 3 Replies View Related

Queries :: Cascading Combo Box - Filter By Field?

Mar 16, 2013

I want to create a cascading combo box - so far so simple, but in all the tutorials I can find the options in the second combo box are exclusive to the first e.g. combo box 1 selects "state", combo box 2 then displays "city". Each city can only be in one state.

In my scenario each city is in several different states - and so far I'm failing to come up with any sort of strategy as to how to deal with this. I tried giving each state a "yes/no" formatted field in the "city" table, figuring if I could use the first combo box to point access to the right field in the "city" table I could filter those records by "WHERE [fieldnominatedbycombobox1] =True" - but I don't know how to make a one combo box determine which field a subsequent combo box filters by.

View 13 Replies View Related

Queries :: Filter Calculated Query Field

Nov 13, 2014

So I have the following query field which calculates another field.

How do I Filter the records in this calculated field to only return TRUE, as if I put "TRUE" in the Criteria for this field (or anything at all) then a parameter message box pops up asking for [Balance].

Code:
Balance1: IIf([Balance]<>0,"TRUE","FALSE")

View 8 Replies View Related

Queries :: How To Filter One Field With 2 Combo Boxes

Jan 31, 2014

I have 2 unbound combo box's on a form 1 called cboclient1 and the another called cboclient2. would like to be able to filter field name client name twice by cboclient1 and cboclient2 here my sql from my query

Code:
WHERE (((Assets.Client)=Forms![report gen]!cboclient1)) Or (((Forms![report gen]!Cboclient1) Is Null));

This works perfectly for cboclient1 problem comes when I try and add cboclient2

I have tried

Code:
WHERE (((Assets.Client)=Forms![report gen]!cboclient1)) Or (((Forms![report gen]!Cboclient1) Is Null))AND(((Assets.Client)=Forms![report gen]!cboclient2)) Or (((Forms![report gen]!Cboclient2) Is Null));[/

This doesn't work at all....

View 2 Replies View Related

Queries :: Filter Query With DateSerial Calculation Field?

Nov 21, 2014

I have a query with a DateSerial Calculation field that I would like to filter the query by. The DateSerial calculates the same day of every year (5/31/"YYYY"). When I try to add a criteria sort to this field, I get a data mismatch error. Here is the code: ThirdMay: DateSerial(Year(DateAdd("yyyy",3,[LastDayYear])),5,31).

How do I get only dates due in 2015 to show? I have tried all the standard date criteria to no avail.

View 1 Replies View Related

Queries :: How To Filter Calculated Field In Query Using A Criteria

Jul 17, 2014

I have query with a calculate field to finds the next service due date but I'm having a problem getting it to only show services due dates in the next 30 days.for some reason I cant add a criteria date()-30...I have to calculate the next service it takes service intervals from maskservicemonths field then find the last service date and generates the next service due date NextService: DateAdd("m",[MaskServiceMonths],[FindLast]).

View 14 Replies View Related

Queries :: Payment Method - Multiple Filter Criteria On Same Field

Jul 30, 2014

I am using MS Access 2007.

I would like to display a report based on the table called "expenses", filtered by:

- "from" (datefrom field) and "to" (dateto field) date on "payment date" field;
- multiple criteria on same field called "payment method" (I would like to include only payment methods "check" and "credit card", but not the other payment methods in the field, such as "cash", "transfer", etc.)

For that end I made a query based on the table "expenses", and in the "payment date", in the criteria field, I entered:

between [form]![formname]![datefrom] and [form]![formname]![dateto]

This works fine so far, however when I attempt to add multiple criteria on the "payment method" field, it does not filter accurately any longer. In the same row of the criteria field where I completed the date criteria, I enter "check". In the next row, same field, I enter "credit card".

Since it doesn't work, I tried putting both arguments in the same line as the date criteria (always in the payment method field) as: "check" and "credit card" but still does not work (now it filters the payment method correctly, but the dates filter appear as if I have never completed them).

View 3 Replies View Related

Queries :: Multi-Select List Box As Filter For Query Field

May 16, 2013

On [Form1] I have a Multi-Select Listbox[List1] which shows Job Numbers [WBS] (The job numbers displaying are a result of a separate query filtered by the Fiscal Year combobox). When I click [CMD1] I want [Query1] results to only be selected [WBS] from the form.What is the best way to code this? I'm a beginner when it comes to VBA.

View 12 Replies View Related

Making A Look-Up Form Work The Way I Want It To Work!

Feb 27, 2006

We are creating a simple database to maintain driver license information for faculty, staff, and students who use cars from the university’s motor pool.

To do this, I have created two tables: tblDRIVER and tblLICENSE.

The fields in tblDRIVER are:
pkfDriverIndex
strLastName
strFirstName
strInitial
strAddress1
strAddress2
strCity
strState
strZIP
datBirthDate

The fields in tblLICENSE are:
pkfUpdateIndex
fkfDriverIndex
datDateUpdated
strState
strLicNumber
datExpirationDate
ynViolations
ynActive

Information about the driver is stored in tblDRIVER and the driver’s license information is stored in tblLICENSE. Periodically, we run a report that identifies drivers whose licenses are due to expire within a certain number of days. All this works fine.

My problem is that I am trying to create a lookup form that will load from a data entry form that will permit the Motor Pool Clerk to look up a driver by name and review the licensing information (which appears as a subform).

All this sort of works- I am using a combo box (based on a query) to look up a driver’s last name (which it does) and to populate all the driver’s information on the look-up form (including license information in a subform). Currently, the combo box locates the driver (including the unique index, last, and first names), and populates the form with first and last name but the rest of the information is not displayed on the look up form. Worse still, sometimes one driver’s last name matches up with the first name of the next driver listed in the table! This seems to happen only if a look up is attempted more than once. What gives.

Thanks for the help.

View 9 Replies View Related

Forms :: Filter A Form Based On A Field In Another Form

Jul 13, 2014

I have a form (Suppliers) with a button that opens another form (Product List).

At the moment the form (Product List) is set as a pop-up form and when opened lists all products.

I want to filter the pop-up form to only display products for the current supplier displayed in the Supplier Form.

I have tried the following below without success - I have varied this but the filter does not apply ...

Private Sub Form_Load()
DoCmd.OpenForm "Product List", acNormal, , "SupplierID = " & SupplierID
End Sub

View 3 Replies View Related

Possible To Filter A Form Based On A Calculated Field...?

Apr 12, 2006

Hi.
I have an Order form that is based on an Order table. In this form there are also a number of text boxes (not bound to table) with calculations, and one final text box with a deadline based on these calculations.
Is it possible to filter this form based on the deadline? I can do this by making the form based on a query, but then again I'm not able to edit the information in the form.... Please anyone???

View 2 Replies View Related

Filter Combo Box Query From A Form Field

Feb 21, 2008

If anybody could shine some light it would be much appreciated.
I have 3 tables:
Task table---*Task ID, Task(txt), Start(date/Time), finish(date/Time)
Tasking table---*TaskingID, TaskID, EmployeeID
employee table---*EmployeeID, Name(txt)
The employee and task table are joined to the tasking table. This is so i can have a task which has many employees and an employee with many tasks. What i'm try to do is create a form which starts a task with a subform to add employees, but with combo box which filters employees with tasks which over-lap the task in focus(start and finish fields) but still showing employees which do not yet have any tasks. the start and finish fields are hh:nn dd/mm/yy.
If anybody can help i would be very grateful

Jim

View 3 Replies View Related

Filter Combo Box Query From A Form Field

Feb 21, 2008

If anybody could shine some light it would be much appreciated.
I have 3 tables:
Task table---*Task ID, Task(txt), Start(date/Time), finish(date/Time)
Tasking table---*TaskingID, TaskID, EmployeeID
employee table---*EmployeeID, Name(txt)
The employee and task table are joined to the tasking table. This is so i can have a task which has many employees and an employee with many tasks. What i'm try to do is create a form which starts a task with a subform to add employees, but with combo box which filters employees with tasks which over-lap the task in focus(start and finish fields) but still showing employees which do not yet have any tasks. the start and finish fields are hh:nn dd/mm/yy.
If anybody can help i would be very grateful

Jim

View 3 Replies View Related

Forms :: Multi Field Filter On A Form?

Mar 16, 2015

I have a filter on a form : There is a text box (txtSearchP) where the user will enter their search term then click button to apply the filter:

Private Sub cmdSearchP_Click()
' Filter by Programme Description
Me.Filter = [Programme_Desc] Like "*" & Me.txtSearchP & "*"
Me.FilterOn = True
Me.Requery
End Sub

This works fine but actually I want to search two fields for the text entered in txtSearchP [Programme_Desc] OR [Programme]

I've tried this but it doesn't work:

Private Sub cmdSearchP_Click()
' Filter by Programme Code and Description
Me.Filter = [Programme_Desc] Like "*" & Me.txtSearchP & "*" OR [Programme] Like "*" & Me.txtSearchP & "*"
Me.FilterOn = True
Me.Requery
End Sub

I know I could give the users two text boxes and two Buttons one for each and this would work but I don't want the form to be cluttered.

View 4 Replies View Related

Queries :: Filter Data From A Table Using Combo Box In A Form

Feb 19, 2015

I would like to filter a data from a table using a combo box in a form.

The field I would like to filter is called ManufacturersID in MainTable and consist of text and wildcard characters [e.g., Teledyne O&G (ODI), TE Connectivity (Deutsch), etc...], so are the combo strings which are extracted from the table ManufacturersTable. It is not filtering anything. I suspect that the wildcards and spaces are the problem. I also tried to get the combo string to a text box in the form and use it in the query with no success. Additionally, I also tried Like [Forms]![FormName]![textbox] in query criteria but still not filtering...

How can I overcome this?

View 2 Replies View Related







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