DatePart query with Date() was working, no longer works

[dalcazar]

Hi all, I had a query that was working but has since broken due to one of the tables being moved, now I can't get the DatePart function to work.

I'm trying to get the year from today's date by using the expression Year: DatePart("yyyy",Date())

This was working before, (although for some reason the saved version says Year: DatePart("yyyy"IDate()) <---- note the pipe | character in there.

Now, I can't get it to work at all. It just says "The expression you entered contains invalid syntax. You omitted an operand or operator, you entered and invalid character co comma, or you entered text without surrounding it in quotation marks.

As far as I can research, the syntax is correct, it just refuses to work. Any ideas on the cause/fix for this?

 

[June7]

Table moved where? That should not affect this expression. Why is pipe character in expression instead of comma? Can you change it?

Could use Year(Date())

Year is a reserved word. Advise not to use reserved words as names. I would use Yr for alias name.

 

[arnelgp]

change the Pipe character to comma again.
your query got corrupted.

 

[Gasman]

I would just use Year() and be done with it.

 

[Josef P.]

There is probably | set as list separator in Windows.

So I can at least reproduce an error.

1. Set list separator

2. insert SQL in Query (SQL View)

SELECT TestTab.ID, DatePart("yyyy",Date()) as Y
FROM TestTab;

3. Change to Design view

4. Execute query => Error

When you try to switch to the SQL view, the same error message appears.
The error message shown in #1 appears when you try to change the entry in the query editor.

 

[Mike Krailo]

If that is the case, then the next question is why in the heck would anyone change the list separator like that? Is that a per user thing or global for the whole computer?

 

[Josef P.]

Or you ask the question like this: Why does Access use the Windows settings when it seems to work only with the Windows default settings?

 

[Gasman]

Using a reserved word as well ?

 

[Josef P.]

The error is not caused by a reserved word. In my example in #5, no reserved word is used.

 

[Gasman]

The error is not caused by a reserved word. In my example in #5, no reserved word is used.

Not saying it was, just that a reserved word was being used.

 

[dalcazar]

There is probably | set as list separator in Windows.

So I can at least reproduce an error.

1. Set list separator
View attachment 109210

2. insert SQL in Query (SQL View)

SELECT TestTab.ID, DatePart("yyyy",Date()) as Y
FROM TestTab;

3. Change to Design view
View attachment 109211

4. Execute query => Error
View attachment 109213
When you try to switch to the SQL view, the same error message appears.
The error message shown in #1 appears when you try to change the entry in the query editor.

Well called good sir, this was indeed the issue. Access was replacing the list separator automatically.

 

[arnelgp]

if it is constantly changing the comma to a pipe, probably a Regional setting problem?