Hi
Im trying to create a query that uses a combo box as the filter criteria. However the combo box needs to use wildcards, which i would have thought would have been *[forms]![control]![combo1]*. However this doesnt seem to work, anyone know what im missing?
Cheers
Michael
So I've got a form set up, and it uses a combo box to find the name of a persons record to populate the form. Simple enough stuff, the wizard takes you through it. It works fine.
However the people that use the database have kinda thrown me a curveball by asking if the search function can search any part of the name. For example, you've got a John Smith. If you enter Smith into the combo box, it won't find the record because it's the second name, you have to type in John.
Is there a way to use wildcards in the combobox so you can type in first or last names and get the same normal combo box effect?
hi guys.. i want to include a search form in my database.. here is my current SQL statement:
SELECT tblCompany.CompanyIDPK, tblCompany.CompanyName, tblCompany.CompanyAddress, tblCompany.ContactPerson, tblCompany.ContactPosition, tblCompany.OfficeNo, tblCompany.MobileNo, tblCompany.FaxNo, tblCompany.EmailAdd
FROM tblCompany
WHERE (((tblCompany.CompanyName)=[Forms]![frmSearchClient]![txtSearchFor]));
but this statement only returns the exact name of the company... if the name of the company in the database is ABC company and you searched for "ABC", it will return zero results... so how can i improve on this? that even if they enter only "ABC", it will return a result.
thanks a lot and good day...
Hi all
I'm making a student report card. Teachers enter student data via a bound form. I have a text field that teachers enter their class which brings up student names. They fill oput the form accordingly.
I have 6 classes of grade 7. 7A, 7B, 7C and so on.
If a teacher enters something weird like (7 (space) A) the query bound to the form will return nill results because it's looking for something that doesn't exist. I need an error message to appear which says no records will be returned because you haven't entered the correct parameter value. (Because they have entered 7spaceA rather than just 7A.
I could just use a combo and this would solve all my problems. But it's not always practical. I was thinking I could use a wild card where if the first character was a number then at least some values would be returned. There is method to my madness... I actually want to apply the same code elsewhere when running reports.
Is there a way I can enforce some kind of input mask in an event proceedure that can do this??
I appreciate any advice.
Hi,
I have data of type "number" shown in the drop-down combo-box in a form. It has some codes in the event of "After Update".
My problem is that i can't type in something like 13* in the combo-box in order to get anything starts from 13. It says "The value you entered isn't valid".
The reason is that i want user to be able to use the wildcard. I know that i can do this with text. I'm not allowed to change the structure of the table in the database.
This combo-box shows IDs from one of the exisiting tables. That IDs is of type "autonumber" in the table.
Is there a better way of doing this?
Thank you in advance
I've got a query that is defeating me, but that might be due to a week's worth of insomnia related sleep problems. My issue exists in the following query, of the four IIF statments in the HAVING portion of the query and the need to select all if the statement is false for each one of the four queries, the way it is now partly works by returning all results where none of the values are null, I need to get null results as well. I've tried not including the false action for them, a wildcard to select all and just can't get around this problem. Any help appreciated.
SELECT DISTINCT vw_mdb_SalesSummary.Channel, vw_mdb_SalesSummary.Store_Number,
[AOI Category groups].ProductType AS Category, dbo_Contact.Source_of_Inquiry,
dbo_Company.Rn_Descriptor AS Store_Name, vw_mdb_SalesSummary.Job_Number,
Sum(vw_mdb_SalesSummary.Extended_Price) AS Sales1, vw_mdb_SalesSummary.Designer_Code,
vw_mdb_SalesSummary.Manager_Code, 0 AS Sales2
FROM ((dbo_Company RIGHT JOIN vw_mdb_SalesSummary ON dbo_Company.Store_Number = vw_mdb_SalesSummary.Store_Number)
LEFT JOIN dbo_Contact ON vw_mdb_SalesSummary.Job_Number = dbo_Contact.Job_Number)
LEFT JOIN [AOI Category groups] ON vw_mdb_SalesSummary.Category = [AOI Category groups].SalesCategory
WHERE (((vw_mdb_SalesSummary.Order_Date) Between [Forms]![frmLandS]![currentStart] And [Forms]![frmLandS]![currentEnd]))
GROUP BY vw_mdb_SalesSummary.Channel, vw_mdb_SalesSummary.Store_Number, [AOI Category groups].ProductType,
dbo_Contact.Source_of_Inquiry, dbo_Company.Rn_Descriptor, vw_mdb_SalesSummary.Job_Number,
vw_mdb_SalesSummary.Designer_Code, vw_mdb_SalesSummary.Manager_Code, 0
HAVING (((vw_mdb_SalesSummary.Channel)=IIf([Forms]![frmLandS]![Channel]<>"",[Forms]![frmLandS]![Channel],[Channel]))
AND ((vw_mdb_SalesSummary.Store_Number)=IIf([Forms]![frmLandS]![Store]<>"",[Forms]![frmLandS]![Store],[vw_mdb_SalesSummary]![Store_Number]))
AND (([AOI Category groups].ProductType)=IIf([Forms]![frmLandS]![AOI]<>"",[Forms]![frmLandS]![AOI],[ProductType]))
AND ((dbo_Contact.Source_of_Inquiry)=IIf([Forms]![frmLandS]![source]<>"",[Forms]![frmLandS]![source],[Source_of_Inquiry])));
I am trying to get the OrderBy to recognize wildcards (or to that effect). A scenario would be I would have more than one name in a field, EX: Name2, 4, 6. How do I use wildcards in the OrderBy and they are recognized.
ORDER BY (Table.Field)="Name1", (Table.Field)="Name2", (Table.Field)="Name3", (Table.Field)="Name4", (Table.Field)="Name5", (Table.Field)="Name6", (Table.Field)="Name7", (Table.Field)="Name8";
I have a table called Coded_Data with one particular field called Codes. This field list a number and a respective code to identify what type of code it is. For instance 123456789-AD...123456789-ADS. I am wanting to create to two columns. One listing all work orders coded with AD and the other withADS. When I use the Like condition with % it returns both AD and ADS in same column. I understand why because the "%AD" returns anything that starts with AD so this is why I get both codes. I only want to get the the AD codes in one column and ADS in the other. I have duplicated the table twice since I am trying to retrieve data from the same field and table but display in 2 separate columns. The duplicated table has a different alias. The problem is in the where clause.
where (((coded_data.codes) LIKE "%AD")) or ((coded_data_1.codes) LIKE "%ADS");
I have a query that has a criteria from a field on a form...
[forms]![frmSearchCriteria]![Company_Name]
I would like to put wild cards * on eitherside that it uses will bring up with anything with what is written into the field.
I have tried putting wild cards on the form field and on the criteria of the query. Any Ideas? thanks
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...
I have a Qry that has a criteria of
Like "*" & [Forms]![FrmSearch![Cmbo1]
However on this field there are null field entries and even if the cmbo box is null it wont display these results.
I tood out the & [Forms]![FrmSearch![Cmbo1] section and just left the wild card and again only displayed the records where this field had an entry.
How can i get it so that when no selcetion has been made in the combo box all the records show up.
Many Thanks
I have created a simple Unbound search form based on a 'Bookings' table. There are 4 comboboxes that the user can choose to search on, 'Client_ID', 'BookingType', 'FundingArea' and 'ChargeTo'. I have created a query that uses the following criteria;
Like "*" & [Forms].[frmLBT_db_Tool].[cmbName] & "*"
Like "*" & [Forms].[frmLBT_db_Tool].[cmbBookingType] & "*"
Like "*" & [Forms].[frmLBT_db_Tool].[cmbFundingArea] & "*"
Like "*" & [Forms].[frmLBT_db_Tool].[cmbChargeTo] & "*"
Using this criteria any of the comboboxes can be left blank and the results will display the chosen combobox used, or if 2 comboboxes were used the results would be further filtered.
The problem I have is that if a name was chosen (cmbName - Client_ID) and the Client_ID was 2, then all Client_ID records which have a 2 in them are returned, 2, 12, 20-29,32,42,52 etc...
Is there a way to overcome this, or am I going about it all wrong?
Is it possible to update vlues in a table using wildcards. I want to use a query to search for certain records that meet a certain criteria and modify these values like changing particular dates in the date field to new values.
View 1 Replies View RelatedI have a table with a string field for a street. It might have data such as:
Bleeker Street
or Bleeker St.
I want to create a query that will enable the user to search for a string by entering a parameter. Is there a way to construct a query using wildcards for a string so that if the user types in "Bleeker" both the above values will be returned?
Thanks.
Been trying to use wildcards in fields while Filter By Form instanced in Access 2007.
LIKE, BETWEEN, IN or */% not available?
The form is based on a query. I am guessing this is why the drop downs only show IS NULL or IS NOT NULL as choices instead of the entire unique list existing in the underlying table/query.
I should find the position of a number in a sentence and later extracted. The number is attached always to an "E". ie: E1, E2, E3,E4. The range of the number is 1-4. I have this until now:
X: InStr("E[#]",[Col1_Comment_1])
but it always return 0
I have a Excel spreadsheet with an embedded query that pulls from an Access table. The users use a spreadsheet with an embedded query to search on a field called "Circuit ID", to bring up results showing the history of a given circuit id. Currently they have to enter an exact match, for example if the circuit id value is DHECHUIOY, they need to enter this exact value DHECHUIOY. They want instead to be able to enter DHEC and a wildcard character to bring back every value that begins with DHEC. There doesn't seem to be a way to do this in a spreadsheet with an embedded query.
I finally decided that it would be easier to create a form in Access
How I can create a form which allows users to search on a value using wildcard characters ? (the user enters the wildcard character).
I'm on Access 2012, Windows Vista
How do you search for any number of numeric characters using wildcards? The # symbol only searches for 1, and * obviously includes letters.
Example:
I have:
D1234
D3
D5336767
D123F
My search should only retrieve the first 3 values.
WHERE FieldName LIKE 'D[0-99999999]' does not seem to work.
I have a list of consumables;
Syringe 50ml
Syringe 20ml
Syringe 5ml
Syringe Cap
White Needle
I want to remove only the number and the ml part from the list, so I would end up with;
Syringe
Syringe
Syringe
Syringe Cap
White Needle
If I use
PHP Code:
Replace([DrugNameVial],"50ml","")
I get the desired result for the 50ml syringe size.
I have tried every possible combination of "**ml", "##ml", "Like [0-9]ml all with no success.
How this can be resolved without having to individual enter each syringe size "5ml", "20ml" etc
I can't even just take the text from the right till the first space as this would lead to problems with other consumables in the list.
I have several comboboxes (6) on my form.How to populate these comboboxes with values depending on selected value in previous combobox.
Example.Lets say that you select value "Audi" in combobox 1, then available values in combobox 2 should be "A4","A6","TT" etc. and if you selected "BMW" in combobox 1, then available values in combobox 2 should be "3-series", "5-series" etc...
I have a form that currently uses a "catch all" table for listing available equipment to choose from for an equipment field. I call it tblEquipment. What I want to do is to make it so when I type a name in (1 of 35) in one field of the current record, the record source for the equipment field immediately looks at a different table that has equipment available only for that name. To do this I plan on making 35 different tables with limited data originally found in tblEquipment. I would call these tblEquipment1, tblEquipment2, etc. I do not use a sub form, nor do I want to.
So my questions are:
1) can this be done
2)If it can be done, how can I do it?
Hi everyone,
I have two combo boxes on the same form bound to a table. I want the contents of the next combo box to change based on the previous combo selection e.g
cboContinent cboCountry
Africa Zambia
Africa Congo
Africa South africa
Europe England
Europe Holland
If I choose Africa in cboContinent, I want to see only Zambia, South Africa and Congo under cboCountry and if I choose Europe I want to see only England and Holland
Thanks
Humphrey
I have a Suppliers database which contains a form that will allow me to place orders with Suppliers.The Main form has a combo box that allows me to select the supplier. The combo box is called SupplierID with the following:
Row source: SELECT Suppliers.SupplierID, Suppliers.CompanyName FROM Suppliers ORDER BY Suppliers.CompanyName;
The subform is called Stock Subform witha combo box called ProductID with the following:
Row source: SELECT DISTINCT Products.ProductID, Products.ProductName, Suppliers.CompanyName, Products.Discontinued FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID=Products.SupplierID WHERE (((Products.Discontinued)=0)) ORDER BY Products.ProductName;
Event Procedure - AfterUpdate: Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate
Dim strFilter As String
' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID
[code]..
The Link fields are done on the Purchase Order ID (PONoID).What I want to achieve is to select the supplier from the combo box (SupplierID) on the main form and then the combo box (ProductID) on the subform to filter to only show products directly supplied by the Supplier selected on the Main Form.
I'm looking for a way to have a text box auto fill based on the selection of a combo box on the same form. I cannot use the method i find all over the internet of using multiple columns in the combo box and basing the text box on that because the combo box already has multiple columns being used to determine its own possibilities and other combo box possibilities.
I would really just like the text box to work like this, but im still kinda inexperienced in VBA...
If combo box is "F004-001", then text box is "237"
If combo box is "F004-003", then text box is "280"
I know how to add in an "after update" thing, but i do not know how to do If/then statements.
I'm have quite a difficult time getting a form in Access 2010 to perform the way I would like it to. I have multiple tables that I've created, and a query that contains the data from the various tables. I then ran the form wizard to select the data from the query that I wanted to populate in my form and I've added 2 combo boxes.
What I want to do:
1. I want users to be able to select a category in combo box #1 (example: "Bag")
2. I want users to be able to select a detail in combo box #2 based on the category they selected in combo box #1 (example: Combo box #1, "bag" would populate the following selections for combo box #2: "sandwich" and "tool")
3. I want users to then receive a list of suppliers that provide the product they have selected, either "Bag: Sandwich" or "Bag: Tool"
I have combo box #1 populating a list of categories already. However, I am not able to get combo box #2 to provide choices based on the selection chosen for combo box #1.
Let's assume we have 3 tables:
Order_Category (Order_Category_ID, Order_Type_Name) with 2 records:
1, Minor
2, Major
Order_Type (Order_Category_ID, Order_Type) with 4 records:
1, Book
1, Pencil
2, Car
2, House
Orders (Order_Category_ID, Order_Type, value) with 2 records:
1, NULL, NULL
1, NULL, NULL
2, NULL, NULL
I want to create a Multiple Items form presenting Orders table with two Combo Boxes:
1. A combo box to select Order_Category_ID.
2. A combo box to select Order_Type. When 1 (Minor) is chosen in the first combo box it should show Book and Pencil, when 2 (Major) is chosen it should show Car and House.
Examples in the Internet show how to do it on a 'single row' forms using the RowSource property. I tried to use a query like:
SELECT Order_Type
FROM Order_Type
INNER JOIN Orders ON Order_Type.Order_Category_ID = Orders.Order_Category_ID
WHERE Order_Category_ID = [comboBoxOrderCategoryID]
But it sets same values for all records in the Multiple Items form and it should return different values in each rows based on value in the first combo box (Order_Category_ID).