Avatar billede mohnsen Praktikant
02. juli 2010 - 13:55 Der er 13 kommentarer og
1 løsning

Fjerne specielle tegn via forespørgsel

Hejsa

Jeg har nogle varenumre i access der har forskellig opbygning

f.eks.:

xxx-xxx
xxxxxxxx
xxx-xxx/xx

hvor "x" er tal fra 0-9

Jeg er interesseret i at i en query at kunne se varenumrene uden de specielle tegn som "-" og "/"

Så resultatet bliver
xxxxxx
xxxxxxxx
xxxxxxxx

Varenumrene står i samme kolonne uanset hvilken af formateringerne de kører efter, så forespørgslen skal kunne dække alle 3 typer.

Jeg bruger access 2007

Mvh Mohnsen
Avatar billede terry Ekspert
02. juli 2010 - 14:26 #1
You can use the replace function to remove these characters.

replace("xxx-xxx","-","")
Avatar billede terry Ekspert
02. juli 2010 - 14:34 #2
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
Avatar billede mohnsen Praktikant
05. juli 2010 - 14:56 #3
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.
Avatar billede terry Ekspert
05. juli 2010 - 15:26 #4
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?
Avatar billede mohnsen Praktikant
05. juli 2010 - 15:44 #5
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
Avatar billede terry Ekspert
05. juli 2010 - 15:47 #6
any chance of seeing a little example dB?

ekspertenATsanthell.dk

AT = @
Avatar billede mohnsen Praktikant
06. juli 2010 - 16:09 #7
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
Avatar billede terry Ekspert
06. juli 2010 - 18:51 #8
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!
Avatar billede mohnsen Praktikant
06. juli 2010 - 19:55 #9
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
Avatar billede terry Ekspert
06. juli 2010 - 20:37 #10
: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"
Avatar billede mohnsen Praktikant
06. juli 2010 - 21:45 #11
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.
Avatar billede terry Ekspert
07. juli 2010 - 09:15 #12
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.
Avatar billede mohnsen Praktikant
09. juli 2010 - 06:26 #13
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"
Avatar billede terry Ekspert
09. juli 2010 - 09:34 #14
"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.
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Dyk ned i databasernes verden på et af vores praksisnære Access-kurser

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester