-1

I am setting a list box row source conditionally based on a value:

If MyValue = "" then
   Me.MyList.RowSource = "SELECT...
   FROM...
   RIGHT JOIN...
   GROUP BY...
   ORDER BY...
Else
   Me.MyList.RowSource = "SELECT...
   FROM...
   RIGHT JOIN...
   GROUP BY...
   HAVING MyValue
   ORDER BY...
End if

Where MyValue is some criteria for the HAVING part. This works fine as long as MyValue is not "" (blank). However during compile, I get a Microsoft Access error: "Syntax error on HAVING clause." During runtime, the "else" with HAVING does not occur so no error there. How can I avoid or trap the "Syntax error on HAVING clause." during compile? The usual "On error..." does not trap during compile.

Thanks.

Paul
  • 117
  • 1
  • 1
  • 9
  • 1
    The VBA compiler doesn't show syntax errors in SQL strings. You should show us your *actual* code. – Andre Feb 16 '16 at 22:54

2 Answers2

1

The compiler can't go so far as to insert your code into the listbox field and check the outcome. But, some good practices will help you trouble shoot this. 

Dim strRowSourceSQL as String

strRowSourceSQL  = "SELECT ... FROM ... RIGHT JOIN ..."

If MyValue = "" then
    strRowSourceSQL = strRowSourceSQL & "MORE SQL HERE"
else
    strRowSourceSQL = strRowSourceSQL & "DIFFERENT SQL HERE"
end if

Me.MyList.RowSource = strRowSourceSQL

This arrangement will let you inspect strRowSourceSQL. Set a break point and...

Debug.Print strRowSourceSQL

Once you have that, drop into the Query editor and fix what's wrong. Also, follow these principles.

Community
  • 1
  • 1
Smandoli
  • 6,919
  • 3
  • 49
  • 83
0

Thanks for the suggestions, but I found the problem.

It turned out that I had a left-over query definition in the Property Sheet for the list box that had the offending HAVING clause. When I removed the definition from the Property Sheet, the form opened without the annoying syntax error. The vba code I included in my original post was just fine.

Paul
  • 117
  • 1
  • 1
  • 9