By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,487 Members | 1,747 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,487 IT Pros & Developers. It's quick & easy.

iif statement to change value in field

100+
P: 107
Hello,
if I have a table1
(3 fields) number date warranty
10 1/15/09 on
20 4/10/05 off
30 5/11/04 on
40 8/21/08 on

can I write a conditional iif statement that says if number = 30 and date > 1/10/06 then warranty = off?

Where would I write it - in the sql statement in a query?
The database already has about 5000 records in it and can an if statement change a field that already has data in it. Or should I be doing it in vb?
any ideas
thank you
Jun 4 '09 #1
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,679
@buddyr
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.Number, Table1.Date, Table1.Warranty,
  2. IIf([Number]=30 And [Date]>#1/10/2006#,"Off","On") AS [Warranty Status]
  3. FROM Table1;
Jun 4 '09 #2

100+
P: 107
thank you that works
if I want to make statement use more than one number- can I use a select case statement in access
thank you
Jun 4 '09 #3

NeoPa
Expert Mod 15k+
P: 31,709
To update all the values in your table, you could use the following SQL :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [Table1]
  2. SET [Warranty]=IIf([Number]=30 And [Date]>#1/10/2006#,'Off','On')
NB however, updates are often used to change stored vales when those values are simply calculations. This is rarely a good idea. Instead you should calculate the values as and when required. For further explanation see Normalisation and Table structures.
Jun 4 '09 #4

NeoPa
Expert Mod 15k+
P: 31,709
@buddyr
What do you mean?
Jun 4 '09 #5

ADezii
Expert 5K+
P: 8,679
@buddyr
Buddr, the logic would have to change dramatically. Assuming the Date Value of 1/10/2006 is Constant and only the Number is Variable, then the Calculated Field [Warranty_Status] would have to Call a Public Function and pass to it 2 Arguments to be analyzed, then the appropriate result returned.
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.ID, Table1.Number, Table1.Date, Table1.Warranty,
  2. fCalcWarrantyStatus([Number],[Date]) AS Warranty_Status
  3. FROM Table1;
Expand|Select|Wrap|Line Numbers
  1. Public Function fCalcWarrantyStatus(lngNumber As Long, dteDate As Date) As Variant
  2. If dteDate > #1/10/2006# Then
  3.   Select Case lngNumber
  4.     Case 10
  5.       fCalcWarrantyStatus = "On/Off"
  6.     Case 20
  7.       fCalcWarrantyStatus = "On/Off"
  8.     Case 30
  9.       fCalcWarrantyStatus = "Off"       'Known Response
  10.     Case 40
  11.       fCalcWarrantyStatus = "On/Off"
  12.     Case 50
  13.       fCalcWarrantyStatus = "On/Off"
  14.     Case 60
  15.       fCalcWarrantyStatus = "On/Off"
  16.     Case 70
  17.       fCalcWarrantyStatus = "On/Off"
  18.     Case 80
  19.       fCalcWarrantyStatus = "On/Off"
  20.     Case 90
  21.       fCalcWarrantyStatus = "On/Off"
  22.     Case 100
  23.       fCalcWarrantyStatus = "On/Off"
  24.     Case ...
  25.       fCalcWarrantyStatus = "On/Off"
  26.     Case Else
  27.       fCalcWarrantyStatus = "On/Off"
  28.   End Select
  29. Else
  30.   fCalcWarrantyStatus = Null    'Date <= 1/10/2006
  31. End If
  32. End Function
Jun 4 '09 #6

NeoPa
Expert Mod 15k+
P: 31,709
There is no CASE statement in Access (Jet) SQL (unlike in T-SQL for instance), although ADezii has illustrated that there is in VBA.

Access handles this using (possibly multiple) IIF() function calls.
Jun 4 '09 #7

100+
P: 107
thanks for explaining it
Jun 5 '09 #8

NeoPa
Expert Mod 15k+
P: 31,709
No worries BuddyR. Did you read through all the earlier posts?
Jun 5 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.