format date time in Access / ASP

[mtgriffiths86]

I have created a query using Microsoft access that will work out the difference between a time that i enter into the database (HH:MM:SS) and then to take away the current time.

I have this working inside the access database but i cannot take it from the database and display the result in my webpage.

The SQL query i get after creating my query in access is:

SELECT Flights.Depart_Time, Time() AS [Current_Time], IIf([Depart_time]>Time(),"Will Depart In","Should Have Left") AS SStatus, FormatDateTime([Depart_time]-Time()) AS How_Long, Flights.Airline, Flights.Flight_Number, Flights.Status, Flights.Location
FROM Flights
ORDER BY Flights.Depart_Time;

The error that i have previously had is FormatDateTime

Can anybody help?

Thanks in advance

Matthew

[DrBunchman]

Hi Matthew,

What actually happens when you load the page? Can you show us your code which connects to the db & runs the query and the error message that is produced please.

Thanks,

Dr B

[mtgriffiths86]

Hi,

The error that i am getting is:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Undefined function 'FormatDateTime' in expression.
/assign3/Departures.asp, line 46

Line 46 has the code:

objRecordset.Open "SELECT * FROM Flights_Query ORDER BY Depart_Time ASC",objConnection

The field that i am trying to display is How_Long.

Hope this is what you need.

Thanks again

[jagged]

Hi,

The error that i am getting is:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Undefined function 'FormatDateTime' in expression.
/assign3/Departures.asp, line 46

Line 46 has the code:

objRecordset.Open "SELECT * FROM Flights_Query ORDER BY Depart_Time ASC",objConnection

The field that i am trying to display is How_Long.

Hope this is what you need.

Thanks again

I dont think FormatDateTime is a valid function access SQL.

Try something like Format(depart_time - Time(), "HH:MM")