Re: Using Variables in an INSERT INTO query

From: JulieD (julied_ng_at_hctsReMoVeThIs.net.au)
Date: 02/28/04 

Date: Sun, 29 Feb 2004 00:52:40 +0800

Hi Lee

without really testing what you've provided i think the problem has to do
with concatenation
when you want to concatenation a string you use
"string 1 " & "string 2"
when you want to concatenation variables with a string you use
"string 1 " & variable 1 & "string 2 " & variable 2

so in your code you need to change

 & "VALUES (fld1, fld2, st3, st4, st5, st6)"

to & "Values (" & fld1 & ", " & fld2 & ", " & st3 & ", " etc

but i always have to play around with this to get it exactly right
hope this gets you started though

Cheers
JulieD

"Lee" <anonymous@discussions.microsoft.com> wrote in message
news:397c01c3fe1a$114a9090$a501280a@phx.gbl...
> Hi guys (again!!)
> I'm having trouble trying to get an INSERT INTO query
> working. I want to use the query to append a single row
> of values to a table. This is fine but instead of using
> values I'd like to use variables instead but I can't seem
> to get the query to recognise them. Can you advise on
> the correc syntax? Below is the code I've got at present:
>
> Dim db As Database
> Dim qdf As QueryDef
> Dim fld1 As Field, fld As Field, st3 As String, st4
> As String, st5 As String, st6 As String
>
> fld1 = Forms!frm_InspectionReports!txtReportID
> fld2 = Forms!frm_InspectionReports!txtActualInspID
> st3 = "3"
> st4 = "1"
> st5 = Now()
> st6 = "Standard Letter: blah, blah."
>
> Set db = CurrentDb
> Set qdf = db.QueryDefs("qry_AddLetter")
> qdf.SQL = "INSERT INTO tbl_Letters (ReportID,
> ActualInspID, FromID, ToID, LetterDate, LetterNotes)" _
> & "VALUES (fld1, fld2, st3, st4, st5, st6)"
> DoCmd.OpenQuery qdf.Name
>
> BTW, |'ve tried setting the first two variables as
> Controls and Strings but still with no success.
> What am I doing wrong?!
>
> Thanks for any help you can offer.
>
> Lee

Relevant Pages

  • Re: Too Few Parameters Problem w/Query
    ... when I run the query it grinds for about an hour and then says there ... Domain As String, _ ... ' Description: A generic "concatenation" routine. ... Dim strConcatenate As String ...
    (microsoft.public.access.gettingstarted)
  • Re: syntax...
    ... B&D on the part of the language designer. ... probably handle concatenation of string literals by itself, ... bitwise XOR, or if not that, then exponentiation.) ...
    (comp.lang.misc)
  • Re: Aggregate string concatenation efficiency problem
    ... that involves a query and also involves a string that is being ... The string concatenation operation? ... data from Column5, the column in question, things slow way down. ... The string concatenation that I perform with the string builder ...
    (comp.databases.ms-access)
  • Bugs in the Module::Dependency
    ... Manifying blib/man1/pmd_indexer.plx.1 ... Use of uninitialized value in concatenation or string at ... # Failed test in t/04grapher.t at line 81. ...
    (perl.dbi.users)
  • Re: compatibility level
    ... most likely you are using string ... concatenation to build a name. ... Concatenate null yields null is off by ... You can change your query to handle the null values in building the ...
    (microsoft.public.sqlserver.server)