alsoascientist
Registered User.
- Local time
- Today, 09:08
- Joined
- Mar 26, 2012
- Messages
- 39
Hi All,
I think this should be pretty straight forward but I seem to be missing something with my code!
I have a SQL WHERE argument that builds off of a number of listboxes and eventually creates a query with the result.
The issue that I am having is that it doesn't seem to recognise Null values.
I have tried the code below with half my data set with null and half with zerolength strings and it always returns "FieldName = ' ' OR FieldName = ' '". This means that the null values aren't returned in the result.
I did think about replacing all the null fields with 0strings but I have concerns that these will not stay this way.
There should be a way to return "FieldName Is Null"?
I've also tried
If Ctrl.ItemData(varItem) = "" Then varWhere = varWhere & Ctrl.Name & "Is Null OR "
and
varWhere = varWhere & Ctrl.Name & "='" & Nz(Ctrl.ItemData(varItem),'Is Null' & "' OR "
and
I think this should be pretty straight forward but I seem to be missing something with my code!
I have a SQL WHERE argument that builds off of a number of listboxes and eventually creates a query with the result.
The issue that I am having is that it doesn't seem to recognise Null values.
I have tried the code below with half my data set with null and half with zerolength strings and it always returns "FieldName = ' ' OR FieldName = ' '". This means that the null values aren't returned in the result.
I did think about replacing all the null fields with 0strings but I have concerns that these will not stay this way.
There should be a way to return "FieldName Is Null"?
Code:
For Each Ctrl In RptFrm.Controls
If Ctrl.ControlType = acListBox Then
If Ctrl.Visible = True Then
For Each varItem In Ctrl.ItemsSelected
If IsNull(Ctrl.ItemData(varItem)) = True Then varWhere = varWhere & Ctrl.Name & "Is Null OR "
varWhere = varWhere & Ctrl.Name & "='" & Ctrl.ItemData(varItem) & "' OR "
Next
End If
End If
I've also tried
If Ctrl.ItemData(varItem) = "" Then varWhere = varWhere & Ctrl.Name & "Is Null OR "
and
varWhere = varWhere & Ctrl.Name & "='" & Nz(Ctrl.ItemData(varItem),'Is Null' & "' OR "
and