Solved Multiple options on one field

[debsamguru]

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]

Hi. Check out the link I posted in this other thread. I am hoping you might get some ideas from it.

request several data in a parameter

Hi guys, I am trying to create a query that asks me for several data in a parameter. For example, in my table I have 1, 2, 3, 4, 5 and 6 as data. and I want a parameter that allows me to enter several data, to return me the numbers that I entered.

www.access-programmers.co.uk

 

[debsamguru]

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 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.

 

[Gasman]

Use In() ?

 

[theDBguy]

Use In() ?

Not always...

 

[Pat Hartman]

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().

 

[SHANEMAC51]

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

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)

 

[debsamguru]

The problem is still that I need this to work in a query as I am using this query to export to excel.

 

[Gasman]

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]

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]

Sorry I haven't responded, I got waylaid with other parts of the db!

So, here is a screenshot of the selection form.

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.

    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.

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

    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]

If you never have more than 8 flags, you could use bit wise logic, and store these flags in a single byte.

 

[debsamguru]

Does anyone have any idea on how to export this?

 

[theDBguy]

Does anyone have any idea on how to export this?

Have a look at the CopyFromRecordset method.

 

[debsamguru]

I've tried to use a querydef but I'm obviously doing something wrong as nothing seems to happen

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

    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]

Try refreshing the collections after you add the querydef

 

[debsamguru]

Adding a me.refresh after the set qdf made no change.

 

[sonic8]

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

What is Filename?

 

[debsamguru]

FileName is the output file defined further up in the code. I know that that part works.