Filter Query on user input 1

[barra47]

I have a table with a field vehicle_no

this field contains numbers of rail cars
but the field can have more than one number eg
1. 1872
2. 1872 1873
3. A961
4. 1873 A961 ARL293
etc

I want a user to enter a number eg 1872 and the results to show all records with 1872 in it even if there is another number in that field.

result should be
1. 1872
2. 1872 1873

I tried this
SELECT [Open Quote Page].[Vehicle Number]
FROM [Open Quote Page]
WHERE ((([Open Quote Page].[Vehicle Number]) Like [enter No]));
but the results only gave me record 1

1. 1872

Can you please tell me where Im going wrong and what is the solution

Thanks in advance

 

[lespaul]

LIKE requires *:

LIKE AN* - returns all records that field STARTS with AN
LIKE *AN - returns all records that field ENDS with AN
LIKE *AN* - returns all records where AN appears in the field

you will need to enclose your parameter prompts with * in order for this to work.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[barra47]

I tried your suggestion but as soon as i tried to run it it came up with a syntax error


SELECT [Open Quote Page].[Vehicle Number]
FROM [Open Quote Page]
WHERE ((([Open Quote Page].[Vehicle Number]) Like *[enter No]*));

 

[PHV]

SELECT [Vehicle Number]
FROM [Open Quote Page]
WHERE [Vehicle Number] Like '*' & [enter No] & '*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[barra47]

If I write it like this I get the correct answer, but then it is not a user input query

SELECT [Open Quote Page].[Vehicle Number]
FROM [Open Quote Page]
WHERE ((([Open Quote Page].[Vehicle Number]) Like "*1872*"));

it is when I use the [enter vehicle No ] prompt with * that it wont work

 

[barra47]

Excelent
Thank you PHV that worked just fine
I can now finish my form

thanks again and thanks for your input also lesli