Case Statement SQL Update

D

db

hi,

I use this update query populate the [FactorRating] column in a table;
however, i'd like to use a VBA Case statement to set the full range (high,
mod high, mod low, and low) programmatically rather than chaning the values
in the WHERE clause each time.

UPDATE MergedCobbs SET MergedCobbs.FactorRating = "High"
WHERE FactorSum > 64 And FactorSum <= 96;

Thanks,
db
 
T

tina

try this:

Public Function isRating(ByVal lngFactor As Long) As String

'assumes that the FactorSum value is a number, and is a Long Integer.

Select Case lngFactor
Case 65 To 96
isRating = "High"
Case 'next number or range of numbers
isRating = "Mod High"
'etc, etc, etc.
Case Else 'use this if you need a default for records outside of all
parameters
isRating = 'whatever default you need
End Select

End Function

in the Update query, in place of the "High" (etc, etc), write

isRating(tblName.FactorSum)

*test this out in a copy of your db before trying it on the live database*

hth
 
D

db

That worked perfectly, thanks!!

could i trouble you for an explaination of the these lines:

Public Function isRating(ByVal lngFactor As Long) As String
Select Case lngFactor

isRating is the name of the function
lngFactor is the name of the Case statement? could you explain the logic of
how this works?

Thanks,
dave
 
T

tina

isRating is the name of the function
correct.
lngFactor is the name of the Case statement? no.

could you explain the logic of how this works?
see below.

*ByVal lngFactor As Long* creates a variable of data type Long, to be used
as the "container" to hold the SumFactor value of each record in the query.
when you call the function from the query - where you entered
*isRating(tblName.SumFactor)* - the system dumps the value of SumFactor
(from the current query record) into the "container" lngFactor. the function
then runs, using the value held in lngFactor.

*As String* tells the system to return the value (in this case, to the
query) as a string (text) data type.

*Select Case lngFactor* tells the system to select the case that matches
the value held in the variable lngFactor. so, for instance, Case 65 to 96
looks to see if the value of lngFactor is between 65 and 96 - if not, then
the next Case is compared to (again) the value of lngFactor, etc, etc, etc.
look up Select Case in Access VBA Help for more information.

hth
 
C

Chris Nebinger

The only change I would make is to declare both the
argument and the return value as variants.

Variants are the only datatype that can handle a null
value. In db's, null values are to be expected. If you
try and pass a null to that function, you will get a
#Error.


Public Function isRating(ByVal lngFactor As Variant) As
Variant


Chris Nebinger
 
T

tina

hi db. i did reply yesterday, showed up in my Outbox, but not in the
newsgroup for some reason. here it is again.
isRating is the name of the function correct.

lngFactor is the name of the Case statement? no.

could you explain the logic of how this works?
see below.

*ByVal lngFactor As Long* creates a variable of data type Long, to be used
as the "container" to hold the FactorSum value of each record in the query.
when you call the function from the query - where you entered
*isRating(tblName.FactorSum)* - the system dumps the value of FactorSum
(from the current query record) into the "container" lngFactor. the function
then runs, using the value held in lngFactor.

*As String* tells the system to return the value (in this case, to the
query) as a string (text) data type.

*Select Case lngFactor* tells the system to select the case that matches
the value held in the variable lngFactor. so, for instance, Case 65 to 96
looks to see if the value of lngFactor is between 65 and 96 - if not, then
the next Case is compared to (again) the value of lngFactor, etc, etc, etc.
look up Select Case in Access VBA Help for more information.

you might take note of Chris's post also. you may want to make the changes
he suggests, or try the following:

in the Update query, in place of the "High" (etc, etc), write

isRating(Nz(tblName.FactorSum, ""))

again, remember to test in a copy of your db first.

hth
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top