?
Solved

Why am I getting this error with FormatDateTime?

Posted on 2006-06-07
1
Medium Priority
?
590 Views
Last Modified: 2008-01-09
Hi Experts,

I have the following error:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Undefined function 'FormatDateTime' in expression.
/intermodal/load_record/SearchQueries.asp, line 307

line 307 is: rs.Open strSQL,conn

This is my query:

SELECT Load.LoadID, Load.SAPDate, Load.DeliveryNo, Load.ShipmentNo, Load.ManualNote, ShipFrom.ShipFrom, Load.FromProv, ShipTo.ShipTo, Load.ToProv, Load.PayorOfFreight, Transporter.TransName, [ContainerInitial] & ' ' & [ContainerID] AS [Container], Load.Notes, Users.UserID, Format([CreatedOn],'dd/mm/yy') AS CreatedOnDate, Load.UpdatedBy, Load.UpdatedOn, [Container Initials].ContainerInitial, Load.ContainerID FROM (((Users INNER JOIN (Transporter RIGHT JOIN Load ON Transporter.TransID = Load.TransporterUsed) ON Users.User_Key = Load.CreatedBy) LEFT JOIN ShipFrom ON Load.ShipFromID = ShipFrom.ShipFromID) LEFT JOIN ShipTo ON Load.ShipToID = ShipTo.ShipToID) LEFT JOIN [Container Initials] ON Load.ContainerInitialID = [Container Initials].ContainerInitialID WHERE FormatDateTime([CreatedOn],2) Between #05/01/2006# And #05/31/2006# AND ShipFrom.ShipFrom='CA1D' ORDER BY Load.SAPDate DESC;

Thanks
0
Comment
Question by:indyng
1 Comment
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 2000 total points
ID: 16851519
Because FormatDateTime is a VBScript function, not an SQL command. You probably want to use the Format command as you have earlier in your query:

SELECT Load.LoadID, Load.SAPDate, Load.DeliveryNo, Load.ShipmentNo, Load.ManualNote, ShipFrom.ShipFrom, Load.FromProv, ShipTo.ShipTo, Load.ToProv, Load.PayorOfFreight, Transporter.TransName, [ContainerInitial] & ' ' & [ContainerID] AS [Container], Load.Notes, Users.UserID, Format([CreatedOn],'dd/mm/yy') AS CreatedOnDate, Load.UpdatedBy, Load.UpdatedOn, [Container Initials].ContainerInitial, Load.ContainerID FROM (((Users INNER JOIN (Transporter RIGHT JOIN Load ON Transporter.TransID = Load.TransporterUsed) ON Users.User_Key = Load.CreatedBy) LEFT JOIN ShipFrom ON Load.ShipFromID = ShipFrom.ShipFromID) LEFT JOIN ShipTo ON Load.ShipToID = ShipTo.ShipToID) LEFT JOIN [Container Initials] ON Load.ContainerInitialID = [Container Initials].ContainerInitialID WHERE Format([CreatedOn],'dd/mm/yy') Between #05/01/2006# And #05/31/2006# AND ShipFrom.ShipFrom='CA1D' ORDER BY Load.SAPDate DESC;
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question