Fjerne specielle tegn via forespørgsel

You can use the replace function to remove these characters.

replace("xxx-xxx","-","")

You could remove all charcters with something like

Replace(Replace([FiledNameInTable], "-", ""), "/", "")



Or a query it might be an idea to make a function which can replace all the "special" characters at the same time.


Function MyReplace(s As String) As String

MyReplace = Replace(Replace(s, "-", ""), "/", "")

End Function

Then call function in query


SELECT MyReplace([FiledNameInTable]) as varenumre FROM YourTable

Terry you are simply the best.

No matter what kind of problem people has, who come up with a solution - thanks man.

This solution however gives me a problem:

I have an excel file that has a direct link to that access database, and when I press "Update" I get the response:
(Der er en ikke-defineret funktion "replace" i udtrykket)

Jeg har været ude for det samme når jeg forsøger med NZ funktionen.

Hvis du (eller en anden) ved hvordan man klarer dette, så sig venligst til.

Men Terry, der er point til dig.

Thanks for the kind words :o)

Are you saying that you can get Replace to work but not when trying to update a linked Excel file?

Which version of Access are you using, I seem to rememeber that REplace wasnt available in earlier version of Access.

Can you give a bit more info please?

Yes that´s what im saying.

I have 2 different computers on which a work with access and excel, and I have the issue on both.

I have access and excel 2007 installed

any chance of seeing a little example dB?

ekspertenATsanthell.dk

AT = @

Hej Terry

Tak for at du kiggede på min database.

Jeg har lavet et 2work-around med nogle IIf og left og mid funktioner, der giver mig samme resultat.

Smid et svar

Mohnsen

svar

I dont understand why you need to use iif and left etc.

Convert should work in Access, also the danish version I am sure!

Convert??

Mener du her replace?

Oplevede du ikke samme problem, med at excel ikke kunne opdatere fra access datakilden pga. at jeg havde brugt access funktionen replace/erstat

:o)
Yes Replace


"Oplevede du ikke samme problem, med at excel ikke kunne opdatere fra access datakilden pga. at jeg havde brugt access funktionen replace/erstat"

I dont think we are understanding each other 100% :o(

You said in a mail
"It´s not in Excel that I´m using the replace function. The function runs in access, but I want to see the result i excel, by getting the data from the access file, as in the files I send you.

So I dont understand this, which says (I think) that you are trying to update in Excel doesnt it?

""Oplevede du ikke samme problem, med at excel ikke kunne opdatere fra access datakilden pga. at jeg havde brugt access funktionen replace/erstat"

When you open the excel file I send you

if you right click on the table that is inside the excel file, and choose "opdater", then you will see an error report saying (Der er en ikke-defineret funktion "replace" i udtrykket)

The data in excel that you are trying to update comes from the access database that I also send to you.

The reason why excel fails to update, is because I have used the function replace in the access query from which excel gets it´s data.

I think that we are seeing the same things, and are talking about the same things, just using different words. :-)

Now I know that you also suggested some other sollutions, but the files I send you was an example of cause, and what I use in real life is more complex, and demands that I do it like this.

I understand the problem you get.

The query which is in Access only shows the data (in Access) it doesnt actually update. When you open the query in Excel the SQL actually runs in Excel NOT in Access. Access is only a flat file which gets read by Excel. If it were a database server then the SQL would execute in the server and return the data (converted).

But because Access is a flat file then Excel reads the SQL from Access and then tries to execute it (in Excel). But Replace isnt a valid "Excel" SQL so it cant.

Using IIF and left etc will only work if the special characters are always in the same position, replace can do it anywhere.

Not knowing your full requirement obvioulsy doesnt help me give the best solution.

But hopefully you have something which works now.

hmm. OK.

I always asumed that the queries was run by access, and merely the results was transferred to excel through "update".

To me this just seems like bad programming from microsoft:

they make a function in excel called "get data from access"

But you can´t get data if your access file has used certain functions, and they don´t explain which functions that you can´t use, and what to use instead.

The function in excel should rather be called
"get data from access, if you´re lucky"

"To me this just seems like bad programming from microsoft:"

Well that depend on how you look at it.


Try thinking of it like this. Access isnt running in the background so it cant do anything. Excel can get the data as it is in the table just like reading a file and then using its own functions to convert it.

Its actually the same with Access if it gets data from an SQL Server. Lets say "SELECT * FROM tbl1 WHERE ID = 1"

This selects ALL records and returns them to Access. Then Access goes through them one by one to see if ID = 1.
Access uses indexes if they exist so it finds what it wants then it stops reading the rest of the records.

In Access its possible to use something called pass-through queries. This sends the SQL to the server and the SQL gets executed at the server and only the result is returned. When using pass-through queries the SQL must conform to the rules of the server (SQL Server syntax).

This is a bit like when you try selecting records from access from Excel. Excel reads the SQL from Acess and decides if it is has a syntax it understands. If it understands it then it can execute the SQL (again inside Excel).
So in this case the SQL syntax must be Excel compliant.