Re: Extract data to new fields

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Gordzilla (gordzilla2000_at_hotmail.com)
Date: 03/24/04 

Date: Tue, 23 Mar 2004 21:06:02 -0500

Not that simple.
On the line indicated below, I need the fieldname to be evaluated but with
the quotes

strSql = "INSERT INTO "
                strSql = strSql & " tmpNewSurvey "
                strSql = strSql & " ( SurveryId, Question, Response ) "
                strSql = strSql & " SELECT "
                strSql = strSql & " SurveryId "
                strSql = strSql & " , " & """" & fld.name & """" & " AS
Question "
===> strSql = strSql & " , " & """" & fld.name & """" & " AS Response
"
                strSql = strSql & " FROM "
                strSql = strSql & " tblSurvey ; "

Currently it gives me strSQL ="INSERT INTO tmpNewSurvey ( SurveyId,
Question, Response ) SELECT SurveyId , "P1S1Q01" AS Question ,
"P1S1Q01" AS Response FROM tblSurvey ; "
and I need
strSQL ="INSERT INTO tmpNewSurvey ( SurveyId, Question, Response )
SELECT SurveyId , "P1S1Q01" AS Question , P1S1Q01 AS Response FROM
tblSurvey ; "

"John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:4pc160h45rg5vosod9hpb4vejst0uhq076@4ax.com...
> On Tue, 23 Mar 2004 13:46:15 -0800, "Gordzilla2000"
> <anonymous@discussions.microsoft.com> wrote:
>
> >So close.
> >
> >The fld.value comes up as an error every time.
> >I change both of them to read fld.name
> >Which results in the following SQL statement:
> >
> >"INSERT INTO tmpNewSurvey ( SurveyId, Question, Response ) SELECT
SurveyId , "P1S1Q01" AS Question , "P1S1Q01" AS Response FROM
tblSurvey ; "
> >
> >This is perfect except the Response portion should read P1S1Q01 AS
Response with no " around the field name.
> >This SQL statement gives me the exact extraction I need. I just can't
figure out how to amend strSql to get rid of the "
>
> Replace them with [Square brackets]:
>
> INSERT INTO tmpNewSurvey ( SurveyId, Question, Response ) SELECT
> SurveyId , [P1S1Q01] AS Question , [P1S1Q01] AS Response FROM
> tblSurvey ;
>
> John W. Vinson[MVP]
> Come for live chats every Tuesday and Thursday
> http://go.compuserve.com/msdevapps?loc=us&access=public

Relevant Pages

  • RE: Need Help with Notinlist
    ... Response As Integer) ... Dim strsql As String, x As Integer ... x = MsgBox("Do you want to add this Equipment to the list?", ... understand is that you have a combo box that lists equiment. ...
    (microsoft.public.access.formscoding)
  • Re: repli
    ... Private Sub Program_NotInList(NewData As String, Response As Integer) ... Dim cmd As ADODB.Command ... Dim strSQL As String, strMessage As String ...
    (microsoft.public.access.forms)
  • Re: repli
    ... Private Sub Program_NotInList(NewData As String, Response As Integer) ... Dim cmd As ADODB.Command ... Dim strSQL As String, strMessage As String ...
    (microsoft.public.access.forms)
  • Re: Graceful/Efficient Not In List Event
    ... You must set Response to acDataErrAdded if you've added the value. ... > Private Sub cmbProjectID_NotInList(NewData As String, ... > Dim strMsg As String ... > DoCmd.RunSQL strSQL, False ...
    (microsoft.public.access.formscoding)
  • RE: Run code if record does or does not exist for value entered in for
    ... Private Sub cboTestMethod_NotInList(NewData As String, Response As Integer) ... Dim strsql As String, x As Integer, strsqlM As String, NewProperty As ... Response = acDataErrContinue ...
    (microsoft.public.access.formscoding)