Uk Postcode Search Criteria

[dan.cawthorne]

I Have a Text Box Set to Text for Post Codes,

As The Input Mask is Rubbish!!

What I Want to Be able To Do With In My Query,

Is On The Post Code Field is to do a Search Criteria In The Box That
Will Bring Up Results By Only Inputing the First Part


E.G

B77 and Not B77 3NW

Or

WS11 and Not WS11 0JN

Even Though The Postcode Field Will Hold The Full Postcode

 

[Andy Hull]

Hi Dan

If I understand correctly, you want to be able to enter part of a postcode
in a text box on a form and have a query use this to filter its results.

If the form is called MyForm and the text box is called EnterPostcode then
the where clause of your query will look like...

where TableName.Postcode like [Forms]![MyForm].[EnterPostcode] & "*"

hth

Andy Hull

 

[dan.cawthorne]

Hi Dan

If I understand correctly, you want to be able to enter part of a postcode
in a text box on a form and have a query use this to filter its results.

If the form is called MyForm and the text box is called EnterPostcode then
the where clause of your query will look like...

where TableName.Postcode like [Forms]![MyForm].[EnterPostcode] & "*"

hth

Andy Hull

I Have a Text Box Set to Text for Post Codes,

As The Input Mask is Rubbish!!

What I Want to Be able To Do With In My Query,

Is On The Post Code Field is to do a Search Criteria In The Box That
Will Bring Up Results By Only Inputing the First Part

B77 and Not B77 3NW

WS11 and Not WS11 0JN

Even Though The Postcode Field Will Hold The Full Postcode

Not Working, Doing Something Wrong,

Ive Created a From Called "Suppliers_PostCode_Dialog"

And I Have an Unbound Textbox Called "EnterPostCode"

With a Button Which Opens a Form Called "SuppliersPostCodeResults"

Form "SuppliersPostCodeResults" is Based On a Query Called
"Qry_Supplier_Postcode"

The Query "Qry_Supplier_Postcode" Field [Post Code] Criteria Is Set To
Look at The Unbound EnterPostCode Box

Example Code Works If I Type the Full Post Code in

Forms![Suppliers_PostCode_Dialog]![EnterPostCode]

But i Don't Want To Type The Full Post Code In Just The First Part

But That Code Dont Allow That and The Query Comes Blank.

I Tried This Code In The Criteria Box In Column Post Code

WHERE Suppliers.[Post Code] like [Forms]![Suppliers_PostCode_Dialog].
[EnterPostcode] & "*"

And Access Automaticly Added Square Brackets Around The [WHERE
Suppliers]

Where am I Going Wrong?

 

[dan.cawthorne]

Hi Dan

If I understand correctly, you want to be able to enter part of a postcode
in a text box on a form and have a query use this to filter its results.

If the form is called MyForm and the text box is called EnterPostcode then
the where clause of your query will look like...

where TableName.Postcode like [Forms]![MyForm].[EnterPostcode] & "*"

hth

Andy Hull

Not Working, Doing Something Wrong,

Ive Created a From Called "Suppliers_PostCode_Dialog"

And I Have an Unbound Textbox Called "EnterPostCode"

With a Button Which Opens a Form Called "SuppliersPostCodeResults"

Form "SuppliersPostCodeResults" is Based On a Query Called
"Qry_Supplier_Postcode"

The Query "Qry_Supplier_Postcode" Field [Post Code] Criteria Is Set To
Look at The Unbound EnterPostCode Box

Example Code Works If I Type the Full Post Code in

Forms![Suppliers_PostCode_Dialog]![EnterPostCode]

But i Don't Want To Type The Full Post Code In Just The First Part

But That Code Dont Allow That and The Query Comes Blank.

I Tried This Code In The Criteria Box In Column Post Code

WHERE Suppliers.[Post Code] like [Forms]![Suppliers_PostCode_Dialog].
[EnterPostcode] & "*"

And Access Automaticly Added Square Brackets Around The [WHERE
Suppliers]

Where am I Going Wrong?

I Managed to Achieve It By Puttings The Following in Me Post Code
Column Field

Like "*" & [Forms]![Suppliers_PostCode_Dialog]![EnterPostCode] & "*"

Seems To Do The Trick

Though Read Some where That Not Very Stable

 

[Andy Hull]

Hi again Dan

What I gave you was the SQL version.

When in the query design screen you just need to enter the following into
the criteria field...

Like [Forms]![Suppliers_PostCode_Dialog]![EnterPostCode] & "*"

This is the same as you have tried but without the first asterisk.

If you have an * at the beginning as well then it will look for what you
have typed anywhere in the postcode so if you put DY you will get DY14 7RT
and BS11 6DY

If you leave out the first * then DY will give all postcodes beginning with DY

Andy Hull

Hi Dan

If I understand correctly, you want to be able to enter part of a postcode
in a text box on a form and have a query use this to filter its results.

If the form is called MyForm and the text box is called EnterPostcode then
the where clause of your query will look like...

where TableName.Postcode like [Forms]![MyForm].[EnterPostcode] & "*"

hth

Andy Hull

:
I Have a Text Box Set to Text for Post Codes,

As The Input Mask is Rubbish!!

What I Want to Be able To Do With In My Query,

Is On The Post Code Field is to do a Search Criteria In The Box That
Will Bring Up Results By Only Inputing the First Part

B77 and Not B77 3NW

WS11 and Not WS11 0JN

Even Though The Postcode Field Will Hold The Full Postcode

Not Working, Doing Something Wrong,

Ive Created a From Called "Suppliers_PostCode_Dialog"

And I Have an Unbound Textbox Called "EnterPostCode"

With a Button Which Opens a Form Called "SuppliersPostCodeResults"

Form "SuppliersPostCodeResults" is Based On a Query Called
"Qry_Supplier_Postcode"

The Query "Qry_Supplier_Postcode" Field [Post Code] Criteria Is Set To
Look at The Unbound EnterPostCode Box

Example Code Works If I Type the Full Post Code in

Forms![Suppliers_PostCode_Dialog]![EnterPostCode]

But i Don't Want To Type The Full Post Code In Just The First Part

But That Code Dont Allow That and The Query Comes Blank.

I Tried This Code In The Criteria Box In Column Post Code

WHERE Suppliers.[Post Code] like [Forms]![Suppliers_PostCode_Dialog].
[EnterPostcode] & "*"

And Access Automaticly Added Square Brackets Around The [WHERE
Suppliers]

Where am I Going Wrong?

I Managed to Achieve It By Puttings The Following in Me Post Code
Column Field

Like "*" & [Forms]![Suppliers_PostCode_Dialog]![EnterPostCode] & "*"

Seems To Do The Trick

Though Read Some where That Not Very Stable

 

[dan.cawthorne]

Hi again Dan

What I gave you was the SQL version.

When in the query design screen you just need to enter the following into
the criteria field...

Like [Forms]![Suppliers_PostCode_Dialog]![EnterPostCode] & "*"

This is the same as you have tried but without the first asterisk.

If you have an * at the beginning as well then it will look for what you
have typed anywhere in the postcode so if you put DY you will get DY14 7RT
and BS11 6DY

If you leave out the first * then DY will give all postcodes beginning with DY

Andy Hull

On 24 Jul, 11:02, Andy Hull <[email protected]>
wrote:
Hi Dan
If I understand correctly, you want to be able to enter part of a postcode
in a text box on a form and have a query use this to filter its results.
If the form is called MyForm and the text box is called EnterPostcode then
the where clause of your query will look like...
where TableName.Postcode like [Forms]![MyForm].[EnterPostcode] & "*"
hth
Andy Hull
:
I Have a Text Box Set to Text for Post Codes,
As The Input Mask is Rubbish!!
What I Want to Be able To Do With In My Query,
Is On The Post Code Field is to do a Search Criteria In The Box That
Will Bring Up Results By Only Inputing the First Part
E.G
B77 and Not B77 3NW
Or
WS11 and Not WS11 0JN
Even Though The Postcode Field Will Hold The Full Postcode
Not Working, Doing Something Wrong,
Ive Created a From Called "Suppliers_PostCode_Dialog"
And I Have an Unbound Textbox Called "EnterPostCode"
With a Button Which Opens a Form Called "SuppliersPostCodeResults"
Form "SuppliersPostCodeResults" is Based On a Query Called
"Qry_Supplier_Postcode"
The Query "Qry_Supplier_Postcode" Field [Post Code] Criteria Is Set To
Look at The Unbound EnterPostCode Box
Example Code Works If I Type the Full Post Code in
Forms![Suppliers_PostCode_Dialog]![EnterPostCode]
But i Don't Want To Type The Full Post Code In Just The First Part
But That Code Dont Allow That and The Query Comes Blank.
I Tried This Code In The Criteria Box In Column Post Code
WHERE Suppliers.[Post Code] like [Forms]![Suppliers_PostCode_Dialog].
[EnterPostcode] & "*"
And Access Automaticly Added Square Brackets Around The [WHERE
Suppliers]
Where am I Going Wrong?

I Managed to Achieve It By Puttings The Following in Me Post Code
Column Field

Like "*" & [Forms]![Suppliers_PostCode_Dialog]![EnterPostCode] & "*"

Seems To Do The Trick

Though Read Some where That Not Very Stable

Thanks Thats Cleared It Up, and Thanks For the Other Recent Post in my
new one