IsNull and empty fields - prob. easy

How can I change this code to test for empty values as well as null
values for Case 2?

' ... build "Approval" criterion expression
Select Case Me.frameApproval.Value
Case 1 ' Approved
strWhere = strWhere & "SPNDSBUS='X' And "
Case 2 ' Not Approved
strWhere = strWhere & "IsNull(SPNDSBUS) And "
Case Else ' Either
' no code needed
End Select

Many thanks

Deb

Standard practice for this is to concatenate a zero-length string to the
variable you want to test, and then test the length of the resulting string;
this tests for both Null and ZLS in your variable. So your expression would
become:
strWhere = strWhere & "(Len(SPNDSBUS & "") = 0 ) And "

HTH,

Rob


Debbiedo wrote:
How can I change this code to test for empty values as well as null
values for Case 2?

' ... build "Approval" criterion expression
Select Case Me.frameApproval.Value
Case 1 ' Approved
strWhere = strWhere & "SPNDSBUS='X' And "
Case 2 ' Not Approved
strWhere = strWhere & "IsNull(SPNDSBUS) And "
Case Else ' Either
' no code needed
End Select

Many thanks

Deb

On Jun 4, 5:00*pm, "Rob Parker"
wrote:
Standard practice for this is to concatenate a zero-length string to the
variable you want to test, and then test the length of the resulting string;
this tests for both Null and ZLS in your variable. *So your expression would
become:
* * * *strWhere = strWhere & "(Len(SPNDSBUS & "") = 0 ) And "

HTH,

Rob



Debbiedo wrote:
How can I change this code to test for empty values as well as null
values for Case 2?

' ... build "Approval" criterion expression
Select Case Me.frameApproval.Value
* *Case 1 *' *Approved
* * * *strWhere = strWhere & "SPNDSBUS='X' And "
* *Case 2 *' *Not Approved
* * * *strWhere = strWhere & "IsNull(SPNDSBUS) And "
* *Case Else *' *Either
* * * *' no code needed
End Select

Many thanks

Deb- Hide quoted text -

- Show quoted text -

Thanks,

I had to change the code to

strWhere = strWhere & "(Len(SPNDSBUS & '') = 0 ) And "

(single quote for checking ZLS)

Slowly getting there.

One more question.

I have a query (Not Notified Query) that checks for unmatched records
betwen two tables. How would I add an option button (Case 4) that
would return these values in the details section.

Deb

Hi Deb,

Sorry about missing that the expression would need single quote characters,
since it was inside a quoted string; glad you picked that up and corrected
it ;-)

As for your new question: I'm not sure exactly what you're trying to do, and
you've posted no details of your form's recordsource, or what else is
happening on your code. It seems, from your question, that you're wanting
to change the form's recordsource to the records returned from a different
query. That's do-able via code, but you'd also need to program for all
other options to ensure that the form's original recordsource is in place
(or restore it if it's not). And it will only work if both queries are
returning recordsets with the same fieldnames (assuming that you have a form
with bound controls in the detail section). Here's an example of code (for
a command button) that will swap a form's recordsource between two queries:

Private Sub cmdSwap_Click()
If Me.RecordSource = "qry1" Then
Me.RecordSource = "qry2"
Else
Me.RecordSource = "qry1"
End If
End Sub

Hopefully that will get you onto the right track. If not, I suggest you
post a new question (so that maybe someone on your side of the world will
respond quicker than I do), giving more details of exactly what you're
wanting to do.

Again, HTH,

Rob


Debbiedo wrote:
snip
Thanks,

I had to change the code to

strWhere = strWhere & "(Len(SPNDSBUS & '') = 0 ) And "

(single quote for checking ZLS)

Slowly getting there.

One more question.

I have a query (Not Notified Query) that checks for unmatched records
betwen two tables. How would I add an option button (Case 4) that
would return these values in the details section.

Deb