price files

M

MichiganJimmy

how do i have my relail price figured by [ cost*costmultiplier]
if cost is 1or less multimplier is 4 if cost is less than 5 but greater than
1 multiplier is 3 and so on. i have both cost and multiplier now but i have
to address each sold part each time and i have 500000 parts. i need to
automate proccess. and multiplier must by based on cost. thanks
 
N

Nikos Yannacopoulos

Jimmy,

If the rules won't be changing, the best way to do it is by means of a
VBA function, something like:

Function Multiplier(Cost As Double) As Double
Select Case Cost
Case Is <= 1
Multiplier = 4
Case 1 To 5
Multiplier = 3
Case Is > 5
Multiplier = 2
End Select
End Function

Just paste the code in a general module and save, then you can get the
multiplier whenever required by calling the function and passing on the
cost as a parameter, so
Multiplier(4)
will return 3.

If you need more flexibility (at the possible price of speed if used in
queries / reports returning high number of records), you could make a
three field table (tblMultipliers) like, for instance:

CLow cHigh Mult
0 1 4
1.001 5 3
5.001 99999 2

and retrieve the multiplier by means of:

DLookup("[Mult]", "tblMultipliers", "[CLow] <=" & [Cost] & " And " &
[Cost] & " <= [CHigh]")

(all in one line, watch out for wrapping in your newsreader).

HTH,
Nikos


HTH,
Nikos
 
A

axeman422

well you will deffinately need an if statement

if [cost] = 1 or < 1 then
[retail] = [cost] * 4

elseif [cost] < 5 then
[retail] = [cost] * 3

elseif [cost] < 10 then
[retail] = [cost] * 2
'or how ever it goes after that

end if


[cost] being you cost value
[retail] being the retail price
 
N

Nikos Yannacopoulos

Jamie,

This is not a database forum, this is an *MS-Access* database forum,
therefore whether the solution is inaccessible outside of MS-Access is
irrelevant here.
I know you are an SQL junkie, but I'll still stick with the VBA function
because it can be called from anywhere in the project.

Nikos
 
N

Nikos Yannacopoulos

I'm not the SQL expert here, you are, so I'll take your word for it. Can
you use this in, say, a control's source in a report? I don't know how
to do it.

Nikos
 
N

Nikos Yannacopoulos

Jamie,

I was just making a point on the advantages of using VBA instead of SQL
in this particular case (the answer is you can't) - while SQL is
probably the way to go in other cases. As a matter of fact, I use SQL
within VBA quite extensively, trying to make the most of both worlds -
although, admittedly, I am nowhere near your expertise on SQL.

Anyway, my view (for what it's worth) is that Access's strength barely
lies in Jet, it mostly lies in all those other wonderful things which
can be done with it (automation, UI etc.), therefore I consider the use
of VBA the real driving force behind it.

Regards,
Nikos
 
M

MichiganJimmy

one day when has it , it< just what i needed, flows thru my froms. again i am
just a wana bee haha. but really the sql seamed to be alittle more frendly
to me . probibly because i sort of understoud how the formula read and had a
weird idea of where to put it. can you give me any directions on getting ridd
of the dashes in my upc code, when i scan there is no dashes so it wont
locate item in table. by the way i just have to say THANKS SO VERY MUCH all
of you.

axeman422 said:
well you will deffinately need an if statement

if [cost] = 1 or < 1 then
[retail] = [cost] * 4

elseif [cost] < 5 then
[retail] = [cost] * 3

elseif [cost] < 10 then
[retail] = [cost] * 2
'or how ever it goes after that

end if


[cost] being you cost value
[retail] being the retail price

MichiganJimmy said:
how do i have my relail price figured by [ cost*costmultiplier]
if cost is 1or less multimplier is 4 if cost is less than 5 but greater than
1 multiplier is 3 and so on. i have both cost and multiplier now but i have
to address each sold part each time and i have 500000 parts. i need to
automate proccess. and multiplier must by based on cost. thanks
Click to expand...
Click to expand...
 
M

MichiganJimmy

hey oneday i need to send you a beer . you my freind are the winner, dont no
if i made the right choise but for what i need at the moment it appears to be
GREAT . thank you so very much. got any cool sql formulas to take the dashes
out of a # example 00-111-2222-44-5 needs to read as 00111222445. price
file hes dashes and when i scan bar code there is none.
 
Top