Hello everyone,

I need help modifying my query:

SELECT DISTINCT EqInitial, EqNr, LastEventDt, DateDiff("d", StartDt, Date() ) AS TotalDays
FROM EQUIPMENT;

The table Equipment is populated with data that comes from a third party so I cannot change it. Sometimes the StartDt field of this table has no data and when executing the query above the DateDiff function will obviously not return anything for the TotalDays field of that particular record.

I need to populate the TotalDays field with 1 everytime the StartDt fields has no data.

I though about using the IIF function, but I’m not sure if this is the best approach and I don’t know the syntax to “make” the TotalDays field show 1. :confused:

Your help is greatly appreciated .

Hello everyone,

I need help modifying my query:

SELECT DISTINCT EqInitial, EqNr, LastEventDt, DateDiff("d", StartDt, Date() ) AS TotalDays
FROM EQUIPMENT;

The table Equipment is populated with data that comes from a third party so I cannot change it. Sometimes the StartDt field of this table has no data and when executing the query above the DateDiff function will obviously not return anything for the TotalDays field of that particular record.

I need to populate the TotalDays field with 1 everytime the StartDt fields has no data.

I though about using the IIF function, but I’m not sure if this is the best approach and I don’t know the syntax to “make” the TotalDays field show 1. :confused:

Your help is greatly appreciated .

This will work if the Date field is NULL which I'm assuming it is if the date is actually stored as a date. Just replace your DateDiff with the one below, should work.

DateDiff("d", NZ(StartDt,Date()-1), Date() ) AS TotalDays

Thanks for the quick reply.
Date (), represents the current time, it is not a field in my table. DateDiff substract the current date minus the StartDt. The field that is sometimes Null is the StartDt.

I modified DJKarl suggestion an little and it works: TotalDays: Nz(DateDiff("d",[Start Dt],Date()),1)