Access 2010: Is it possible to send an operator plus a value to a query from a form?

I need to pass either the value 0 or <> 0 to a query from a form. Passing 0 works fine but <>0 (or <> 0) returns blank. Is this a syntax thing or is it even possible?
John CarrollAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BitsqueezerCommented:
Hi,

there are a bunch of methods to do that, here is one: Use DoCmd.OpenForm and the argument "OpenArgs" of this command. Here you can specify any text value you want, i.e. "ID = 0" or "ID <> 0".

In Form_Load of the form where you want to use it you can then use something like this:

Me.RecordSource = "SELECT A,B,C FROM MyTable " & IIf(Nz(Me.OpenArgs) <> "", " WHERE " & Nz(Me.OpenArgs),"")

Open in new window


Cheers,

Christian

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
If you are consuming the results of the query into a recordset variable, you could do your own substitution by accessing the query's SQL property and then using the Replace() function.
John CarrollAuthor Commented:
Let me explain in more detail. There are 5 different values in my Record Status table. My main form has a toggle button (Active/Cancelled) to allow the user to select active records of cancelled records. The toggle button populates a hidden text box with <>0 to all active records or 0 to show cancelled records. The RecordStatus field in the subform's query draws it's criteria from the hidden field on the main form.

When the criteria is 0 the subform's query works fine but when the criteria is set to <>0 the query is blank . Is there any way to pass a value of <>0 without having to build a dynamic query string for the subform's record source?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

aikimarkCommented:
If these are positive numeric values, you could replace the =0 and <>0 expression with a Sgn([fieldname])=0  or Sgn([fieldname])=1, which would be implemented with
Where
Sgn([fieldname1])=[parmFilter1Value] And
Sgn([fieldname2])=[parmFilter2Value] And
Sgn([fieldname3])=[parmFilter3Value] And
Sgn([fieldname4])=[parmFilter4Value] And
Sgn([fieldname5])=[parmFilter5Value]

You would pass the different parmFilter#Value values (0 or 1) to the query.  

You could also also alter the query, adding five new fields with the Sgn() function.
John CarrollAuthor Commented:
I understand with the Sgn() function does but not sure how this would o what I need. I either need to see all records equal to 0 or all not equal 0. All I am trying to determine is if it is possible to pass the <> operator with 0 from a form to the criteria of a query. E.g  with [Forms]![frmFormName]![txtFieldName] as the criteria of the field in the query.

If someone can confirm that this is not possible I'll have to build a dynamic query string and assign it to the record source of the subform.
aikimarkCommented:
It does not work if the field(s) might contain negative numbers.
aikimarkCommented:
You could also create expressions in your query that compare the field(s) = 0.  You would then compare these expressions to True or False.
Example:
Where
([fieldname1]=0)=[parmFilter1Value] And
([fieldname2]=0)=[parmFilter2Value] And
([fieldname3]=0)=[parmFilter3Value] And
([fieldname4]=0)=[parmFilter4Value] And
([fieldname5]=0)=[parmFilter5Value]
John CarrollAuthor Commented:
One thing I did notice is that in the criteria for the query field both of these work:

[Forms]![frmFormName]![txtFieldName]
<>[Forms]![frmFormName]![txtFieldName]

If there was just a way to bring the value back from the form to reproduce the second example.
aikimarkCommented:
You can certainly directly reference a form control in your query.  I've supplied three different ways of solving your problem and you have just supplied another.  Please pick one.

While writing this, I just thought of two more that involve the CBool() and Eval() functions.
BitsqueezerCommented:
Hi,

what you cannot do is fill a field with an expression and expect that you can use that expression by simply using the field reference (like "Forms!..."). It would be used as a string containing the expression.

The really easier way of solving this is - don't use "<> 0" as a hidden text field - the user cannot see that so you don't need the display of "<> 0", you only need that for internal use, so why do you not simply fill the hidden text box with "0" or "1" (or -1, if you want to have a True value)?

In this case you can distinguish the records in the subform by using something like "WHERE [Forms]![frmFormName]![txtFieldName] = True" (or False, or 0/1, whatever you prefer).

If you want to have a kind of filter, you can also use an Iif/Switch like that:

WHERE 1 = (Switch(Nz([Forms]![frmFormName]![txtFieldName]) = "0"  , IIf(Nz(MyField,0) =  0, 1, 0),
				  Nz([Forms]![frmFormName]![txtFieldName]) = "<>0", IIf(Nz(MyField,0) <> 0, 1, 0),
				  0))

Open in new window


Cheers,

Christian
PatHartmanCommented:
As Christian already pointed out, you can't change the structure of a query on the fly and that is what you are attempting to do.  Changing a relational operator could conceivably cause the query engine to chose a different execution plan.  Therefore, once the query is built and saved as a querydef, RO's cannot be changed.  Parameters can only substitute "data" values.  

If your values are simply true and false, then
Select ...
From ...
Where SomeField = Forms!yourform!yourcontrol;

will work.

If you need to change the structure of the query, then you'll need to build it in VBA and pass it to the form/report you are opening.
John CarrollAuthor Commented:
aikimark I understand that you offered some solutions but I didn't understand how to implement it.

Bitsqueezer the Immediate If could work but I haven't had a chance to test because I was testing another solution that is now working but I am going to also test with the IIF.

Just to clarify, the values are not true/false. The records in question can have up to 6 different status levels but in this case I either needed to pass =0 for cancelled or <>0 to the query to show everything else. I remember trying to solve this issue about a year ago but gave up and evoked a filter via code.

Through extensive troubleshooting, I noticed that that when 0 is passed to the query (from the form), the SQL showed WHERE (((tblBookings.BookingStatusID)=0)) so Access adds the "=" automatically when building the string. If attempting to pass <>0 from the form the SQL string ends up looking like WHERE (((tblTourBookings.BookingStatusID)=<>0)) because Access is not expecting anything but a value and still adds the "=" which makes sense.

This all started when I added another option group with buttons A-Z to the form to narrow down a massive list to show only last names that begin my the letter button clicked. This is accomplished using the same filtering technique had been using for Active/Canceled. But now I have 2 separate filtering functions which battle each other thus the reason why I revisited passing the Active/Canceled value directly to the query.

Then a light went (ding!) to attempt to filter on 2 fields using code so I experimented and this is what I did.

Private Sub opgFilter_AfterUpdate()

Dim strFilterBy As String

    Select Case opgFilter.Value
        Case 1
            opgFilter = "A"
        Case 2
            opgFilter = "B"
        Case 3
            opgFilter = "C"
        Case 4
            opgFilter = "D"
        Case 5
            opgFilter = "E"
        Case 6
            opgFilter = "F"
        Case 7
            opgFilter = "G"
        Case 8
            opgFilter = "H"
        Case 9
            opgFilter = "I"
        Case 10
            opgFilter = "J"
        Case 11
            opgFilter = "K"
        Case 12
            opgFilter = "L"
        Case 13
            opgFilter = "M"
        Case 14
            opgFilter = "N"
        Case 15
            opgFilter = "O"
        Case 16
            opgFilter = "P"
        Case 17
            opgFilter = "Q"
        Case 18
            opgFilter = "R"
        Case 19
            opgFilter = "S"
        Case 20
            opgFilter = "T"
        Case 21
            opgFilter = "U"
        Case 22
            opgFilter = "V"
        Case 23
            opgFilter = "W"
        Case 24
            opgFilter = "X"
        Case 25
            opgFilter = "Y"
        Case 26
            opgFilter = "Z"
        Case 27 'Show All
            opgFilter = ""
    End Select

If Me!opgBookingStatus = 1 Then 'Show active bookings
    If opgFilter <> "" Then
        With Me.frmTourBookingsSubform_Cust.Form
            .Filter = "[LastName] Like '" & opgFilter & "*' And [BookingStatusID] <> 0"
            .FilterOn = True
        End With
    Else
        With Me.frmTourBookingsSubform_Cust.Form
            .Filter = "[BookingStatusID] <> 0"
            .FilterOn = True
        End With
    End If
ElseIf Me!opgBookingStatus = 0 Then 'Show cancelled bookings
    If opgFilter <> "" Then
        With Me.frmTourBookingsSubform_Cust.Form
            .Filter = "[LastName] Like '" & opgFilter & "*' And [BookingStatusID] = 0"
            .FilterOn = True
        End With
    Else
        With Me.frmTourBookingsSubform_Cust.Form
            .Filter = "[BookingStatusID] = 0"
            .FilterOn = True
        End With
    End If
End If

End Sub

Open in new window


If anyone has suggestions to optimize this code feel free to chime in but I have tested it and it is working so far.
aikimarkCommented:
@Frozone
...but I didn't understand how to implement it
Until your most recent comment, you hadn't shown us any database object (form, query) or code for which to suggest a context-specific solution.
BitsqueezerCommented:
Hi,

it should be also work with this shorter version:

Private Sub opgFilter_AfterUpdate()

    Dim strFilter As String
    
    If opgFilter.Value = 27 Then
        opgFilter = ""
    Else
        If opgFilter.Value >= 1 And opgFilter.Value <= 26 Then
            opgFilter = Chr(opgFilter.Value + 64)
        End If
    End If

    With Me.frmTourBookingsSubform_Cust.Form
        If opgFilter <> "" Then
            strFilter = "[LastName] Like '" & opgFilter & "*' And [BookingStatusID] # 0"
        Else
            strFilter = "[BookingStatusID] # 0"
        End If
        
        Select Case Me.opgBookingStatus
            Case 1 'Show active bookings
                strFilter = Replace(strFilter, "#", "<>")
            Case 0 'Show cancelled bookings
                strFilter = Replace(strFilter, "#", "=")
            Case Else
                strFilter = ""
        End Select
        .Filter = strFilter
        If strFilter <> "" Then .FilterOn = True
    End With
End Sub

Open in new window


That's what I meant at the beginning: There are lot of possible ways to filter a query, but you didn't describe it detailed enough.

Cheers,

Christian
aikimarkCommented:
I've looked at the code and it doesn't help me with multiple conditions.  Since you mentioned that there would be five that the user could select, we would benefit by knowing the other four numeric column names, in addition to BookingStatusID that can be filtered as well as the associated form controls that supply that information.

Likewise, the query SQL for the sub report would be most helpful.
John CarrollAuthor Commented:
Yes because all I really wanted to know was if it was possible to pass a <>0 value from a form to a query. If the answer was no then I was confident I could do it a more complicated way so I was trying to avoid that if possible. But I do very much appreciate those who offered various suggestions beyond the original question.
aikimarkCommented:
"<>0" can certainly be in the filter property string.
John CarrollAuthor Commented:
Not sure what you mean by "the other four numeric column names". This suggest to me that you think there is a separate field for each status but I could be misunderstanding. Booking Status is a lookup table.

There are actually a total of 6 status levels of a bookings. So in this case we need to either see all active bookings or cancelled bookings.

BookingStatusID      Status
0      Cancelled
1      Pre-Booked
2      Pending
3      Confirmed
4      Paid-In-Full
9      Staff

The code I posted seems to be working but I also printed out the code that Bitsqueezer offered so I am studying that.
aikimarkCommented:
I didn't like changing the opgFilter value and would recommend you change a local variable instead.
Example:
    Dim strOpgFilter As String
    
    Select Case opgFilter.Value
        Case Is >= 27
            strOpgFilter = vbNullString
        Case 1 To 26
            strOpgFilter = Chr(opgFilter.Value + Asc("A") - 1)
        Case Else
            strOpgFilter = vbNullString
    End Select

Open in new window

aikimarkCommented:
Do you allow your user to filter on any particular non-zero booking status or is your user interface strictly a cancelled or everything-else proposition?
aikimarkCommented:
The approach I usually take in similar situations is to build a filter string with concatenation or Join() and then feed that string into the filter method/property.  This is the approach that Christian is taking as he builds his strFilter string.

I'm inferring from your opg control name that this is a radio button group with mutually exclusive user selections/choices.
John CarrollAuthor Commented:
Exactly, either cancelled or everything else.

I am being told that N, R and T returns a blank query/subform which is quite bizzar so I need to troubleshoot. Oh the joy of programming...
aikimarkCommented:
that means that no one has a last name that begins with any of those three letters.
John CarrollAuthor Commented:
Actually it means that I put the wrong value in three of the option buttons. When you copy and past a new button the default value is -1 so instead of a value of 14 for instance I had -114. It's called sloppy work at 3am. Wish everything was that easy to fix.
PatHartmanCommented:
Yes because all I really wanted to know was if it was possible to pass a <>0 value from a form to a query
I gave you the answer - NO.  You can pass only data values.  The relational operator is structural and it cannot be passed as a parameter.

Select fld1, fld2 from tbl1 where somefield = 456 and someotherfield <> 0;

There are two pieces of data in this query - 456 and 0.  Those are the ONLY parts that can be parameterized.

Select fld1, fld2 from tbl1 where somefield = Forms!yourform!control1 and someotherfield <> Forms!yourform!control2;
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.