[RESOLVED] Uk postcodes poor structure
Hi guys,
I am wondering how i address this problem i am trying to search a table of uk postcodes the problem is simply the table contains part postcodes and i am trying to search with full postcode I.e
Table contents:
IG1
IG10
N1
SE1
SE10
SE16EJ
SE16FJ
but the postcode being typed in is complete
Typed by user:
IG1 3GH
IG10 3PG
N1 2NN
SE1 2BN
SE103JC
These are just examples I am awear of the LIKE sql but this works in reverse to what i am trying to achieve........ I think.
Any direction would be great.
Re: Uk postcodes poor structure
the LIKE can work any side of the equation, you just have to structure it right.
Assumptions: @postalCode is the parameter passed in sPostalCode is the field in the database....
Code:
WHERE @postalCode LIKE (sPostalCode + '%') --- for a starts with
WHERE @postalCode LIKE ('%' + sPostalCode + '%') --- for a contains
WHERE @postalCode LIKE ('%' + sPostalCode) --- for a ends with
-tg
Re: Uk postcodes poor structure
Hi,
Thanks for the reply forgive my ignorance but that code looks almost the same as mine and seems like it is doing exacly what my sql statement does. The "@" symbol is the main difference is this correct because when i added it to my string i got an error. this is my SQL string.
Code:
StrSql = "SELECT * FROM Postcodes WHERE Postcode LIKE '%" + ActiveSheet.Range("E19").Value + "%'"
rs.Open StrSql, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
Please could you clarify?
Re: Uk postcodes poor structure
Almost.... need to reverse it from what you had.
Try this:
Code:
StrSql = "SELECT * FROM Postcodes WHERE '" & ActiveSheet.Range("E19").Value & "' LIKE(Postcode + '%')"
You didn't specify the database or the language platform, so I assumed VB and SQL Server - the @ is used to denote a parmeter in SQL Server...
-tg
Re: Uk postcodes poor structure
Sorry i was very silly there its access i am using from a vb app - Thanks very much for this some thing else I have learned