[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2016-11-15
16
Medium Priority
?
96 Views
Last Modified: 2016-11-16
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!
0
Comment
Question by:Dustin Stanley
  • 7
  • 6
  • 3
16 Comments
 
LVL 24

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 1000 total points
ID: 41888929
replace(fieldname, " ", "-")

Open in new window

0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 41888932
And Replace ([YourFieldName],"Scholl","School")

:-)
1
 
LVL 1

Author Comment

by:Dustin Stanley
ID: 41888977
Thanks I will try these tomorrow and see if I can get them to work. Thanks!
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 75
ID: 41888999
To be fair ... I was kidding  - but you will need brackets ([]) around the FieldName ...
1
 
LVL 1

Author Comment

by:Dustin Stanley
ID: 41889917
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
 
LVL 24

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 1000 total points
ID: 41889991
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
 
LVL 1

Author Comment

by:Dustin Stanley
ID: 41890000
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
 
LVL 24
ID: 41890007
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
 
LVL 1

Author Comment

by:Dustin Stanley
ID: 41890010
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
 
LVL 24
ID: 41890013
you're welcome

 I also added to my comment, maybe after you read it ~
0
 
LVL 1

Author Comment

by:Dustin Stanley
ID: 41890014
Nope error just SkuIDURL
0
 
LVL 24
ID: 41890019
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
 
LVL 1

Author Comment

by:Dustin Stanley
ID: 41890076
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
 
LVL 1

Author Closing Comment

by:Dustin Stanley
ID: 41890098
Thanks For The Help!
0
 
LVL 24
ID: 41890113
you're welcome, Dustin ~ happy to help
0
 
LVL 75
ID: 41890676
@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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

612 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