Like "*" & [What Item] & "*" - doesn't work?

[Newbwan]

Using Access 2007
So I am trying to perform a search using a query;

Like "*" & [What Item] & "*"

It searches the table "ITEM" I also have the same search under "COMPANY" and "PART NUMBER" but when I run it nothing appears. Even when the search criteria is blank I get no entries. there are 1005 separate entries that it should be searching, but it appears to be searching none. I did notice that when I messed with some settings I could get it to return entries when I typed a number in the ITEM search, but there was no numbers in any of the descriptions? I think it is searching the main index key, but why? :banghead:The table I have it under should be the DESCRIPTION of each ITEM. I hope that made sense.

 

[Brianwarnock]

Could you post your SQL so that we can see exactly what you have coded.

Brian

 

[Newbwan]

I didn't code this, this is the result of access after typing the LIKE "*"... into the criteria section of the query, I hope this helps.

SELECT Price.Stock, Price.Company, Price.Price, Price.[Part Number]
FROM Price
WHERE (((Price.Stock) Like "*" & [What Item] & "*") AND ((Price.Company) Like "*") AND ((Price.Price) Like "*") AND ((Price.[Part Number]) Like "*") AND ((Price.ID) Like "*"));

PS: I have changed the criteria for the other columns to just return any information "like "*" ( At least I think that's what I did)

 

[Brianwarnock]

Yep when you code and save in the design grid then view the SQL Access goes mad with the brackets.

That looks fine, if you get a hit on the Stock then you should select the record, if you just hit enter when asked for the parameter [what item] then you should pull all records.

If you don't then there must be something odd going on, I would take it step by step removing all but the stock test and maybe hard coding a simple Like.

Brian

 

[MSAccessRookie]

I didn't code this, this is the result of access after typing the LIKE "*"... into the criteria section of the query, I hope this helps.

SELECT Price.Stock, Price.Company, Price.Price, Price.[Part Number]
FROM Price
WHERE (((Price.Stock) Like "*" & [What Item] & "*") AND ((Price.Company) Like "*") AND ((Price.Price) Like "*") AND ((Price.[Part Number]) Like "*") AND ((Price.ID) Like "*"));

PS: I have changed the criteria for the other columns to just return any information "like "*" ( At least I think that's what I did)

Based on the change to your code, none of the statements that are marked in RED actually do anything any more, and could be removed for efficiency sake. You may ignore that advice if those statements are intended to be remediated later. The rest of the WHERE statement looks OK to me.

 

[Brianwarnock]

Access I think he knows that, it was a change to simplify things but in reality he wants to be able to query all columns

I did suggest removing them for a step by step testing regime.

Brian

 

[Newbwan]

Well, then something odd must be going on....
I do not know if it makes a difference, the Item field and Company field are being pulled from separate tables. When I type a number ( ex 8 )into the search parameter I come up with results, is it a possibility that access is searching the ID field instead of the Description field?

Break down of query for informational purposes

Query purpose - locate entry by description and give results of that entry "cost, company, FULL description."
Query is looking at a table that pulls information of ITEM from a PRODUCT table, and pulls information of COMPANY from a COMPANY table.

Anyway if I type a word into the description ( ex screw ) no results occur. I figure I messed up somewhere, but I don't know where.

PS Sorry should have mentioned that I have attempted to remove all but the one field in an attempt to make it work, the results are still 0. Even if I tell access to search right from the criteria section of the design view, I still get nothing. I can use the query search in each table individually, but I want the results from the table that matches everything up.

 

[Brianwarnock]

The only table/ query mentioned in the SQL is price, is this a query based on another query. When it runs with no criteria specified ie no Where clause, is the data as you would expect with data in all fields?.

Brian

 

[Newbwan]

Yeah the data looks right in the table. Everything matches to where it is supposed to. This query is the only thing I have other then tables. No other query's no forms, nothing. :banghead: :banghead:at the moment these smiley represent my thinking process

there are three tables

Item
Company
Price ;

Price is a mixture of Company and Item (id field and company/description field) - so each item has a company and a price.
​

I want to search for Items and have it return all companies and prices related to that search.

I am sure I messed up as this is the first time I have ever tried this, I just do not know where.

PS I do want the ability to search all columns at some point, but once I figure out why one is not working I am assuming I can apply that remedy to all.

 

[Brianwarnock]

I'm sorry I'm going to have to go and prepare my evening meal, plus I'm out tonight and all day tomorrow. We retired folk lead bust lives


But it sounds like we have a design problem here with data duplicated I would like more info regarding the tables and their relationships. I am pre 2007 otherwise I would ask to see you DB, perhaps somebody else will come on and look at this.

Brian

 

[Newbwan]

That's too bad, for me. Thanks for the help anyway... anyone else out there? Enjoy your food, and being all busy in your retirement .. some day I will be busy too.

 

[jdraw]

You say you have 3 tables
Item
Company
Price

Tell us more about thee tables - what fields they have, what are the keys, how are the tables related?

What exactly would be your result from your query?

Many times posters mix up AND and OR, AND means all fields must match, OR implies any field with a match is a "hit".

 

[Newbwan]

Company : Table

Fields
Id (KEY)- Company - Phone - Fax - Contact - Location - Shipping -Manager - Regional Sales

​

Item : Table

Fields
Id (KEY)- Description
​

Price : Table

Fields
Id (KEY)- Company (see below) - Item (see below) - Part # - Price
​

The PRICE:TABLE pulls the COMPANY:FIELD from COMPANY:TABLES-FIELD:ID and FIELD:COMPANY

The PRICE:TABLE pulls the ITEM:FIELD from ITEM:TABLE-FIELD:ID and FIELDESCRIPTION

The purpose of the query is to search in the PRICE:TABLE and have the related information displayed.

For example:

EX1
Want to know where to get a "blue towel" but can not remember the color? Search under ITEM for "towel" the query will then return all entries with "towel" and all related information - which company has it, what price it is and what is the part number.

EX2
Need to know what company offers what? Search for "Bob's cloth" under COMPANY and the query will return all items,price and part numbers that match "Bob's Product's"

I hope that made sense. I am almost sure no that I did something wrong in defining the relationship's. When I do search for a number I get results. I think the query is searching the ID fields in the related tables (COMPANY:TABLE and ITEM:TABLE) but I am unable to get it to point elsewhere and match all the fields up to give a whole picture of the product.

 

[jdraw]

Where does Part # fit??
(And you shouldn't use special characters # in your field and table names) Use only alphanumerics and _


It seems you have this set up:
Company--->CompanySuppliesProduct<------Products

In the Products table you could have the current selling price.

In the CompanySuppliesProduct
you would have the CompanyId and ProductId (make these a unique compound index). I would have an autonumber PK such as CompanySuppliesProductID . In this table you would also record the AgreedToSellingPrice. This could could account for sales, customer loyalty program , discount etc. If you only have Price in the Products Table, you could get into issues for reporting/querying past Sales.

(I know you haven't mentioned Sales but I have seen similar situations that grow into Sales/Orders etc)

 

[Newbwan]

Thanks but this is not a sales database, it is a purchasing database. The price is different for each company on similar parts. (And is updated once every two months)
ex.
Item - Company - Price - Part Number
Blue Towel - Bobs Clothes - 1.50 - 15698bl
Blue Towel - Lisa's - 1.29 - bltwl12

The part number works in as each company has a different system to order from, therefore a different part number. In some cases a different description but once I figure out what I have done wrong here then I will expand. I think I am going to link this to a thread in design, as It is looking more like I messed up... Unless I missed what you where trying to tell me?

It seems you have this set up:
Company--->CompanySuppliesProduct<------Products

Kinda but I would label it more (if I understand what you have done)
Company ---->CompanyItemsPartnumberProduct<----Product
​

 

[jdraw]

I'm not following

each company has a different system to order from, therefore a different part number

which to me says
Each Company Orders Parts. I don't see an Order or PurchaseOrder in your set up.

From a design view I see tables for

Company, Order and Part. Traditionally this would be

Company/Customer--->Order---->OrderItem<---Product

see this link for a more detailed model

http://www.databaseanswers.org/data_models/customers_and_orders/index.htm

Are you familiar with Normalization?
http://www.phlonx.com/resources/nf3/

 

[Newbwan]

Slow down my friend, My brains about to explode. I am not selling anything. This entire database is supposed to list where to buy. I am the customer here. What I mean is that for every part there are multiple companies to purchase from. Each company has its own price and part number for each part. - so NO customer only suppliers. Am I making sense?

Think of it like this. Lets say I build cars. On the car there are lug nuts and wheels. I can buy lug nuts from five different sources, each sources having a different price. Same thing goes for wheels. I do not resell these parts, they are placed on to a finished product that is sold later, not relevant to this database ( I know it should be, and can be, but it is not. Due to reasons beyond my control).

I have read some for dummies books on HTML that touched SQL and Database design, and have read what I can find on the internet. I will check out the links you gave me, I also have a for dummies access 2007 that I have just started reading. I hope my answer reveal itself soon.

I took a read on the normalization, that link was more informative then I have found thus far, I understand a little better and for that I gave you thanks.

 

[jdraw]

Am I making sense?

Yes, and this is somewhat typical. You know your environment and your business. We don't. We only know what you tell us. And we don't know your background/expertise. We try to "make the best guess", but it's only a guess at best. So, there is lots of room for miscommunication/misunderstanding on all sides.

In post 17 you identified yourself as the Customer. You Purchase Parts from different Suppliers. These different Suppliers have different identification schemes for the Parts.

So between Suppliers and Parts you have a Many to Many relationship.
To resolve this you need a junction table. Do you ever negotiate the Prices of the parts you buy? Or do you simply pay what the Supplier asks; or do you get "sales deals" where the price you pay is not the same as the Suppliers published Price?

Suppliers--->SuppliedParts<-----Parts

So without a standard product codification system, you rely on a description, or perhaps a previous purchase to identify parts--is that correct?
What document, if any, do you complete to purchase a Part from a Supplier?
Do you only buy 1 part at a time from a Supplier?

 

[gemma-the-husky]

with regard to your problem - why dont you strip out the like statements, then add them back one at a time.

maybe the 3 like statements are not working together in the way you think they should be.

 

[Brianwarnock]

Dave he has tried that and what he says in post 7 is interesting, is he using table lookups and if so do they impact the way criteria work? I have never used table lookups and know nothing about working with them.

Brian