Run-time error '3296' when trying to do a SQL join in VBA


I have the following code that I'm trying to use to calculate a median value for a table.  Previously, I did not use the sqlString variable, but instead typed the SQL string right into the openrecordset line.  This worked, but at the time, I was not using the join, I was only pulling data from the source table.  Then I tried adding the JOIN so that my results would be limited to the records in table "qryBranchRegion".  I'm joining by column "Level2" (the same name in the source table and source query).  That didn't work.  Finally tried putting my SQL string into a variable and calling it that way.  It still doesn't work, I get the following error:

Run-time error '3296':

Join expression not supported.



Any suggestions?



Thanks!


My code:
---------------------------------------------

Function MedianByRegion(tName$, fldName$) As Single
  Dim MedianDB As DAO.Database
  Dim ssMedian As DAO.Recordset
  Dim sqlString As String

  Dim RCount%, i%, x%, y%, OffSet%
  Set MedianDB = CurrentDb()
  sqlString = "SELECT [" & fldName$ & "] FROM [" & tName$ & "] INNER JOIN qryBranchRegion ON " & tName$ & ".Level2 ORDER BY [" & fldName$ & "];"
  Set ssMedian = MedianDB.OpenRecordset(sqlString)
           
  ssMedian.MoveLast
  RCount% = ssMedian.RecordCount
  x% = RCount% Mod 2
  If x% <> 0 Then
     OffSet% = ((RCount% + 1) / 2) - 2
     For i% = 0 To OffSet%
        ssMedian.MovePrevious
     Next i
     MedianByRegion = ssMedian(fldName$)
  Else
     OffSet% = (RCount% / 2) - 2
     For i% = 0 To OffSet%
        ssMedian.MovePrevious
     Next i
     x% = ssMedian(fldName$)
     ssMedian.MovePrevious
     y% = ssMedian(fldName$)
     MedianByRegion = (x% + y%) / 2
  End If
  ssMedian.Close
  MedianDB.Close
   
 
End Function

---------------------------------------------
andymacdAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

perkcCommented:
Try:

sqlString = "SELECT [" & tName$ & "].[" & fldName$ & "] FROM [" & tName$ & "] INNER JOIN qryBranchRegion ON " & tName$ & ".Level2 = qryBranchRegion.Level2 ORDER BY [" & fldName$ & "];"

perkc

0
andymacdAuthor Commented:

perkc,
Tried your code, now I get a Run-Time error '3061':  Too few parameters.  Expected 1.

Any more ideas?



Thanks!
0
perkcCommented:
Create the following fuction:

Function TestMedianByRegion(tName$, fldName$) As String
TestMedianByRegion = "SELECT [" & tName$ & "].[" & fldName$ & "] FROM [" & tName$ & "] INNER JOIN qryBranchRegion ON " & tName$ & ".Level2 = qryBranchRegion.Level2 ORDER BY [" & fldName$ & "];"
End Function

Then from the 'Immediate' window run the following:

debug.Print TestMedianByRegion("TypeTableNameHere","TypeFieldNameHere")'Change the table and field name for your database info

Then let me know what it returns.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

andymacdAuthor Commented:

Hello, it returns:
SELECT [tblCurrentAllocate].[retpabtothh] FROM [tblCurrentAllocate] INNER JOIN qryBranchRegion ON tblCurrentAllocate.Level2 = qryBranchRegion.Level2 ORDER BY [retpabtothh];

0
perkcCommented:
Try:

sqlString = "SELECT [" & tName$ & "].[" & fldName$ & "] FROM [" & tName$ & "] INNER JOIN qryBranchRegion ON " & tName$ & ".Level2 = qryBranchRegion.Level2 ORDER BY ]" & tName$ & "].[" & fldName$ & "];"
0
andymacdAuthor Commented:

I used this:

 sqlString = "SELECT [" & tName$ & "].[" & fldName$ & "] FROM [" & tName$ & "] INNER JOIN qryBranchRegion ON " & tName$ & ".Level2 = qryBranchRegion.Level2 ORDER BY [" & tName$ & "].[" & fldName$ & "];"

Same as above, but I had to swap one of the brackets to go the other way.  Same 3061 error message.  Aarghh!!

0
perkcCommented:
What are the data types of the following fields:

tblCurrentAllocate.retpabtothh
tblCurrentAllocate.Level2
qryBranchRegion.Level2(Where is this pulled from, give me the data type from the table)
0
andymacdAuthor Commented:

tblCurrentAllocate.retpabtothh -->  Number Double
tblCurrentAllocate.Level2  -->  Text
qryBranchRegion.Level2  -->  Text  (rather, this a select query of a table which has this field as text)

0
perkcCommented:
Can you post the SQL statement from thr 'qryBranchRegion' query?
0
andymacdAuthor Commented:
SELECT BRANMAST.LEVEL2
FROM BRANMAST
WHERE (((BRANMAST.BRN_ID3)=[Forms]![frmSelectBranch]![cboSelectBranch]));
0
perkcCommented:
If the BRN_ID3 field is numeric try:

sqlString = "SELECT [" & tName$ & "].[" & fldName$ & "] FROM [" & tName$ & "] INNER JOIN (SELECT [BRANMAST].[LEVEL2] " _
& "FROM [BRANMAST] WHERE ((([BRANMAST].[BRN_ID3])=" & [Forms]![frmSelectBranch]![CboSelectBranch] & "))) as testqryBranchRegion " _
& "ON " & tName$ & ".Level2 = testqryBranchRegion.Level2 ORDER BY [" & tName$ & "].[" & fldName$ & "];"

otherwise try:

sqlString = "SELECT [" & tName$ & "].[" & fldName$ & "] FROM [" & tName$ & "] INNER JOIN (SELECT [BRANMAST].[LEVEL2] " _
& "FROM [BRANMAST] WHERE ((([BRANMAST].[BRN_ID3])=" & [Forms]![frmSelectBranch]![CboSelectBranch] & "))) as testqryBranchRegion " _
& "ON " & tName$ & ".Level2 = testqryBranchRegion.Level2 ORDER BY [" & tName$ & "].[" & fldName$ & "];"

Just kind of shooting in the dark here. I can get this to work on my computer and can't get the error you're receiving. That error implies that the query is expectng more information that is being provided.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve BinkCommented:
Do not accept this as a solution.

I think perkc correctly identified the issue, though the user may or may not have realized how to implement a fix.  For query "qryBranchRegion" to return, it needed a parameter from an open form.  Seeing as how that is the ONLY parameter in the entire set of queries, I'm of the opinion that a) the form was not open, or b) the field was not populated.  If perkc's last comment did not resolve the issue, troubleshooting the form reference would be the next step.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.