ansaurus

Question

Getting around the lack of a Left Trim(string, char[]) function in JET / Access

Answer 1

A:

I'm not sure what context you are running this but this seems to show the parameter order is different and uses double quotes instead of single quotes(I haven't used Access in awhile so maybe it doesn't matter), also try square brackets on column name:

http://www.techonthenet.com/access/functions/string/replace.php

Replace(LTrim(Replace([ADDRNO], "0",  " "))," ", "0")

If that gives the same error just try the replace function by itself to narrow down the problem:

Replace ("alphabet", "a", "e")

If this works then you know the Replace function works, and there is some other issue.

Edit: If it doesn't work at all, then Replace is likely a VBA function available only in the Access application, and is not part of Jet. You could try some combination of Left/Right function and chop the string up, this can get quite ugly. I personally would just iterate over the record set and use C# code to modify the values. Hopefully you don't have such a large number of records that this would be a problem.

AaronLS 2010-07-07 20:42:59

I do not believe you can use replace with ADO http://bytes.com/topic/access/answers/209056-using-replace-function-ado-access-db-visual-basic-6-a

Remou 2010-07-07 20:53:30

Remou is correct. Even just using replace does not work; I still get the undefined function error.

DonaldRay 2010-07-07 21:09:42

Didn't know if you were running the query form C# as well. In that case I'd just do it in C# instead of a query. It probably won't perform much worse than Jet would.

AaronLS 2010-07-07 21:34:19

Why would you assume stepping through a recordset in C# would be slower than a Jet UPDATE command? I think you're nuts to suggest that Jet would perform as slowly, and would like to hear some justification for the suggestion (other than simple garden-variety Jet/Access bigotry).

David-W-Fenton 2010-07-08 02:54:44

@David A loop in C# would typically be slower because it would be no faster than an O(n) operation, whereas database engines have various ways of implementing set based operations in better than linear time. So typically the database engine is faster. How a query scales is not very significant if you are working with small result sets. If you have to embed complex string manipulations into the query, you can start to see the query's performance degrade significantly. So there is a balancing act of whether to have a very complex set based operation, vs a less complex iterative operation.

AaronLS 2010-07-08 17:44:10

So, you agree with me that doing it in C# is likely to be slower than letting Jet do it. Right? Obviously, what matters is what functions are available to you via the interface you are using to interact with your database. I am an Access developer so I never interact with Jet/ACE except from Access so I don't have at my fingertips a list of which functions Jet/ACE supports via ODBC/OLEDB. If Val() is supported, then that's clearly the easiest way to do it.

David-W-Fenton 2010-07-08 20:01:34

Answer 2

A:

I think it's just the order of your parameters that is wrong:

debug.? Replace("My string", "i", "o")   -> "My Strong"

iDevlop 2010-07-07 20:43:12

Seems like AaronLS was faster than me :-/

iDevlop 2010-07-07 20:44:25

Now, that is weird. You are correct in that the parameters are wrong, but it seems the Replace function works from within Access. However, I am still getting an error that says Replace is an undefined function when using ADO.NET

DonaldRay 2010-07-07 21:10:55

Answer 3

A:

You can use Trim and Replace.

Jeff O 2010-07-07 20:45:25

Answer 4

+3 A:

I suggest

Val(ADDRNO)

It will return the number portion without the leading zeros.

Remou 2010-07-07 21:39:46

Certainly this is the simplest solution if the data is nothing but numbers with leading zeros.

David-W-Fenton 2010-07-08 02:57:44

This did it for me. I guess I was lucky they used a zero for padding; I pity the person who has to deal with nonwhitespace, nonzero padding. Aside from a few data issues ('00N/A'), everything works well. Thanks a ton!

DonaldRay 2010-07-08 12:44:27

ansaurus

tags:

views:

answers:

Getting around the lack of a Left Trim(string, char[]) function in JET / Access

related questions