Need Help with LIKE

  • Thread starter Thread starter Tony B
  • Start date Start date
T

Tony B

Hi All

I am trying to match postcodes(UK) to a field that is derived from the
first part of the postcode. i.e. Postcode = bb12 1aa and field =
bb1. However if field bb1 is allocated to say user1 and bb12 is
allocated to user 2 then both are returned when I use this in the
query:
WHERE (((Top_75.POSTCODE) Like [User_postcodes]![Postcode] & "*"))

<<<

Is there a way to ensure that only the user with the exact match is
shown.

TIA

Tony
 
Tony,

This is one of the flaws with a database that is not normalized. Whenever
you try to put more than one piece of information in a single field, you end
up with this type of problem. I'd recommend that you break your PostCode
field into individual fields which only contain a single piece of
information in each.

What would the entire post code look like for the bb1 example you gave? I
would think that the format of the PostCode would require it to read bb01,
but you are obviously programming for a location other than the US, so I
obviously don't understand your postal system designations.

Dale

Tony B said:
Hi All

I am trying to match postcodes(UK) to a field that is derived from the
first part of the postcode. i.e. Postcode = bb12 1aa and field =
bb1. However if field bb1 is allocated to say user1 and bb12 is
allocated to user 2 then both are returned when I use this in the
query:
WHERE (((Top_75.POSTCODE) Like [User_postcodes]![Postcode] & "*"))

<<<

Is there a way to ensure that only the user with the exact match is
shown.

TIA

Tony
 
Thanks for the reply Dale.

I am in the UK and unfortunately ours must be one of the least standad in
the world....

The postcodes can be :

B1 0AA
B12 0AA
BB1 0AA
BB11 0AA

but never B01 0AA if that makes it clearer.

Regards

Tony


Dale Fye said:
Tony,

This is one of the flaws with a database that is not normalized. Whenever
you try to put more than one piece of information in a single field, you end
up with this type of problem. I'd recommend that you break your PostCode
field into individual fields which only contain a single piece of
information in each.

What would the entire post code look like for the bb1 example you gave? I
would think that the format of the PostCode would require it to read bb01,
but you are obviously programming for a location other than the US, so I
obviously don't understand your postal system designations.

Dale

Tony B said:
Hi All

I am trying to match postcodes(UK) to a field that is derived from the
first part of the postcode. i.e. Postcode = bb12 1aa and field =
bb1. However if field bb1 is allocated to say user1 and bb12 is
allocated to user 2 then both are returned when I use this in the
query:
WHERE (((Top_75.POSTCODE) Like [User_postcodes]![Postcode] & "*"))

<<<

Is there a way to ensure that only the user with the exact match is
shown.

TIA

Tony
 
Thanks for the reply Dale.

I am in the UK and unfortunately ours must be one of the least standad in
the world....

The postcodes can be :

B1 0AA
B12 0AA
BB1 0AA
BB11 0AA

but never B01 0AA if that makes it clearer.

Regards

Tony


Dale Fye said:
Tony,

This is one of the flaws with a database that is not normalized. Whenever
you try to put more than one piece of information in a single field, you end
up with this type of problem. I'd recommend that you break your PostCode
field into individual fields which only contain a single piece of
information in each.

What would the entire post code look like for the bb1 example you gave? I
would think that the format of the PostCode would require it to read bb01,
but you are obviously programming for a location other than the US, so I
obviously don't understand your postal system designations.

Dale

Tony B said:
Hi All

I am trying to match postcodes(UK) to a field that is derived from the
first part of the postcode. i.e. Postcode = bb12 1aa and field =
bb1. However if field bb1 is allocated to say user1 and bb12 is
allocated to user 2 then both are returned when I use this in the
query:
WHERE (((Top_75.POSTCODE) Like [User_postcodes]![Postcode] & "*"))

<<<

Is there a way to ensure that only the user with the exact match is
shown.

TIA

Tony
 
Thanks for the reply Dale.

I am in the UK and unfortunately ours must be one of the least standad in
the world....

The postcodes can be :

B1 0AA
B12 0AA
BB1 0AA
BB11 0AA

but never B01 0AA if that makes it clearer.

Regards

Tony

Dale Fye said:
Tony,

This is one of the flaws with a database that is not normalized. Whenever
you try to put more than one piece of information in a single field, you end
up with this type of problem. I'd recommend that you break your PostCode
field into individual fields which only contain a single piece of
information in each.

What would the entire post code look like for the bb1 example you gave? I
would think that the format of the PostCode would require it to read bb01,
but you are obviously programming for a location other than the US, so I
obviously don't understand your postal system designations.

Dale

Tony B said:
Hi All

I am trying to match postcodes(UK) to a field that is derived from the
first part of the postcode. i.e. Postcode = bb12 1aa and field =
bb1. However if field bb1 is allocated to say user1 and bb12 is
allocated to user 2 then both are returned when I use this in the
query:


WHERE (((Top_75.POSTCODE) Like [User_postcodes]![Postcode] & "*"))

<<<

Is there a way to ensure that only the user with the exact match is
shown.

TIA

Tony

As long as there is always that space after the first grouping, have
the user enter the space also, i.e.
b1space
bb1space

Or it that's too much for the user to handle, change the SQL to:
Like [User_postcodes]![Postcode] & " *"

The space will be automatically added to all parameter entries.
 
Hi fredg

Thanks for the tip.Or it that's too much for the user to handle, change the SQL to:
Like [User_postcodes]![Postcode] & " *"

<<

This works Fine

Regards

Tony

fredg said:
Thanks for the reply Dale.

I am in the UK and unfortunately ours must be one of the least standad in
the world....

The postcodes can be :

B1 0AA
B12 0AA
BB1 0AA
BB11 0AA

but never B01 0AA if that makes it clearer.

Regards

Tony

Dale Fye said:
Tony,

This is one of the flaws with a database that is not normalized. Whenever
you try to put more than one piece of information in a single field,
you
end
up with this type of problem. I'd recommend that you break your PostCode
field into individual fields which only contain a single piece of
information in each.

What would the entire post code look like for the bb1 example you gave? I
would think that the format of the PostCode would require it to read bb01,
but you are obviously programming for a location other than the US, so I
obviously don't understand your postal system designations.

Dale

Hi All

I am trying to match postcodes(UK) to a field that is derived from the
first part of the postcode. i.e. Postcode = bb12 1aa and field =
bb1. However if field bb1 is allocated to say user1 and bb12 is
allocated to user 2 then both are returned when I use this in the
query:


WHERE (((Top_75.POSTCODE) Like [User_postcodes]![Postcode] & "*"))

<<<

Is there a way to ensure that only the user with the exact match is
shown.

TIA

Tony

As long as there is always that space after the first grouping, have
the user enter the space also, i.e.
b1space
bb1space

Or it that's too much for the user to handle, change the SQL to:
Like [User_postcodes]![Postcode] & " *"

The space will be automatically added to all parameter entries.
 
Back
Top