Re: Append Query help

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Assuming that, say, submit_type is a text field, rather than

"," & Me![CboSubmittype] & ","

you'd use

",'" & Me![CboSubmittype] & "',"

Exagerated for clarity, that's

", ' " & Me![CboSubmittype] & " ' ,"

That'll work unless the value to be stored contains an apostrophe. In that
case, you need either

",""" & Me![CboSubmittype] & ""","

or

",'" & Replace(Me![CboSubmittype], "'", "''") & "',"

(Again, for clarity, that Replace function call is
Replace(Me![CboSubmittype], " ' ", " ' ' "))

Note that transfer_date is a date field, you need to delimit the value with
#, and you need to ensure that it's in a format that Access will correctly
interpret. Since you have no control over what date format your users may
have chosen in their Regional Settings, and since Access doesn't respect
many of the Regional Settings choices, you're best off using

"," & Format(Me![txtTransDate], \#yyyy\-mm\-dd\#") & ","

if txtTransDate contains a date only, or

"," & Format(Me![txtTransDate], \#yyyy\-mm\-dd hh\:nn\:ss\#") & ","

if it contains both a date and time.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"cableguy47905 via AccessMonster.com" <u4349@uwe> wrote in message
news:7708361696068@xxxxxx
Thank you both very much for your quick replies. I am having trouble
adding
the single quotes to the string. Each time I do, it thinks that the rest
of
the string is just comments. How do I add the single quotes to the VBA
string?

This is what I currently have that creates the string below:
stMapSQL = "INSERT INTO TBL_Map (MapID, submit_type, Push_to_Mainframe,
file_format, fields_ignore, logic, map_status, Business_Need, PPED_map,
Comments_map, transfer_date, test_file, revision_change) VALUES (" & Me!
[txtMapID] & "," & Me![CboSubmittype] & "," & Me![txtPushToMF] & "," & Me!
[txtFileFormat] & "," & Me![txtfields_ignore] & "," & Me![TxtLogic] & ","
&
Me![CboMapStatus] & "," & Me![CboBusinessNeed] & "," & Me![chkPPED] & ","
&
Me![TxtComments] & "," & Me![txtTransDate] & "," & Me![TxtTestFile] & ","
&
Me![txtRevision] & ");"


Thanks again,
Lee

Jana wrote:
I am trying to do an append query on the fly. I have several different
types
of textboxes, check boxes, and combo boxes as input. The ones that I
seem to
[quoted text clipped - 22 lines]
--
Message posted via
AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200708/1

Lee:
If you need to insert the text 'New Request' into the map_status field
of the TBL_Map, then you need to wrap it with single quotes. Same
thing goes for Technology Issue.

So, you should change it as follows:
INSERT INTO TBL_Map (MapID, submit_type, Push_to_Mainframe,
file_format,
fields_ignore, logic, map_status, Business_Need, PPED_map,
Comments_map,
transfer_date, test_file, revision_change) VALUES (NewMap,FTP,H244ftp,
delimited,none,none,'New Request','Technology
Issue',-1,none,,none,none);

We might be better able to help you if you post the entire code that
is building your SQL string, since it is not clear where you're
getting the values from.

HTH,
Jana

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200708/1



.

Relevant Pages

  • Re: Can this be "refactored"? A simple wrapper function to display MySQL data sets in tabular form
    ... to generate a string that is N times some component string, e.g., "-". ... Clarity of intention -- How easy is it to see what is being done? ... functional programming paradigm). ... FORMAT and LOOP have their places. ...
    (comp.lang.lisp)
  • Re: Append Query help
    ... I am having trouble adding ... the single quotes to the string. ... How do I add the single quotes to the VBA ... of textboxes, check boxes, and combo boxes as input. ...
    (microsoft.public.access.formscoding)
  • Re: Length of a string?
    ... separate statement, for clarity ... I guess there are languages where strings are structures, ... and % is used for structure member selection. ... They could have use .LENGTH.s for string length. ...
    (comp.lang.c)
  • Re: How to exclude a string using regexp pattern?
    ... data, if you are typing these in as Java source String literals, then you'll ... expressed in your XML ends up being implemented by the standard Java regexp ... if not then most/all regexp engines have some similar feature which you ... The regexp itself is also oversimplified (just for clarity -- or what passes ...
    (comp.lang.java.programmer)
  • Re: hide taskbar
    ... It's becasue your ShowIndow is using 64-bit Longs. ... System.Int32 for clarity ... > (ByVal lpClassName As String, ByVal lpWindowName As String) As IntPtr ...
    (microsoft.public.dotnet.framework.compactframework)