You could limit the search to the first 5 characters if that would be
acceptable:
WHERE Left(CurrentCust.Zip,5)=Left(ProspectiveCust,5)
"James Armstrong" <armstrongjc@hotmail.com> wrote in message
news:7cf26243.0408021239.7479cf34@posting.google.c om...[color=blue]
> Hello all -
>
> Hopefully there's a quick answer to this one, but I haven't found any
> good info in my reference books...
>
> I have a list of customers and a list of prospective customers. All I
> care about in these lists are the customer name and zip code. I
> basically want to compare my list of prospective customers to the list
> of current customers and make sure none of the prospects are doing
> business with me already.
>
> The problem is this - the ZIP codes are varied - some are 5 digits,
> some are 5+4. I don't want a record to be missed by the query because
> of this, so I want to say:
>
> WHERE CurrentCust.Zip LIKE (the value of ProspectiveCust.Zip + %
> wildcard)
>
> However, I'm unsure of the syntax there... I've tried
>
> WHERE CurrentCust.Zip LIKE ProspectiveCust & '%' to no avail
>
> I'm using Access 2003.
>
> Can anyone help?
>
> Thanks much,
>
> Jim[/color]
SQL query referencing field values + wildcards 
