Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009
    Posts
    37

    Unanswered: Error 3075 - missing operator

    Hi - I just don't know what I am doing wrong here. I am trying to run a query through VB in access. I actually wrote the query using the access tools and then just copied and pasted the sql into the vb code and yet it does nto work! this is driving me bonkers.

    here is the code:
    DoCmd****nSQL ("INSERT INTO [(SE)_tbl_Exchange_Rates] ( CODE, COUNTRY, [Month], RATE, DataSource, [Month] )" & _
    "SELECT tbl_Holding.F1, tbl_Holding.F2, tbl_Holding.date_updated, tbl_Holding.F4, tbl_Holding.DataSource, [(SE)_tbl_Exchange_Rates].Month" & _
    "FROM tbl_Holding LEFT JOIN [(SE)_tbl_Exchange_Rates] ON tbl_Holding.date_updated = [(SE)_tbl_Exchange_Rates].Month" & _
    "WHERE ((([(SE)_tbl_Exchange_Rates].Month) Is Null));")

    all I want to do is append new data into the table. For that purpose I have linked the month fields with a left join and stipulated that the date in question is not yet in that table. I need to do this since the data I am trying to import might also contain some data from last month which is also in the table. This data i then want to use to update the table in a second step following on from this first one.

    help please I am going crazy here...

    anyone any advise how I can avoid this in the future... where am i always going wrong!

  2. #2
    Join Date
    May 2009
    Posts
    258
    Add a space before the double quote character at the end of the line. Your SQL looks like this right now:
    Code:
    INSERT INTO [(SE)_tbl_Exchange_Rates] ( CODE, COUNTRY, [Month], RATE, DataSource, [Month] )SELECT tbl_Holding.F1, tbl_Holding.F2, tbl_Holding.date_updated, tbl_Holding.F4, tbl_Holding.DataSource, [(SE)_tbl_Exchange_Rates].MonthFROM tbl_Holding LEFT JOIN [(SE)_tbl_Exchange_Rates] ON tbl_Holding.date_updated = [(SE)_tbl_Exchange_Rates].MonthWHERE ((([(SE)_tbl_Exchange_Rates].Month) Is Null));
    Regards,

    Ax

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you have these sort of errors assignthe SQL to a varaibale first, then examine the SQL
    Code:
    strSQL="INSERT INTO [(SE)_tbl_Exchange_Rates] ( CODE, COUNTRY, [Month], RATE, DataSource, [Month] )" & _
    "SELECT tbl_Holding.F1, tbl_Holding.F2, tbl_Holding.date_updated, tbl_Holding.F4, tbl_Holding.DataSource, [(SE)_tbl_Exchange_Rates].Month" & _
    "FROM tbl_Holding LEFT JOIN [(SE)_tbl_Exchange_Rates] ON tbl_Holding.date_updated = [(SE)_tbl_Exchange_Rates].Month" & _
    "WHERE ((([(SE)_tbl_Exchange_Rates].Month) Is Null));"
    DoCmd****nSQL (strSQL)
    why?
    you can insert a watch or display the sql in a msgbox to make certain that what you are sending to the SQL engine is what you think you are sending.
    its far easier, in my books, to debug what you are sending. its also a heck of a lot easier for others to try to debug
    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
  •