Creating a field for a query with code

[NWTools]

I am using a form with combo boxes to create parameters and check boxes to select fields for a query. I am using an If statement to look at a check box on the form to see if it is checked. If so, then a field from the table being queried is added to the query. All that works OK. The problem is if the check box is not checked. Then I do not want the field added to the query. Looking at the code you can see I have Field1 = "". This is not working. A column is added to the query and it is titled unnamed. If the check box is unchecked, I do not want the field added to the query. I just do not know what to make Field1 equal to to prevent a field from being added to the query.

Private Sub cmdTestQuery_Click()

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim Field1 As String
Dim ComboMachine As String

DoCmd.DeleteObject acQuery, "Test"
'delete the existing query test so a new one can be created


If Forms![frmMgtRptMenu]![ckbxOIT] = True Then
'looks at my form at a check box to see if it is checked. If it is then it adds a field to the query
'and assigns it to the variable
Field1 = "tblLinerTesting.LinerOITMinutes"
Else
Field1 = ""
'Here is my problem. As long as Field1 in the If statement is true, I am OK
'However, if the Else statement is used, then a column called unnamed is added to the query
'When the If statement goes to the Else clause, I want no column added to the query.
End If

Set dbs = CurrentDb
strSQL = "SELECT tblLinerTesting.LinerID, tblLinerTesting.LinerRollNumber, tblLinerTesting.LinerMachine, tblLinerTesting.LinerType, tblLinerTesting.LinerThickness, tblLinerTesting.LinerResin, tblLinerTesting.LinerProdDate,[" & Field1 & "] " & vbCrLf & " FROM tblLinerTesting WHERE tblLinerTesting.LinerMachine = [Forms]![frmMgtRptMenu]![ComboMachine];"
Set qdf = dbs.CreateQueryDef("Test", strSQL)
DoCmd.OpenQuery "Test"

Set db = Nothing
Set qdf = Nothing



End Sub

 

[John Big Booty]

Before posting code again please have a read of this thread

Why not do thing this way;

Private Sub cmdTestQuery_Click()

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim Field1 As String
Dim ComboMachine As String

DoCmd.DeleteObject acQuery, "Test"
'delete the existing query test so a new one can be created


If Forms![frmMgtRptMenu]![ckbxOIT] = True Then
'looks at my form at a check box to see if it is checked. If it is then it adds a field to the query
'and assigns it to the variable


strSQL = "SELECT tblLinerTesting.LinerID, tblLinerTesting.LinerRollNumber, tblLinerTesting.LinerMachine, tblLinerTesting.LinerType, tblLinerTesting.LinerThickness, tblLinerTesting.LinerResin, tblLinerTesting.LinerProdDate,tblLinerTesting.LinerOITMinutes " & vbCrLf & " FROM tblLinerTesting WHERE tblLinerTesting.LinerMachine = [Forms]![frmMgtRptMenu]![ComboMachine];"

Else
strSQL = "SELECT tblLinerTesting.LinerID, tblLinerTesting.LinerRollNumber, tblLinerTesting.LinerMachine, tblLinerTesting.LinerType, tblLinerTesting.LinerThickness, tblLinerTesting.LinerResin, tblLinerTesting.LinerProdDate " &vbCrLf & " FROM tblLinerTesting WHERE tblLinerTesting.LinerMachine = [Forms]![frmMgtRptMenu]![ComboMachine];"
End If

Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("Test", strSQL)
DoCmd.OpenQuery "Test"

Set db = Nothing
Set qdf = Nothing



End Sub

 

[NWTools]

Thanks John and thanks for the tip about posting code.