Query part of a field

[Oldsoftboss]

Problem: Boss would like a telephone type index thingy that he can enter suppliers details.

Foreseen problem: He would like a field where he can put the type of equipment the supplier has, ie some suppliers can supply fans, motors, belts and another supplier may supply belts, bearings, grease etc.

If you place all the types of supplies in one field, can you call a query on part of the field, ie say 'belts' from above would give both suppliers but motors would return only one?

 

[DW]

http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=106489#post106489


The download may help.

Dave

 

[Oldsoftboss]

Thank for the prompt reply. I am interested in using a wildcard search. Have created a query and when I put - like "belts*" - in the criteria of the query I get all the Suppliers with belts and anything listed after it.

When I put - like "*belts*" - in the criteria of the query I get all the Suppliers with belts and anything listed either before or after it.

Teriffic. Thats how I want it to work.

Problem: What syntax should I use in the query to refer the wildcard search to a text box on my form. I have tried
Like*(Forms!Suppliers!Search)*
Like (*(Forms!Suppliers!Search)*)
and many other combinations, without success returning any results.

Any help would be appreciated.

Dave.

 

[DW]

Sounds to me you need to manipulate the query criteria.

Can you put a demo together and I'll have a look for you.

I'm here for only a couple of hrs.

Dave

 

[Oldsoftboss]

Fantactic, Hope you can help.

 

[Oldsoftboss]

Tried this and it seemed to work (After reading vauge and unrelated help files)

in the criteria - Like "*" & [forms]![Address]![Search] & "*"

 

[DW]

Well done if you've sorted it, but see attachment, it should do what you want.

One thought though.

Your fields contains data like:-

Tin,Plastic
Wood,Metal

I would really look to change this for fear of causing problems later on.
I feel one item for each field is better.

Tin
Plastic
Wood
Metal

Check my table, you'll see I made some additions.

Good Luck Dave

 

[Oldsoftboss]

Thank for your work Dave. The way you've done the search is similar to a search options database I downloaded some time ago.
The reason for having several names in the one field is that my boss is a computer scatterbrain - just put it anywhere and of course it will be in the exact spot is should be !!
I thought having one field - PUT IT IN THERE YOU IDIOT would eliminate stuff being entered here there and averywhere.
I've attached the DB for you to have a look at to see where I'm up to. Seems to work OK. I've deleted two forms from this database to make it small enough to zip and send.

On the form you will see 'Double Click to view details'. This refers to the record selector, the small square adjacent to the record, on the left, to view the details.

 

[Oldsoftboss]

Wasn't quick enough. Have deleted 2 forms from this DB as it was too big to zip end Email !