Access textbox with multiple values to query

Question

I have an Access database, where I have a form with a textbox (named c1) and a button. When I click the button it opens a datasheet form with information filtered by the textbox value.

The button vba looks like this:

c1.Value = Replace(c1.Value, ",", " Or ")
DoCmd.OpenForm ("dsForm")

The query behind the datasheet looks something like this in design view:

Field:      Name1  |  Name2
Criteria:          |  Like [Forms]![Menu]![c1].[value]

This is so I could later export the results of this query to excel.

So my issue is that I want to enter values into the textbox and separate them with a comma, which would be later turner into an Or by vba. Why I'm doing this with 1 textbox not multiple, is because I could have many values that I want to search by.

Right now it works if I enter one value into the textbox, but when I enter 2 values it's not working. I'm pretty sure that the query is taking the whole statement as a string for example if I enter 110,220 it's supposed to be Like "110" or "220", but on the query it would be Like "110 or 220".

I've tried by setting the field to be either a string or a number as well. How would I manipulate the criteria on a query from vba?

Craig Gross · Accepted Answer · 2016-10-13 22:24:22Z

1

I recommend writing a SQL string with the IN statement instead of the OR, and using the OpenArgs event to pass data from the main form over to the datasheet form.

Main Form Button Code

Dim sql as String

sql = "Select * From [table name] Where Name2 IN (" & c1 & ")"
DoCmd.OpenForm "dsForm", acFormDS, , , , , sql

Datasheet form (dsForm) Code -- Use the Form_Load event.

Private Sub Form_Load()
  Me.RecordSource = Me.OpenArgs
End Sub

The IN statement allows you to use commas. The OpenArgs event allows you to pass values from one form over to another.

answered Oct 13, 2016 at 22:24

Craig Gross

963 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

geeFlo Over a year ago

Why couldn't you just use sql = "WHERE Name2 in (" & c1 & ")" and then DoCmd.openform "dsform", acformDS, , sql and put the SQL statement where the Where statement should be. Then you don't need to pass any openargs or use the recordsource statement in the other form

Craig Gross Over a year ago

@geeFlo You are correct, that fits the situation even better. Though the proper wording is done without the "WHERE" reserved word. 'sql = Name2 in (" & c1 & ")" '

Preston · Accepted Answer · 2016-10-13 13:54:33Z

0

Actually my first method was terrible, read the values into an array like this:

Sub y()
a = "a,b,c,d"

'Split into 1d Array
b = Split(a, ",", , vbTextCompare)

For c = 0 To UBound(b)
    Debug.Print b(c)
Next c

End Sub

You can loop through the array as in the debug.print loop and use each value separately.

answered Oct 13, 2016 at 13:54

Preston

8,26511 gold badges64 silver badges96 bronze badges

Collectives™ on Stack Overflow

Access textbox with multiple values to query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related