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

RE post #5, the sql breaks down to this , as I understand things

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

The additional Like "*" phrases do nothing.
 
I think Brian understands what my issue is, but seems to be at a loss like myself.
I hope this post can clarify things for you guys.

1. Yes the way to identify items is using the description, I have used the auto number as the primary key.
2. I have systematically stripped all like statements from the query. The resulting search is zero when searching the fields that are derived from the other tables. When the search is done to a field that is native to the table the search works as expected.
2a. I have tried to link the query to other tables inside the query design, but still get no results EX the company field points to the company:table instead of the item:table
3. I am using look-ups to link the data together. each cell is a drop down menu under the fields ITEM and COMPANY. Is this wrong? I am thinking so as the query is not working.

4. I started a post under tables -as I thought this thread may actually be in the wrong spot - http://www.access-programmers.co.uk/forums/showthread.php?t=241888
plog asked for sample data - I have included it below.

Company:Table
Id (KEY),Company,Phone,Contact
1,Gregg's Lugs,895.965.1258,Lisa
2,Bobs Wheel,895.965.1856,Sam

Item:Table
Id (KEY),Description
3,5/8" Chrome Plated Lug
4,1/2" No Plate Lug
5,5/8" No Plate Lug

Price:Table
Id (KEY),Company,Item,Part Number,Price
6,Gregg's lugs,5/8" Chrome Plated Lug,5/8crmpl,$.50
7,Gregg's lugs,1/2" No Plate Lug,1/2plain,$.20
8,Gregg's lugs,5/8" No Plate Lug,5/8plain,$.35
9,Bobs Wheel,5/8" Chrome Plated Lug,5/8crmpl,$1.25
10,Bobs Wheel,1/2" No Plate Lug,1/2plain,$.89
11,Bobs Wheel,5/8" No Plate Lug,5/8plain,$1.01

I just realized as I typed this out that under the fields Company,Item (IN PRICE:TABLE) I told access to also link the ID fields, is that correct?
 
jdraw - and anyone else that wants clarification -

:D - You got it
:rolleyes: - I think I got that
:confused: - Possible problem

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

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

Suppliers--->SuppliedParts<-----Parts -- Like above I thought that I did this?

So without a standard product codification system, you rely on a description, or perhaps a previous purchase to identify parts is that correct?--yes (image mac level reached - so insert big smile face here)
What document, if any, do you complete to purchase a Part from a Supplier? ----Excel file
Do you only buy 1 part at a time from a Supplier?-----I wish, some places get upwards of a thirty item PO, others get one Item a time.

Purpose of DATABASE
I would like to have a system in place that allows searching of all items that we use, from parts to sundries. - reason being is that one item can have five or more places that it can be purchased from, usally the lowest cost wins, but sometimes time is more important then cost so I need to be able to see all places that can supply - otherwise I already have this set up in excel... but it takes a few seconds to think it out. It would be nice to incorporate a PO system and eventually a costing system, whereas access can track the finished product and what items have gone into its production, but that is not for now. and since I am leaving my current position probably never. I want to have the search ability so a new person can skip the months learning curve of where to buy what and focus on getting the what for less.

Like always I hope this helps - I really want to be able to search ITEM, and have all related information returned.
 
Are you using Table Lookups in price table for Item to description in Item table. ?

Brian
 
Brian -
I think yes.
The fields ITEM and COMPANY in Price:Table are a look-up to DESCRIPTION in Item:Table and COMPANY in Company:Table.
 
I could not see a Foreign Key mentioned anywhere. If I am right how are these tables linked.

It appears that Lookups are being used in the tables. They must be removed.
 
Then you cannot use Like , in fact any search needs to look for the primary key as that is what the field contains if my simple tests are correct, I had never used Table Lookups until researching this problem.

The field contains the primary key of the record for the data but displays the data. Presumably this is one of the reasons experts say do not use Table Lookups and why I thought there was a design issue before I went AWOL.

Interestingly nobody else mentioned this so I began to think I was wrong, but I think that you need a redesign , not my strong point after & years retired.

Brian
 
I see.. it is not a query problem but a straight up design problem. Got it.. not sure about fixing it, but as rain lover mentioned there is no foreign key. So in short table look-up is bad bad bad... at least the like criteria.

Thanks
 
Newbwan said:
I see.. it is not a query problem but a straight up design problem. Got it.. not sure about fixing it, but as rain lover mentioned there is no foreign key. So in short table look-up is bad bad bad... at least the like criteria.

Thanks
Click to expand...

Actually skip the
at least the like criteria.
Click to expand...
the experts say they have no good or redeeming features.

Brian
 
That's good to know. Look-up not good to use under any circumstances.
 
this thread isnt working properly for me.


Anyway, I hadn't realised there was a lookup

the table actually stores the lookup ID - the field type will be long integer, no doubt. It's just that you "see" the looked up value.

make your query include the base table, and the lookup table, and put your search in the reference field from the lookup table field

the other way is to use a combo box to pick the product id you want, and then jump to it directly. needs a bit of code, though.
 
Newbwan said:
That's good to know. Look-up not good to use under any circumstances.
Click to expand...

Not quite True.

When you use a Lookup you usually use the Primary Key and another Field like Name or some other description. The Primary key is usually hidden but it is the field you use to find the data.

So if you search for Colour "RED" it may have a primary Key of Number 34748. You need to match the Number not the actual Colour.

Best thing to do is NOT to use LookUps at the TABLE level. However at the FORM level the LookUp is a great tool.

Using a Combo Box that shows the Colours and has the PK hidden here is a sample piece of search Code.

Code: 
Private Sub CboSearch_AfterUpdate()
    
   ' Find the record that matches the control.
    Dim rs As Object
 
 ' Save Record before running this Function
    Me.Dirty = False
    
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ColourPK] = " & Str(Nz(Me![cboSearch], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    Me.cboSearch = Null
  
End Sub

Have a Look at this Link.

http://access.mvps.org/access/lookupfields.htm

Hope this helps.

BTW. To search multiple fields requires more complex coding but at least this is a start.
 
Best thing to do is NOT to use LookUps at the TABLE level. However at the FORM level the LookUp is a great tool.
Click to expand...

We were only ever discussing table lookups, but I guess a bit of clarification doesn't hurt.

Brian
 
You must log in or register to reply here.

Similar threads

J
Form Not Showing All Records From Table
Replies
4
Views
473
Gasman
Gasman
H
Problem with Search Boxes
2
Replies
24
Views
1,410
Pat Hartman
P
A
Simple warehouse data entry
Replies
12
Views
675
arnelgp
arnelgp
H
Solved Search across multiple fields
Replies
8
Views
865
Harry Paraskeva
H
D
Table Search and Total
Replies
18
Views
805
DDJKSA
D

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom