Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    insert into string value (A2k3, SP1)

    Hi All,
    I'm having a bit of a problem inserting a field *value* using an Insert Into sql function.
    The field (in the following example) is Fld2 and it's *value* can be one of the following forms:
    Val1
    Val1, Val2
    Val1, Val2, Val3
    I know it is the commas which is messing with the Insert.
    I had originally used strData1 and that worked well on my machine (WinXP, Acc2k3), but upon installing the db on another machine (Machine2, WinXP, AccXP, all patches, etc.), it would fail, giving an error message of "can't find the field "|", etc. OK fine, went back to my machine and worked out strData2. I don't know if this will work on Machine2 as I haven't been able to try it yet, but on my machine it works only half the time.
    If I call the Insert from a command button on the main form, strData2 works fine...if I call it from a popform, which is also called from a command button on the main form, using exactly the same code (except for a couple of Forms!.... references in place of Me!....references), I get the return of the "can't find field..." error.
    So it's pretty obvious that I am missing something in trying to implement this Insert.
    Would someone please show me the proper way?

    Thank you.
    <pre>strData1 = "INSERT INTO tblTemp(Fld1, Fld2, Fld3) "
    strData1 = strData1 & "SELECT [Fld1], """ & [Fld2] & """ As [PFld2], [Fld3] "
    strData1 = strData1 & "FROM qryMyQuery.....etc
    </pre>

    <pre>strFld2 = PersonDesignators(S)
    strData2 = "INSERT INTO tblTemp(Fld1, Fld2, Fld3) "
    strData2 = strData2 & "SELECT [Fld1], '" & strFld2 & "', [Fld3] "
    strData2 = strData2 & "FROM qryMyQuery.....etc
    </pre>

    <pre>Public Function PersonDesignators(S As String) As String
    Dim lPos As Integer
    Dim strFirst As String
    Dim strSecond As String
    Dim strThird As String

    S = Trim(S)
    lPos = InStr(S, ",")
    If lPos = 0 Then Exit Function 'No Spaces in string!
    strFirst = Left(S, lPos - 1)

    lPos = InStr(Mid(S, lPos + 1), ",")
    If lPos = 0 Then 'Only One Space!!
    strSecond = Right(S, 3)
    Else
    strSecond = Mid(S, InStr(S, ",") + 2, InStr((Len(strFirst)), S, ",") + 1)
    strThird = Right(S, 3)
    End If

    PersonDesignators = strFirst & Chr(34) & (", ") & Chr(34) & _
    strSecond & Chr(34) + ", " & Chr(34) & strThird
    </pre>


    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: insert into string value (A2k3, SP1)

    I don't understand what you're trying to do (and why). Could you post a stripped down copy of the database, so that we can get an idea of what your code refers to? See <post#=401925>post 401925</post#> for instructions.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: insert into string value (A2k3, SP1)

    Hi Hans,
    I don't think I can get a stripped down db. I can try, but I don't think I can make it that *little*.
    As to why.....the Insert Into is used to insert records into a temporary table that is also used as the Record Source of a report. The Command button on the main form is used to create this tblTemp and then print (or preview) the default report. The popform, also called by a command button on the main form, also creates tblTemp and gives the user a chance to choose any of the 4 reports which can be used and/or set the default report used, for the set of data displayed in the main form. It is in this populating of tblTemp in which the Insert Into statement is being used.
    Refering to my initial post on this:
    strData1 worked fine on my machine, both palces it is used, but did not work on another machine in either place used.
    strData2 works (on my machine) to create tblTemp and print/preview the default report, but does not work when the popform is doing the creation.

    I am aware that the commas, used in separating the instances in which the value in the field is actually multiple values; i.e. <Val1, Val3> or <Val1, Val2, Val>, are causing the problems during the actual Insert. The Select portion of the sql statement works fine. I just need to Insert the entire field into a field in tblTemp. It doesn't need to be separated into separate fields.

    If this is not enough, too vague, too much reiteration then I will *try* and get a stripped copy of the db attached. I'll start doing that now in case this explanation isn't an explanation.

    Thank you.

    gdr
    ---
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  4. #4
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: insert into string value (A2k3, SP1)

    Hi Hans,
    I'll try it with just the 2 forms in question along with the 1 module and leave out a bunch of the rest.
    At least that should give you a start to see what I am trying to do. Then if needed I can "fill" it out according to your questions.
    Thank you.

    gdr
    ---
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: insert into string value (A2k3, SP1)

    Do you really want to have double quotes around each of the up to three values in the end result?

  6. #6
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: insert into string value (A2k3, SP1)

    That's just it. Originally I was using '" & [Fld2] & "' thinking that would give me ==> "Val1, Val2, Val3" and so inserted without any quotes just the values. The second try was to get something, anything to get the value(s) into the field. The value(s) should be stored in the field without any quotes.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: insert into string value (A2k3, SP1)

    Can you explain what the function PersonDesignators is supposed to do? It gives very strange results when I try it, but since I don't know its purpose, it's impossible to say whether it works correctly.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: insert into string value (A2k3, SP1)

    OK then. Does this do what you want?
    <code>
    strData2 = "INSERT INTO tblTemp (Fld1, Fld2, Fld3) "
    strData2 = strData2 & "SELECT [Fld1], '" & S & "', [Fld3] "
    strData2 = strData2 & "FROM qryMyQuery.....etc
    </code>
    I simply used S instead of strFld2 = PersonDesignators(S).

  9. #9
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: insert into string value (A2k3, SP1)

    Hi Hans,
    That's exactly what I ended up with and I do know that it works both on my machine and the other, where this db is to be used. I am now writing a response to your post previous to this one, hopefully clearing up some of the questions you still may have. I still have questions on why my first try would work on my machine and not the second machine, but that is included in a follow-up post (which should have been a *prior to* post) to this one.
    Thanks a ton (metric if you wish) <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>.

    gdr
    ---
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  10. #10
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: insert into string value (A2k3, SP1)

    Hi Hans,
    Sorry this has become so convoluted, it really didn't seem to me to start that way. <img src=/S/notmyfault.gif border=0 alt=notmyfault width=15 height=15> <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>
    Let's see if I can really muck this up: <img src=/S/anigrin.gif border=0 alt=anigrin width=19 height=19>

    All that I have been trying to accomplish is use an Insert Into sql statement to append values into a temporary table. Amongst other things, that table is used as the control source to a set of 4 reports. From the MainForm, I have a command button with which the user may print or preview the report that has been chosen as the default report for the record on the MainForm. I also have a command button which brings up a popform. This popform gives the user the ability to print out any of the 4 reports and to be able to chose 1 of the 4 as the default report. The following was my 1st attempt...it worked on my machine, but did not work on the machine that this db is to be used on. It gave me that "...can't find field "|"...error. I am assuming that error was generated during the Insert phase of the sql statement.
    <pre>strData1 = "INSERT INTO tblTemp(Fld1, Fld2, Fld3) "
    strData1 = strData1 & "SELECT [Fld1], """ & [Fld2] & """ As [PFld2], [Fld3] "
    strData1 = strData1 & "FROM qryMyQuery.....etc
    </pre>

    Here I, again, am confused. It seemed to me that an Insert, from a query to a table, with all properties the same between the 2 fields, should happen quite readily. I mean there shouldn't be any real difference between "Val1" and "Val1, Val2" and "Val1, Val2, Val3". Or as I see it the Field value surrounded by quotes. But again it didn't work on Machine2.
    So that's where the PersonDesignator came in. (This function is a variation on some code I got here in the Lounge, that gets a string ready for an IN operator. For reference that is <post#=37044>post 37044</post#>.) I really did not expect it to work, but when it did when called form the command button on the MainForm I had to try it from the popform. It didn't work from there. I then tried some other variations with varying amounts of success, none total.
    So I stumbled onto your solution. I didn't need it on the form, but I added a textbox whose source is the field in question. I set S = Me!txtFld2, then used the following in the Insert sql.
    <pre>'" & S & "'
    </pre>

    which works on all machines I have tried it on.
    Now to me, [Fld2] (from the Select portion of the Insert sql) should have equaled what S is. Which is why, I guess, it took me so long to figure it out. I hope I've made myself at least a little bit clear on this point, 'cause this is the point that has me totally confused. Why isn't Fld2 = S? They are both derived (as it were) from the same field.
    Anyway, again, thank you so much for your time. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

    gdr
    ---
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: insert into string value (A2k3, SP1)

    Frankly, I have no idea. Since the database you attached only contains some forms and a module, but no data, it is impossible to test it. So it'll probably remain a mystery...

Posting Permissions

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