LTrim

[Fay]

Using Access03.

Want to use combo box to find records. The field to be used is the address
line which has the box address as well as the street. I want to be able to
trim off the box number and pull up just one occurrence of Hasket Lane, or
other road. So once I get the combo box together using the wizard how do I
trim off the box address and limit the recordset to only one occurrence for
each street? Then in resulting recordset to see one entry for each box
address.

Thanks

 

[Ken Snell [MVP]]

Create a query that is based on the table that has the data you want to
display. Create a calculated field in this query that will display just the
"street" portion of the address field (not knowing the format of the street
address field's data, it's hard to definitively say how to do this, but I'll
give a generic idea in the sample query below). Then change the query to a
Totals query (click the Greek letter sigma; looks like a zig-zag E) on the
toolbar. Under the calculated field, in the Totals box, choose Group By.

Example:

SELECT Mid([AddressField], InStr([AddressField], " ") + 1) AS StreetName
FROM TableName
GROUP BY Mid([AddressField], InStr([AddressField], " ") + 1)
ORDER BY Mid([AddressField], InStr([AddressField], " ") + 1);