Need help in some queries code

[nicpkh via AccessMonster.com]

My problem is I have 3 customer that using currency USD. In the field MC that
is sales for whole customer that start with code M* all of them using 1 fixed
currency except these 3 customer.

Right now in my queries under field MC I using this code MC: Sum(IIf(
[customer] Between "L*" And "N*",[mv],0))

So what code do I need to put in so that the 3 customer start with M195GI,
M194GI and M196TW will automatically convert the USD to RM based on 3.27
currency rate?

Thank you for your reply.

 

[Marshall Barton]

My problem is I have 3 customer that using currency USD. In the field MC that
is sales for whole customer that start with code M* all of them using 1 fixed
currency except these 3 customer.

Right now in my queries under field MC I using this code MC: Sum(IIf(
[customer] Between "L*" And "N*",[mv],0))

So what code do I need to put in so that the 3 customer start with M195GI,
M194GI and M196TW will automatically convert the USD to RM based on 3.27
currency rate?

You could try something like:

MC: Sum(IIf(customer Like "[LN]*" ,mv, IIf(customer Like
"M*". 3.27 * mv, 0)))

BUT, what will you do tomorrow when the rate is different.
Generally you should put the conversion rate in a table
along with the date it was valid. Then you can modify the
query to get the rate for the appropriate date from the
table instead of having to change the query and dealing with
the issue of yesterday's transactions not knowing what rate
was used.

 

[nicpkh via AccessMonster.com]

Thank for your reply. FYI i using fixed exchange rated. i just take the rate
from the beginning of the month and use it for the whole month till the next
month end.

Anyway I will try the code u posted.

My problem is I have 3 customer that using currency USD. In the field MC that
is sales for whole customer that start with code M* all of them using 1 fixed

[quoted text clipped - 6 lines]

M194GI and M196TW will automatically convert the USD to RM based on 3.27
currency rate?

You could try something like:

MC: Sum(IIf(customer Like "[LN]*" ,mv, IIf(customer Like
"M*". 3.27 * mv, 0)))

BUT, what will you do tomorrow when the rate is different.
Generally you should put the conversion rate in a table
along with the date it was valid. Then you can modify the
query to get the rate for the appropriate date from the
table instead of having to change the query and dealing with
the issue of yesterday's transactions not knowing what rate
was used.

 

[John W. Vinson]

Thank for your reply. FYI i using fixed exchange rated. i just take the rate
from the beginning of the month and use it for the whole month till the next
month end.

That's an EXCELLENT argument for storing the rate in a Table, with a date
field, or better two datefields (EffectiveDate and EndDate); and looking up
the exchange rate using a query. This will let you determine what the exchange
rate WAS four months ago (you might need to know that!); it will remove the
necessity of opening the query design once a month to tweak the number in a
complex nested IIF statement; all in all it's *much easier*, not harder.

John W. Vinson [MVP]

 

[nicpkh via AccessMonster.com]

I have try this code on my queries, and the problem is all the sales value
for MC field start from 1 Jan to 28 Jan change. I calculate it and the
different is 10 times bigger than the normal sales. I only want these 3
customer to times by 3.27. But the output is all the sales value for this MC
field change. MC:

Sum(IIf([customer] Between "L*" And "N*",[MV], IIf([customer] like "M195GI"
or "M194GI" or "M196TW", [MV]*3.27,0)))

Your help will be much appriciate. Thank

 

[John W. Vinson]

I have try this code on my queries, and the problem is all the sales value
for MC field start from 1 Jan to 28 Jan change. I calculate it and the
different is 10 times bigger than the normal sales. I only want these 3
customer to times by 3.27. But the output is all the sales value for this MC
field change. MC:

Sum(IIf([customer] Between "L*" And "N*",[MV], IIf([customer] like "M195GI"
or "M194GI" or "M196TW", [MV]*3.27,0)))

Please post the actual SQL of the query.

This code has no reference at all to the MC field, or to any dates. I have no
idea what you mean by "from 1 Jan to 28 Jan change".

You may be misunderstanding the function of the IIF; you're certainly
misunderstanding the BETWEEN and LIKE operators! The BETWEEN operator does
*not* do anything with wildcards such as *; the LIKE operator does.

Could you explain in words - without jargon if possible - what you have in
the table, and what you want to have in the table when you're done, with a
couple of rows of example?

John W. Vinson [MVP]