Deleting Spaces - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Deleting Spaces

Author

Topic

dsmith1000
Starting Member

6 Posts

Posted - 2001-11-27 : 09:31:26

I hope you guys can help me out here....
Got a little problem. Running ASP on Win NT 4.0 with IIS4, Access 2000. Got this query -

"SELECT [offices].[officeName]
FROM offices
WHERE offices.officeName Not In (SELECT office FROM dailyFlash Where fDate = [todayDate];)
ORDER BY [office];"

Using this query to get all the offices that have not placed a daily flash report into the system. How do I take out the spaces in the office name in both the main query and the subquery and compare that data? (For some reason the space goes away when I am updating with some office names. Therefore the need to compare without the spaces.)

I have tried this approach -

"SELECT [offices].[officeName]
FROM offices
WHERE REPLACE(offices.officeName,"" "","""") Not In (SELECT REPLACE(office,"" "","""") FROM dailyFlash Where fDate = [todayDate];)
ORDER BY [office];"

but I am getting -

Microsoft JET Database Engine error '80040e14'

Undefined function 'Replace' in expression.

/flashreport/admin/adminPage.asp, line 105

Line 105 is where the statement is executed.
Any ideas on what I can do? I can't find any documentation on a replace function in SQL unfortunately.

Thanks,
dave

izaltsman
A custom title

1139 Posts

Posted - 2001-11-27 : 09:52:32

I believe Access has SUBSTITUTE function that you can use instead of REPLACE... And if you only need to get rid of leading/trailing spaces (and keep the ones in the middle of office names), you can use TRIM function.

dsmith1000
Starting Member

6 Posts

Posted - 2001-11-27 : 10:22:56

Appreciate the input. I tried -

SELECT [offices].[office]
FROM offices
WHERE SUBSTITUTE([offices].[office]," ","") Not In (SELECT SUBSTITUTE([office]," ","") FROM dailyFlash Where fDate = [flashdate];)
ORDER BY [office];

-but I am getting an "Undefined Function 'SUBSTITUTE' in expression" error. Is there something I am missing? I tried single quotes instead of the doubles with the same results.

Thanks for the help,
dave

izaltsman
A custom title

1139 Posts

Posted - 2001-11-28 : 11:11:04

Umm... You're right. Access does not seem to recognize SUBSTITUTE or REPLACE. I believe there is a way to do this, but it's been too long since I've used Access... If you need a quick workaround, I believe that in your Access database you can create a module with a VBA function like the one below (replace function is definitely available in VBA) This should allow you to reference this function in your queries (e.g. WHERE my_replace([offices].[officeName]) NOT IN ...)

VBA function would look something like this:

Function my_replace(srcText As String) As String
my_replace = Replace(srcText, " ", "")
End Function

Hopefully someone will suggest a better way though...
Good luck!

mono
Starting Member

36 Posts

Posted - 2001-11-28 : 15:31:54

You can find the first space using:
nz(instr(0, [office], " ")
Call that sp1.

'Remove' it using:
left([office], sp1 - 1) & right([office], len([office]) - sp1)
Call that spr1. This will work even if [office] is null or has no spaces.

You can remove the second space using:
nz(instr(sp1, spr1, " ") as sp2
left(spr1, sp2 - 1) & right(spr1, len(spr1) - sp2)

If you can live with the assumption that there won't be more than N (you choose a value) spaces then you can generate an expression in the manner above to remove up to N spaces.

It has the advantage of not requiring a custom function. That's about the only advantage.

You can nes the calculations to have only a single (large!) expression but that would cause repeated calculation of subexpressions and the stripping would become at least quadratic in N.

In Access you can reuse calculated values in other columns, which avoids recalculating expressions, as you would do with 2 columns (one for the int and one for the string) per possible-space-to-be-removed and 2 strings to do it to ([office] and [officeName]) you need 4N columns.

Better long term solutions include building a table of aliases and maintaining it or avoiding the problem by centralising the office name entry and enforcing relationships.

dsmith1000
Starting Member

6 Posts

Posted - 2001-11-29 : 14:59:26

I am new at writing modules. So naturally I am struggling. But then I sometimes struggle at things I know well... :(

Anyway, I added a module, which pulled the VB editor. I typed in the code -

Function SUBSTITUTE(srcText As String) As String
SUBSTITUTE = Replace(srcText, " ", "")
End Function

Then I saved it. And it shows up in the Modules in Access, named SUBSTITUTE. Next I changed my query to read -

SELECT [offices].[office]
FROM offices
WHERE SUBSTITUTE([offices].[office]) Not In (SELECT SUBSTITUTE([dailyFlash].[office]) FROM dailyFlash Where fDate = [flashdate];)
ORDER BY [office];

There is probably something else I need to do somewhere, but don't have a clue what or where. I am still getting the error "Undefine function 'SUBSTITUTE' in expression".

Thanks for the help,
dave

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-11-29 : 15:05:22

I don't think you can use the Access module function in the middle of your SELECT statement like this. I think you'll need to build your SELECT statement as a string, referencing the SUBSTITUTE function outside of the string to insert values. Something along the lines of

sSQL = "SELECT [offices].[office] FROM offices WHERE " & SUBSTITUTE([offices].[office]) & " Not In (SELECT " & SUBSTITUTE([dailyFlash].[office]) & " FROM dailyFlash Where fDate = [flashdate];)
ORDER BY [office];"

-------------------
It's a SQL thing...

izaltsman
A custom title

1139 Posts

Posted - 2001-11-29 : 15:48:31

Crap... Access allows using VBA functions in its queries internally (if you cut and paste your SQL code into access query window -- it will run), but apparently it does not allow this for queries submitted via either OLEDB or ODBC.

My bad... And as always -- Mark is right...

dsmith1000
Starting Member

6 Posts

Posted - 2001-11-30 : 09:54:45

I am accessing the query from ASP, but the query is in the database. I just call the query and pass it the parameter. But I am still getting the error when I try to open the query in Access. Here is the ASP code -
<%
Set CN=Server.CreateObject("ADODB.Connection")
CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../flash.mdb") & ";"
Set RS = Server.CreateObject("ADODB.Recordset")
SQL = "getNoData " & WeekDay(date)
%>
From that I get the recordset of the locations that have not reported. Anyway, the query is in Access - any idea why the module is not working? It is the first one I have ever made, so if there is anything obvious I probably still need to know it. :)

Appreciate all the help guys,
dave

mono
Starting Member

36 Posts

Posted - 2001-11-30 : 12:02:35

I don't think you can run VBA function from Access queries with Access via ODBC/ADO etc. When running such queries through Access, you have the use of VBA because Access loads the relevant DLLs.
When using ODBC et al, you are connecting to the JET engine and nothing else. I don't see how (or why) the JET engine would load the VBA dlls. They may not even be on the server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources