MS Access Replace Spaces In Title With Characters Like (-) Dashes In a New Field

I am wanting to keep track of my Products Website page URL. My website uses the products title as the URL extension.

Example:
Website name = www.Store.com

Product Name = Yellow Big Toy School Bus

Website URL Complete = www.Store.com/Product/ Yellow-Big-Toy-Scholl-Bus/


So how can I do this the easy way and concatenate "www.Store.com/Product/" & "Title with Dashes" & "/"

I know how to concatenate but I have to replace them spaces?

Thanks!
LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
replace(fieldname, " ", "-")

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
And Replace ([YourFieldName],"Scholl","School")

:-)
1
 
Dustin StanleyEntrepreneurAuthor Commented:
Thanks I will try these tomorrow and see if I can get them to work. Thanks!
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
To be fair ... I was kidding  - but you will need brackets ([]) around the FieldName ...
1
 
Dustin StanleyEntrepreneurAuthor Commented:
Yes Joe that was a perfect LOL ;)
Took me a minute but its those little errors that can mess Access all up!

Ok I am at a loss. With the parenthesis I get a Compile Error expected: =

Without the Parenthesis I get nothing to replace the spaces. I have tried with brackets without brackets. Calling a public sub.

I am trying to do this on an AfterUpdate on a Control called SkuNm and then get the other Control SkuIDURL to update correctly. It shows and updates the URL but still has spaces! These below is what I have tried. Plus more.

Private Sub SkuNm_AfterUpdate()
Me.SkuIDURL.value = "htt p:// Store .com/index.php/product/" & (Me.SkuNm.value) & "/"
'Replace Me![SkuIDURL].value," ","-"
'Replace Forms!frmSkusEntry!SkuIDURL.value, " ", "-"
'Replace [Forms]![frmSkusEntry]![SkuIDURL].value, " ", "-"
'Call URLSpaceCorrection
End Sub

**I broke the link up above on purpose.

Thanks for the help!
0
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
Joe, Dustin doesn't have spaces or special characters in his fieldnames so brackets aren't needed

Hi Dustin,

you have extra spaces in the url but that would not be causing a compile error. I think the fieldname/controlname for url might be wrong so I susbstituted url_controlname (you will need to look this up and change it). Trim removes leading and trailing spaces from the value.

try:
if not isnull(Me.SkuNm.value) then
   Me.url_controlname = "http://Store.com/index.php/product/" _
      & replace( trim (Me.SkuNm.value) ," " ,"-" ) & "/" 
end if

Open in new window

0
 
Dustin StanleyEntrepreneurAuthor Commented:
Nevermind I got it! Does this look good or is there a better way? Thanks for the help!

Me.SkuIDURL.value = "htt p://Store .com/index.php/product/" & (Me.SkuNm.value) & "/"
Me.SkuIDURL.value = Replace(Me.[SkuIDURL].value, " ", "-")

Open in new window

0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
better to do it all in one step. Also test SkuNm first to make sure it is filled out -- try the code I gave you --  you still have spaces -- there should not be any.

You are referencing 2 names: SkuIDSurpURL and SkuIDURL. This field should be called SkuURL in the table design -- so
1. close the form
2. fix that and change the Control Source and Name for that control to match the field name
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Ok Crystal thanks. The spaces were on purpose so there isn't a true link generated here in the forum to some random site.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome

 I also added to my comment, maybe after you read it ~
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Nope error just SkuIDURL
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
I would remove ID from the fieldname to be consistent. When ID is in a fieldname, that implies it is a Long Integer (or AutoNumber). At least that is what we did when designing your tables. This field is Text.

> "SkuIDSurpURL and SkuIDURL"

I see I am not the only ones who edits posts! You fixed that ...
0
 
Dustin StanleyEntrepreneurAuthor Commented:
I don't know why the single step one wasn't working. It just kept adding on to the existing sting value. But this one is working perfect for me.

Private Sub SkuNm_AfterUpdate()
If Not IsNull(Me.SkuNm.value) Then
 Me.SkuNm.value = Trim(Me.SkuNm.value)
  Me.SkuIDURL.value = "http://Store.com/index.php/product/" & (Me.SkuNm.value) & "/"
   Me.SkuIDURL.value = Replace(Me.[SkuIDURL].value, " ", "-")
   Else
   Exit Sub
   End If
End Sub

Open in new window


I have the ID in there because it is the URL to that specific SkuID. Which I can change it.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thanks For The Help!
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, Dustin ~ happy to help
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
@Crystal ... I thought it was a field name in a query, in which case you would need brackets around it, spaces or not.
But since it's a value on a Form, then no.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.