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

 

[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?

 

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

 

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

 

[KitaYama]

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

1- If you use integer data type, you can use much more flags.
for example 4478 in binary is 1000101111110. there's 13 flags.

2- Unfortunately I couldn't find a way to use bitwise in a query.
Another time to avoid bit flags is when you are storing the information in a database. Most database back-ends do not support bitwise operations in SQL statements, so you can’t test individual flag settings to filter your result set. Plus, ODBC (version 3 anyway) doesn’t support any scalar functions that provide this capability, regardless of what the back end can do.

Imagine you have a table with more than 10 thousands records. Now you want to view a list of records that bit 3,5,6,7,8 are true.
Do you have any solution?
I had to use a function that made the query so slow.

Edit : @theDBguy 's solution seems we have a way to use bitmasks in a query.

 

[theDBguy]

1- If you use integer data type, you can use much more flags.
for example 4478 in binary is 1000101111110. there's 13 flags.

2- Unfortunately I couldn't find a way to use bitwise in a query.
Another time to avoid bit flags is when you are storing the information in a database. Most database back-ends do not support bitwise operations in SQL statements, so you can’t test individual flag settings to filter your result set. Plus, ODBC (version 3 anyway) doesn’t support any scalar functions that provide this capability, regardless of what the back end can do.

Imagine you have a table with more than 10 thousands records. Now you want to view a list of records that bit 3,5,6,7,8 are true.
Do you have any solution?
I had to use a function that made the query so slow.

Have you seen this? Hope it helps...

Bitwise Operation in an Access Query

Let me preface this article by saying this is not a topic I am really familiar with. I only decided to write it to share an experience I had...

thedbguy.blogspot.com

 

[KitaYama]

Have you seen this? Hope it helps...

Bitwise Operation in an Access Query

Let me preface this article by saying this is not a topic I am really familiar with. I only decided to write it to share an experience I had...

thedbguy.blogspot.com

Not this one, but similar ones.
As your blog shows, SQL statement doesn't support bitwise. You have to write your own function that not only brings down the speed, but it makes the code too complicated.
Back to my example, imagine you have a field that covers 20 flags and you need a query to show a list of records to show the state of 1,2,3,8,9,11,17,18 bits. You have to add 8 fields to your query and in each of them call your function to return a value for that field to receive something like :

ID	Flag1	Flag2	Flag3	Flag8	Flag9	Flag11	Flag17	Flag18
135	True	False	True	True	True	False	False	False

And imagine the sql runs on a table with several hundred thousands of records.
If you have a better way, I'm all ears. Because I really need it.

I know you're an expert and a very good one and also you don't need to hear it from a newbie, but just take a look at this page for fun:

Advantages and disadvantages of using bit masks in database

Not so long ago I talked to my colleague and he was definitely against using bit masks because it is hard to understand all the values that are stored in the database. In my opinion it is not alway...

softwareengineering.stackexchange.com

Edit : My apologies to OP for going off topic.