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
extract first and last day of month in field
-
- PlatinumLounger
- Posts: 4364
- Joined: 26 Apr 2010, 17:36
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: extract first and last day of month in field
For example:
SELECT [DATE1]-Day([DATE1])+1 AS MESE, Min([DATE1]) & " - " & Max([DATE1]) AS GAMMA
FROM MYTABLE
GROUP BY [DATE1]-Day([DATE1])+1;
SELECT [DATE1]-Day([DATE1])+1 AS MESE, Min([DATE1]) & " - " & Max([DATE1]) AS GAMMA
FROM MYTABLE
GROUP BY [DATE1]-Day([DATE1])+1;
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4364
- Joined: 26 Apr 2010, 17:36
Re: extract first and last day of month in field
sorry for delay, the code work greaaaaaaaaaaaaaaaaaaaaTTTTTTTTTTTTTTTTTTTTTTTTTT!!!!!!!!!!!!!!!!!!!!HansV wrote:For example:
SELECT [DATE1]-Day([DATE1])+1 AS MESE, Min([DATE1]) & " - " & Max([DATE1]) AS GAMMA
FROM MYTABLE
GROUP BY [DATE1]-Day([DATE1])+1;
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
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: extract first and last day of month in field
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;
SELECT [DATE1]-Weekday([DATE1],2)+1 AS MESE, Min([DATE1]) & " - " & Max([DATE1]) AS GAMMA
FROM MYTABLE
GROUP BY [DATE1]-Weekday([DATE1],2)+1;
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4364
- Joined: 26 Apr 2010, 17:36
Re: extract first and last day of month in field
Work!HansV wrote: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;
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?
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: extract first and last day of month in field
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"
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4364
- Joined: 26 Apr 2010, 17:36
Re: extract first and last day of month in field
HansV wrote: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?
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: extract first and last day of month in field
Are you sure that the field and table names are correct? It works without errors for me.
Best wishes,
Hans
Hans