Undefined function 'Date' in expression.

[andydarly]

This is exactly the error message I get when trying to run a query in Access 2013 on a Windows 7 Machine (I have run very successfully for many months). All of a sudden it has stopped working and I have no idea why.

I have been through some of the advice already posted about references and unchecking any that contain the word "MISSING" (I only have 4 libraries checked and none contain the word missing).

I have tried adding more references and coming back out to test run - sadly this does not work. I have removed all the reference libraries that I can and re-tried - sadly this does not work either.

The formula I am using is along the lines of >=Date()-(IIf(Weekday(Date())>=6,Weekday(Date())-6,Weekday(Date())+1))-35 (so that I get a specific rolling range always starting on a Friday). Like I say I have been using these for many months and no issue until yesterday, when it appeared sporadic but today it is affecting everything and other peoples' databases using similar formulae.

Has some update been put through stopping me using this formula?

If I change this to an actual date range it works fine but I have many queries within the database and changing them all on a weekly basis is something i would much rather avoid.

can anyone help?

Please?!?

[jforbes]

Where is this formula located? Is it in a SQL statement where clause or on a Form Control? In code? Could you provide the entire formula/SQL statement/code?

• Missing references are often a cause of this, but sounds like you have that covered.
• Make sure your project compiles.
• Perform a Find on all of your code for "Date". You'll get a lot of results, but you may find that you have created, imported, copied and pasted a Date() method from the internet or someplace else.
• If this is in a SQL statement, it maybe because of an error at a previous point in the SQL, or by skipping a required Keyword, like FROM tTable
• You can try to include the Namespace, DateTime.Date() and see if your error goes away. If it does, then it means you have a problem with name resolution and nearly for sure have another method with the name of Date(). If it doesn't then it probably means the error is actually somewhere else in the Function/SQL.

[Oralloy]

You might want to try the function Now(), but I am shooting from the hip, so this may not be a valid suggestion.

[PhilOfWalton]

I had that problem recently as well. I think I cured it by doing a decompile.

Phil

[andydarly]

Hi.

It's within the Criteria line of the Design View of the query.

The SQL looks like :
SELECT [Date LookUp].Year, [Date LookUp].Week, SCT.[Metric Date], [Date LookUp].Day, SCT.[Service Group 1 Name], SCT.[Service Group 2 Name], SCT.[Service Group 3 Name], SCT.[Service Call Type], Sum(SCT.[Calls Offered]) AS [SumOfCalls Offered], Sum(SCT.[Calls Answered Count]) AS [SumOfCalls Answered Count], Sum([SCT]![Calls Offered]-[SCT]![Calls Answered Count]) AS Abandoned, Sum([SCT]![Connected Duration]+[SCT]![Hold Duration]+[SCT]![Acw Duration]) AS THT, Sum(SCT.[Connected Duration]) AS [SumOfConnected Duration], Sum(SCT.[Hold Duration]) AS [SumOfHold Duration], Sum(SCT.[Acw Duration]) AS [SumOfAcw Duration], Sum(SCT.[Total Time To Answer]) AS [SumOfTotal Time To Answer]
FROM SCT INNER JOIN [Date LookUp] ON SCT.[Metric Date] = [Date LookUp].Date
GROUP BY [Date LookUp].Year, [Date LookUp].Week, SCT.[Metric Date], [Date LookUp].Day, SCT.[Service Group 1 Name], SCT.[Service Group 2 Name], SCT.[Service Group 3 Name], SCT.[Service Call Type], [Date LookUp].[Yr & Wk]
HAVING (((SCT.[Metric Date])>=#9/2/2016#) AND ((SCT.[Service Group 1 Name]) Not In ("SG1_BUS_Business","SG1_DEFAULT","SG1_NOW_TV","SG 1_OTH_Non_Specific","SG1_SAL_Sales","SG1_SAL_Sales _ROI","SG1_SBC_BusinessCloud","SG1_SCH_SupplyChain ","SG1_SER_Service_ROI","SG1_SkyQ_ROI")))
ORDER BY SCT.[Metric Date], [Date LookUp].[Yr & Wk];


What I really don't understand is that nothing has changed in the Db and it has only recently started happening. I also have another DB with similarly set out date ranges in queries and they DO work. I can't work out where the difference is :(

[andydarly]

I don't know much about Access at the minute. What is a decompile and how do I do it please?

[PhilOfWalton]

I use a little batch file that creates a copy of my existing database "Test" and calls it "Decompile Test".

In the batch file there is a switch which says /Decompile which tells Access to decompile the newly copied database.

At the end of the procedure, I rename the original database "Test 10 Oct 2016", and rename "Decompile Test" back to "Test"

This is my batch file that can be edited with Notepad.

Copy "E:\Phil Data\Access\MDB 2010\Test.accdb", "E:\Phil Data\Access\MDB 2010\Decompile Test.accdb"
"E:\Program Files (X86)\Microsoft Office\Office14\MSACCESS.EXE" "E:\Phil Data\Access\MDB 2010\Decompile Test.accdb" /decompile
 

Phil

[jforbes]

To Decompile/recompile a database, I usually {Windows}+R (to open up the Run Dialog Box) and then use:

msaccess /decompile

Access will open, and will decompile the first database that is opened. So then, I open the troublesome database from the Recent Documents.

[jforbes]

You've got a field named "Date". Date is a reserved word in SQL. You can technically use "Date" to name an object, but it is not recommended. You would be better off with something like "ActionDate", "CreateDate", or "SchedDate".

I would rename your field if at all possible.

You can use reserved words if you Bracket them the way you bracketed the Table name with spaces. It's very possible that this will fix your Query:

FROM SCT INNER JOIN [Date LookUp] ON SCT.[Metric Date] = [Date LookUp].[Date]

Again, I would rename your field if at all possible.

[andydarly]

Hi, all. Thanks for your all your help.

I tried a couple of these and couldn't get them to work in the way I wanted so I ended up recreating the Database from scratch.

This seemed to do the trick. Access really confuses me...