Generate a query of Project ID based on a comma separated list in a text box.

[rhewitt]

Hi All,



I'm trying to generate an OR query using a user inputted comma separated list. I've replaced the comma's with Or statements, but I'm getting an error when I enter more than one value into the textbox. I'm assuming this is because the value is treated as text rather than two numbers separated by an Or.



The first box is the textbox that the user enters Project numbers into (tboxProjects). The second box is the Query (tboxQuery) generated from the user's entry using a simple Replace() call. The second textbox will be hidden once the bugs are ironed out. When the user clicks generate query three things happen.

1. Check if the box is null, if it's null give a message saying you have to enter at least one value.
2. If tboxProjects has a value, generate a query: Me.tboxQuery = Replace(Me.tboxProjects.Value, ",", " Or")
3. Run a report using tboxQuery as the input of project numbers.

If I run this with only one number, it works as expected. If I have any more than one number it fails with the following error:


I'm assuming that as the numbers are combined with Or, MS Access is converting the entire value to a string. So when the [Forms]![frmProjectID].[Form]![tboxQuery] is replaced with "25 Or 29", Access can't figure out why a string would be used to search on an integer field.

Any suggestions or work-arounds would be greatly appreciated.

[orange]

What is the value of tboxquery

in this

Me.tboxQuery = Replace(Me.tboxProjects.Value, ",", " Or")

?

I would recommend a space in Replace(Me.tboxProjects.Value, ",", " Or ") --between Or and "

Where is the code to generate the query?
Also, be aware that users often make typos.

[rhewitt]

Your advice on anticipating user's will make errors is well taken. This is mostly a first crack to see if this technique will work. I fully intend on going back to fix errors my users might make in an iterative process.

Is white space ignored in VBA? By adding a space after Or in the replace call would it avoid errors for users that try to input something like: 52,53,54 ?

Here's my code for the button:

Private Sub btnGenQuery_Click()
    If IsNull(Me.tboxProjects) Then
        MsgBox "You must enter at least one project.", vbInformation
    Else
        Me.tboxQuery = Replace(Me.tboxProjects.Value, ",", " Or ")
        Debug.Print Me.tboxQuery
        
        DoCmd.OpenReport "DOItempPrjNums", acViewReport
    End If
End Sub

[orange]

And what was the value of me.tboxquery?
The space should not be an issue. I thought you might get replacements such as 45 or68, and that would be a problem(at least in my view).

So hidden somewhere in your processing is the "query". It will be the recordsource of your report.

Can you show us what the report recordsource is? Both the original and rendered SQL with the constraint?

[rhewitt]

When the value of tboxProjects is 54, 56
The value of tboxQuery is 54 Or 56

For the form RecordSource, I simply put [Forms]![frmProjectID].[form]![tboxQuery] in the criteria field for ProjectID in the Query Builder.



Can I pass a query value in the DoCmd.OpenReport call as the optional [WhereCondition] instead of referencing the value on the form?

[rhewitt]

I've tried sending the query as the WhereCondition of the OpenReport command. My query is showing up in the "Filter" portion of the Report's property sheet, but it's generating the report for ALL records instead of the records included in the WhereClause.

Private Sub btnGenQuery_Click()
    Dim query As String
    
    If IsNull(Me.tboxProjects) Then
        MsgBox "You must enter at least one project.", vbInformation
    Else
        Me.tboxQuery = Replace(Me.tboxProjects.Value, ",", " Or")
                
        query = "ProjectID = " & Me.tboxQuery
        Debug.Print query
        DoCmd.OpenReport "DOItempPrjNums", acViewReport, , query
    End If
End Sub

[rhewitt]

FACE PALM MOMENT.

A Query can't read ProjectID = 52 Or 53 it must read ProjectID = 52 Or ProjectID = 53

Edited the Replace function include "ProjectID = " after the Or.

Private Sub btnGenQuery_Click()
    Dim query As String
    
    If IsNull(Me.tboxProjects) Then
        MsgBox "You must enter at least one project.", vbInformation
    Else
        Me.tboxQuery = Replace(Me.tboxProjects.Value, ",", " Or ProjectID = ")
                
        query = "ProjectID = " & Me.tboxQuery
        Debug.Print query
        DoCmd.OpenReport "DOItempPrjNums", acViewReport, , query
    End If
End Sub

[orange]

Ok, that is true. However, there is another construct that you could use by adjusting your code a bit.

Instead of
WHERE ProjectID = 52 Or ProjectID = 53

you can use
WHERE ProjectID In ( 52 , 53 ) ... as long as ProjectId is numeric

[BlueIshDan]

https://www.accessforums.net/code-re...orm-44282.html