Build Sql Statements VBA

[MJC22]

I am interested in developing a search screen for users. There are a dozen or more fields which the users can select Yes, No, Unknown or leave blank. I would like to be able to build a Sql Statement from what the users have selected and refresh my query which is displayed in a subform on the same from.

The users would be able to see the matching data and then copy and paste the results it to an excel file to further interogate.

I've attached a zip file containing an example of what I am trying to achieve.

Idealy, I was hoping someone could provide me with a few points on building sql statements in vba and then using them in a query.

Any help/advice or even examples would be very much appreciated.

Kind regards,
Mike

[MJC22]

Hi,

I looked on a few other websites and was able to create a solution

For anyone interest attached is my solution. Please let me know if it could be improved as this is my first time using Sql

Mike

[NeoPa]

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.