how to rea this access text type

[henryhandle]

After I was in wrong forum try it here again
I have this colum, ordernumber
(type is Text) with values like this:
\_\14053
11115
\_\14092
1010
if i read with select ordernumber from ordertable
I get a data type error !
With this query, which has been recommended in other forum:
Select Replace(Replace([JobNumber],'\',''),'_','') from tablexxx

I get
ERROR: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Undefined Function ‘Replace’

I
How could be for example only the 14053 read?
(ps: it is a servlet, which read from database)
Thanks

 

[Crowley16]

so, you are running this sql statement from a servlet, and not within access itself?

the error message suggests that you've not linked some of the access base libraries.

if this is the case, and you have to run this from outside of access, then you'd have to construct your sql statement to parse out the odd symbols without using custom functions.

--------------------
Procrastinate Now!

 

[henryhandle]

I cant run this statement from access too!!!.
In access a becomm the same Error (undefined function)

 

[Crowley16]

in that case, check your references...

Alt+F11 to open vba ide
Tools -> References

I think the replace command is in the standard VBA library, so make sure that's ticked.

--------------------
Procrastinate Now!

 

[PHV]

Depending of the version of access you may have to code your own replace UDF (User Defined Function) in a standard code module:

Public Function myReplace(myString, myFind As String, myRepl As String)
If Len(Trim(Nz(myString & ""))) > 0 Then
  myReplace = Replace(myString, myFind, myRepl)
End If
End Function

Anyway if you want to get rid of the noisy characters in your table:
UPDATE tablexx SET ordernumber = Mid([ordernumber],4) WHERE ordernumber Like '\_\*';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[henryhandle]

thanks,
P1) there is no tools -> pereference
only tools -> options
and under Options i didnt find somewhat
to has relation with library !!
p2)select comic_colum from comic_table where comic_colum = 12345
how can i compare the valus of this colum(f.e. \_\14053)with number (12345) befor i replaced something?
What you recommand in this case means, that i should replace and than
run the sql staemeent to compare

3) the company, where i'm working wo'nt to update
this comic table!

 

[Crowley16]

no, I said, open the VBA IDE, by typing ALT+F11 that's where the references would be...

and that's where you'd need to put any custom functions as well...

--------------------
Procrastinate Now!