A Select Query question

[aexley]

I'm trying to create a query that selects records based on matching the first one or two letters from a postcode field to any one of 50 area codes ( representing the sales guys area).

My problem is this:

1) Can I do this in the criteria section of an ordinary query? I'm not familiar with SQL statements having managed to avoid them by using queries.

I know how to select the appropriate letters from the postcode field (using the LEFT function) but how do I do the matching?

Thanks in advance for your help.

QFTD: "Burn your bridges if you must, but there's never any harm in carrying an inflatable dinghy." - Me

 

[Ed2020]

Have a look at the "Like" operator in Help.

e.g. - SELECT tblAddresses.* FROM tblAddresses WHERE (((tblAddresses.PostCode) Like "B90*&quot);

HTH,

Ed Metcalfe.

 

[aexley]

Thanks, the 'Like' operator has definitley progressed this.

This is what I have so far:

WHERE (((Left([All Contacts]![Postcode],2)) Like ([Postcodes].[Postcode])))

Unfortunately, this doesn't return any records at all although there are 70 records to choose from.

Can u see anything wrong with the above?

This is just the crucial part of the query I can put the rest on if you like.

Thanks

 

[Doina]

You are missing the "*" from the LIKE operator:

WHERE (((Left([All Contacts]![Postcode],2)) Like ([Postcodes].[Postcode])))

Doina

 

[Ed2020]

Try:

WHERE (((Left([All Contacts]![Postcode],2)) Like ([Postcodes].[Postcode] & "*&quot))

HTH,

Ed Metcalfe.

 

[CosmoKramer]

If I'm reading this right, it looks like the WHERE statement should be reversed:

WHERE ((Postcodes.Postcode) Like (Left([All Contacts]![Postcode],2) & "*");

 

[aexley]

Wow, lots of responses, thanks.

My total query now stands like this:

SELECT [All Contacts].Postcode, [All Sites].ID, [All Quotes].

, [All Contacts].[NEXT CALL DATE]
FROM Postcodes, ([All Sites] INNER JOIN [All Contacts] ON [All Sites].ID = [All Contacts].ID) INNER JOIN [All Quotes] ON [All Sites].ID = [All Quotes].[Site ID]
WHERE ((([All Contacts].Postcode) Is Not Null) AND (([All Quotes].

)=&quot;Submitted&quot AND (([All Contacts].[NEXT CALL DATE])<=Date()+7) AND ((Left([All Contacts]![Postcode],2)) Like ([Postcodes].[Postcode] & &quot;*&quot))
WITH OWNERACCESS OPTION;

This works like a charm. Many thanks.

Just one more thing if you would be so kind

Life never being simple, some of my area codes are only one letter long, for instance 'N' is Inner London North. The query picks these up fine but it is also picking up NN codes and NE codes which are outside of the designated areas and not in my Postcode table.

I suppose the problem is 'If the postcode has only one letter at the beginning only process that letter but if it has two process both'

I fear that this may becoming quite complex but any more assistance you could provide but would be much appreciated.

Many thanks in advance.