+ Post New Thread
Results 1 to 5 of 5
Office Software Thread, Access form problem in Technical; I'm not sure if this is the right forum for this but I can't find anywhere more appropriate. Our help ...
  1. #1

    Join Date
    Feb 2006
    Location
    South Cumbria
    Posts
    199
    Thank Post
    26
    Thanked 29 Times in 24 Posts
    Rep Power
    22

    Access form problem

    I'm not sure if this is the right forum for this but I can't find anywhere more appropriate.
    Our help desk system uses an Access database. To speed up data entry for small jobs I created a form which allows the tech to enter a comment and close the call without going through the tedious process of opening the job and completing the fields manually. It works beautifully as long as the comment string doesn't contain an apostrophe but if it does it errors.

    The SQL I'm using is "INSERT INTO tblProgress ([NewDate],[JobNo],[Progress],[Inits]) VALUES (Date(), " & JBN & ",' "& Com &" ','" & Idn & "');"
    where Com is the comment string taken from the input form. (the other fields are completed automatically) I know apostrophes are a problem area in SQL but can anyone suggest how I can allow users to use apostrophes without triggering an error.

    The error is, run time error 3075, syntax error (missing operator) in query expression "doesn't work'

    Using 'not working' instead of 'doesn't work' is fine but I can't expect users to know that

  2. #2
    Jamo's Avatar
    Join Date
    Jan 2009
    Posts
    1,350
    Thank Post
    66
    Thanked 175 Times in 147 Posts
    Rep Power
    60
    My thoughts would be parse the string in the text box before the SQL query is run. By parsing the text string you could remove any charachters which are illegal

  3. #3
    tommccann's Avatar
    Join Date
    Jun 2009
    Posts
    252
    Thank Post
    46
    Thanked 39 Times in 31 Posts
    Rep Power
    0
    could i see your database and forms??

    but @Jamo parsing the input would in theory fix the illegal expression

  4. #4

    Join Date
    Feb 2006
    Location
    South Cumbria
    Posts
    199
    Thank Post
    26
    Thanked 29 Times in 24 Posts
    Rep Power
    22
    I've found the answer - replace the " ' " with " ' ' ".
    The actual syntax is Com = Replace(Com, "'", "''").

    Thanks for your comments

    Tom did you want to see the forms etc anyway or were you just interested to help me find the solution
    Last edited by Hecate; 14th December 2009 at 03:17 PM. Reason: additional comment

  5. #5
    tommccann's Avatar
    Join Date
    Jun 2009
    Posts
    252
    Thank Post
    46
    Thanked 39 Times in 31 Posts
    Rep Power
    0
    No id love to see it if you dont mind!!

    pm it me, or upload it here! Thanks

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 0
    Last Post: 25th June 2009, 03:45 PM
  2. [MS Office - 2007] MS Access 07 - Relationships problem
    By edd in forum Office Software
    Replies: 20
    Last Post: 15th May 2009, 06:20 PM
  3. moving form access to sql server
    By RabbieBurns in forum Office Software
    Replies: 3
    Last Post: 27th August 2008, 03:20 PM
  4. My Docs access problem
    By Ste_Harve in forum Windows
    Replies: 2
    Last Post: 30th August 2007, 12:32 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •