Help With Wildcard Criteria On SubForm

Feb 21, 2005

I have a form with subform which use six fields on the form to determine
what data is returned in the subform. As long as every field has a valid entry the data is returned
correctly. What I would like to add to this is if a particular criteria field is left blank it will return all
values in that field. The current SQL Statement looks like this:

SELECT QryItemQtyProductLineLinked.FINISH, QryItemQtyProductLineLinked.SIZE, QryItemQtyProductLineLinked.COLOR, QryItemQtyProductLineLinked.BASISWGT, QryItemQtyProductLineLinked.PRODUCT_LINE, QryItemQtyProductLineLinked.MWIEGHT, QryItemQtyProductLineLinked.DFLT_VENDOR_ID, *
FROM QryItemQtyProductLineLinked
WHERE (((QryItemQtyProductLineLinked.FINISH)=[Forms]![FrmProductLineLookup]![MatchProduct]) AND ((QryItemQtyProductLineLinked.SIZE)=[Forms]![FrmProductLineLookup]![MatchSize]) AND ((QryItemQtyProductLineLinked.COLOR)=[Forms]![FrmProductLineLookup]![MatchColor]) AND ((QryItemQtyProductLineLinked.BASISWGT)=[Forms]![FrmProductLineLookup]![MatchBWeight]) AND ((QryItemQtyProductLineLinked.PRODUCT_LINE)=[forms]![frmproductlinelookup]![MatchProduct]) AND ((QryItemQtyProductLineLinked.MWIEGHT)=[Forms]![FrmProductLineLookup]![MatchMWeight]) AND ((QryItemQtyProductLineLinked.DFLT_VENDOR_ID)=[Forms]![FrmProductLineLookup]![MatchVendor]));

Thank you in advance for any help!

View Replies


ADVERTISEMENT

Wildcard Criteria With Input Box

Feb 28, 2008

hi,

new to the forum so hello everybody,
what i have is a table of contacts. this table has a field called locations. i have created a queriy that askes me the location which works ok. i could also have the criteria that has like "lo*" in it

but what i really need it the query to run ask me the location and i only put in LO* OR BI* etc
and the results i get are either london or birmingham etc

thanks for you help
steve

View 2 Replies View Related

Adding Wildcard Character To Query Criteria

Aug 11, 2005

I've got a query that does exactly what this (http://www.access-programmers.co.uk/forums/showthread.php?t=89564&highlight=null+records) chap got his to do.

However I want to add a "*" character to the criteria to allow for searches with partial matches. Here's the criteria that works:

[title]=[Forms].[Search]![Title] Or [Forms].[Search]![Title] Is Null

However when I try the logical extension:

[title]=[Forms].[Search]![Title] & "*" Or [Forms].[Search]![Title] Is Null

this doesn't work, and nor does:

[title]=([Forms].[Search]![Title] & "*") Or [Forms].[Search]![Title] Is Null

WHAT DO I DO? I'M TIRED AND I CANT THINK STRAIGHT! :eek: thanks and sorry for being so dumb!

View 6 Replies View Related

Queries :: Ignore Wildcard If Criteria Is Blank

Dec 16, 2014

In a database am building, I want to run a query with the criteria dependant on which field the user populates in a form.

The form has a number of fields that the user can select from including our reference number, the client's reference number and the site address.

I would like the user to be able to select the site address using a wildcard so that they can enter a part of the address such as "This Street" instead of "45 This Street" and the user be presented with all of the records matching "This Street".

I tried using the criteria:

Code:
Like "*" & [Forms]![SearchJobs]![SearchAddressLine1] & "*"

Which works perfectly as long as this field is populated. If this field is not populated, entering details in any other field bring up every record in the database.

Code:
IIf(IsNull([Forms]![SearchJobs]![SearchAddressLine1]),Null,[Forms]![SearchJobs]![SearchAddressLine1] & "*")

The full sql of my query is:

Code:
SELECT Jobs.JobsSalesEnquiryRecordNumber, Jobs.JobsJobStatus, Jobs.JobsEnquiryDate, Jobs.JobsTakenBy, ClientDetails.ClientDetailsURN, ClientDetails.ClientDetailsName, ClientDetails.ClientDetailsAddressLine1, ClientDetails.ClientDetailsAddressLine2, ClientDetails.ClientDetailsAddressLine3, ClientDetails.ClientClientPostCode, Jobs.JobsClientJobNumber, Jobs.JobsAlternativeClientJobNumber, Jobs.JobsClientContact,

[Code] ....

View 12 Replies View Related

Forms :: Applying Parameter Based Wildcard Search On Subform Filter

Jul 31, 2014

I have a Main form, and a subform which lists client details. On the Main form I have an unbound field. I want to be able to type a word into this unbound field and have it display all company names that have this word in them. ie. I type "Ltd" into the unbound field and it displays all companies with "Ltd" in the title.

I have created a query that does exactly this (Like ("*" & [Enter Word] & "*")), it displays a dialog box and I type in "Ltd" and it displays all relevant companies.

I have tried everything I know to make this work when I use the unbound field on the Main form, but I've had no luck.

View 3 Replies View Related

Hide Subform If Criteria Is Met

Apr 12, 2006

Hi,
I have a main form which has a subform with another subform in it. Subform 1 gives details of a loan and a balance field which is calculated in subform 2 after amt paid is entered - using a macro on exit.

If the balance is zero after amt paid is entered. I want the amt paid field in subform 2 to be locked or disabled - preventing the user from entering further payments by mistake.

How can I do this?

View 4 Replies View Related

Using Optional Criteria For A Subform Query

Sep 4, 2005

Hi,

Is there a simple way to add optional criteria in a query for a subform? Basically, what I'm trying to do is have a number of comboboxes in the parent form that indicate what records are shown in the subform (these records could then be added and/or deleted). I would like to be able to have a combobox such as "Month" that would filter the data shown in the subform to that month. If no month is selected, then the subform would show all months.

Any help would be much appreciated.

View 2 Replies View Related

Query Criteria Cannot Reference Subform

Jul 19, 2007

Hi,

Trying to run an append query for a specific record on a subform but won't recognise the subform control.

The Master form is fmSickEdit do I have to reference the Master file in the SQL?

The SQL for the query is:

INSERT INTO tblDisLetter ( SickID, DateAdvisedDisciplinary )
SELECT tblSick.SickID, Date() AS [Date]
FROM tblSick
WHERE (((tblSick.SickID)=[Forms]![fmsubSickListEditVersion]![SickID]));


Thanks

View 2 Replies View Related

Using VBA To Set Criteria In A Query And Refresh A Corresponding Subform

Dec 20, 2007

I have a Main Form, "frmBuildingSearch", which has a subform "frmBuildingSearchSub". frmBuildingSearch has a couple of input text boxes & 1 command button: txtCity, txtSite, txtBuilding, cmdSearch.
I want the command button to refresh my subform (which is run from a query, "qryBuildingSearch") based upon the text that is in the text boxes.

If there is any value in txtCity, then I would like to set the criteria of my City field in the query to:
Like "*" & [Forms]![frmBuildingInfo]![txtCity] & "*"

The same would go for any value in txtSite and txtBuilding.

I would like to use VBA to do this.

View 2 Replies View Related

General :: More Than Two Criteria To Filter A SubForm

Sep 28, 2012

I need to filter a subform based on the values on the mainform.

Criteria are Multiple. The idea is to show the user the records that are already existing in the table.

First two criteria works, stuck with the third one

I.Criteria - Creative(text type)

Private Sub Creative_AfterUpdate()
Me.frmCommercialWithDetails_subform.Form.Filter = _
"Creative ='" & Creative & "'"
Me.frmCommercialWithDetails_subform.Form.FilterOn = True
End Sub

[Code] ....

Also, how to split the code into two lines

"Creative ='" & Creative & "' And Act_ID = " & Act_ID

when broken into:

"Creative ='" & Creative & "' _
And Act_ID = " & Act_ID

is automatically saved as:

"Creative ='" & Creative & "'" _

And Act_ID = " & Act_ID " and this generates an error on execution.

View 5 Replies View Related

Using Subform Entry As Criteria In A Query

Nov 14, 2012

I have the following Form and Subform. Form name is "100-Select Form". Subform name is "103-Report Dates". Within the subform I have two fields I'm using, "Start Date" and "End Date'.

In my query I have a date field. I want the query to read from the Subform (if I open the subform directly my criteria listed below works, but when the main form is open, my query doesn't seem to be able to read from the subform).

Here is the criteria I have in the date field of my query: >=[Forms]![103-Report Dates]![Start Date] And <=[Forms]![103-Report Dates]![End Date]

I have a feeling that I somehow should be pointing my criteria first to '100-Select Form', and then to '103-Report Dates' within that form, but I'm just not sure how to write this and nothing I'm trying is working.

View 2 Replies View Related

Using Current Data On SubForm As Query Criteria

Feb 6, 2005

I have a Form> [Management]

I have a SubForm> [Management_History]

There is data in the [Year] field and the [Season] field.

I placed a button on the subform to create a report based on the data of the current "Sub Record" .

Basically, I want to use the data in the subform to create a small "Lookup" report.

OK:

I have the Management Form
I have the Management History SubForm
I Placed a Button Called PRICE LIST
The button kicks off a Macro that previews my PRICE LIST REPORT
A Query is the RECORD SOURCE for my PRICE LIST REPORT.

In the QUERY, I have two fields that I specify criteria "hopefully" based on the sub form record I am viewing.

I does not work on the SubForm. However if I Open the SUBFORM directly, the report pops up correctly.

What syntax in my QUERY is required to GET the data right frm the current SUB-FORM??

I tried the following:

Like [Forms]![Management]![Management_History].[Year]

Like [Forms]![Management]![Management_History].[Season]

View 2 Replies View Related

Opening A Subform Based On Criteria From A Combo Box

Sep 30, 2004

The posting in archive t-31918 was very helpful and almost does the trick. I have a subform with gifttype that has only giftID and gifttype. There are four different gift types and I need a subform to open in a specific location, as a subform. The code given by the archive (my version):

Private Sub Gifttype_AfterUpdate()
If Me.[Gifttype] = "restricted/unrestricted" Then
DoCmd.OpenForm "frmgift1"
ElseIf Me.[Gifttype] = "planned gift" Then
DoCmd.OpenForm "frmplannedgift"
End If
...

Opens what I want as subform as a regular form.

How can I get the correct subform to populate a certain area on the master form?

Thank you!
Pat Wright

View 1 Replies View Related

Filter Form Based On Subform Criteria

Feb 28, 2005

I need to open my form with a filter attached that is based on criteria already existing in the subform. I've tried putting the following code in the OnOpen event of my main form :
Code:Dim stDocName As StringDim stLinkCriteria As StringstDocName = "Campaign Form"stLinkCriteria = "[Campaign - Last Contact Status subform].Form![Communication Response] in ("2","4","10","11")"DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormPropertySettings, acWindowNormal

Basically I want to open my main form (Campaign Form) and only show records where the "Communication Response" is 2,4,10 or 11(a variable on my subform "Campaign - Last Contact Status subform"). Which in itself is a problem because I can't seem to code all of these variables- it will only accept one.

When I run the above code I am getting asked to enter a parameter value for "Campaign - Last Contact Status". I'm not too sure where this is picking this up from or how to fix it. I've even tried to run this based on the underlying query of "Campaign - Last Contact Status subform" with the same results.

Can someone help me? All I want to do is open the form based on the criteria I specify which is contained in a field in the subform. It doesn't sound that hard but it seems to be getting the better of me!

View 4 Replies View Related

Filter Main For Based On Subform Criteria

May 2, 2005

Hi,
I've posted this same question a few months ago, started something else and forgotten about the problem, now I really need to solve this! (I had no luck with the original post - code still not working).

I want to apply a filter to my main form based on criteria showing on a subform. This is the code I've got (applied to the OnOpen event of the main form):

Code:Private Sub Form_Open(Cancel as Integer)Dim stDocName As StringDim stLinkCriteria As String stDocName = "Campaign Form"stLinkCriteria = "[Forms]![Campaign - Last Contact Status subform]![Communication Response]=1" DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormPropertySettings, acWindowNormalEnd Sub

The main form is called Campaign Form
The subform is called Campaign - Last Contact Status subform
The field I want to filter on is Communication Response

The criteria I want to filter is : I only want to show records from the Campaign Form (main form) where the status of the Communication Response on the Campaign - Last Contact Status subform (subform) is equal to 1.

I've tried a few variations of the above code but the results are always the same - prompting to enter a paramater value. Now I'm just fishing and hoping I'll get it right.

FYI. the subform only contains one record for each record on the main form. It works a bit like this :
Customer||Date||Communication Response(1=open, 2=closed)
One||20/4||1
Two||21/4||2
Three||21/4||1
One||23/4||2

The corresponding record on the subform for customer One will show the communication response of 2 as this is the most recent date. Therefore you now have the following :
Customer||Communication Response
One||2
Two||2
Three||1

Now I want to only show those records that have a Communication Response of 1. Remember this field appears on a subform. This is the part I am having trouble coding.

I know I've probably just explained the most irrelavent details but if anyone can help me it would be greatly appreciated!!

View 1 Replies View Related

Queries :: Textbox On Subform As Query Criteria

Jun 3, 2013

I'm trying to build a query to use as a filter in the DoCmd.OpenForm function.

I have done this several times before, referencing values from forms e.g.

Point To
Field: LeadID
Table: Lead
Criteria: [Forms]![GridDisplay1]![LeadID]

This filter is used in the procedure:

Code : DoCmd.OpenForm "LeadDetails", acNormal, Point_To", , , acDialog, """"

And it works great, I click a button next to the record I want to select (records displayed on continuous form) and it opens the Lead Details form on that particular record.

However now I am trying to accomplish the same thing, except instead of a continuous form I need to draw from a continuous sub-form. The form "BrokerMgmt" contains a sub-form named "BrokerSearch." The RecordSource for the sub-form is set once the user enters data into a few textboxes on the main form and clicks the search button:

Code:
Private Sub brkSearch_Click()
Dim argCount As Integer
On Error GoTo Err_Handler
If IsNull(brkFirstName.Value) And IsNull(brkLastName.Value) And IsNull(brkCompany.Value) Then
MsgBox "You Need To Select Some Values", vbCritical, "Lead Tracking"

[Code] ....

The sub-form then displays basic info such as Name, Company, State, and the "BrokerID" which is the primary key of the "Broker" table. This "BrokerID" is displayed in "Textbox 20" so I set a button next to each result to on_click perform the procedure:

Code:
DoCmd.OpenForm "BrokerDetails", acNormal, "Point_To_Broker2", , , acDialog, """"
"Point_To_Broker2" is set up in the same style as "Point_To":
Field: BrokerID
Table: Broker
Criteria: [Forms]![BrokerSearch]![Text20]

However instead of the BrokerDetails form opening to the appropriate record I get a msg box asking for a parameter. Why is this? Even if I enter the correct BrokerID as the parameter it still gives me an error saying you can't go to the specified record.

View 11 Replies View Related

Modules & VBA :: Filter Continuous Subform With Two Criteria

Feb 11, 2014

I am trying to use this code to filter a continuous subform based on two criteria but getting a data type mismatch error.

It works fine if I filter by just one or another. Perhaps it is not possible

Code:
Dim intSpouseEntityID As Integer
intSpouseEntityID = Nz(DLookup("[EntityID]", "qryEntitiesLocations", "[ContactIDNumber] =" & Me.Spouse), 0)
If intSpouseEntityID > 0 And Not IsNull(Me.subformContactsHomeAddress.Form.EntityID) Then
MsgBox ("There are two spouse addresses please delete one and try again")
DoCmd.Save
DoCmd.OpenForm "frmContactAddresses", , , "EntityID=" & Me.txtEntityID Or "EntityID =" & intSpouseEntityID
End If

View 2 Replies View Related

Forms :: Summing In Subform Text Box With Criteria

Nov 14, 2013

I am trying to sum the values in a textbox on a subform using a textbox in that subforms footer. Currently I have the textbox's control source to be this:

=Sum(IIf([chkInvoiceSent]=True,[txtPaymentAmount],0)).

This isn't filtering the appropriate records and is instead summing everything.I want to sum payment amount only if the chkinvoicesent box is checked off. Is the proper way to do this with a Dsum? I wanted to do it with the sum because it is faster and more elegant, or so I thought!

View 3 Replies View Related

Forms :: Using Subform As Record Source Criteria

Apr 16, 2013

I am trying to get a follow-on form to reference a subform. Here is what I currently have going:

I have two (2) tables: 00 Orders and 00 Products; both forms have primary keys "Orders ID" and "Product ID" respectively.

The "Orders ID" in "00 Orders" has a one-to-many relationship to "Orders ID" in "00 Products".

The user adds the main order details to "00 Orders" Table via "01 New Order" Form, and adds the ordered products to "00 Products" Table via a "00 Products Sub Add" Subform.

The user can then update the order via "02 Update Order" Form which has a "00 Products Sub Update" Subform.

This is working perfectly!

The "00 Products Sub Update" Subform has a "Contr Details" Button for each product ordered which opens a follow-up "00 Products Sub Update Contract Details" Form to allow the user to add additional details to the "00 Products" Table. I did this to reduce the size of the main form as the additional details are not needed to be viewed as the order process is going through the stages.

In the Record Source of this "00 Products Sub Update Contract Details" Form I have the criteria for the "Product ID" set to [Forms]![00 Products Sub Update]![Product ID]. This ensures only the additional details for the selected product come up on the form.

Everything works perfectly; I can add new orders, I can update orders, and the selected product comes up when I press the "Contr Details" Button on the "00 Products Sub Update" when it is a stand-alone Form.

What the problem is, Access asks for the user to "Enter Parameter Details" for "Forms!00 Products Sub Update!Product ID" when the "Contr Details" Button is selected in the "00 Products Sub Update" when it's a subform within the mainform.

So everything works when I use "00 Products Sub Update" as a Form by itself, but Access does not seem to be seeing the "Product ID" when "00 Products Sub Update" is a subform.

View 14 Replies View Related

Filter Master Form Based On Subform Criteria

Jun 12, 2012

I have a Master form which includes tabs within the tab I have subforms. I am looking for a way to filter my master form based on data found within my sub form.

I have a check box within one of the sub-forms called "softwareInstall" if yes the box would be checked. I want to filter all customers within my main form with a check box in my main form to turn on and show all customer who has this box check within the subform SoftwareInstall=True.

View 9 Replies View Related

Queries :: CheckBox On SubForm Based On Query To Toggle Criteria

Aug 16, 2013

I have a SubForm "assignments" based on a Query, which has criteria to filter dates and also to filter 0 and 1 of the checkbox ...

The question is:

How do I put in that SubForm one or more Checkbox to "enable" and "disable", only the criteria of such query? So, toggle, for example, those jobs that are not completed (Checkbox of the query=0) and those that do ...

View 1 Replies View Related

General :: CheckBox On A SubForm Based On Query To Toggle Criteria

Aug 16, 2013

I have a SubForm "assignments" based on a Query, which has criteria to filter dates and also to filter 0 and 1 of the checkbox ...

The question is:

How do I put in that SubForm one or more Checkbox to "enable" and "disable", only the criteria of such query? So, toggle, for example, those jobs that are not completed (Checkbox of the query=0) and those that do ...

View 2 Replies View Related

How To Use Wildcard?

May 15, 2006

i've try and no luck... using Like "* *"

i'm tryin to create one that if i search for like let say "brisbane" it would show the results of the complete spelling, but let say if i was to just type "b" or "bris" it would show u a list of "B" towns or matching words of "bris".

any assit would be great

View 1 Replies View Related

Pop-Up WildCard

Jan 31, 2005

I have a field in a table that has the following data in it:

W-01-2005
W-02-2005
W-03-2005
W-04-2005
etc..

I created a query against this table and put a pop-up on this field so they can enter the search criteria needed, or leave blank for all.

I'd like to modify this pop-up code to ignore the W- and -2005. When the pop-up appears and asks them what week they want, I'd like for them just to enter 01 or 02 or 03 etc... not W-01, W-02 etc..

This is the code I have currently in this field.

Code: Like [Enter Two Digit Week] & "*"

Thanks, Leap!

View 6 Replies View Related

Forms :: Print Report Based On Subform With Multiple Search Criteria

Jun 14, 2015

I have a problem printing a Subform that uses multiple criteria(in textboxes) as filters.

The search portion of the form works fine. The problem is I have created a report based on the subform and am using the following code to open/filter the report

Code:
Private Sub PrintBtn_Click()
Dim strCriterion As String
Dim strMsg As String, strTitle As String

[Code].....

View 3 Replies View Related

Wildcard Using Mid Statement

Jul 4, 2005

Can anyone say what the syntax would be for the "incorrect" statement below?

Like *Mid([checking]![COMPANY NAME],1,InStr([checking]![COMPANY NAME]," "))*

As the statement above doesnt work, just returns errors.

Thanks in advance

View 1 Replies View Related







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