[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1635
  • Last Modified:

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

---------------------------------------------
0
andymacd
Asked:
andymacd
  • 6
  • 5
1 Solution
 
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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
 
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now