Find spaces in a field in MS Access database

I need to find fields with spaces in them. It is where someone has hit the space bar when in a field . Can you give me a query that would search for this. Table name posstock and field is matrix1. They cannot be found unless you click in that field
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Máté FarkasDatabase Developer and AdministratorCommented:

SELECT * FROM posstock WHERE matrix1 LIKE '* *'

Open in new window

oldmillsaddleryAuthor Commented:
Thanks this works but it also shows fields that have word like" horse saddle" as they have a space also. Is there any way to exclude anything with a entry other than a space  
Rey Obrero (Capricorn1)Commented:
how about updating your table fields to remove space/s?

update posstock
set matrix1=trim([matrix1])
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Máté FarkasDatabase Developer and AdministratorCommented:
row that contains only one or more spaces:
SELECT * FROM posstock WHERE Trim(matrix1) = '' AND matrix1 <> ''

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick MatthewsCommented:

If you are concerned just about leading or trailing spaces, then cap1's SQL is perfect for you.

If you also need to trim "extra" spaces between words (i.e., internal instances of 2+ consecutive spaces should be cut down to 1 space), I like to use Regular Expressions to handle that.  For a fuller treatment, please see my article at

Please add the code below to a regular VBA module, and then run a query like this:

UPDATE SomeTable
SET SomeField = Trim(RegExpReplace(SomeField, " {2,}", " "))
WHERE SomeField Is Not Null


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

oldmillsaddleryAuthor Commented:
Well done excellent solution
Patrick MatthewsCommented:

Pleased to help.

If you wouldn't mind, if you liked that article I linked to, please go to that article's page, and click 'Yes' for 'Was this article helpful?'


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.