SQL Inner Join Problem

[gold007eye]

I'm trying to figure out what I am doing wrong here. I'm trying to create the SQL MakeTable query on the fly and all the other sql statements are working fine. When I am trying to do the "INNER JOIN" that is where I am running into problems and getting the error message: 3061 Too Few Parameters. Expected 9.

Can someone please help me as to what I am doing wrong?

Here is the code throwing the error.

    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

 

[spikepl]

Yes. SQL is just like VBA, and needs spaces to understand what you have written.

Insert

debug.print strSQL

in your code

and inspect the output in the Immediate window. That should show where spaces are missing.

 

[gold007eye]

When I inserted the line you mentioned here is what I'm getting in the immediate window.

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

Not sure how this helps me with missing spaces? It looks like it is getting the ID correctly, but just having issues on the Inner Join.

 

[spikepl]

SQL is a language. Spaces delimit the language elements it needs to understand. Missing spaces lead to a number of sometimes erroneous messages, because the poor thing has no clue what you meant.

There is one space missing in your SQL. I could tell you where it is, but where would the learning experience then go?

 

[gold007eye]

Lol.. true.. I will see if I can figure out the missing space; and am also trying some different code at the same time.

 

[spikepl]

AS to queries in general, to avoid silly problems like these, I tend to the most extent possible to build the queries in the query builder, copy the working SQL from there, and amend it as necessary, while preserving the Holy Spaces

 

[gold007eye]

That's what I usually try and do, but it seems like it gets crazy with needing ' and " and '" and &'s lol.. That's where I run into the problems and as you said the spaces. I always think extra spaces are bad in coding.

 

[gold007eye]

I'm at a total loss here.. I have tried at least 20 different ways and I keep getting the same error. Tried adding space in different places, brackets, etc. to know avail :\ Any other hints please?

 

[spikepl]

WHERE( should be WHERE (

 

[spikepl]

Annother hint for the future: hunting bugs like these- remove stuff, conditions etc, and add them again one by one

 

[spikepl]

Andbytheway,extraspacesarenotalwaysbad

 

[gold007eye]

I actually had tried the space after "WHERE" and it still gave me same error.

Am I making things more difficult by splitting it up between 2 lines?

I tried it on 1 line with same parameter error.

 

[spikepl]

Ok. try dumping the INTO stuff, and just work on the SELECT first...and is your ID a string or number?For with numbers you don't need ''

 

[gold007eye]

ID is the PK auto generated as a "Number" type.

 

[spikepl]

then unwrap the ID from the wicked ''

 

[gold007eye]

Arrrghh.. This is driving me bonkers. Still getting parameter error. Here is the full code that I'm using. I'm not sure if this will help. Also having it look at a form for the value shouldn't be causing any problems correct? I tried removing the "INTO" code and get the same results.

Function MakeTable(TableName As String) As Boolean
Dim strSQL As String, Db As DAO.Database

'   Creates a table (TableName) with data = to the WHERE clause.
'On Error GoTo errhandler

'---=== Create the Update SQL Code from our values ===---
'---=== "TableName" Created based on EDS Net ID Field on "SAL Lookup" form.  FROM "Letter Information" table.  WHERE "Letter Information" table = Criteria (values) from specific form. ===---
'---=== Begin Letter Preview Code ===---
If CurrentProject.AllForms("Provider Letters").IsLoaded Then
    strSQL = "SELECT * INTO " & TableName & " FROM [Letter Information] WHERE" _
    & "((([Letter Information].Analyst)='" & Forms![Provider Letters]![Analyst] & "') AND" _
    & "(([Letter Information].[Letter Type])='" & Forms![Provider Letters]![Letter Type] & "')AND" _
    & "(([Letter Information].[Letter Date]) = #" & Forms![Provider Letters]![Letter Date] & "#)AND" _
    & "(([Letter Information].[Provider Name])='" & Forms![Provider Letters]![Provider Name] & "'));"

ElseIf CurrentProject.AllForms("Letter Search - Results").IsLoaded Then
    strSQL = "SELECT * INTO " & TableName & " FROM [Letter Information] WHERE" _
    & "((([Letter Information].Analyst)='" & Forms![Letter Search - Results]![Analyst] & "') AND" _
    & "(([Letter Information].[Letter Type])='" & Forms![Letter Search - Results]![Letter Type] & "')AND" _
    & "(([Letter Information].[Letter Date]) = #" & Forms![Letter Search - Results]![Letter Date] & "#)AND" _
    & "(([Letter Information].[Provider Name])='" & Forms![Letter Search - Results]![Provider Name] & "'));"
    
ElseIf CurrentProject.AllForms("Search Form").IsLoaded Then

    If Screen.ActiveControl.Name = "Print-LD" Then
        strSQL = "SELECT * INTO " & TableName & " FROM [Letter Information] WHERE" _
        & "((([Letter Information].Analyst)='" & Forms![Search Form]![Analyst-C] & "') AND" _
        & "(([Letter Information].[Letter Type])='" & Forms![Search Form]![Letter Type-C] & "')AND" _
        & "(([Letter Information].[Letter Date]) = #" & Forms![Search Form]![Letter Date] & "#));"
    
    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] & "'));"
        
        '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] & "'));"
        
    Else:
        MsgBox "Failure", , "Failure"
    End If

Else:
    'Do Nothing
End If
'----=== End Letter Preview Code ===----

'Use Current Database
    Set Db = CurrentDb()

'Run the SQL Query
    Debug.Print strSQL
    Db.Execute strSQL
    
'If no errors return true
    MakeTable = True

ExitHere:

Set Db = Nothing
    Exit Function

errhandler:
    'There is an error return false
    MakeTable = False
    With Err
        MsgBox Err.Number & " - " & Err.Description, vbExclamation, "Make Table Function Error..."
    End With
    Resume ExitHere
End Function

 

[spikepl]

Hey .. IF you want it solved then burying me in muck is not going to help. Let's stikc to ONE swlstring.

Your code stil contains '' around the ID

Take ONE sqlString and make it work. Remove the INTO part, unwrap the ID from '', and see what that gives. If it still fails, post the string here, and say what error messag

 

[spikepl]

and honestly , your entire code is missing spaces here and there . Check all AND and WHERE -both need a space before and after.

 

[gold007eye]

Sorry wasn't trying to bury you. I did try removing the " " before from the ID; then no ID value was being returned in the immediate window. I will re-write with what you are asking and let you know the results.

 

[gold007eye]

Funny thing is all the other SQL statements are working perfectly. Just this dern Inner Join one that isn't. I added the space with the WHERE statement and it actually made no difference to the error.

Does the fact that I am writing this in a Module as a Function have any bearing on the code layout?