How to handle null in SQL statement

[ultimateguy]

I have an SQL statement in VBA and I am concatenating into it criteria from a combo box. The row source of the Combo Box is:

SELECT Categories.ID, Categories.Category FROM Categories UNION Select Null as AllChoice , "(All)" as Bogus From Categories
ORDER BY Categories.Category;

The purpose of All is to be able to choose All Categories as a criteria for the query. This works when I run the query in Access.

For my next application, I am forced to convert this query into VBA code with an SQL statement. The chunk of the VB statement giving me issues is:

WHERE (((Categories.ID) = " & [Forms]![frmCreateTriviaNight]![cboCategory1] & ")) Or (((" & [Forms]![frmCreateTriviaNight]![cboCategory1] & ") Is Null))

The VBA version of this query works fine when I have a specific Category chosen. For example if Category 22 is chosen then the statement becomes:

WHERE (((Categories.ID) = 22)) Or (((22) Is Null))

which works. But if the Category combo box has (All) selected, then the statement becomes:

WHERE (((Categories.ID) = )) Or ((() Is Null))

Which throws an error for invalid syntax. How can I deal with this null value in my VBA code?

[Ruegen]

WHERE tblTableTitle.FieldTitle Is Null

also you can use parenthesis

so ((tblTableTitle.FieldTitle) is null)

[Ruegen]

I have an SQL statement in VBA and I am concatenating into it criteria from a combo box. The row source of the Combo Box is:

WHERE (((Categories.ID) = )) Or ((() Is Null))

Which throws an error for invalid syntax. How can I deal with this null value in my VBA code?

well here you are saying catagories table field ID is equal to well... nothing

then

or nothing is null.

Usually just reading it for what it is helps determine the problem.

so you have to handle a NULL result.

Since you are receiving null results from the combo box you have to accommodate for it.

One way is using the nz() function. So normally nz(forms!frmName!combo, something happen here if it is null)

[June7]

Is this code behind frmCreateTriviaNight? Use Me alias to shorten code.

The criteria following the Or is nonsense.

ID field is number type so LIKE operator and wildcard won't work.

No alternative value with Nz() will return all records. So try an IIf().

WHERE Categories.ID " & IIf(IsNull(Me.cboCategory1), ">0", "=" & Me.cboCategory1) & ";"


What are you doing with this SQL - opening a recordset object?

[ultimateguy]

Thanks, the IIf worked. I am using this to change the SQL statement in an existing query.

[June7]

Why do you need to change query? Why not just apply filter to form or report?

[ultimateguy]

I need to generate multiple sets of data based on the same query. But the user can specify different criteria for each set of data. I have comboboxes for each set. So rather than create tons of pre-made queries, I am using VBA code to cycle through the sets and change the criteria accordingly for each new set of data.

[June7]

Users really should not interact with tables and queries.

So again - why not apply filter to form or report? Form or report uses the query (without filter) as RecordSource. Code applies filter to form or report.

DoCmd.OpenReport "report name", , , "ID" & IIf(IsNull(Me.cboCategory1), ">0", "=" & Me.cboCategory1)

[ultimateguy]

I didn't know I could do it that way. I need to use cboCategory1, cboSubCategory1, cboType1, and cboDifficulty1 as criteria. cboCategory1 and cboSubCategory1 are combo boxes with column(0) as the ID, and cboType and cboDifficulty have a text string for column(0). I also need to do a SELECT TOP with number of values specified by the user in txtQuestionsPerRound, and those questions have to be randomly sorted.

Is it possible to do all of this with a filter in this fashion?

I am doing this to generate one round of trivia. My end goal is to generate a report containing multiple rounds of trivia. Would the best way to do this be to have a report with a subreport for each round? Can I filter the subreports in this fashion?

[June7]

Only the main report can have the filter passed with the DoCmd.OpenReport. Here is example of code to construct filter criteria http://allenbrowne.com/ser-62code.html

Subreports are synchronized with the main report by the Master/Child Links properties. Subreport RecordSource can be an SQL statement with dynamic parameters. http://www.datapigtechnologies.com/f...mtoreport.html

[ultimateguy]

Thanks. Is there any way to swap out a single record in a report, leaving the others the same?

[June7]

I don't know what that means. Records are retrieved by query. What does 'swap out' mean?

[Ruegen]

confused myself...

swap?

hide?

[ultimateguy]

Sorry, should have been more clear. Currently the report has a record source equal to an SQL statement with dynamic parameters based on the user's combo box selections. The statement produces random Questions and Answers.

So for example, the user generates one round of ten questions and is then shown the report displaying this information. Suppose that the user decides one of those questions is not suitable. In this situation, the user needs to have one of these options:

1) Replace this single question with another random question based on the same criteria used to generate the other questions (while keeping the others unchanged)
2) Replace this single question with another question from the Questions table that is specifically chosen by the user
3) Replace this single question with a new question that is not currently in the database. The user inputs a new question into the Questions table, and then this question takes the place of the unsuitable question in the report.

I suspect that these actions are not possible given the structure of the report?

[Ruegen]

So the user doesn't select the questions, the questions are selected for them based on the combo boxes they have updated.

i.e. group1 gives you 20 random group 1 questions? group 2 gives you 20 random group 2 questions? and if you don't like one of those questions you want the user to be able to remove it....?