• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

Need help formatting a string from a text box

I have a form which displays the name of the Holiday and I wish to use that name to update a field in another field in another table.  However the way that I have written my code it errors out on the fact that there are spaces in the name of the Holiday, such as "Washingons Birthday" or "Fourth of July".  It would not be a problem if it stripped out the spaces and inserted the name without the spaces but I am not sure of the function to call to do that.  I am using Access 2007 in an ADP format and this is only part of the query.  I have been able to get it functional to this point.

Where it is failing is when I reference Left([Forms]![Holiday]![Holiday],25)
strSQL = " Update AdjustedSchedule"
    strSQL = strSQL & " Set AdjustedSchedule.HOLADJUST =" & [Forms]![HOLIDAY]![DOW] & ","
    strSQL = strSQL & " AdjustedSchedule.HOLIDAY =" & Left([Forms]![HOLIDAY]![HOLIDAY], 25) & ","
    strSQL = strSQL & " AdjustedSchedule.STATUS = 'Holiday';"
    DoCmd.RunSQL (strSQL)

Open in new window

0
JasBrad
Asked:
JasBrad
  • 3
  • 2
  • 2
2 Solutions
 
NorieVBA ExpertCommented:
You need to enclose the values in single quotes, just as you have for the STATUS field.
0
 
NorieVBA ExpertCommented:
Oops, forgot the code.

strSQL = " Update AdjustedSchedule"    
strSQL = strSQL & " Set AdjustedSchedule.HOLADJUST ='" & [Forms]![HOLIDAY]![DOW] & "',"    
strSQL = strSQL & " AdjustedSchedule.HOLIDAY ='" & Left([Forms]![HOLIDAY]![HOLIDAY], 25) & "',"   
strSQL = strSQL & " AdjustedSchedule.STATUS = 'Holiday';"    DoCmd.RunSQL (strSQL)

Open in new window

0
 
borkiCommented:
I think you are missing the string delimiters. Try

strSQL = " Update AdjustedSchedule"
    strSQL = strSQL & " Set AdjustedSchedule.HOLADJUST =" & [Forms]![HOLIDAY]![DOW] & ","
    strSQL = strSQL & " AdjustedSchedule.HOLIDAY = '" & Left([Forms]![HOLIDAY]![HOLIDAY], 25) & "',"
    strSQL = strSQL & " AdjustedSchedule.STATUS = 'Holiday';"
    DoCmd.RunSQL (strSQL)

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
JasBradAuthor Commented:
That worked but one other question.  How do I get it to ignore apostrophes in the name? or is that not possible?
0
 
borkiCommented:
You would need to parse the string before hand and replace any instance of a single apostrophe with two apostrophes, eg

replace(string, "'", "''")

or

Left(Replace([Forms]![HOLIDAY]![HOLIDAY], "'", "''"), 25)

Very hard to read ;-)
0
 
NorieVBA ExpertCommented:
borki

You can always use Chr(39).

Replace(string, Chr(39), Chr(39) & Chr(39))

Open in new window

Or, if you are using it a lot declare a constant.
Const SQ = "'"  ' Single Quote

Open in new window

Still quite messy though.:)
0
 
JasBradAuthor Commented:
Thanks!!
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now