Formula help requested

[Guest]

hello gurus

I'm quite new to Access and I need help writing a function (I think). My
form has two combo boxes, cboGroup and cboTask. When the user chooses a
Group and a Task, I need to write the corresponding value of Category to
txtCat, according to the table shown below.

Category
Group 1 2 3 4
1 A B C D
2 B C D A
3 C D A B
4 D A B C

For example, when Group = 3 and Task = D, then txtCat must hold the value 2.

thanks in advance for any assistance
Terry

 

[Carl Rapson]

hello gurus

I'm quite new to Access and I need help writing a function (I think). My
form has two combo boxes, cboGroup and cboTask. When the user chooses a
Group and a Task, I need to write the corresponding value of Category to
txtCat, according to the table shown below.

Category
Group 1 2 3 4
1 A B C D
2 B C D A
3 C D A B
4 D A B C

For example, when Group = 3 and Task = D, then txtCat must hold the value
2.

thanks in advance for any assistance
Terry

You could do all this with a formula, but it might be easier to set up a
table that contains these relationships. That way, it only takes one line of
code to set txtCat, plus you can change the relationships, or add additional
groups and/or tasks, at any time by simply updating the table.

The table (tblJunction) would look something like:

fldGroup Number(Integer)
fldTask Text(1)
fldCategory Number(Integer)

A typical entry would look like: 1, A, 1

Now you can set txtCat by using DLookUp:

txtCat = Nz(DLookUp("fldCategory", "tblJunction", "fldGroup=" & cboGroup & "
AND fldTask=""" & cboTask & """"),0)

Note the additional quotes arounf the Task value, since it's a string.
Another benefit is that you can populate cboGroup and cboTask directly from
the table:

cboGroup.RowSource = "SELECT DISTINCT fldGroup FROM tblJunction ORDER BY
fldGroup;"
cboTask.RowSource = "SELECT DISTINCT fldTask FROM tblJunction ORDER BY
fldTask;"

Carl Rapson

 

[John W. Vinson]

hello gurus

I'm quite new to Access and I need help writing a function (I think). My
form has two combo boxes, cboGroup and cboTask. When the user chooses a
Group and a Task, I need to write the corresponding value of Category to
txtCat, according to the table shown below.

Category
Group 1 2 3 4
1 A B C D
2 B C D A
3 C D A B
4 D A B C

For example, when Group = 3 and Task = D, then txtCat must hold the value 2.

thanks in advance for any assistance
Terry

"string out" your table:

Group Task Category
1 A 1
1 B 2
1 C 3
....
4 C 4

Then you can simply join this table to your Form's recordsource table, joining
Group to Group, Task to Task, and display the category.

John W. Vinson [MVP]