Solved Format Function to get current week or current quarter data (1 Viewer)

hrdpgajjar

Registered User.
Local time
Tomorrow, 02:59
Joined
Sep 24, 2019
Messages
51
Hi all,
I need to get report of material sent by current week with a query. I am able to get report of current month by following funtion,

Format([Material Sent Date], "yyyymm") - by this function I am able to get current month data and,

Format([Material Sent Date], date()) - by this function I am able to get current date (todays date) data

but I need to get data of current week and current quarter by the above function.

Please help how can I do that.


Thank you,
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:29
Joined
Sep 21, 2011
Messages
14,047
Not sure about the quarter but week would be Format(datefield,"ww") I believe?

Have you tried Google?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:29
Joined
May 7, 2009
Messages
19,169
you can use

Format([Material Sent Date], "ww") '==> the return is string.
DatePart("ww", [Material Sent Date]) '==>return numeric
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:29
Joined
Oct 29, 2018
Messages
21,358
Hi all,
I need to get report of material sent by current week with a query. I am able to get report of current month by following funtion,

Format([Material Sent Date], "yyyymm") - by this function I am able to get current month data and,

Format([Material Sent Date], date()) - by this function I am able to get current date (todays date) data

but I need to get data of current week and current quarter by the above function.

Please help how can I do that.


Thank you,
Hi. Format() doesn't have a quarter symbol, but DatePart() does.
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:29
Joined
Sep 21, 2011
Messages
14,047
Not in front of a computer now. Did that work?

I was just going by this.

It appears it does :)

Code:
? Format(Date, "q") 
2

I had tried qq-ww with date, but that gave
Code:
? Format(Date, "qq-ww") 
22-15

now I see that I needed the "q" just the once. :)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:29
Joined
May 7, 2009
Messages
19,169
what did you do so far?

then you need to Modify [Material Sent Date] field (change it to Date/Time).
 

hrdpgajjar

Registered User.
Local time
Tomorrow, 02:59
Joined
Sep 24, 2019
Messages
51
what did you do so far?

then you need to Modify [Material Sent Date] field (change it to Date/Time).
the field is already a date/time format. And I am putting the format function in query filed. Is it alright or I m doing something wrong?
 

hrdpgajjar

Registered User.
Local time
Tomorrow, 02:59
Joined
Sep 24, 2019
Messages
51
Format() always returns a string?
You were using it yourself, so you should be aware?
I am aware of this. Already get it working in current month format. But anyhow it does not work for current week..
 

hrdpgajjar

Registered User.
Local time
Tomorrow, 02:59
Joined
Sep 24, 2019
Messages
51
what did you do so far?

then you need to Modify [Material Sent Date] field (change it to Date/Time).
Can you send me the sample data base with just name and date columns with the working query for my reference pls..
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:29
Joined
May 7, 2009
Messages
19,169
see Query1
 

Attachments

  • DateElement.accdb
    384 KB · Views: 503

Gasman

Enthusiastic Amateur
Local time
Today, 21:29
Joined
Sep 21, 2011
Messages
14,047
I am aware of this. Already get it working in current month format. But anyhow it does not work for current week..
Code:
? format(date,"ww")
15
 

Users who are viewing this thread

Top Bottom