iif statement to change value in field

[buddyr]

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

[ADezii]

@buddyr

SELECT Table1.Number, Table1.Date, Table1.Warranty,
IIf([Number]=30 And [Date]>#1/10/2006#,"Off","On") AS [Warranty Status]
FROM Table1;

[buddyr]

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

[NeoPa]

To update all the values in your table, you could use the following SQL :

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.

[NeoPa]

@buddyr
What do you mean?

[ADezii]

@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.

SELECT Table1.ID, Table1.Number, Table1.Date, Table1.Warranty,
fCalcWarrantyStatus([Number],[Date]) AS Warranty_Status
FROM Table1;

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

[NeoPa]

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.

[buddyr]

thanks for explaining it

[NeoPa]

No worries BuddyR. Did you read through all the earlier posts?