extract first and last day of month in field

[sal21]

I have a field DATE1 with dates.
i need to exctract with sql a first and last day of each mont.

example of DATE1

12/05/2014
01/05/2014
07/05/2014
21/06/2014
01/05/2014
07/06/2014
...
ecc...

i need 01/05/2014 - 12/05/2014, 07/06/2014 - 21/06/2014...

note:
in field DATE1 i can have duplicate dates

[HansV]

For example:

SELECT [DATE1]-Day([DATE1])+1 AS MESE, Min([DATE1]) & " - " & Max([DATE1]) AS GAMMA
FROM MYTABLE
GROUP BY [DATE1]-Day([DATE1])+1;

[sal21]

For example:

SELECT [DATE1]-Day([DATE1])+1 AS MESE, Min([DATE1]) & " - " & Max([DATE1]) AS GAMMA
FROM MYTABLE
GROUP BY [DATE1]-Day([DATE1])+1;

sorry for delay, the code work greaaaaaaaaaaaaaaaaaaaaTTTTTTTTTTTTTTTTTTTTTTTTTT!!!!!!!!!!!!!!!!!!!!


hummmmm....
With the same dates in fields, is possible to make the "block" of dates based a week?

Note:
in filed all dates are, sure, a working day

[HansV]

You could use

SELECT [DATE1]-Weekday([DATE1],2)+1 AS MESE, Min([DATE1]) & " - " & Max([DATE1]) AS GAMMA
FROM MYTABLE
GROUP BY [DATE1]-Weekday([DATE1],2)+1;

[sal21]

You could use

SELECT [DATE1]-Weekday([DATE1],2)+1 AS MESE, Min([DATE1]) & " - " & Max([DATE1]) AS GAMMA
FROM MYTABLE
GROUP BY [DATE1]-Weekday([DATE1],2)+1;

Work!
But only a prob with the 2 query.
In access ide all work perfect, instead if i insett the qury in sql="....." in ide of vba have error: type of data not corredpondent.
Why?

[HansV]

If you want to use the SQL in VBA code, you have to change " - " to ' - ', and make sure that you insert spaces before FROM and GROUP BY.

Code: Select all

    sql = "SELECT [DATE1]-Day([DATE1])+1 AS MESE, Min([DATE1]) & ' - ' & Max([DATE1]) AS GAMMA" & _
        " FROM MYTABLE GROUP BY [DATE1]-Day([DATE1])+1"

[sal21]

You could use

SELECT [DATE1]-Weekday([DATE1],2)+1 AS MESE, Min([DATE1]) & " - " & Max([DATE1]) AS GAMMA
FROM MYTABLE
GROUP BY [DATE1]-Weekday([DATE1],2)+1;

SINTIAX ERROR IN fROM EXPRESSION:

SQL = "SELECT Min([DATA CONT]) & ' - ' & Max([DATA CONT]) AS GAMMA FROM CASSA GROUP BY [DATA CONT]-Weekday([DATA CONT],2)+1"

Correct?

[HansV]

Are you sure that the field and table names are correct? It works without errors for me.