select field

[Guest]

Hi,

I have a form in which the user will select a value from the combo box,
which then passes the value to a query. The value being passed though,
corresponds to the name of a field in the query. I would like to return
records where the value of the selected field is not null.

Here's my sql which does not work (it returns all of the records):

WHERE ((tblSEA3_SurveyNumbers.Forms!frmSelectSurvey!cboSelectSurvey is not
null)));

So, if in my form I select a survey called "screening", I would then like to
select the records where the value in the field screening is not null. Any
ideas? Thanks.

 

[Guest]

You'll need to use code to set the SQL to the query using the new field as a
criteria
Before you are running the query, try something like

Currentdb.QueryDefs("QueryName").SQL="Select * From tblSEA3_SurveyNumbers
Where " & Me![cboSelectSurvey] & " Is Not Null"

Docmd.OpenQuery "QueryName"

 

[Guest]

If your survey tables were normalized, this would be much easier. However,
you will probably need to write DAO code that changes the SQL property of a
saved query. For instance:

Dim db as DAO.Database
Dim qd as DAO.QueryDef
Dim strSQL as String
Set db = Currentdb
Set qd = db.QueryDefs("qselYourQueryName")
strSQL = "SELECT .... [" & Me.cboSelectSurvey & "] " & _
strSQL = strSQL & " FROM .... "
strSQL = strSQL & " WHERE [" & Me.cboSelectSurvey & "] is not null;"
qd.SQL = strSQL
Set qd = Nothing
Set db = Nothing

 

[Guest]

You can't with a simple SQL statement. If you need to go to different fields
to find the data, your tables aren't normalized properly.

If it were an Action query, you could use DoCmd.RunSQL or the Execute method
in code; however, they don't return records like a select query.

You might get something to work with CreateQueryDef.

But in the long run it would be best to normalize the data so what you are
looking for is in just two columns. For example instead of this:

Screening Viewing Reviewing etc,
1 2 3

You have this:

ReviewType ReviewValue
Screening 1
Viewing 2
Reviewing 3

and so on.