Help with a VB code

[crwilliams]

Hi,

I have a VB code in my database which has a DISTINCTROW statement I am trying to update.

Am I allowed to post it here so someone can take a look and tell me what's wrong?

 

[CJ_London]

yes - post away. Use the advance editor and surround the code with the code tags to preserve the indenting - responders tend to ignore all but the simplest unindented code because it is difficult to read.

You say VB code, but distinctrow is a SQL element so please be clear about what you mean. Also provide some narrative about what your code is supposed to do, where you are using it (access, excel, etc) and the type of db you are connecting to (access, sql server etc) and details of any error codes or unexpected results. If relevant, provide some sample data and an example of the expected result

Also don't waste your time disguising the code by changing names etc, it only wastes our time if you end up with a typo.

 

[crwilliams]

There could be many things wrong with my code but I am presently getting the following error: Error number 3135 "Syntax error in Join Operation" I am using this in an Access Database. I am updating a very long procedure someone else wrote. I need to change this statement only. I was receiving another message "3296 Join expression not supported" so I changed all my "INNER JOIN" to "JOIN" now I do not get that error message, instead, I get the other one.

Const strcQuerySQL As String = "SELECT DISTINCTROW tbl_Project_Team_Depts.Business_Unit_Name, tbl_Project_Team_SME.[SME Name], tbl_Project_Team_SME.[SME Number], tbl_Project_Team_SME.[SME Email], tbl_Regions.Region_Abbrev" _
                             & " FROM ((tbl_Assign_Regions_Trans_Dist JOIN tbl_Region_SME_Assignments ON tbl_Assign_Regions_Trans_Dist.Assignment_ID = tbl_Region_SME_Assignments.Assignment_ID) JOIN ((tbl_Regions JOIN (((tbl_Project_Team_Depts JOIN tbl_Dept_Assignments ON tbl_Project_Team_Depts.[Dept ID] = tbl_Dept_Assignments.Dept_ID)" _
                             & " JOIN tbl_Project_Team_SME ON tbl_Dept_Assignments.SME_ID = tbl_Project_Team_SME.ID) JOIN tbl_Region_Assignments ON tbl_Project_Team_SME.ID = tbl_Region_Assignments.SME_ID)" _
                             & " ON tbl_Regions.[Region ID] = tbl_Region_Assignments.Region_ID) JOIN tbl_TDB_SME_Assignments ON tbl_Project_Team_SME.ID = tbl_TDB_SME_Assignments.SME_ID)" _
                             & " ON (tbl_Region_SME_Assignments.SME_ID = tbl_Project_Team_SME.ID) & (tbl_Assign_Regions_Trans_Dist.Region_ID = tbl_Regions.[Region ID])) JOIN tbl_TDB_Dept_Assignments ON (tbl_TDB_Dept_Assignments.TBD_Designation = tbl_TDB_SME_Assignments.TDB_Designation) & (tbl_Dept_Assignments.Dept_ID = tbl_TDB_Dept_Assignments.Dept_ID) WHERE (((tbl_Regions.Region_Abbrev)='" _
                             & " ((tbl_TDB_SME_Assignments.TDB_Designation)=1) & ((tbl_TDB_Dept_Assignments.TBD_Designation)=1) & ((tbl_Assign_Regions_Trans_Dist.Trans_Dist_Assign)=1) &((tbl_Region_SME_Assignments.Assignment_ID)<11))='"

 

[CJ_London]

you need to use inner join rather than join for Access.

It is a big query and difficult to decipher, suggest you add back the 'inner' then in the immediate window type

?strcQuerySQL

and hit enter - that will convert the string into something you can copy and paste into the sql window of the query builder. When done, go to the query builder window to see what your query looks like - it should give you some clues but my guess is you are missing a bracket somewhere in the FROM part of the sql.

Also, your WHERE clause does not look right, looks like you are using & when you should be using AND.

Best way to do these is to develop the query in the query builder, then go to the SQL view and copy and paste into your code. Then you can carefully edit it to include the line breaks for readability

 

[crwilliams]

I am so not very good at any of this. I posted this along with all the details in the "Modules and VBA" section. Originally I got my new SQL statement from the query I updated from the "SQL view" I tried to simply replace the text in my procedure with it and it all turned Red. That is when I replaced the "AND" with "&" and the "INNER JOIN" with "JOIN". I would be so grateful if you could take a look. I posted the original procedure and the new SQL view statement in my other post.
So confused.

 

[CJ_London]

I posted this along with all the details in the "Modules and VBA" section

You should not double post, at least not without providing a link to the previous post. Nobody likes spending time helping to find others are suggesting the same things.

Put it down to experience this time.

Other thread here - please respond on other thread

http://www.access-programmers.co.uk/forums/showthread.php?t=276669