Solved DatePart function error

[davegoodo]

I'm having problems getting the DatePart function to work. At present, when I run the query I get an error "Invalid Procedure Call". As far as I know I'm doing the right thing but obviously not completely. I've attached a picture of the QBE grid and also the SQL for the query in question.

I'm using my horse racing database to run this query. I'm using the Transaction table and I want to group results by Month.
The command I've issued in the first column is:
Format(Mth: DatePart("mm",[RaceDate])
the other columns are counting, summing and grouping.
I've looked online for answers but nothing helped, so I'm turning to the forum for help, please?
Thanks,
I just want to add that I would like to get the syntax for ordering the query in year and month. (I have records for Dec 2023 that I also want to include in the query), as in Dec 23, Jan 24, Feb 24 etc.
Can anyone help please?

 

[June7]

Should include SQL text within your post, not an attachment.

Only one "m". Review https://support.microsoft.com/en-us/office/datepart-function-26868a79-5505-4e5a-8905-6001372223fa

You should probably include Year in the grouping.

YYMM: Format(RaceDate, "yyyymm")

 

[davegoodo]

Should include SQL text within your post, not an attachment.

Only one "m". Review https://support.microsoft.com/en-us/office/datepart-function-26868a79-5505-4e5a-8905-6001372223fa

You should probably include Year in the grouping.

YYMM: Format(RaceDate, "yyyymm")

Thanks for your quick response!

OK, only one "m" that's great, no wonder I was getting the error.
I've just run it and it works, thank you.
I will review that link that you've added, no doubt I'll learn more about it.

I'll make sure I include the SQL in my posts in future, I'm fairly new to the forum but I need to do what's expected, thanks again.
I'll try out the format statement you've supplied as well. Will that return months and years as in Dec23, Jan24, Feb24. I'll try it out first.
That's great, thanks.

 

[Gasman]

First thing I do is google for the syntax?
I used datepart() access

DatePart Function - Microsoft Support

support.microsoft.com

 

[davegoodo]

First thing I do is google for the syntax?
I used datepart() access

DatePart Function - Microsoft Support

support.microsoft.com

Thanks for the response, I'm not sure what you mean with: I used datepart() access ?
I looked up the link it refers to DatePart as well. Thank you.

 

[Gasman]

That was the serach term I used to get that link.
There apparently is DatePart for SQL and others, so to ensure I got the correct sysntax for Access, I added the Access word and Googled a second time.

 

[davegoodo]

That was the serach term I used to get that link.
There apparently is DatePart for SQL and others, so to ensure I got the correct sysntax for Access, I added the Access word and Googled a second time.

I see, thanks for the update Gasman!

 

[davegoodo]

I see, thanks for the update Gasman!

That was the serach term I used to get that link.
There apparently is DatePart for SQL and others, so to ensure I got the correct sysntax for Access, I added the Access word and Googled a second time.

Just a question, how do you combine Format with DatePart, I used it earlier but got an error. Could you help?

 

[ebs17]

DatePart, Month and Year return an integer, Format returns text.
With text, more bytes have to be processed than with an integer, which is reflected in different performance - it may be that performance also plays a role at some point.

SELECT Year(RaceDate) * 100 + Month(RaceDate) AS YM
FROM TableX 
GROUP BY Year(RaceDate) * 100 + Month(RaceDate)

SELECT Format(RaceDate, "yyyymm") AS YM
FROM TableX 
GROUP BY Format(RaceDate, "yyyymm")

Because of the effort involved in grouping, the first variant will be around 30 percent faster than the second variant with Format.

You could also leave month and year as separate columns and use them that way.

 

[davegoodo]

DatePart, Month and Year return an integer, Format returns text.
With text, more bytes have to be processed than with an integer, which is reflected in different performance - it may be that performance also plays a role at some point.

SELECT Year(RaceDate) * 100 + Month(RaceDate) AS YM
FROM TableX
GROUP BY Year(RaceDate) * 100 + Month(RaceDate)

SELECT Format(RaceDate, "yyyymm") AS YM
FROM TableX
GROUP BY Format(RaceDate, "yyyymm")

Because of the effort involved in grouping, the first variant will be around 30 percent faster than the second variant with Format.

You could also leave month and year as separate columns and use them that way.

That's fantastic! It's something I can use to group my data further. That's food for thought and application. I shall study that further and play around with my data, thanks very much for that.

Just a question, what's with the multiplication by 100?

 

[Gasman]

Why not just try it and see?

? year(date)*100 + month(date)

 

[davegoodo]

Why not just try it and see?

? year(date)*100 + month(date)

Pardon my ignorance but where do I run it? Is it SQL or VBA?
I just tried SQL but it wants an Update or Select or something, I give up what is it?

 

[davegoodo]

Pardon my ignorance but where do I run it? Is it SQL or VBA?
I just tried SQL but it wants an Update or Select or something, I give up what is it?

OK, I get it, its VBA, I just ran it in the Immediate window and it gives 202404. Very good! So this is some way I can organise my YYYYMM, correct? Writing some VBA to organise the data? The question mark gave it away.

 

[davegoodo]

DatePart, Month and Year return an integer, Format returns text.
With text, more bytes have to be processed than with an integer, which is reflected in different performance - it may be that performance also plays a role at some point.

SELECT Year(RaceDate) * 100 + Month(RaceDate) AS YM
FROM TableX
GROUP BY Year(RaceDate) * 100 + Month(RaceDate)

SELECT Format(RaceDate, "yyyymm") AS YM
FROM TableX
GROUP BY Format(RaceDate, "yyyymm")

Because of the effort involved in grouping, the first variant will be around 30 percent faster than the second variant with Format.

You could also leave month and year as separate columns and use them that way.

My apologies. I've just run the SQL and it does exactly what I'm wanting. I was asleep at the wheel. Didn't think it through. That's great I'll be able to apply this in my queries. I've got to check out now it's 21:00 in Australia where I live. I'll get back into it tomorrow and let you know how I get on. Thanks very much for the insights! I'll get back to you. My wife has dinner ready.

 

[davegoodo]

Why not just try it and see?

? year(date)*100 + month(date)

What I would like to know is why multiplying by 100 works?
The result is great, I've been able to apply this to my queries and they are working beautifully but my inner mathematician wants to know why?
Thanks for sticking with me on this.

 

[Gasman]

Year is 2024.
Now you could concantenate the month 4, but wuld have to format it as "04", else when you get to month 10, that would sort first.
So by multiplying the year by 100 you get 202400, and if you add 4 you get 202404.
If you also wanted days, you would multiply year by10000, month by 100 and then add days.

Though there are other ways to do this. BOS Cobol used to process dates that way years ago.

? year(date)*10000 + month(date)* 100 + Day(date)
 20240422

 

[ebs17]

What I would like to know is why multiplying by 100 works?

The overall expression produces a number using mathematical calculation, i.e. 202404 for the current month.
So the multiplication only serves to put 2024 in front of the 4.

There are always requirements where the year and month must appear in the expression, for example with column names in pivot tables.
Again, the calculation is to produce an integer instead of a string. To put it bluntly: the computer can calculate better and more efficiently with numbers than with text.

 

[davegoodo]

Year is 2024.
Now you could concantenate the month 4, but wuld have to format it as "04", else when you get to month 10, that would sort first.
So by multiplying the year by 100 you get 202400, and if you add 4 you get 202404.
If you also wanted days, you would multiply year by10000, month by 100 and then add days.

Though there are other ways to do this. BOS Cobol used to process dates that way years ago.

? year(date)*10000 + month(date)* 100 + Day(date)
20240422

Thank you. I see what is happening now, the year * 100 creates 2 placeholders for months to slot in. Very good. Thanks for explaining. What a good idea!

 

[davegoodo]

The overall expression produces a number using mathematical calculation, i.e. 202404 for the current month.
So the multiplication only serves to put 2024 in front of the 4.

There are always requirements where the year and month must appear in the expression, for example with column names in pivot tables.
Again, the calculation is to produce an integer instead of a string. To put it bluntly: the computer can calculate better and more efficiently with numbers than with text.

Thanks, I just replied to @Gasman and said what a good idea this is. Using year * 100 to create 2 placeholders for the months to slot in. That is certainly lateral thinking, well done. You had me fooled but I see it now. Most importantly it has helped me to put the months and years in correct order which is great and solves my problem. (No need for DatePart() in this case). Thank you very much for putting forward the idea! I have spent the afternoon rewriting some of the queries I have and they all work wonderfully now, in the correct order. Much appreciated, 10/10. I'm going to log out now so I can get down to finishing my report, which you've helped so much with.

 

[davegoodo]

Year is 2024.
Now you could concantenate the month 4, but wuld have to format it as "04", else when you get to month 10, that would sort first.
So by multiplying the year by 100 you get 202400, and if you add 4 you get 202404.
If you also wanted days, you would multiply year by10000, month by 100 and then add days.

Though there are other ways to do this. BOS Cobol used to process dates that way years ago.

? year(date)*10000 + month(date)* 100 + Day(date)
20240422

I forgot to mention your 10000 * year formula which creates 4 placeholders. You've certainly turned my head on this ideas. Very lateral thinking.
Thanks a lot for these ideas and pushing me to try it when I couldn't see the forest from the trees. Really great and creative! I won't be forgetting these tricks into the future. This is the great thing about forums, just the ideas you can pick up that you wouldn't find in books. Fantastic.
Thanks again @Gasman really great.