Replacing Wildcard Characters in a query

[DaveBerube]

First post. Tried my best to search of the topic, but no luck:

I am trying to replace a # character and all characters after in a text field.
U set up a Select query and used the following expression:
Expr1: Replace([Part Number],"[#]*","")

It doesn't change any of the records. Is this possible to do in Access 2007?
I'd rather not go into VBA, but am willing with enough direction. Just assume I'm an idiot

 

[way2bord]

Replace() doesn't work with wildcards. Maybe try regular expressions.

 

[DaveBerube]

What do you mean regular expressions?

 

[way2bord]

Regular expressions is used to manipuate text. But it has a bit of a steep learning curve.

Try this instead:
Left([Part Number],instr(1,[Part Number], "#") - 1)

 

[DaveBerube]

Does that work in a select query interface or do I need to go into SQL view?

I'm not getting non wildcards to work so I'm pretty sure I'm just doing something stupid.
I can't post screen shots, but I'm putting the expression in the field row of the query interface and using a select query.

I think my problem is my imagination is more advanced than my programming skills(Not necessarily saying much). The instructions for what I'm trying to do are never at a low enough level.

 

[way2bord]

Does that work in a select query interface or do I need to go into SQL view?

I'm not getting non wildcards to work so I'm pretty sure I'm just doing something stupid.
I can't post screen shots, but I'm putting the expression in the field row of the query interface and using a select query.

I think my problem is my imagination is more advanced than my programming skills(Not necessarily saying much). The instructions for what I'm trying to do are never at a low enough level.

Just drop it into the query interface and take a look to see if its what you want.

 

[billmeye]

Here is a function that you could use:

Public Function ReplaceText(strTEXT As String) As String
      
      Dim txtlen, CheckChr, Counter As Long, CurChar, NewText As String

20    Counter = 0
30    txtlen = Len(strTEXT)

40    Do While Counter < txtlen
50    Counter = Counter + 1
60    CheckChr = Mid(strTEXT, Counter, 1)
70    Select Case CheckChr
      Case "[", "#", "]", "*"
      'remove character
90    strTEXT = Left(strTEXT, Counter - 1) & Right(strTEXT, txtlen - Counter)
100   txtlen = Len(strTEXT)
110   Counter = Counter - 1
120   End Select
130   Loop
140   ReplaceText = strTEXT
End Function

You could also add characters to remove or you can add characters to do something else (like replace with something else) by adding additional Case and code.

 

[DaveBerube]

Just drop it into the query interface and take a look to see if its what you want.

Sorry to be such a pain, but how do I 'drop' it into the query interface?

Does it go in the field row as an expression in a select query or in the 'update to' row of an update query?

 

[DaveBerube]

I can't post pictures yet, but I guess I can post the SQL.

This is how my orginal request looked in SQL. I'm pretty sure I must be putting it in the wrong place or something. Seems it should be an update Query, but that's not what the instructions I found showed.

SELECT Replace([Field1],"[#]*","") AS Expr1
FROM Table1;

:banghead:

 

[billmeye]

SELECT ReplaceText([Field1]) AS Expr1
FROM Table1;

 

[way2bord]

If you use the function (requires you to add the code of the function above) provided by billmeye - use

SELECT ReplaceText([Field1]) AS Expr1
FROM Table1;

If you want to try my quick fix - use

SELECT Left([Field1],InStr(1,[Field1],"#") - 1) AS Expr1
FROM Table1;

 

[DaveBerube]

After typing up a whole response explaining how that didn't work and why I didn't understand it I guess I explained it to myself. Now I even understand what Bill was saying. It was an addition to replace the the field once I defined the expression.

What I ended up with was doing an update query and droping the Left() function into the update to row. Works good now. Thanks:


UPDATE Field1 SET Table1.[Field1] = Left([Field1],InStr(1,[Field1],"#")-1)
WHERE (((Table1.[Field1]) Like "*[#]*"));