Remove all characters after SF

[murphyhg]

I am doing an Access 2003 query and I need to remove all characters after SF. SF appears like this 2,000 - 10,000 SF, I cannot use a counting method to determine the position because SF could appear like this also. 1,000 - 2,000 SF

Thanks for your help.

 

[Gammachaser]

InStr() will get you the position of 'SF' in the string (as long as it is the first occurrence of 'SF' that you are looking for. Left() will strip away the remainder.

Function StripAfterSF(WhatString as String) as String

StripAfterSF = Left(WhatString,InStr(1,WhatString,"SF") +1)

End Function

Of course, you would put in some code to account for zero length strings or a string without 'SF' in it if you are not 100% sure that you have control over the WhatString input.

 

[murphyhg]

Thanks I tried that and I am getting this
Too few parameters. Expected 2.

Here is my code
SELECT Left(TENANT_TBL.STORE_SIZE,InStr(1,TENANT_TBL.STORE_SIZE,"SF") +1)
FROM TENANT_TBL
WHERE TENANT_TBL.DISPLAY = 1
AND TENANT_TBL.IMAGE_FILE = 'image'
ORDER BY TENANT_TBL.TENANT_NAME

 

[Gammachaser]

Well, that was not at all what I was expecting you to use that for...

The code I gave you will work if you get the results set and then apply the function to the Store_Size (e.g. if you are using the results in a form or report. On the form or report, set the bound control's ControlSource to "=StripAfterSF(STORE_SIZE)"

It will also work for a Recordset -- apply the function to the recordset field after the results are returned.

Dim rst as Recordset
Dim sql as String

sql = "SELECT TENANT_TBL.STORE_SIZE " & _
      "FROM TENANT_TBL " & _
      "WHERE TENANT_TBL.DISPLAY = 1 " & _
      "AND TENANT_TBL.IMAGE_FILE = 'image' " & _
      "ORDER BY TENANT_TBL.TENANT_NAME"

Set rst = CurrentDB.OpenRecordset(sql)
rst.MoveFirst
MsgBox StripAfterSF(rst!STORE_SIZE)
rst.Close
Set rst = Nothing

You could also use the function in a query to create a calculated field that would return as part of the dataset.

Someone more experienced than I am might be able to show you a way to make your method work, but I can't think of a way right now.

 

[PWise]

how about

Select TENANT_TBL.STORE_SIZE,
replace(TENANT_TBL.STORE_SIZE,"SF","")
FROM TENANT_TBL
WHERE TENANT_TBL.DISPLAY = 1
AND TENANT_TBL.IMAGE_FILE = 'image'
ORDER BY TENANT_TBL.TENANT_NAME

 

[Gammachaser]

According to the original post, he wants to remove everything after 'SF' -- not the 'SF' itself. Apparently there is more information in the field that he does not want. Replace does not play well with wildcards.

 

[dhookom]

With this SQL (I removed the tablename.) it should work. You are setting an order by a field that isn't in your select list. You are also mixing text delimiters of single and double quotes. I don't know which is correct for your locale.

What do you expect if STORE_SIZE doesn't contain "SF"?

SELECT 
Left(STORE_SIZE,InStr(1,STORE_SIZE,"SF") +1)
        FROM TENANT_TBL
        WHERE DISPLAY = 1
        AND IMAGE_FILE = 'image'
        ORDER BY TENANT_NAME

Duane
Hook'D on Access
MS Access MVP

 

[murphyhg]

I modified the query to read

SELECT Left(STORE_SIZE,InStr(1,STORE_SIZE,"SF") +1) AS R_STORE_SIZE
FROM TENANT_TBL
WHERE DISPLAY = 1

The error that is returned to me is
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

 

[dhookom]

You originally stated "Access 2003 query" and now seem to have added what doesn't look at all like an Access error. Can you please provide important information about the environment this is being run?

We can't see your data, your program, your code, etc.

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

It's straightforward.

Left(YourControl,InStr(YourControl,"SF")-1) will remove everything to the left of SF. Now just concatenate the SF back and you're done.

Left(Forms!Members!Email,InStr(YourControl,"SF")-1) & "SF"

The only potential problem is if there's a possibility of "SF" being in the earlier part of the string as well.

Basically you're trimming everything up to the SF, removing it and everything that follows, and then replacing the SF.

 

[MacroScope]

This is the exact same solution that Gamnmachaser offered, except s/he did it in one step and didn't concatenate, where I did, and Gammachaser put it into a function where I did not. I tested it and I know it works.

Personally, I'd probably just put it into the AfterUpdate event of the control in question so that it would never be a part of the data to begin with, but if the information is needed for other purposes and you want to do the stripping in SQL, do it the easy way. Open a query and put the InStr expression in as an expression in the query and run the query. Once you're satisfied that it works and returns what you want to see, just switch to SQL view and copy the code.