First database = major issue and no more time.

[Shapes]

Today is my second day a attempting to create my first access database and am having a major issue:

I have two combo boxes in a form [Status] and [Time] each has four possible answers.

I want combine the answers selected in the combo boxes to decide the [Rating] to be given in a third box on the form - which will be linked to a table.

I have a experience with long excel IF formula but none with VBA.

I really want to avoid long nested If statement with multiple And OR conditions - and the syntax issues that come with them. I have been reading for hours about iif, switch and select statement.

An example of my thinking:

=IIf([Status]="Terrible","Extreme",IIf([Status]="Large" And [Time]="0-4","Extreme",IIf([Status]="Large" And [Time_Catagory]="4-8 hours","Extreme",[”Nope”])))

--- In the past, in Excel, I would have added more If statements and played with the syntax until the formula has worked.

However, I recently decided to try and teach myself Access (2010) and I some VBA so I could move away from multiple spreadsheets with large datasets and things like 7 deep nested if statements.

I am doing this for work and really can not burn much more time just googling and watching youtube to try and resolve this. My boss wants a result and will force me back to excel if I can deliver.

Any help or advice on this would be greatly appreciated. I really don't want to have to run back to Excel with my tail between my legs.

After just a couple of days looking at Access and Normalising I can see why it is time Work retired the multiple large spreadsheets and move to database solution.

In more general terms if there is a youtube series, site or book a newbie to Access 2010 should consume please also let me know.

Thank you in for taking the time to look at my problems and post I really do appreciate it.

 

[Mr. B]

When you are attempting to evaluate more than one selection using VBA you can use the Select Case statement.

First, create a case statement for each of the options available from the first combo box.

Then, create another Select Case statement for each of the option available from the second combo box.

This will allow the selected values from each of the combo boxes to be identified. You can store the values and/or what ever you are needing to save in the appropriate variable type.

Then you can concatenate the located values together and put them in another variable or into another control on your form.

 

[Richard McCabe]

Mr. B
Looks like there are 16 possibilities for the four choices in the two fields.
If status = A And Time = 1 Then
xxxxxxx
yyyyyy
zzzzz
End if
If Status = B and Time = 2 Then
ZZZZZZ
SSSSSS
End If
Fourteen more and we are done. I have done similar things, but not needing so many conditions and have had no trouble. There is also an If, Then, Else If, we can use if we can find a good description of the proper syntax, but I haven't found it yet.

If you know of a good way to specify which of fpur indexes to use from control buttons on a form, let me know.

Thanks,
Richare McCabe

 

[Mr. B]

My suggestion for using nested Select Case statements is still a valid solution. Have you tried using that opiton?

If you don't want to use the Select Case option you can use:

If status = A And Time = 1 Then
     xxxxxxx
ElseIf   status = A And Time = 2 then
     xxxxxxx
ElseIf  status = A And Time = 3 then
     xxxxxxx
ElseIf  status = B And Time = 1 then
     xxxxxxx
Else
     xxxxxxx
End If

If this is not what you want then you could use something like:

If status = A then
     If Time = 1 then
          xxxxxxx
     ElseIf Time= 2 then
          xxxxxxx
     Else
          xxxxxxx
     Endif
ElseIf status = B then
      If Time = 1 then
          xxxxxxx
     ElseIf Time= 2 then
          xxxxxxx
     Else
          xxxxxxx
     Endif
Else
      If Time = 1 then
          xxxxxxx
     ElseIf Time= 2 then
          xxxxxxx
     Else
          xxxxxxx
     Endif
End If