Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Unanswered: English to French Code -- INSERT PROBLEM

    Greetings all,

    I switched a program, including an INSERT statement from an English to a French computer (i.e., the latter formats everything in French format), and started getting a 3346 error (a mismatch between the number of fields in the table and the number of inputs).

    Here is the faulty code:

    Dim aSQL As String
    aSQL = "INSERT INTO m_trans ( mem_id, trans, storCour, gru, credit, a_frais, paiment, paiType, paiment2, paiType2, comment, chkNo) VALUES"
    aSQL = aSQL + "( " & txtMemID.Value & ",' " & aTrans & "', " & txtCour.Value & ", " & txtGruFr.Value & ", " & Nz(txtDepot.Value, 0)
    aSQL = aSQL + ", " & Nz(txtArr.Value, 0) & ", " & Nz(txtPai.Value, 0) & "," & frmPayment.Value & ", " & Nz(txtPai2.Value, 0) & "," & frmPayment2.Value & ", '" & txtExplain.Value
    aSQL = aSQL + "'," & Nz(txtChkNo.Value, 0) & " )"


    DoCmd.SetWarnings False
    DoCmd****nSQL aSQL
    DoCmd.SetWarnings True

    The code runs fine on an American-formatted machine, but not on a French - formnatted one. I tried eliminating all the NZ references. Didn't help. Any ideas?

    Regards

    John Smith
    Aylmer, Quebec

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so can we see the actual SQL, not the VBA that creates the SQL, but the actual SQL

    the most likely candidate is going to be punctuation
    France use the comma as the decimal spearator, a fullstop as the thousands separator
    French also makes more use of the ' symbol inside a block of text. you may need to consider using the escape character sequence which is two '
    eg
    Code:
    'make text values safe
    aTrans = MakeSafe(aTrans, vbtrue)
    ...
    Code:
    public function MakeSafe(MyString as string, optional Encode as boolean = true) as string
    'if Encode = true then 
    'replaces ' with ''
    'otherwise removes escape character
    if Encode = true then
      MyString = replace(MyString,"'","''")
      'its possible the function may have been called more than once
      MyString = replace(MyString,"'''","''")
    else
      MyString = repalce(MyString,"''","'")
    endif
    return MyString
    Exit function
    it could be that you need to ensure the host computer's Office localisation also needs to be set.

    however in the absence of seeing the SQL that is actually tripping up its difficult to say
    Last edited by healdem; 09-06-12 at 08:08.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Insert Problem

    Hi,

    Thanks for the reply.

    Amazing how one can totally forget crap -- with a raw insert in Access, you have to put protections for " ' " and " " " on the string data being inserted into the database. My function is similar to yours, and is called "SQLFixup". That said, I don't think the insert failed on a " ' " (there was no attempt to insert one), however, it may have failed on the following:

    "Automne 2012 -- dépôt, etc"

    I am wondering if it failed on the comma? I will have to go to the club today to test this theory (I do not have a machine with French Canadian localization). As you suggest, I will also take a look at the machine's localization settings. I will let everyone know what I find.

    Thanks again

    Regards

    John S

  4. #4
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Ah, the Solution.....

    Turns out that the failure was occurring on text boxes that were formatted for the Quebec format for currency (essentially American style with commas and periods reversed) . The boxes in the "format" property looked as if they had a mask.

    Solution: treat the textboxes as containing string values: surround them in" ' " even though they are numeric

    ( as in --- ....... " , ' " & nz(txtPayment.value, 0) & " ' , ")

    Strange quirk

    Regards

    John Smith
    Aylmer, Quebec, Canada

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or just strip out the thousands separator......
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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