Query By Form VB

[TechTutors]

Hello Everyone!

To begin with, I would like to thank you all for taking the time in
assisting me! Your help is very much appreciated!

I have a field that contains both negative and positive numbers ((set to
currency to represent (-) credits and (+) debits)). My client would like to
run a few different custom searches. The first search could translate to
something like this ::: >= 500 OR <= -500 The other would look something
like ::: >= -500 OR <= 500

How would I code for those scenarios in VB?

The VB I currently have is provided by a free script provided by Allen
Brown. He was kind enough offer some guidance, but is unable to assist me
all the way through.

'Net Cash Balance
If Not IsNull(Me.txtNetCashBal1) Then
strWhere = strWhere & " ([NET_CASH_BAL] >= " & Me.txtNetCashBal1 &
") AND "
End If

If Not IsNull(Me.txtNetCashBal2) Then
strWhere = strWhere & " ([NET_CASH_BAL] < " & txtNetCashBal2 + 1 &
") AND "
End If

Any assistance offered would be greatly appreciated!

Many Thanks,
-E

 

[Klatuu]

Your criteria needs to allow for all possibilities. Try this version:

If Not IsNUll(Me.txtNetCashBal1) And Not IsNull(Me.txtNetCashBal2) Then
strWhere = "[NET_CASH_BAL] BETWEEN " & Me.txtNetCashBal1 & _
" AND " Me.txtNetCashBal2
ElseIf Not IsNull(Me.txtNetCashBal1) And IsNull(Me.txtNetCashBal2) Then
strWhere = strWhere & "[NET_CASH_BAL] >= " & Me.txtNetCashBal1
ElseIf Not IsNull(Me.txtNetCashBal2) And IsNull(Me.txtNetCashBal1) Then
strWhere = strWhere & "[NET_CASH_BAL] <= " & Me.txtNetCashBal2
Else
strWhere = vbNullString
End If

If Not IsNull(Me.txtNetCashBal2) Then
strWhere = strWhere & " ([NET_CASH_BAL] < " & txtNetCashBal2 + 1 &
") AND "
End If