Between two numbers update Query

[Jon]

Hi,

I'm working on a table similar to this:

FileNum Amt AmtRequired
1 10,000
2 9,000
3 50,000
4 30,000


I'd like to use the update query to get results similar to what is
below.
Ex. If between 0 - 10,000 then amount required is 10,000
If between 20,000 - 50,000 then amount required is 50,000

FileNum Amt AmtRequired
1 10,000 10,000
2 9,000 10,000
3 50,000 50,000
4 30,000 50,000

Thank you again for the help.

Jon

 

[Duane Hookom]

I would write a small function in a "business rules module".
Function GetAmtRequired(dblAmt As Double) As Long
Select Case dblAmt
Case 0 To 10000
GetAmtRequired = 10000
Case 20000 To 50000
GetAmtRequired = 50000
Case Else
GetAmtRequired = 0
End Select
End Function
What do you expect for values between 10000 and 20000 or >50000?

 

[Jon]

I would write a small function in a "business rules module".
Function GetAmtRequired(dblAmt As Double) As Long
Select Case dblAmt
Case 0 To 10000
GetAmtRequired = 10000
Case 20000 To 50000
GetAmtRequired = 50000
Case Else
GetAmtRequired = 0
End Select
End Function

Thanks, Duane. I tried pasting this into the module form and when I
tried running it, it requested the name of a macro (there were none).
I haven't worked with modules before, am I missing something?

What do you expect for values between 10000 and 20000 or >50000?

I've done a select query to find one number from a column less than,
for example, 10,000 and another column is between 400,000 and 700,000.
What I need now is another column to show what number I'm using to
make the determination.

 

[Duane Hookom]

You should open a new, blank module and paste the code into it. Save the
module as "modBusinessRules".
You can then create an update query to update the AmtRequired field with
this expression:
GetAmtRequired([Amt])

You lost me on your final paragraph. I trust you understand it.