Simple count sql query not working

[sfpx]

Sorry for the vague title.

 

Here is the simple statement

 

ExecuteSQL("select count(Distinct Date) from Reports";"";"") 

 

The final query is more complicated than that but I simplified it because I know that the problem relies here.

 

This query will return and error ("?")

 

If I create a calculated field Date2 (=Date) and replace it in the query, then the query works.

 

The only thing I can see is that I have other fields named "Date" in other tables but as you can see these other tables are not even in the example query so it's clear that the Date is the on of the table Reports.

 

That must be a simple thing I don't get it .

[Wim Decorte]

I'm pretty sure that "Date" is a reserved SQL keyword.

In general it is a bad idea to name fields with the same name as is commonly used for datatypes (date, number, text,...)

[Gastion]

Hello,

 

ExecuteSQL("select count(Distinct /"Date/" ) from Reports";"";"")  should work...

 

I agree with Wim, Date is a reserved word. , It's better to use dat to name this type of field.

[sfpx]

Hello,

 

ExecuteSQL("select count(Distinct /"Date/" ) from Reports";"";"")  should work...

 

I agree with Wim, Date is a reserved word. , It's better to use dat to name this type of field.

 

I had already tried that and it did not work.

 

Anyway, the reserved word explanation makes sense.

 

Thanks