Solved Format Function to get current week or current quarter data

[hrdpgajjar]

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]

Not sure about the quarter but week would be Format(datefield,"ww") I believe?

Have you tried Google?

 

[arnelgp]

you can use

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

 

[cheekybuddha]

See if this thread helps

Grouping by quarter depends on your definition of quarter.

For a simple definition (ie Q1:Jan-Mar; Q2:Apr-Jun; etc) see this SO thread.

Fiscal quarters will depend on your Financial year definition.

 

[theDBguy]

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.

 

[arnelgp]

Format() doesn't have a quarter symbol

how about:

Format$(Date, "q")

 

[theDBguy]

how about:

Format$(Date, "q")

Not in front of a computer now. Did that work?

I was just going by this.

Format function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

 

[Gasman]

Not in front of a computer now. Did that work?

I was just going by this.

Format function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

It appears it does

? Format(Date, "q") 
2

I had tried qq-ww with date, but that gave

? Format(Date, "qq-ww") 
22-15

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

 

[theDBguy]

It appears it does

Thanks! I guess MS Docs need to be updated. Cheers!

 

[cheekybuddha]

Not in front of a computer now. Did that work?

I was just going by this.

Format function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

You have to scroll further down to 'User-defined date/time formats' section.

 

[theDBguy]

You have to scroll further down to 'User-defined date/time formats' section.

LOL. My phone screen doesn't go down that far.

 

[hrdpgajjar]

Not sure about the quarter but week would be Format(datefield,"ww") I believe?

Have you tried Google?

Tried it and not working for me.

 

[hrdpgajjar]

you can use

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

It both gives "Date type mismatch error"

 

[arnelgp]

what did you do so far?

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

 

[Gasman]

It both gives "Date type mismatch error"

Format() always returns a string?
You were using it yourself, so you should be aware?

 

[hrdpgajjar]

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]

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]

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]

see Query1

 

[Gasman]

I am aware of this. Already get it working in current month format. But anyhow it does not work for current week..

? format(date,"ww")
15