455,487 Members | 1,747 Online
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
8 Replies

 Expert 5K+ P: 8,679 @buddyr Expand|Select|Wrap|Line Numbers SELECT Table1.Number, Table1.Date, Table1.Warranty, IIf([Number]=30 And [Date]>#1/10/2006#,"Off","On") AS [Warranty Status] 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

 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 UPDATE [Table1] 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

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

 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 SELECT Table1.ID, Table1.Number, Table1.Date, Table1.Warranty, fCalcWarrantyStatus([Number],[Date]) AS Warranty_Status FROM Table1; Expand|Select|Wrap|Line Numbers Public Function fCalcWarrantyStatus(lngNumber As Long, dteDate As Date) As Variant If dteDate > #1/10/2006# Then   Select Case lngNumber     Case 10       fCalcWarrantyStatus = "On/Off"     Case 20       fCalcWarrantyStatus = "On/Off"     Case 30       fCalcWarrantyStatus = "Off"       'Known Response     Case 40       fCalcWarrantyStatus = "On/Off"     Case 50       fCalcWarrantyStatus = "On/Off"     Case 60       fCalcWarrantyStatus = "On/Off"     Case 70       fCalcWarrantyStatus = "On/Off"     Case 80       fCalcWarrantyStatus = "On/Off"     Case 90       fCalcWarrantyStatus = "On/Off"     Case 100       fCalcWarrantyStatus = "On/Off"     Case ...       fCalcWarrantyStatus = "On/Off"     Case Else       fCalcWarrantyStatus = "On/Off"   End Select Else   fCalcWarrantyStatus = Null    'Date <= 1/10/2006 End If End Function Jun 4 '09 #6

 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

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