Link to home
Start Free TrialLog in
Avatar of sasa1
sasa1

asked on

Format date problem

I use FormatDateTime function and I have from ASP:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'FormatDateTime' in expression

Why?
Avatar of robbert
robbert

> Undefined function 'FormatDateTime' in expression

Can it be the exact error message is,

Undefined expression in function 'FormatDateTime'
Avatar of sasa1

ASKER

No this is the error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'FormatDateTime' in expression
What's the code?
Avatar of sasa1

ASKER

The expression is:

  sql = "SELECT Format([TimeStamp],""h"")*1 AS callHour, Sum(IIf([effective]=True,1,0)) AS efCalls, Sum(IIf([effective]=True,0,1)) AS regCalls, Count(tblContacts.ContactID) AS allCalls"
    sql= sql & " FROM tblClientDetails INNER JOIN (tblClientStatus INNER JOIN tblContacts ON tblClientStatus.StatusID=tblContacts.StatusID) ON tblClientDetails.ClientID=tblContacts.ClientID"  
    sql= sql & " WHERE (FormatDateTime(TimeStamp,2) Between " & Request.QueryString("from_date") & " And " & Request.QueryString("to_date") & ") And ((tblClientDetails.ClientType) Like IIf(IsNull(" & Request.QueryString("client_type") & "),""*""," & Request.QueryString("client_type") & ")) "
    sql= sql & " GROUP BY FormatDateTime(TimeStamp,4)   "
I don't believe FormatDateTime is a supported ACCESS function.  You can call that function in vbscript against data that has been returned by your Access query, but you can't use it "inline" with your query against the database.

You should use Format([TimeStamp],OPTIONS) instead.
"TimeStamp" isn't defined within that function.

 sql = "SELECT TIMESTAMP, Format([TimeStamp],""h"")*1 AS callHour, Sum...

might work.
ASKER CERTIFIED SOLUTION
Avatar of nonesuch
nonesuch

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If they're writing ASP, what Access supports doesn't have anything to do with it. ASP uses standard SQL statements.

However, though your rationale is wrong, your result is right -- formatdatetime isn't going to work the way you have it. Actually, I've NEVER got that to work for anything.

Use cdate. If you're comparing something to a date, it has to BE a date. If timestamp comes from a form, it's NOT a date, it's text.

And if you're using Access, make a query there that gives you the overall dataset you need, and use your SQL statement to further fine tune it. That way you don't need as complicated of a SQL statement, and it's much easier to debug.
And I'd check everything...
Do you know for sure that this
Format([TimeStamp],""h"")*1
works?

You can't do this
Sum(IIf([effective]=True,0,1))
or this
Like IIf(IsNull("
in ASP.

This is NOT Access. Do NOT set up a query in Access and use SQL view to copy it. It's NOT going to work.

Make your query, and connect to IT instead of a table. ASP will treat it EXACTLY like a table, and you can run SQL statements (much SIMPLER SQL statements) on it to pull out subsets of data.
webwoman, in Access queries, you can mix SQL and VBA code.
Yes, but it's NOT Access. It's ASP. I can't imagine that this...
Sum(IIf([effective]=True,1,0))
is going to work in a SQL select statement. It might work in an Access query.

Is there even an IIF in ASP? I thought that was strictly a VBA thing...
Avatar of Gary
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept Answer by nonesuch
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
GaryC123
EE Cleanup Volunteer