runtime error 3075 (1 Viewer)

Hansi · Jun 14, 2018

Code:

DoCmd.RunSQL (" SELECT (LEA , SO_ID , CRM_ORDER , SO_STATUS , TASK_NAME , TASK_STATUS , TASK_WG , ASSIGNED_DATE , CLOSED_DATE , ACCOUNT_NO , SERVICE_TYPE , ORDER) FROM All_Detail" & _
              " DLookup('lea','so_id','crm_order','so_status','task_name','task_status','task_wg','assigned_date','closed_date','account_no','service_type','order_type')" & _
              " INSERT INTO Main( Report_Date, [LEA , SO_ID , CRM_ORDER , SO_STATUS , TASK_NAME , TASK_STATUS , TASK_WG , ASSIGNED_DATE , CLOSED_DATE , ACCOUNT_NO , SERVICE_TYPE , ORDER] )" & _
               " VALUES( rep_date, DLookup('lea','so_id','crm_order','so_status','task_name','task_status','task_wg','assigned_date','closed_date','account_no','service_type','order_type') );"), True

My code gets run-time error 3075 syntax error by saying to remove commas in SELECT statement.
I don't know how to fix it.

Galaxiom · Jun 14, 2018

The DLookups are not even close to valid.

I can't see what you are trying to do with them but suggest you review the syntax of an INSERT query.

The_Doc_Man · Jun 14, 2018

Obviously an accidental re-post.

As to what you have written, that is clearly not less than three separate queries' worth of activity.

You have a SELECT with a list of fields followed by a FROM. That's one query right there.

The DLookup stuff doesn't belong in a query (usually) because that is the kind of thing you do by JOINing two tables via SQL. Further, DLookup will look up ONE and ONLY ONE value. You have a list of values but no obvious selection critera. In any case, that is clearly not useful in that context. However, if you ask me, the syntax error occurs near or inside that DLookup, since that function is LEGAL inside of an SQL statement. Not advisable, but technically legal.

Then, you have an INSERT INTO sequence. Some of the fields have unmatched bracketing (e.g. "[LEA" - opening but no closing bracket) and it appears that you were trying to jam the output of that DLookup into a multi-field bracketing - because I saw the closing bracket in ", ORDER]" and it appears to match the DLookup.

SQL, unlike English, cannot jam together separate activities. SELECT statements represent one action. INSERT INTO statements are a separate action. I don't even know where to begin on characterizing the DLookup, but it appeared after a FROM clause in a way that would guarantee confusion.

No disrespect is intended, Hansi, because we ALL started with limited knowledge of SQL and Access. However, you need to do more studying before you attempt something as complex as I suspect you are attempting.

A final word of advice here: We do best if we know IN WORDS what you are trying to do. Showing us isolated code snippets rarely gives us enough to work on. Shut down the techie part of your brain for a minute and tell us in descriptive terms what you are trying to do.

runtime error 3075 (1 Viewer)

Hansi

New member

Galaxiom

Super Moderator

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread