DatePart - Can I change the default «firstweek»

[joepineapples]

This is my first thread so be gentle.

I curently have a query based on a payments table. In the payments table there is a field called date (when the payment was processed).

I want to be able to display, in my query, the quarter that this payment was made in. It is based on financial quarters so quarter 1 starts on April the 1st.

The default of firstweek seems to be January the 1st. Can I change it to April the 1st - if so how?

Many Thanks

 

[neileg]

Yes you can. The full syntax is DatePart(interval, date[,firstdayofweek[, firstweekofyear]]).

This is in Access help. Note that you need to have a code window open for Access to display all help topics (dead helpful, that!). You can go the the Modules window and press New to open a code window.

You shouldn't use Date as a field name as it is a reserved word. There is potential confusion with the Date() function. Chang your name to ProcessDate or something like that.

 

[joepineapples]

Thanks neileg.

Good point about renaming the date field.

I'm still struggling though.

This is the syntax I have:

Quarter: DatePart("q",[PaymentDate])

This gives me the quarter - the problem is it's based on the year starting on Jan the 1st. I want it to start from April the first.

Is there a value I can enter in [firstweekofyear] that can give me the correct quarter?

I'm very new to Access so you'll have to explain it to me as if I'm an idiot.

 

[neileg]

You need to read the Access help file, that's why I explained how to get the full help. Yes, you need to include a value for [firstweekofyear] but I can't explain it any better than the help file!