0

I'm trying pull the year out of the date field, CPARS.[Add Date]. The format is (MM/DD/YYYY). It is giving me a "compile error in query expression year.(CPARS.[Add Date])". Why might it be doing this?

SELECT CPARS.ID, YEAR(CPARS.[Add Date])
FROM CPARS

Ive tried all the other date functions: year, month, day, datepart, datediff in many examples and I still get a compile error. The data type is Date/Time.

Out of curiosity I attempted the count function and it worked perfectly. All date functions are not working.

Preston Richardson
  • 21
  • 1
  • 7
  • A compile error on inbuilt functions can be caused by broken references, see https://stackoverflow.com/a/27350292/3820271 – Andre Jul 10 '17 at 15:11
  • Also possibly useful: [Decompile](http://stackoverflow.com/a/3268188/3820271) – Andre Jul 10 '17 at 15:14
  • Your error says `year.(CPARS.[Add Date])`. Note the period after `year.` - check if that is also there in your actual code, it probably should not be there. – Peter B Jul 10 '17 at 15:52

3 Answers3

0

Try using DatePart:

SELECT CPARS.ID, DatePart("yyyy",[Add Date])
FROM CPARS
BoogieMan2718
  • 109
  • 1
  • 10
  • @PrestonRichardson did you verify that the [Add Date] field is of the date/time data type with the format set to Short Date? Also, are you sure there is no record in the table for which this field contains bad data or nulls? – BoogieMan2718 Jul 10 '17 at 14:57
  • @PrestonRichardson I was able to get this to run correctly in a test table: `SELECT CPARS.ID, DatePart('yyyy',[CPARS]![Add Date]) AS Expr1 FROM CPARS;` – BoogieMan2718 Jul 10 '17 at 15:02
  • Doubled checked and the field is data/time data type with short date format. Tried the last code and still got the compile error. Must be an underlying problem and not the code – Preston Richardson Jul 10 '17 at 15:19
  • @PrestonRichardson the only other thing I could find that is not code related is [this](https://social.msdn.microsoft.com/Forums/en-US/d857e99c-7fe8-4308-b098-259643f6d9d9/how-do-i-fix-compile-error-in-query-expression-date?forum=accessdev) – BoogieMan2718 Jul 10 '17 at 15:25
0

Try this;

SELECT CPARS.ID, datepart(yyyy,CPARS.[Add Date])
FROM CPARS

Is 'CPARS.[Add Date]' actually a date field? Or is it a varchar or something?

  • It is an actual date field. The data type is date/time formatted for short date. I keep getting the compile error regardless of the code used so it must be a different problem. – Preston Richardson Jul 10 '17 at 15:20
0

There can be two reasons:

  1. You have missed references. Solution: Open Visual Basic editor (Alt+F11): Tools->References Uncheck all references like: "MISSING: ...".
  2. The error can appear on 64 bit computers with an old Visual Basic code. Solution: You have to add "PtrSafe" commands. Open Visual Basic editor (Alt+F11). Compile your code: Debug->Compile. The Access shows you where PtrSafe command should be added.
Kinga the Witch
  • 129
  • 1
  • 5