Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • 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
 
NorieData ProcessorCommented:
You need to enclose the values in single quotes, just as you have for the STATUS field.
0
 
NorieData ProcessorCommented:
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
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!

 
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
 
NorieData ProcessorCommented:
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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

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