Solved Multiple options on one field (1 Viewer)

debsamguru

Member
Local time
Today, 16:01
Joined
Oct 24, 2010
Messages
75
Hi,

I have a Status field that can contain the numbers 1 - 5 which relate to various statuses. I have a form with check boxes that allows users to select one or more of these statuses. However I can't work out how to word the criteria in the query.

I am creating a StatusSearch field in my code which builds up the criteria and which is used in the criteria field i.e. "Like [Forms]![OrdersSearchF]!StatusSearch" but it just doesn't work. If I hard code the criteria e.g. "like 4 or 5", it works fine (the SQL shows as ((OrderHeaderT.StatusID) Like 4 Or (OrderHeaderT.StatusID)=5)) but as soon as I try it with a passed string, it doesn't work.

What do I need to pass in the string?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:01
Joined
Oct 29, 2018
Messages
19,164
Hi. Check out the link I posted in this other thread. I am hoping you might get some ideas from it.

 

debsamguru

Member
Local time
Today, 16:01
Joined
Oct 24, 2010
Messages
75
I get how this would be useful in writing sql but, in this case I am hoping just to pass the field to the query. As soon as I set the field in the variable, it stops working.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:01
Joined
Oct 29, 2018
Messages
19,164
I get how this would be useful in writing sql but, in this case I am hoping just to pass the field to the query. As soon as I set the field in the variable, it stops working.
I know. I was hoping you would get an idea from it. I can't explain it right now, because I am not able to open that page from work. I'll see if I can find another post with the explanation. The idea is the same. The reason is the same. You just have to apply it differently in your specific case.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:01
Joined
Feb 19, 2002
Messages
36,866
NEVER use LIKE unless the string you are searching with contains wild cards. You are searching for specific, discrete values so you would use = as the relational operator, not LIKE.

Not sure why In() is a problem. That seems to be what is called for here.

The problem with In() is you have to build the Where clause with VBA to use it. You cannot pass the In() string as an argument because that would alter the structure of the query. Arguments can only be single value substitutions.

Here is a database with several examples of creating and using an In().
 

Attachments

  • FillFormFields_20210319.zip
    101.6 KB · Views: 122

SHANEMAC51

Active member
Local time
Today, 18:01
Joined
Jan 28, 2022
Messages
294
Here is a database with several examples of creating and using an In().
sorry that I borrowed your table
, the list is not the only solution, it is quite possible to do with a field, especially if the list is long, it will be difficult to see the choice in it

I offer an example based on a field with a value of 0 2-4 -5 6-7 9, in which
Code:
0     show fields with no value(null)
2-4  interval from 2 to 4
-5    temporarily not needed
6-7  more interval
9     more value

you can see everything that was requested and it is important to return the temporarily blocked
moreover, the filter is possible by one of the fields, in this case by yellow or pink

you can also make 3 filtering fields (2 numeric and 1 symbolic, for partial search by name)
 

Attachments

  • у0224-10.png
    у0224-10.png
    26.8 KB · Views: 170
Last edited:

debsamguru

Member
Local time
Today, 16:01
Joined
Oct 24, 2010
Messages
75
The problem is still that I need this to work in a query as I am using this query to export to excel.
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:01
Joined
Sep 21, 2011
Messages
10,850
The problem is still that I need this to work in a query as I am using this query to export to excel.
You say 'you build up the criteria' ?, so where is the problem?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:01
Joined
Feb 19, 2002
Messages
36,866
The problem is still that I need this to work in a query as I am using this query to export to excel.
This is the first we've heard that you intend to export the query.

Have you looked at the examples I posted? Two of them create SQL which is then used to replace a recordsource. You could also save the SQL as a querydef. Then instead of exporting a table, you export the saved querydef. I'm not sure if you can embed a SQL string in the TransferSpreadsheet but I'd try it first and if that doesn't work save the querydef and export that.
 

debsamguru

Member
Local time
Today, 16:01
Joined
Oct 24, 2010
Messages
75
Sorry I haven't responded, I got waylaid with other parts of the db!

So, here is a screenshot of the selection form.
OrdersSelectF.png


When I click on Select Orders, it takes me to the next form where display my filtered records.
By using the following code, I populate the StatusSearch field which is picked up in the next field.

Code:
    Me.StatusSearch.Value = ""

    If ActiveYN = 0 Then
        Me.ActiveParameter.Value = ""
    Else
        Me.ActiveParameter.Value = 1
        strIN = "1,"
    End If

    If PartShippedYN = 0 Then
        Me.PartShippedParameter.Value = ""
    Else
        Me.PartShippedParameter.Value = 2
        If strIN <> "" Then
            strIN = strIN & "2,"
        Else
            strIN = "2,"
        End If
    End If

    If ShippedYN = 0 Then
        Me.ShippedParameter.Value = ""
    Else
        Me.ShippedParameter.Value = 3
        If strIN <> "" Then
            strIN = strIN & "3,"
        Else
            strIN = "3,"
        End If
    End If

    If InvoicedYN = 0 Then
        Me.InvoicedParameter.Value = ""
    Else
        Me.InvoicedParameter.Value = 4
        If strIN <> "" Then
            strIN = strIN & "4,"
        Else
            strIN = "4,"
        End If
    End If

    If PaidYN = 0 Then
        Me.PaidParameter.Value = ""
    Else
        Me.PaidParameter.Value = 5
        If strIN <> "" Then
            strIN = strIN & "5,"
        Else
            strIN = "5,"
        End If
    End If

    If strIN <> "" Then
        Me.StatusSearch.Value = Left(strIN, Len(strIN) - 1)
    End If

Long-winded but it works to a degree. I got it working for the form display by applying the passed field to my query as the form loaded.
Code:
Private Sub Form_Load()
    Dim StatusString As String
    Dim query As String

    StatusString = Forms!OrdersSelectF!StatusSearch

    query = "SELECT * FROM OrderSelectionQ WHERE OrderHeaderT.StatusID IN(" & StatusString & ");"
    Me.RecordSource = query
    
End Sub

Great. Step one done. However, the other part of the conundrum is that I have an Export button on the form which allows me to export the data on the form. It currently runs
Code:
    DoCmd.OutputTo acOutputQuery, "OrderSelectionQ", acFormatXLSX, FileName, True
but obviously that won't work here because the OrderSelectionQ has been filtered by my parameters. I don't want to use acOutputForm because that has too many unnecessary fields.

Any ideas gratefully accepted.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 16:01
Joined
Sep 12, 2006
Messages
14,901
If you never have more than 8 flags, you could use bit wise logic, and store these flags in a single byte.
 

debsamguru

Member
Local time
Today, 16:01
Joined
Oct 24, 2010
Messages
75
I've tried to use a querydef but I'm obviously doing something wrong as nothing seems to happen
Code:
Private Sub Form_Load()
    Dim StatusString As String
    Dim fullquery As String
    Dim qdef As dao.QueryDef
    Dim db As dao.Database

    Set db = CurrentDb

    StatusString = Forms!OrdersSelectF!StatusSearch

    fullquery = "SELECT * FROM OrderSelectionQ WHERE OrderHeaderT.StatusID IN(" & StatusString & ");"
    Me.RecordSource = fullquery
    Set qdef = db.CreateQueryDef("tmpExportQry", fullquery)

then when I go to export

Code:
    DoCmd.OutputTo acOutputQuery, "tmpExportqry", acFormatXLSX, FileName, True
    DoCmd.DeleteObject acQuery, "tmpExportqry"

There are 2 issues -

1. Having processed the Sub Form_Load, I would expect the tmpExportQry to be available to be looked at - but it isn't there until after I click on the export button. It's obviously because the query isn't run until later.
2. No records are in the tmpExportQry which sort of defeats the object.

I know I'm doing something wrong but I can't work out exactly what.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:01
Joined
Feb 19, 2002
Messages
36,866
Try refreshing the collections after you add the querydef
 

debsamguru

Member
Local time
Today, 16:01
Joined
Oct 24, 2010
Messages
75
FileName is the output file defined further up in the code. I know that that part works.
 

Users who are viewing this thread

Top Bottom