|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
April 26th, 2007, 03:18 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Trim Data
I know this may be answered somewhere but I am new at this and need very
specific steps. Screen shots also help. I am running a query. In one of the fields "addr: it has information like this example. 236 West Rezanof Drive, Kodiak, AK 99615 or Kodiak, AK 99615 or Some may be blank. The only thing I want from this field is the street address without the city state or zip. So I guess something that will trim everything after the first comma knowing that sometime the field is blank. 
|
|
#2
April 26th, 2007, 03:43 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Trim Data
Unfortunately, address data is not standard enough for the computer to be
able to pick the street name all the time. Looking at your data, can you identify a way that will work for most records? For example, is it everything before the first comma? If so you could use Instr() to locate the comma, and Left() to get the characters to the left of that. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Finger Tips" wrote in message ... I know this may be answered somewhere but I am new at this and need very specific steps. Screen shots also help. I am running a query. In one of the fields "addr: it has information like this example. 236 West Rezanof Drive, Kodiak, AK 99615 or Kodiak, AK 99615 or Some may be blank. The only thing I want from this field is the street address without the city state or zip. So I guess something that will trim everything after the first comma knowing that sometime the field is blank.
|
|
#3
April 26th, 2007, 03:48 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Trim Data
Try this --
Left([YourFieldName], InStr([YourFieldName], ",")-1) -- KARL DEWEY Build a little - Test a little "Finger Tips" wrote: I know this may be answered somewhere but I am new at this and need very specific steps. Screen shots also help. I am running a query. In one of the fields "addr: it has information like this example. 236 West Rezanof Drive, Kodiak, AK 99615 or Kodiak, AK 99615 or Some may be blank. The only thing I want from this field is the street address without the city state or zip. So I guess something that will trim everything after the first comma knowing that sometime the field is blank.
|
|
#4
April 26th, 2007, 06:10 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Trim Data
Do I place this in the criteria line when making the query?
"KARL DEWEY" wrote: Try this -- Left([YourFieldName], InStr([YourFieldName], ",")-1) -- KARL DEWEY Build a little - Test a little "Finger Tips" wrote: I know this may be answered somewhere but I am new at this and need very specific steps. Screen shots also help. I am running a query. In one of the fields "addr: it has information like this example. 236 West Rezanof Drive, Kodiak, AK 99615 or Kodiak, AK 99615 or Some may be blank. The only thing I want from this field is the street address without the city state or zip. So I guess something that will trim everything after the first comma knowing that sometime the field is blank.
|
|
#5
April 26th, 2007, 06:30 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Trim Data
If so I am getting an "Invalid Procedure Call" message when trying to run it.
"Finger Tips" wrote: Do I place this in the criteria line when making the query? "KARL DEWEY" wrote: Try this -- Left([YourFieldName], InStr([YourFieldName], ",")-1) -- KARL DEWEY Build a little - Test a little "Finger Tips" wrote: I know this may be answered somewhere but I am new at this and need very specific steps. Screen shots also help. I am running a query. In one of the fields "addr: it has information like this example. 236 West Rezanof Drive, Kodiak, AK 99615 or Kodiak, AK 99615 or Some may be blank. The only thing I want from this field is the street address without the city state or zip. So I guess something that will trim everything after the first comma knowing that sometime the field is blank.
|
|
#6
April 26th, 2007, 07:08 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Trim Data
Open your query in design view and in the Field row of the grid add this in a
blank. Street: Left([YourFieldName], InStr([YourFieldName], ",")-1) -- KARL DEWEY Build a little - Test a little "Finger Tips" wrote: Do I place this in the criteria line when making the query? "KARL DEWEY" wrote: Try this -- Left([YourFieldName], InStr([YourFieldName], ",")-1) -- KARL DEWEY Build a little - Test a little "Finger Tips" wrote: I know this may be answered somewhere but I am new at this and need very specific steps. Screen shots also help. I am running a query. In one of the fields "addr: it has information like this example. 236 West Rezanof Drive, Kodiak, AK 99615 or Kodiak, AK 99615 or Some may be blank. The only thing I want from this field is the street address without the city state or zip. So I guess something that will trim everything after the first comma knowing that sometime the field is blank.
|
|
#7
April 26th, 2007, 07:22 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Trim Data
Received an "Error #" in the field when it ran the query
"KARL DEWEY" wrote: Open your query in design view and in the Field row of the grid add this in a blank. Street: Left([YourFieldName], InStr([YourFieldName], ",")-1) -- KARL DEWEY Build a little - Test a little "Finger Tips" wrote: Do I place this in the criteria line when making the query? "KARL DEWEY" wrote: Try this -- Left([YourFieldName], InStr([YourFieldName], ",")-1) -- KARL DEWEY Build a little - Test a little "Finger Tips" wrote: I know this may be answered somewhere but I am new at this and need very specific steps. Screen shots also help. I am running a query. In one of the fields "addr: it has information like this example. 236 West Rezanof Drive, Kodiak, AK 99615 or Kodiak, AK 99615 or Some may be blank. The only thing I want from this field is the street address without the city state or zip. So I guess something that will trim everything after the first comma knowing that sometime the field is blank.
|
|
#8
April 26th, 2007, 08:00 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Trim Data
Copy and paste your SQL in a post. Open query in design view, click on menu
VIEW - SQL View, hightlight all, copy, and paste in a post. -- KARL DEWEY Build a little - Test a little "Finger Tips" wrote: Received an "Error #" in the field when it ran the query "KARL DEWEY" wrote: Open your query in design view and in the Field row of the grid add this in a blank. Street: Left([YourFieldName], InStr([YourFieldName], ",")-1) -- KARL DEWEY Build a little - Test a little "Finger Tips" wrote: Do I place this in the criteria line when making the query? "KARL DEWEY" wrote: Try this -- Left([YourFieldName], InStr([YourFieldName], ",")-1) -- KARL DEWEY Build a little - Test a little "Finger Tips" wrote: I know this may be answered somewhere but I am new at this and need very specific steps. Screen shots also help. I am running a query. In one of the fields "addr: it has information like this example. 236 West Rezanof Drive, Kodiak, AK 99615 or Kodiak, AK 99615 or Some may be blank. The only thing I want from this field is the street address without the city state or zip. So I guess something that will trim everything after the first comma knowing that sometime the field is blank.
|
|
#9
April 26th, 2007, 09:40 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Trim Data
If your field does not have a comma in it that will error, since Left
will not accept a negative number. It will accept zero and positive numbers. So the trick is to add the comma to the end of the field value in the instr function call. That way the function will always return 1 when the field is null or blank or doesn't contain a comma other than then one you have temporarily added. LEFT([YourField], Instr(1.[YourField] & ",",",")-1) '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Finger Tips wrote: Received an "Error #" in the field when it ran the query "KARL DEWEY" wrote: Open your query in design view and in the Field row of the grid add this in a blank. Street: Left([YourFieldName], InStr([YourFieldName], ",")-1) -- KARL DEWEY Build a little - Test a little "Finger Tips" wrote: Do I place this in the criteria line when making the query? "KARL DEWEY" wrote: Try this -- Left([YourFieldName], InStr([YourFieldName], ",")-1) -- KARL DEWEY Build a little - Test a little "Finger Tips" wrote: I know this may be answered somewhere but I am new at this and need very specific steps. Screen shots also help. I am running a query. In one of the fields "addr: it has information like this example. 236 West Rezanof Drive, Kodiak, AK 99615 or Kodiak, AK 99615 or Some may be blank. The only thing I want from this field is the street address without the city state or zip. So I guess something that will trim everything after the first comma knowing that sometime the field is blank.
|
|
#10
April 27th, 2007, 01:04 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Trim Data
Hi John, yes some of the fields only contain 2 commas and others are blank.
WHen I insert this statement though I get an "The expression you entered contains an Invalid Syntax" "John Spencer" wrote: If your field does not have a comma in it that will error, since Left will not accept a negative number. It will accept zero and positive numbers. So the trick is to add the comma to the end of the field value in the instr function call. That way the function will always return 1 when the field is null or blank or doesn't contain a comma other than then one you have temporarily added. LEFT([YourField], Instr(1.[YourField] & ",",",")-1) '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Finger Tips wrote: Received an "Error #" in the field when it ran the query "KARL DEWEY" wrote: Open your query in design view and in the Field row of the grid add this in a blank. Street: Left([YourFieldName], InStr([YourFieldName], ",")-1) -- KARL DEWEY Build a little - Test a little "Finger Tips" wrote: Do I place this in the criteria line when making the query? "KARL DEWEY" wrote: Try this -- Left([YourFieldName], InStr([YourFieldName], ",")-1) -- KARL DEWEY Build a little - Test a little "Finger Tips" wrote: I know this may be answered somewhere but I am new at this and need very specific steps. Screen shots also help. I am running a query. In one of the fields "addr: it has information like this example. 236 West Rezanof Drive, Kodiak, AK 99615 or Kodiak, AK 99615 or Some may be blank. The only thing I want from this field is the street address without the city state or zip. So I guess something that will trim everything after the first comma knowing that sometime the field is blank.
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
