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

hrdpgajjar

Registered User.
Local time
Today, 15:52
Joined
Sep 24, 2019
Messages
37
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, 11:22
Joined
Sep 21, 2011
Messages
10,352
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
Today, 18:22
Joined
May 7, 2009
Messages
16,134
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, 03:22
Joined
Oct 29, 2018
Messages
18,792
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, 11:22
Joined
Sep 21, 2011
Messages
10,352
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
Today, 18:22
Joined
May 7, 2009
Messages
16,134
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
Today, 15:52
Joined
Sep 24, 2019
Messages
37
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
Today, 15:52
Joined
Sep 24, 2019
Messages
37
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
Today, 15:52
Joined
Sep 24, 2019
Messages
37
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
Today, 18:22
Joined
May 7, 2009
Messages
16,134
see Query1
 

Attachments

  • DateElement.accdb
    384 KB · Views: 397

Gasman

Enthusiastic Amateur
Local time
Today, 11:22
Joined
Sep 21, 2011
Messages
10,352
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