apostrophe error in macro

  • Hello

    I have a conditional macro that I use to check data entered on a form [invoice log table 4]. My checking looks at the supplier name [supplier name], and invoice number [invoiceno] fields, in a table [invoice log table], to see if the invoice already exists, and should thus not be entered again.
    I'm doing this checking with the following bit of code as the condition

    DLookUp("[InvoiceNO]","[Invoice log table]","[InvoiceNO] = '" & [Forms]![Invoice Log Table 4]![InvoiceNO] & "'" & " AND [Supplier Name]= '" & [Forms]![Invoice Log Table 4]![Supplier Name] & "'") Is Not Null

    This code works fine until the supplier name has an apostrophe in it, at which point an syntax error message pops up. I've tried to use 4 double quotes ("""") in front of and after the supplier name, which worked for the VBA part, but it doesnt seem to work here.

    Looking forward to your bright ideas and thanks in advance.

  • Re: apostrophe error in macro

    Did you try using Ascii codes instead? like 34 for Double Quotes?

    'Not Quite sure of exact syntax but something like this may work
    ... AND [Supplier Name]= " & Chr(34) &  Chr(39)  & [Forms]![Invoice Log Table 4]![Supplier Name] & " & Chr(39) & Chr(34) Is Not Null ...


  • Re: apostrophe error in macro

    hey jva, cheers for the reply, i tried what u suggested, and moved it from a conditional macro to VBA.

    i now have the following code:

    DLookup("[invoiceno]", "[invoice log table]", "[invoiceno] = " & Chr$(34) & [Forms]![Invoice Log Table 4]![InvoiceNo] & Chr$(34) & " and [Supplier Name]= " & Chr$(34) & [Forms]![Invoice Log Table 4]![Supplier Name] & Chr$(34))

    the code returns a blank supplier name if the supplier name has an apostrophe, and it crashes if the supplier name doesn't contain an apostrophe.... :confused:
    any ideas?

  • Re: apostrophe error in macro [solved]

    I am not sure if that's possbile since i'm calling the field, and not a specific record.

    I solved this by using the following code:

  • Re: apostrophe error in macro [solved]

    I can't see why not.

    Just assign Me![Supplier Name] to a string variable, use the string functions to search for and replace the apostrophe with a wild card in the string variable.

    Then use the string variable in sCriteria instead of Me![Supplier Name].


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!