DatePart results in #Func! (1 Viewer)

B

bigal.nz

Registered User.
Local time
Today, 14:03
Joined
Jul 10, 2016
Messages
92
Hi Guys,

I am trying to use DatePart("mm", [MAS.DateStart]) in :

Code: 
SELECT DatePart("mm", [MAS.StartDate]) As Month FROM MAS;

Some results are blank, which is fine, but the majority are #Func!

The DateStart column is set to data type date. I am in NZ so we use dd/mm/yyyy.

Any ideas?

Cheers

AL
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:03
Joined
Aug 30, 2003
Messages
36,124
I think you want a single "m" for the argument.
 
B

bigal.nz

Registered User.
Local time
Today, 14:03
Joined
Jul 10, 2016
Messages
92
Duh. That helped. Can I also return from datepart month and year?
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:03
Joined
Aug 30, 2003
Messages
36,124
"yyyy" will return the year. If you want both together, the Format() function may be a simpler option.
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:03
Joined
Aug 30, 2003
Messages
36,124
Try

Format([StartDate],"mmyyyy")
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:03
Joined
May 7, 2009
Messages
19,229
its only one "m" not double. if yiu want to get the name if month use format(startdate,"mmm") or format(startdate,"mmmm")
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:03
Joined
Aug 30, 2003
Messages
36,124
No problem. Just said goodbye to my daughter, on her way home to Wellington!
 
M

Mark_

Longboard on the internet
Local time
Yesterday, 19:03
Joined
Sep 12, 2017
Messages
2,111
bigal.nz said:
The DateStart column is set to data type date. I am in NZ so we use dd/mm/yyyy.
Click to expand...

For future note, type "DATE" does not care what format you use. It is stored internally as a number. dd/mm/yyyy, mm/dd/yyyy, or yyyymmdd are formats for a value that would be something like 40145.331999456 in the database.

Letting you know because if you try thinking of the stored data as "18/04/2017" rather than some number you can get yourself totally confused.
 
B

bigal.nz

Registered User.
Local time
Today, 14:03
Joined
Jul 10, 2016
Messages
92
pbaldy said:
No problem. Just said goodbye to my daughter, on her way home to Wellington!
Click to expand...

Windy Welly lol.

I am now trying to get a bit more tricky with the query:

Code: 
SELECT MAS.Pathway, Count(*) As CountPathway, format([MAS.DateStart], "mmm yyyy") As MonthYear
FROM MAS
GROUP BY MAS.PAthway, MonthYear;

However I am not getting a count per month. I am getting:

Pathway Count Month/Year
Sales 1 Dec 2017
Sales 1 Dec 2017
Develop 1 Dec 2017
Develop 1 Dec 2017
Develop 1 Dec 2017

rather than

Sales 2 Dec 2017
Develop 3 Dec 2017

etc

Help!
 
Last edited:
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:03
Joined
Aug 30, 2003
Messages
36,124
You'd have to group by the formatted column, not the actual date.
 
M

Mark_

Longboard on the internet
Local time
Yesterday, 19:03
Joined
Sep 12, 2017
Messages
2,111
Special note; for sorting I'd recommend adding a column that you are not going to show with the date formatted as YYYYMM so that you can keep it in proper order. Hate to see December before February.
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:03
Joined
Aug 30, 2003
Messages
36,124
Happy to help!
 
You must log in or register to reply here.

Similar threads

J
Solved 7 reports 1 or 2 buttons
2
Replies
21
Views
640
jazsriel
J
ardy
Solved Expression Builder Latest Date in Forms
Replies
10
Views
451
ardy
ardy
B
Avoid duplicate records (overlapping dates)
Replies
18
Views
617
arnelgp
arnelgp
G
Code compilation problem
Replies
9
Views
488
George-Bowyer
G
O
Using Cross Tab Query In Report
Replies
3
Views
522
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom