Please Help

[richh779]

Hi I’m new to access and I need to produce a simple querie but it’s proving harder than expected.

I have a customer list which I need to create a report from by searching the first two letters of the postcode. I’ve read several books on it but none of the formulae seam to work. Any help would be appreciated as I’m well and truly stumped.

 

[RuralGuy]

Welcome to AWF and look at Left([YourField],2) = "aa"

 

[SimonB1978]

Hi,

Your query should look something like this:

SELECT field1, field2, ...
FROM your_table
WHERE Left(postcode_field, 2) = 'what_you_want'

or

SELECT field1, field2, ...
FROM your_table
WHERE postcode_field LIKE 'XX*'

Is that what you need?

Simon B.

 

[richh779]

Hi and thanks. That works fine but what I need to be able to do is enter the search prefix manually rather than the user having to create a querie for each search. Is this doable?

 

[MSAccessRookie]

Hi and thanks. That works fine but what I need to be able to do is enter the search prefix manually rather than the user having to create a querie for each search. Is this doable?

In that case, (using Simon's examples) you would want something like:

Hi,
 
Your query should look something like this:
 
SELECT field1, field2, ... 
FROM your_table 
WHERE Left(postcode_field, 2) = [Enter Postal Code]
 
or 
 
SELECT field1, field2, ... 
FROM your_table 
WHERE postcode_field LIKE '[Enter Postal Code]*'

 

[richh779]

Thanks again but that still isn’t working, I have a column in the table for postcodes which I’ve listed in the queries table along with all the other address info. In the PostCode criteria row I’ve used the following formula but none seem to work [postcode] like “[enter]*” , Left("PostCode",2)=[Enter] , Left([PostCode],2) = "enter".

I need to enter this so we can pull up individual areas ie CM, YO ect. But for some reason I’m unable to get this to work.

Cheers for all your help
Richard

 

[SimonB1978]

Hi,

If you have it in a query try: [postcode] like [enter post code] & '*'

You should always be using ' instead of " in SQL, for portability.

Simon B.

 

[richh779]

Cheers for all your help, it's working now.
Thanks
Richard