Re: Using Variables in an INSERT INTO query

From: Lee (anonymous_at_discussions.microsoft.com)
Date: 02/28/04 

Date: Sat, 28 Feb 2004 12:06:00 -0800

Thanks Julie, I appreciate your help. I've got it
working now thanks to you.

Regards,

Lee

>-----Original Message-----
>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

  • Too Few Parameters Problem w/Query
    ... I am trying to run a concatenate function in a query and I'm having a ... Domain As String, _ ... ' Description: A generic "concatenation" routine. ... Dim strConcatenate As String ...
    (microsoft.public.access.gettingstarted)
  • 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: 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)
  • Re: Using Variables in an INSERT INTO query
    ... when you want to concatenation a string you use ... when you want to concatenation variables with a string you use ... I want to use the query to append a single row ... > Dim qdf As QueryDef ...
    (microsoft.public.access.queries)
  • 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)