Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

using * wildcard in vba access replace function

Posted on 2013-07-01
8
Medium Priority
?
8,573 Views
Last Modified: 2016-11-20
hey guys, i've got this

Replace(working.xls, ".xl*", "")

but it doesn't work. could yall help me out? i read somewhere the people use regexp for this but i wanna try and stay away from it at the moment (cause will need to add references etc)
0
Comment
Question by:developingprogrammer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 21
ID: 39292284
If you are wanting to strip off the file extension  then try this:

? Left("working.xls", Instr("working.xls",".")-1)
working

? Left("working.xlsx", Instr("working.xlsx",".")-1)
working

Open in new window


In a query you could use:

Left([FileNameField], Instr([FileNameField],".")-1)

Open in new window

0
 

Author Comment

by:developingprogrammer
ID: 39292299
Yup yup definitely good but I'm trying to understand why the wildcard * isn't working and what corollaries it has = )
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 1000 total points
ID: 39293517
The Replace() function in VBA does not support wildcards or pattern matching in the string for the  Find parameter.

Unfortunately, you will need to use regexp for pattern matching.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1000 total points
ID: 39294273
I would make one very small change to TheHiTechCoach's approach:

Left([FileName], InStrRev([FileName], ".") - 1)

That way your expression is successful even if the file name itself has periods in it (besides the period that delimits file name and extension).

As to a RegExp approach:

1) Go to my article http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html

2) Add this function to a regular VBA module:

Function RegExpReplace(LookIn As String, PatternStr As String, Optional ReplaceWith As String = "", _
    Optional ReplaceAll As Boolean = True, Optional MatchCase As Boolean = True, _
    Optional MultiLine As Boolean = False)
    
    ' Function written by Patrick G. Matthews.  You may use and distribute this code freely,
    ' as long as you properly credit and attribute authorship and the URL of where you
    ' found the code
    
    ' This function relies on the VBScript version of Regular Expressions, and thus some of
    ' the functionality available in Perl and/or .Net may not be available.  The full extent
    ' of what functionality will be available on any given computer is based on which version
    ' of the VBScript runtime is installed on that computer
    
    ' This function uses Regular Expressions to parse a string, and replace parts of the string
    ' matching the specified pattern with another string.  The optional argument ReplaceAll
    ' controls whether all instances of the matched string are replaced (True) or just the first
    ' instance (False)
    
    ' If you need to replace the Nth match, or a range of matches, then use RegExpReplaceRange
    ' instead
    
    ' By default, RegExp is case-sensitive in pattern-matching.  To keep this, omit MatchCase or
    ' set it to True
    
    ' If you use this function from Excel, you may substitute range references for all the arguments
    
    ' Normally as an object variable I would set the RegX variable to Nothing; however, in cases
    ' where a large number of calls to this function are made, making RegX a static variable that
    ' preserves its state in between calls significantly improves performance
    
    Static RegX As Object
    
    If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = ReplaceAll
        .IgnoreCase = Not MatchCase
        .MultiLine = MultiLine
    End With
    
    RegExpReplace = RegX.Replace(LookIn, ReplaceWith)
    
End Function

Open in new window


3) Use it in an expression like this:

FileNameNoExt = RegExpReplace(FileName, "\.[A-Za-z0-9]+$")
0
 
LVL 21
ID: 39294404
This appeared to be a hypothetical question and the Replace function  not a specific question about working with File Names.   I agree with  matthewspatrick that the change suggested would be good is you were actually processing file names and/or wanting to only strip something off the end of a string.


@ matthewspatrick,  thanks for jumping in and posting the sample code.
0
 

Author Comment

by:developingprogrammer
ID: 39295405
whao guys, super cool. ok let me read this more in depth to give a response befitting what yall have shared with me! = )
0
 

Author Closing Comment

by:developingprogrammer
ID: 39305524
whao guys, fantastic! matthewspatrick i have yet to go through your regexp article in full but definitely lots of stuff i gotta learn from there.

TheHiTechCoach thanks for picking out what i was looking for! thanks guys!! yall are the best!! = ))
0
 

Expert Comment

by:gary franc
ID: 41895077
Below is a function I created that performs wild card replacement of characters.
The TestReplaceWild subroutine can be used to test various replacement strings.
In the example below, the for the given string "test this out (12.4445555%$$$ wgfw///)(ft))" , The returned string is "test this out RR)" for removing characters starting with the first "(" found and ending with the first "ft)" found after the "(". Note that the replace string RR is added for the characters removed.

Sub TestReplaceWild()
Dim s, SStrg, FStrg, LStrg, RStrg
SStrg = "test this out (12.4445555%$$$ wgfw///)(ft))"
FStrg = "("
LStrg = "ft)"
RStrg = "RR"
s = ReplaceWild(SStrg, FStrg, LStrg, RStrg)
Debug.Print SStrg, FStrg, LStrg, RStrg, s
End Sub

Function ReplaceWild(SStrg, FStrg, LStrg, RStrg)
'
'replace SStrg by removing chars FStrg and LStrg, including all chars between them and adding chars RStrg.
'
'
Dim Mess As String, s As String, Strg As String, i As Integer, j As Integer, c As String
'
' check paramters
'
s = "ReplaceWild Function Errors"
On Error GoTo er
c = CStr(SStrg)
c = CStr(FStrg)
c = CStr(LStrg)
c = CStr(RStrg)
GoTo Cont
er:
Mess = "Please Check Parameters (SStrg, FStrg, LStrg, RStrg). Each Item Needs To Be Passed As A Character String."
MsgBox (Mess)
GoTo done
'
Cont:
On Error GoTo done
Strg = Trim(SStrg)
s = Strg
i = InStr(1, Strg, FStrg, 1)
If i <= 0 Then GoTo done
'
' if FStrg not found, exit without making changes
'
j = InStr(i + 1, Strg, LStrg, 1)
'
' remove and insert chars
'
s = Left(Strg, i - 1) & RStrg
If (j > 0) Then s = s + Mid(Strg, (j + Len(LStrg)))
'
done:
ReplaceWild = Trim(s)
End Function
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question