IF statement in Msaccess query

[Ryobi]

Hi,

Can anybody tell what is the correct syntax for a query in msacess when using the If statement. I want to convert the following statement to a query.


If Rs![Delivered] < rs![NewDelivery] Then
rs![Delivered] =NewDelivery
End If

There is a table that contains the Delivered field. I want to check to see if the Delivered field is less then the NewDelivery if so replace the Delivered date with the NewDelivery.

I have type the following on the query, but I get a syntax error.

= If [Delivered] < NewDelivery Then[Delivered] = NewDelivery
End If

I have look in to the Iff statement but is not what I want to want to replace, it seems that the Iff statement returns true of false it does not let me replace the value on the table.

[Ryobi]

I could not get it to work with the if Statement but the following does the SQl something.

UPDATE Delivered SET Delivered.ShipDate = [Delivered]
WHERE (((Delivered.ShipDate)>[Delivered]));

[si_the_geek]

IIF does not return True/False unless you explicitly tell it to - as you specify what it returns.

However, in this case there is no need for any kind of If, all you need to do is add to (or alter) the Where clause.

This is the equivalent of your original code snippet (except it alters all records in the table):

UPDATE Delivered SET Delivered.ShipDate = [Delivered]
WHERE [Delivered] < Delivered.NewDelivery;

[Ryobi]

So if wanted to altered a specific record, such a name the following code should work.

UPDATE Delivered SET Delivered.ShipDate = [Delivered]
WHERE [name] = 'Al Bundy' and [Delivered] < Delivered.NewDelivery;

[si_the_geek]

That's right... but if there is more than one record with the name 'Al Bundy', it will alter all of those.