SQL Inner Join Problem

[spikepl]

    ElseIf Screen.ActiveControl.Name = "Print" Then         strSQL = "SELECT * INTO " & TableName & " FROM [Letter Information] INNER JOIN [Letter Search - Query] ON [Letter Information].ID = [Letter Search - Query].ID WHERE " _         & "((([Letter Information].ID)=" & Forms![Search Form]![SID] & "));"     End If

 

[gold007eye]

Ok.. here is what I tried and result:

strSQL = "SELECT * FROM [Letter Information] INNER JOIN [Letter Search - Query] ON [Letter Information].ID = [Letter Search - Query].ID WHERE " _
    & "((([Letter Information].ID)=" & Forms![Search Form]![SID] & "));"

Immediate Window:

SELECT * FROM [Letter Information] INNER JOIN [Letter Search - Query] ON [Letter Information].ID = [Letter Search - Query].ID WHERE ((([Letter Information].ID)=107892));

Error 3065 Cannot execute a select query

 

[gold007eye]

Update: I copy and pasted the code you showed me and still getting error 3061 Too Few parameters. Expected 9.

ElseIf Screen.ActiveControl.Name = "Print" Then         strSQL = "SELECT * INTO " & TableName & " FROM [Letter Information] INNER JOIN [Letter Search - Query] ON [Letter Information].ID = [Letter Search - Query].ID WHERE " _         & "((([Letter Information].ID)=" & Forms![Search Form]![SID] & "));"     End If

Immediate Window:

SELECT * INTO zzpzdc FROM [Letter Information] INNER JOIN [Letter Search - Query] ON [Letter Information].ID = [Letter Search - Query].ID WHERE ((([Letter Information].ID)=107892));

 

[spikepl]

ok great .. it seemingly ate the SQL, but just cannot run it. and if you put the INTO back in? You get your favorit error back? You do.. hmm ..gotta think

 

[spikepl]

Try to paste this
SELECT * FROM [Letter Information] INNER JOIN [Letter Search - Query] ON [Letter Information].ID = [Letter Search - Query].ID WHERE ((([Letter Information].ID)=107892));into the SQL view of a query in the query designer and run it, and tell me what happens

 

[gold007eye]

It looks like with or without the "INTO" statement it is grabbing the data I need; just erroring out and not creating the table and putting the data into that table.

Only other thing I can think of is this is also the only SQL statement I am using that is looking at a table and a query to match data.

 

[spikepl]

and the field names and count match in both?

 

[gold007eye]

When I pasted that code into a query and ran it it came back with the 1 record that matched ID 107892.

And if I do it as

SELECT * FROM [Letter Information] INNER JOIN [Letter Search - Query] ON [Letter Information].ID = [Letter Search - Query].ID WHERE ((([Letter Information].ID)=[Forms]![Search Form]![SID]));

It pulls up data for whichever record I have selected that matches that ID

 

[gold007eye]

and the field names and count match in both?

Correct. Field names etc match.

 

[spikepl]

hmm... can you try to paste your make table query sql into the query designer, and change the query into an append query? (and of course there must be the proper table to append to). If that works then there might be a problem with a make table query .. and then I am clueless
.. And we will have to call on people who actually know what they are doing

 

[gold007eye]

Using this code I was able to get a "Make Table" query to do what I was trying...

SELECT * INTO zzpzdc
FROM [Letter Information]
WHERE ((([Letter Information].ID)=[Forms]![Search Form]![SID]));

Still need to get this to happen using "strSQL" as that creates the table name based on the users EDS Net ID.

 

[spikepl]

ok great... how about bulding it up slowly by adding bits and pieces

 

[gold007eye]

Working on that now.. to see what happens.

 

[spikepl]

* drumroll *

 

[gold007eye]

1st piece:

Error 3075 - syntax error in query expression ((([Letter Information].ID=[Forms]![Search Form]![SID]));

Code:

strSQL = "Select * INTO zzpzdc FROM [Letter Information] WHERE ((([Letter Information].ID=[Forms]![Search Form]![SID]));"

 

[spikepl]

count brackets

 

[gold007eye]

once I add make them equal whether it be ((( ))) or (( )) or ( ) I get the parameter error "expected 1"

 

[spikepl]

HUH? Utterly weird. Actually they have no function in this particular Sql whatsoever - tried to remove them all? And you are sure SID is not NULL, because that would also give this error -check the debug.print sql thing

 

[gold007eye]

Tried that as well.. once I get rid of brackets it goes back to Parameter error.

 

[spikepl]

SID is not NULL, because that would also give this error -check the debug.print sql thing??