Inner Join Not Supported (1 Viewer)

pbaldy said:

Watch your spacing from line to line. As written, you'll end up jamming things together which will confuse Access.

Click to expand...

pbaldy said:

Given this:

strSQL = strSQL & "SELECT [Mechanical Features].*, [General Project Information].*"
strSQL = strSQL & "FROM [Mechanical Features] "

with no space after the "*" or before "FROM" the concatenated string will be

[General Project Information].*FROM [Mechanical Features]

which Access will not be able to interpret because the asterisk and FROM are run together.

Click to expand...

JustPunkin said:

I'm in the process of writing a query to search for multiple attributes of our projects.

I've been able to write the code to run the query for all of the information I need that is in my main table. I now need to add a bit of code to search for some information that is in a secondary table. The tables are linked by a key number.

I can get this to work if I search criteria in one or the other table, but not in both. I get an error "Runtime Error 3296 Join Expression Not Supported"

This is the beginning of my code. Can anyone help me figure out what I'm doing wrong.

Thanks

Code:Copy to clipboard

Private Sub cmdOK_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryMechFeatureSearch")
 
strSQL = ""
strSQL = strSQL & "SELECT [Mechanical Features].*, [General Project Information].*"
strSQL = strSQL & "FROM [Mechanical Features] "
strSQL = strSQL & "INNER JOIN [General Project Information]"
strSQL = strSQL & "ON [Mechanical Features].[Key #] = [General Project Information].[Key Number]"

Click to expand...

strSQL = SELECT [Mechanical Features].*, [General Project Information].[SIZE=4][COLOR=red][B]*F[/B][/COLOR][/SIZE]ROM [Mechanical Features] INNER JOIN [General Project Information[SIZE=4][COLOR=red][B]]O[/B][/COLOR][/SIZE]N [Mechanical Features].[Key #] = [General Project Information].[Key Number]

MSAccessRookie said:

It looks like you are missing some spaces (highlighted in RED below). Add the spaces and see if the problem goes away. The code you supplied interprets itself to the following:

Code:Copy to clipboard

strSQL = SELECT [Mechanical Features].*, [General Project Information].[SIZE=4][COLOR=red][B]*F[/B][/COLOR][/SIZE]ROM [Mechanical Features] INNER JOIN [General Project Information[SIZE=4][COLOR=red][B]]O[/B][/COLOR][/SIZE]N [Mechanical Features].[Key #] = [General Project Information].[Key Number]

NOTE: Consider changing the names of Table/Column/Report/Form/etc that use special characters. Normal characters for these items are Alphabetic Characters (upper and lower case), Numeric Characters, and a small subset of other characters including "." and "_".

ADDENDUM: I see that while I was called away for an impromptu meeting, pbaldy pointed out the same thing that I did, so the primary advice must be right. Don't forget the Note at the end.

Click to expand...

JustPunkin said:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

strSQL = ""
strSQL = strSQL & "SELECT [Mechanical Features].*, [General Project Information].*"
strSQL = strSQL & "FROM [Mechanical Features] "
strSQL = strSQL & "INNER JOIN [General Project Information]"
strSQL = strSQL & "ON [Mechanical Features].[Key #] = [General Project Information].[Key Number]"[/CODE]

Click to expand...

namliam said:

Atleast your are disambiguating (using DAO.) and using something to 'properly format' your SQL, using this
strSQL = ""
strSQL = strSQL &
One could almost thing you read one of my posts about SQL formatting in code.... One of my pet peaves

Click to expand...