There are so many experts on this board that I am hopeful one of you will take the time to give me some direction on how to proceed.
I have a CSV file of the streets in the city of Ajax, Ontario, AJAX.CSV, with three fields, the first which is null (x), the second which contains a street name, and the third which contains a route number.
Here is a sample of what the AJAX.CSV file looks like:
x BIRD CR 5 x BLACKBURN CR 10 x BLAKE CT 4 x BOLLAND CR 8 x BOOTH CR 5 x BOWERS CT 2 x BOWLES DR 1 x BRADY LN 8 x BRAMWELL DR 11 x BRAND CT 5 x BRAY DR 4 x BRENNAN RD 2 x BRIGHTLY DR 3 x BROCK ST 10 x BROCKLESBY CR 3 x BROCKMAN CR 2 x BROOKS RD 11 x BROOKSBANK CR 9 x BRYANT RD 7
I would like to add data to another of a dozen ROUTE files, using the AJAX.CSV file as the input file. The user would type in the STREET NAME and the AJAX.CSV file is accessed, then the correct ROUTE number (column 3) is applied to the record and added to that route.
Here's my code, no laughter please ...
Sub ADD() Rem MODULE 20 On Error GoTo NOTFOUND ADDCITY = InputBox("PLEASE INPUT THE CITY NAME") If ADDCITY = "" Then Exit Sub If ADDCITY <> "AJAX" Then Beep: Exit Sub ADDSTREET = InputBox("PLEASE INPUT THE STREET NAME") Open "C:\CHERYL\LOOKUP\" + ADDCITY + ".CSV" For Input As #1 For Q = 1 To 70000 If EOF(1) Then Close #1: GoTo NOTFOUND Input #1, A$, B$, C$ If B$ = ADDSTREET Then Close #1: GoTo FOUND Next Q
Here's my problem ... people will sometimes type in the wrong suffix, for example instead of typing in "BIRD CR" exactly they will type in "BIRD CRES", or "BIRD CRS" and the program simply exits at NOTFOUND .
Is there any way I can somehow make it so if someone mispells the suffix, or does not follow the convention, the program will still find the correct STREET and ROUTE?
As always, thank you in advance for your time.