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 |
#11
|
|||
|
|||
Trim Data
Typo on my part. Period should be a comma
LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1) Extra spaces added for clarity '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Finger Tips wrote: 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. |
#12
|
|||
|
|||
Trim Data
Still getting the #Error in the field
"John Spencer" wrote: Typo on my part. Period should be a comma LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1) Extra spaces added for clarity '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Finger Tips wrote: 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. |
#13
|
|||
|
|||
Trim Data
I tested the expression
LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1) and I don't get any error. WHERE do you see the error? In the query itself or are you using this in a report or form? If in the query, please post the SQL of the query. Please copy and post the SQL of your query. (Possibly unneeded instructions follow) Open the query Select View:Sql from the Menu Select all the text Copy it Paste it into the message -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Finger Tips" wrote in message news Still getting the #Error in the field "John Spencer" wrote: Typo on my part. Period should be a comma LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1) Extra spaces added for clarity '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Finger Tips wrote: 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. |
#14
|
|||
|
|||
Trim Data
here is the sql
SELECT dbo_comps11.title AS dbo_comps11_title, dbo_comps11.addr, dbo_comps11.city, dbo_comps11.state, dbo_comps11.zip, dbo_comps11.phone, dbo_paths.id, dbo_paths.title AS dbo_paths_title, dbo_paths.parent, Left([dbo_comps11.addr],InStr(1,[dbo_comps11.addr] & ",",",")-1) AS Street FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent = dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent WHERE (((dbo_paths.parent)=60003)); various scenerios of the data in the orig. field are 1400 N. Scott, Wichita Falls, TX 76306 500 East Peabody Drive, Champaign, IL 61820 Hortonville, WI 54944 3448 Steinway Street, Long Island City, NY 11101 707 Lake Boulevard, Saint Joseph, MI 49085 and Blank The query does run and returns all other fields correctly except this one which returns the text #Error "John Spencer" wrote: I tested the expression LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1) and I don't get any error. WHERE do you see the error? In the query itself or are you using this in a report or form? If in the query, please post the SQL of the query. Please copy and post the SQL of your query. (Possibly unneeded instructions follow) Open the query Select View:Sql from the Menu Select all the text Copy it Paste it into the message -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Finger Tips" wrote in message news Still getting the #Error in the field "John Spencer" wrote: Typo on my part. Period should be a comma LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1) Extra spaces added for clarity '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Finger Tips wrote: 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. |
#15
|
|||
|
|||
Trim Data
BAD brackets. You should surround the tablename and fieldname separately as follows. Left([dbo_comps11].[addr],InStr(1,[dbo_comps11].[addr] & ",",",")-1) AS Street SELECT dbo_comps11.title AS dbo_comps11_title , dbo_comps11.addr , dbo_comps11.city , dbo_comps11.state , dbo_comps11.zip , dbo_comps11.phone , dbo_paths.id, dbo_paths.title AS dbo_paths_title , dbo_paths.parent , Left([dbo_comps11].[addr],InStr(1,[dbo_comps11].[addr] & ",",",")-1) AS Street FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent = dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent WHERE (((dbo_paths.parent)=60003)); If your field name is unique with the two tables you could use Left([addr],InStr(1,[addr] & ",",",")-1) AS Street And if it is unique and has no spaces or other non letter or number characters you can even drop the brackets Left(addr,InStr(1,addr & ",",",")-1) AS Street Hope that takes care of the problem. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Finger Tips" wrote in message ... here is the sql SELECT dbo_comps11.title AS dbo_comps11_title, dbo_comps11.addr, dbo_comps11.city, dbo_comps11.state, dbo_comps11.zip, dbo_comps11.phone, dbo_paths.id, dbo_paths.title AS dbo_paths_title, dbo_paths.parent, Left([dbo_comps11.addr],InStr(1,[dbo_comps11.addr] & ",",",")-1) AS Street FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent = dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent WHERE (((dbo_paths.parent)=60003)); various scenerios of the data in the orig. field are 1400 N. Scott, Wichita Falls, TX 76306 500 East Peabody Drive, Champaign, IL 61820 Hortonville, WI 54944 3448 Steinway Street, Long Island City, NY 11101 707 Lake Boulevard, Saint Joseph, MI 49085 and Blank The query does run and returns all other fields correctly except this one which returns the text #Error "John Spencer" wrote: I tested the expression LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1) and I don't get any error. WHERE do you see the error? In the query itself or are you using this in a report or form? If in the query, please post the SQL of the query. Please copy and post the SQL of your query. (Possibly unneeded instructions follow) Open the query Select View:Sql from the Menu Select all the text Copy it Paste it into the message -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Finger Tips" wrote in message news Still getting the #Error in the field "John Spencer" wrote: Typo on my part. Period should be a comma LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1) Extra spaces added for clarity '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Finger Tips wrote: 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. |
#16
|
|||
|
|||
Trim Data
Still getting the same #Error
"John Spencer" wrote: BAD brackets. You should surround the tablename and fieldname separately as follows. Left([dbo_comps11].[addr],InStr(1,[dbo_comps11].[addr] & ",",",")-1) AS Street SELECT dbo_comps11.title AS dbo_comps11_title , dbo_comps11.addr , dbo_comps11.city , dbo_comps11.state , dbo_comps11.zip , dbo_comps11.phone , dbo_paths.id, dbo_paths.title AS dbo_paths_title , dbo_paths.parent , Left([dbo_comps11].[addr],InStr(1,[dbo_comps11].[addr] & ",",",")-1) AS Street FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent = dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent WHERE (((dbo_paths.parent)=60003)); If your field name is unique with the two tables you could use Left([addr],InStr(1,[addr] & ",",",")-1) AS Street And if it is unique and has no spaces or other non letter or number characters you can even drop the brackets Left(addr,InStr(1,addr & ",",",")-1) AS Street Hope that takes care of the problem. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Finger Tips" wrote in message ... here is the sql SELECT dbo_comps11.title AS dbo_comps11_title, dbo_comps11.addr, dbo_comps11.city, dbo_comps11.state, dbo_comps11.zip, dbo_comps11.phone, dbo_paths.id, dbo_paths.title AS dbo_paths_title, dbo_paths.parent, Left([dbo_comps11.addr],InStr(1,[dbo_comps11.addr] & ",",",")-1) AS Street FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent = dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent WHERE (((dbo_paths.parent)=60003)); various scenerios of the data in the orig. field are 1400 N. Scott, Wichita Falls, TX 76306 500 East Peabody Drive, Champaign, IL 61820 Hortonville, WI 54944 3448 Steinway Street, Long Island City, NY 11101 707 Lake Boulevard, Saint Joseph, MI 49085 and Blank The query does run and returns all other fields correctly except this one which returns the text #Error "John Spencer" wrote: I tested the expression LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1) and I don't get any error. WHERE do you see the error? In the query itself or are you using this in a report or form? If in the query, please post the SQL of the query. Please copy and post the SQL of your query. (Possibly unneeded instructions follow) Open the query Select View:Sql from the Menu Select all the text Copy it Paste it into the message -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Finger Tips" wrote in message news Still getting the #Error in the field "John Spencer" wrote: Typo on my part. Period should be a comma LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1) Extra spaces added for clarity '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Finger Tips wrote: 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. |
#17
|
|||
|
|||
Trim Data
Ok John, I found some other items in the field that may be effecting the
result. Here are some of the data as it appears in the addr field. In these cases I would just want the field returned blank. # 99.5, Key West, FL 33040 (401)-996-1670, Riverside, RI 02915 .., Bridgeton, NJ 08302 .., Huntingdon Valley, PA 19006 .., Jacksonville, FL 32202 .., Lynnfield, MA 01940 .., Phoenixville, PA 19460 .., Tampa, FL 33612 .., Tampa, FL 33612 .., Tampa, FL 33612 .., Williamsport, PA 17702 ...., Anderson, SC 29621 ...., Greenville, SC 29601 /NP/, Redmond, WA 98053 ?, Colchester, VT 05446 _, Boston, MA 02284 "Finger Tips" wrote: Still getting the same #Error "John Spencer" wrote: BAD brackets. You should surround the tablename and fieldname separately as follows. Left([dbo_comps11].[addr],InStr(1,[dbo_comps11].[addr] & ",",",")-1) AS Street SELECT dbo_comps11.title AS dbo_comps11_title , dbo_comps11.addr , dbo_comps11.city , dbo_comps11.state , dbo_comps11.zip , dbo_comps11.phone , dbo_paths.id, dbo_paths.title AS dbo_paths_title , dbo_paths.parent , Left([dbo_comps11].[addr],InStr(1,[dbo_comps11].[addr] & ",",",")-1) AS Street FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent = dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent WHERE (((dbo_paths.parent)=60003)); If your field name is unique with the two tables you could use Left([addr],InStr(1,[addr] & ",",",")-1) AS Street And if it is unique and has no spaces or other non letter or number characters you can even drop the brackets Left(addr,InStr(1,addr & ",",",")-1) AS Street Hope that takes care of the problem. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Finger Tips" wrote in message ... here is the sql SELECT dbo_comps11.title AS dbo_comps11_title, dbo_comps11.addr, dbo_comps11.city, dbo_comps11.state, dbo_comps11.zip, dbo_comps11.phone, dbo_paths.id, dbo_paths.title AS dbo_paths_title, dbo_paths.parent, Left([dbo_comps11.addr],InStr(1,[dbo_comps11.addr] & ",",",")-1) AS Street FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent = dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent WHERE (((dbo_paths.parent)=60003)); various scenerios of the data in the orig. field are 1400 N. Scott, Wichita Falls, TX 76306 500 East Peabody Drive, Champaign, IL 61820 Hortonville, WI 54944 3448 Steinway Street, Long Island City, NY 11101 707 Lake Boulevard, Saint Joseph, MI 49085 and Blank The query does run and returns all other fields correctly except this one which returns the text #Error "John Spencer" wrote: I tested the expression LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1) and I don't get any error. WHERE do you see the error? In the query itself or are you using this in a report or form? If in the query, please post the SQL of the query. Please copy and post the SQL of your query. (Possibly unneeded instructions follow) Open the query Select View:Sql from the Menu Select all the text Copy it Paste it into the message -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Finger Tips" wrote in message news Still getting the #Error in the field "John Spencer" wrote: Typo on my part. Period should be a comma LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1) Extra spaces added for clarity '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Finger Tips wrote: 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. |
#18
|
|||
|
|||
Trim Data
At this point it is time to apply someone's brain and clean up the data.
This is getting pretty complicated. While I could develop a VBA function that would clean out some of this data, it would be more time than I am willing to donate. Sorry. '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Finger Tips wrote: Ok John, I found some other items in the field that may be effecting the result. Here are some of the data as it appears in the addr field. In these cases I would just want the field returned blank. # 99.5, Key West, FL 33040 (401)-996-1670, Riverside, RI 02915 ., Bridgeton, NJ 08302 ., Huntingdon Valley, PA 19006 ., Jacksonville, FL 32202 ., Lynnfield, MA 01940 ., Phoenixville, PA 19460 ., Tampa, FL 33612 ., Tampa, FL 33612 ., Tampa, FL 33612 ., Williamsport, PA 17702 ..., Anderson, SC 29621 ..., Greenville, SC 29601 /NP/, Redmond, WA 98053 ?, Colchester, VT 05446 _, Boston, MA 02284 "Finger Tips" wrote: Still getting the same #Error "John Spencer" wrote: BAD brackets. You should surround the tablename and fieldname separately as follows. Left([dbo_comps11].[addr],InStr(1,[dbo_comps11].[addr] & ",",",")-1) AS Street SELECT dbo_comps11.title AS dbo_comps11_title , dbo_comps11.addr , dbo_comps11.city , dbo_comps11.state , dbo_comps11.zip , dbo_comps11.phone , dbo_paths.id, dbo_paths.title AS dbo_paths_title , dbo_paths.parent , Left([dbo_comps11].[addr],InStr(1,[dbo_comps11].[addr] & ",",",")-1) AS Street FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent = dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent WHERE (((dbo_paths.parent)=60003)); If your field name is unique with the two tables you could use Left([addr],InStr(1,[addr] & ",",",")-1) AS Street And if it is unique and has no spaces or other non letter or number characters you can even drop the brackets Left(addr,InStr(1,addr & ",",",")-1) AS Street Hope that takes care of the problem. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Finger Tips" wrote in message ... here is the sql SELECT dbo_comps11.title AS dbo_comps11_title, dbo_comps11.addr, dbo_comps11.city, dbo_comps11.state, dbo_comps11.zip, dbo_comps11.phone, dbo_paths.id, dbo_paths.title AS dbo_paths_title, dbo_paths.parent, Left([dbo_comps11.addr],InStr(1,[dbo_comps11.addr] & ",",",")-1) AS Street FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent = dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent WHERE (((dbo_paths.parent)=60003)); various scenerios of the data in the orig. field are 1400 N. Scott, Wichita Falls, TX 76306 500 East Peabody Drive, Champaign, IL 61820 Hortonville, WI 54944 3448 Steinway Street, Long Island City, NY 11101 707 Lake Boulevard, Saint Joseph, MI 49085 and Blank The query does run and returns all other fields correctly except this one which returns the text #Error "John Spencer" wrote: I tested the expression LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1) and I don't get any error. WHERE do you see the error? In the query itself or are you using this in a report or form? If in the query, please post the SQL of the query. Please copy and post the SQL of your query. (Possibly unneeded instructions follow) Open the query Select View:Sql from the Menu Select all the text Copy it Paste it into the message -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Finger Tips" wrote in message news Still getting the #Error in the field "John Spencer" wrote: Typo on my part. Period should be a comma LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1) Extra spaces added for clarity '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Finger Tips wrote: 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. |
#19
|
|||
|
|||
Trim Data
John, so there is no trim expression that will just leave everything to the
left of the first , no matter what is and eliminate everything to the right? "John Spencer" wrote: At this point it is time to apply someone's brain and clean up the data. This is getting pretty complicated. While I could develop a VBA function that would clean out some of this data, it would be more time than I am willing to donate. Sorry. '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Finger Tips wrote: Ok John, I found some other items in the field that may be effecting the result. Here are some of the data as it appears in the addr field. In these cases I would just want the field returned blank. # 99.5, Key West, FL 33040 (401)-996-1670, Riverside, RI 02915 ., Bridgeton, NJ 08302 ., Huntingdon Valley, PA 19006 ., Jacksonville, FL 32202 ., Lynnfield, MA 01940 ., Phoenixville, PA 19460 ., Tampa, FL 33612 ., Tampa, FL 33612 ., Tampa, FL 33612 ., Williamsport, PA 17702 ..., Anderson, SC 29621 ..., Greenville, SC 29601 /NP/, Redmond, WA 98053 ?, Colchester, VT 05446 _, Boston, MA 02284 "Finger Tips" wrote: Still getting the same #Error "John Spencer" wrote: BAD brackets. You should surround the tablename and fieldname separately as follows. Left([dbo_comps11].[addr],InStr(1,[dbo_comps11].[addr] & ",",",")-1) AS Street SELECT dbo_comps11.title AS dbo_comps11_title , dbo_comps11.addr , dbo_comps11.city , dbo_comps11.state , dbo_comps11.zip , dbo_comps11.phone , dbo_paths.id, dbo_paths.title AS dbo_paths_title , dbo_paths.parent , Left([dbo_comps11].[addr],InStr(1,[dbo_comps11].[addr] & ",",",")-1) AS Street FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent = dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent WHERE (((dbo_paths.parent)=60003)); If your field name is unique with the two tables you could use Left([addr],InStr(1,[addr] & ",",",")-1) AS Street And if it is unique and has no spaces or other non letter or number characters you can even drop the brackets Left(addr,InStr(1,addr & ",",",")-1) AS Street Hope that takes care of the problem. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Finger Tips" wrote in message ... here is the sql SELECT dbo_comps11.title AS dbo_comps11_title, dbo_comps11.addr, dbo_comps11.city, dbo_comps11.state, dbo_comps11.zip, dbo_comps11.phone, dbo_paths.id, dbo_paths.title AS dbo_paths_title, dbo_paths.parent, Left([dbo_comps11.addr],InStr(1,[dbo_comps11.addr] & ",",",")-1) AS Street FROM dbo_paths INNER JOIN ((dbo_comps11 INNER JOIN dbo_links ON dbo_comps11.id = dbo_links.id) INNER JOIN dbo_dirs ON dbo_links.parent = dbo_dirs.id) ON dbo_paths.id = dbo_dirs.parent WHERE (((dbo_paths.parent)=60003)); various scenerios of the data in the orig. field are 1400 N. Scott, Wichita Falls, TX 76306 500 East Peabody Drive, Champaign, IL 61820 Hortonville, WI 54944 3448 Steinway Street, Long Island City, NY 11101 707 Lake Boulevard, Saint Joseph, MI 49085 and Blank The query does run and returns all other fields correctly except this one which returns the text #Error "John Spencer" wrote: I tested the expression LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1) and I don't get any error. WHERE do you see the error? In the query itself or are you using this in a report or form? If in the query, please post the SQL of the query. Please copy and post the SQL of your query. (Possibly unneeded instructions follow) Open the query Select View:Sql from the Menu Select all the text Copy it Paste it into the message -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Finger Tips" wrote in message news Still getting the #Error in the field "John Spencer" wrote: Typo on my part. Period should be a comma LEFT([YourField] , Instr(1 , [YourField] & "," , ",")-1) Extra spaces added for clarity '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Finger Tips wrote: 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 | |
|
|