price files

[Guest]

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

 

[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

 

[Guest]

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

 

[onedaywhen]

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

As this is a database forum, I would've thought a SQL solution most
suitable:

SELECT cost, SWITCH(
cost <= 1, 4,
cost <= 5, 3,
TRUE, 2
) AS multiplier, cost * multiplier AS result
FROM MyTable;

What are your reasons for saying VBA is best in this scenario? I'll
give one reason against: a VBA function is not accessible outside of
the MS Access UI.

Jamie.

--

 

[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

 

[onedaywhen]

I'll still stick with the VBA function
because it can be called from anywhere in the project.

Of course SQL may be called both within and from outside of the MS
Access project.

Jamie.

--

 

[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

 

[onedaywhen]

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.

Sorry I don't know much about the MS Access UI, I just use Jet
databases e.g. "control's source in a report" means nothing to me. Go
easy on me, Nikos: using SQL in a database product isn't that unusual.

Jamie.

--

 

[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

 

[Guest]

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.

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

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

 

[Guest]

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.

 

[onedaywhen]

got any cool sql formulas to take the dashes
out of a # example 00-111-2222-44-5 needs to read as 00111222445.

Interesting development <g>. It could be tempting to use this:

SELECT REPLACE (text_col, '-', '') AS new_format
FROM MyTable;

but this SQL will only work within the MS Access UI (Access2000 and
above) because Replace() is a function new to VBA6 and Jet only
natively contains the VBA5 functions. If the value is always in the
same format i.e. your regex CHECK constraint for this column (you have
one, right <g>?) is something like:

CHECK (text_col LIKE
'[0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9]')

then you could use this:

SELECT
MID(text_col,1,2)
& MID(text_col,4,3)
& MID(text_col,8,4)
& MID(text_col,13,2)
& MID(text_col,16,1) AS new_format
FROM MyTable;

Jamie.

--