VBA Code for In List

[tucker61]

I have the following code, now the lblsource could be upto 10 different values, So how do i do this instead of having 10 different "OR"s ?

If tbrootcause = "Packaging" And lblsource.Value = "Brand" Or lblsource.Value = "UK Landed Indirect" Or lblsource.Value = "UK Landed direct" Then

 

[arnelgp]

use Instr() function

Const ValidValues As String = "/Brand/UK Landed Indirect/"

If tbrootcause = "Packing" And Instr(ValidValues, [lblsource].Value & "") > 0 Then
...
End If

**
lblsource is a Textbox and not Label control.
if lblSource is a Label, use it's caption property:

If tbrootcause = "Packing" And Instr(ValidValues, [lblsource].Caption) > 0 Then
...
End If

 

[tucker61]

Worked like a dream.

 

[CJ_London]

you can also use the eval function to mimic the sql 'in'

if eval(myfield] & " IN ('Brand','Indirect')") then

doesn't look like it is an issue in this case but if you were looking for "shore" in "/inland/offshore/" then instr would return true - better to look for "/" & "shore" and "/"

 

[jdraw]

tucker,

Depending on volumes and whether the values change frequently, you could also apply the technique described in this link. The author uses it here for excluding things, but can easily be adapted for inclusion. You could add or remove values from the table without changing vba code. Just another option.

 

[Pat Hartman]

Life is too short to spend it maintaining code like this. make yourself a table and use that to control the code.

Just FYI, the code you are currently using is not working as you think it should. When you combine AND and OR operands in a complex expression, you almost always need to use parentheses to ensure that the expression gets evaluated as you intend. For example:

If tbrootcause = "Packaging" And lblsource.Value = "Brand" Or lblsource.Value = "UK Landed Indirect" Or lblsource.Value = "UK Landed direct" Then

This statement is being evaluated as

If (tbrootcause = "Packaging" And lblsource.Value = "Brand")
Or lblsource.Value = "UK Landed Indirect" Or lblsource.Value = "UK Landed direct" Then

When what you probably want is:

If tbrootcause = "Packaging" And
(lblsource.Value = "Brand" Or lblsource.Value = "UK Landed Indirect" Or lblsource.Value = "UK Landed direct") Then

The solutions offered, all of which will work, inherently solve this problem but no one pointed it out specifically.

Also, ALWAYS use "Me." to qualify form/report references. It gives you Intellisense AND it tells Access where a variable is defined and eliminates the need to search all the loaded modules. Value is the default property of a control on a form or report so it can be omitted to make the code more concise.

 

[tucker61]

Life is too short to spend it maintaining code like this. make yourself a table and use that to control the code.

Just FYI, the code you are currently using is not working as you think it should. When you combine AND and OR operands in a complex expression, you almost always need to use parentheses to ensure that the expression gets evaluated as you intend. For example:

If tbrootcause = "Packaging" And lblsource.Value = "Brand" Or lblsource.Value = "UK Landed Indirect" Or lblsource.Value = "UK Landed direct" Then

This statement is being evaluated as

If (tbrootcause = "Packaging" And lblsource.Value = "Brand")
Or lblsource.Value = "UK Landed Indirect" Or lblsource.Value = "UK Landed direct" Then

When what you probably want is:

If tbrootcause = "Packaging" And
(lblsource.Value = "Brand" Or lblsource.Value = "UK Landed Indirect" Or lblsource.Value = "UK Landed direct") Then

The solutions offered, all of which will work, inherently solve this problem but no one pointed it out specifically.

Also, ALWAYS use "Me." to qualify form/report references. It gives you Intellisense AND it tells Access where a variable is defined and eliminates the need to search all the loaded modules. Value is the default property of a control on a form or report so it can be omitted to make the code more concise.

Thanks for this.. I did add the Me. Code when I came to running the code.

The values were all in a table originally, but I have other values in that table as well that I don't want to use as part of my criteria. Hopefully tomorrow I will have a bit more time to look at the table.

I am thinking a new table with the values I am looking for in them.

Sent from my SM-G950F using Tapatalk

 

[CJ_London]

The values were all in a table originally, but I have other values in that table as well that I don't want to use as part of my criteria. Hopefully tomorrow I will have a bit more time to look at the table.

your db and may be appropriate to create another table, but another option is to add a flag field to your existing table to indicate which values are to be used where.

 

[tucker61]

your db and may be appropriate to create another table, but another option is to add a flag field to your existing table to indicate which values are to be used where.

Thought of this, but the original table contains over 1000 entries. I over write this table every month, with data provided by someone else, so i don't think this option would work.

Sent from my SM-T715 using Tapatalk

 

[Pat Hartman]

Sounds like you need to create a new table with just the values you need to control the process. OR, if you foresee multiple paths, add the values plus another field to indicate the path to take. I rarely use Y/N fields for this. I find integers are more flexible.