Date/Time Difference

[sdk]

Hi.I need little help.
table has columns: job, start_date, start_time, end_date, end_time, period. I need a query that will extract all jobs for which it is needed more days than it is provided in the period column.
I'm new in Access, so I have no idea how to do it...

 

[Alansidman]

If I understand correctly, you need two new fields which will contain expressions. The first one will determine the days from by subtracting start_date from the end_date. The second one will contain and expression that will determine if the period exceeds the new calculated column, ie. =iif([NewField]>Period, "True", "False"). In the criteria for this field type True. Run your query.

 

[sdk]

thanks for the answer
Hm, when I enter "true" in the criteria and run a query, the query ask me to enter data for the first new field (I called it datedif), when I leave empty criteria, the query normally runs

 

[Alansidman]

Not sure what the issue is. Post your SQL statement for the query so that we can see what is happening.

Alan

 

[sdk]

SELECT Table1.Job, Table1.start_date, Table1.end_date, Table1.period, DateDiff("d",[start_date],[end_date]) AS datedif, IIf([datedif]>[Period],"True","False") AS Expr1
FROM Table1
WHERE (((IIf([datedif]>[Period],"True","False"))=True));

 

[Alansidman]

I have recreated your issue and have not found a solution yet. Will post back when I have cleared. Very interesting.

 

[Brianwarnock]

You cannot use aliases in Where clauses which is what you are attempting to do, you need to use the calculation in the IIf, which means that you don't need the 2 extra fields.

Brian

 

[Alansidman]

sdk

We all learn something new everyday. Here is the correct SQL. Change your table name to reflect properly.

SELECT NewDates.Job, NewDates.StartDate, NewDates.EndDate, IIf(DateDiff("d",[StartDate],[EndDate]>[Period]),"True","False") AS Expr1, NewDates.Period
FROM NewDates
WHERE (((IIf(DateDiff("d",[StartDate],[EndDate])>[Period],"True","False"))=True));

Alan

Thanks Brian.

 

[Brianwarnock]

Happy to help Alan and pass on info learnt the hard way

BTW sdk it is not good practice to store Date and Time separately, it is much easier to separate if required than to put together, and all date fields are date/time anyway , stored as a Double with before the decimal point the days and after the time.

Also think about whether you want an inclusive or exclusive number of days calculation.

Brian

 

[sdk]

Problem solved.Thank you both.