Best way to do this-complex if, then function?

[p5x]

Wasn't quite sure where to post this.

i've got 2 types of members in my system-member A and member B for the sake of simplicity, there is also 4 types of items that these members can get, lets call them item 1-->4, each item has a different price depending on the item iteself and what member type, A or B, is getting it.
How do i display the correct value (in a text box in a form) if say member type A gets item 2 or member type B gets item 3?

Basically the (only) conditions could be: either A1, A2, A3, A4, B1, B2, B3, B4

atm, i've got 2 records in a table with the prices for member A in one row and prices for member B in the other row..now the only problem is getting the correct value to appear in a form depending on which circumstances belong to the member and which item s/he is getting.

Giving each item a price_ID wouldn't work as there are only 8 possible scenarios, and it also depends on the member type getting the item, not just the item type.

hope this makes sense

 

[p5x]

Maybe im approaching this all wrong but im more used to using excel. in that i would put something like IF conditions=whatever then show cell B20, else show cell B10.
Is there a similar method of referencing fields in access, something like [table name], [record number], [field].

 

[Mile-O]

Rather than using the Excel style IF function, Access lets you use the IIf() function in a similar manner. Look it up in the help.

 

[p5x]

thanks, i've had a look at the IIf function, would I have to use 8 seperate IIf statements to show what I want to?
I'm trying to use the function as a control source of a text box in a form but i'm having trouble referencing some of the fields on the sub form, basically I want to pull the itemtype from a sub form and member type from another sub form (on the same page) but #name? appears in the field i'm calculating.

Is this the correct syntax:

=Iif(Forms![name of form]![name of control] = whatever, "test", "test2")

 

[Mile-O]

Personally, I would build the IIf function in code.

You can then say:

txtYourTextBox = IIf(control = conditionA, 1, IIf(control = conditionB,2,3))

You can nest the IIf function as necesary.

For subform syntax, download the word document from this thread.

 

[p5x]

Can you do that?
I thought the only way of doing it would be the If function.

 

[p5x]

i'm still not sure how i'd have the prices looked up from the table.
How would i get it to look in a certain record based on what was selected in member type and item type?

 

[simongallop]

Create you table with 3 columns. Item_No, Price A, Price B

Now you can return the value needed so if wanting B3 you would look at: DLookUp("[Price B]", "MyPriceTable","[Item_No]=3")

Next step therefore would be: If you have a form with 2 textboxes and you want to return the price to a third box (call the boxes txtMem, txtItem, txtResult)

In txtResult you would have:

=DLookUp(IIf([txtMem]="B","[Price B]","[Price A]"),"[Test]","[Item_No]=" & Val([txtItem]))

HTH

 

[p5x]

oh, is that what you meant. I had thought about that but then decided against it as it would mean having to enter a price twice for each item, even though there are only 8 possible prices.

this is what I was thinking of:

tblcharges:
member_type
charge_item_1
charge_item_2
charge_item_3
charge_item_4

in this table i have 2 records for the 2 members, A and B and the appropriate charge under each heading.

However, I dont think this would work very well, maybe having these fields would help:

charge_item_1_A--->charge_item_4_A
charge_item_1_B--->charge_item_4_B

Or is that not a good idea of doing it? I think the main limitation would be that you wouldn't be able to have more than 2 member types this way but thats not really a problem as I only need 2 member types.

 

[p5x]

sorry, what I meant to say was that you will have to enter a price for each item when you add it to the item list, I wasn't sure that this was a good idea because there are only 8 possible prices that an item can have, so I thought it would be easier to store the 8 prices just once in a table and then look them up depending on the item and member type chosen?

Thanks for the advice btw, looks like ive got a long way to go

 

[p5x]

First of all, can I just say thanks for helping me out with my problem. I apologise if I seem to be dismissing your suggestions but its probably because I don't understand you properly as my grip of access is fairly basic at the moment, as you will have noticed.

So anyway, are you suggesting that I have a table with 8 rows in a table, from what I understand, this would be one of the rows in the table.

PriceID
1

MemberTypeID
1

ProductID
A

UnitPrice
2.00

Is that what you meant?
I would then make 8 rows like the one above, replacing member type ID with 1,2,3,4 and product ID with A, B, C, D and unit price would be changed to whatever the price was for that particular combination of member and product type.

 

[p5x]

ahh, that makes more sense now.
I can see why you were calling my method illogical