Query no longer works in Access

Question

Query no longer works in Access

Navigation

#1 by (0 votes)

0

I am working in a database with information about courses and I created a query to know how many courses each vendor sold per month. This is the query:

SELECT DISTINCTROW tblStudentCourses.SalesID, Format$([tblStudentCourses].[SignDate],'mmmm yyyy') AS [SignDate By Month], Sum(tblStudentCourses.Fee) AS [Sum Of Fee], Avg(tblStudentCourses.Fee) AS [Avg Of Fee], Count(*) AS [Count Of tblStudentCourses]
FROM tblStudentCourses
GROUP BY tblStudentCourses.SalesID, Format$([tblStudentCourses].[SignDate],'mmmm yyyy'), Year([tblStudentCourses].[SignDate])*12+DatePart('m',[tblStudentCourses].[SignDate])-1;

It worked without problems until today when it throws me syntax error with this part Format $ ([tblStudentCourses]. [SignDate], 'mmmm yyyy' I have not changed any parameter or relation in the table .

Could someone tell me why it stopped working and how to fix it?

Thanks:)

sql ms-access

asked by Paula Micheli 12.01.2018 в 23:03

source

1 answer

Fill footable table with JSON Problems installing Eclipse in Fedora

score 0 · Answer 1

As a general recommendation to work with dates, you should do so in canonical format (ISO 8601) and yyyymmdd. Depending on how you access the MDB, you should check the driver configuration, ODBC, regional configuration of the operating system ...

Usually a date 12/12/2018 can work but incorrectly when going to 12/13/2018 or 13/12/2018 depending on where you place the month

In addition, the treatment may differ from Access to SQL , especially with dates, internally you should try to make the numerical comparisons and not by text.

It is more efficient and less problematic to operate with 201803 by March 2018 than to do it in "March 2018"