Need help for Fiscal Month Query

[ipatch]

CAn you please help me return monthly data on a fiscal month:

my monthly cut-off is every 6th of the month. Therefore I need data returned from every 6th day of the month to 5th day of the following month. That is on a monthly basis.



Thnak you

[burrina]

SEE Post # 7

HTH

[ipatch]

Ok. I will try to upload the the table. thank you

[ipatch]

Hi sir! where will I send my file? thank you

[burrina]

You attach it here using the Go Advanced tab.

[ipatch]

Sorry I cant upload the file. it says error. i'll try again later. Anyway, I already tried the query but it did not return any data.. thank you again

[burrina]

Here are some helpful Date Criteria;

The current month:
DateSerial(Year(Date()), Month(Date()), 1)
The next month:
DateSerial(Year(Date()), Month(Date()) + 1, 1)
The last day of the current month:
DateSerial(Year(Date()), Month(Date()) + 1, 0)
The last day of the next month:
DateSerial(Year(Date()), Month(Date()) + 2, 0)
The first day of the previous month:
DateSerial(Year(Date()), Month(Date())-1,1)
The last day of the previous month:
DateSerial(Year(Date()), Month(Date()),0)
The first day of the current quarter:
DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 1, 1)
The last day of the current quarter:
DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 4, 0)
The first day of the current week (assuming Sunday = day 1):
Date() - WeekDay(Date()) + 1
The last day of the current week:
Date() - WeekDay(Date()) + 7
The first day of the current week (using settings in Options dialog box):
Date() - WeekDay(Date(), 0) + 1
The last day of the current week:
Date() - WeekDay(Date(), 0) + 7

HTH

[ipatch]

Hello sir, here's my raw mdb file. Kindly help to return data for each month with every 6th cut-off. thank you. Total in that date range on a monthly basis will be fine. thank you again

[burrina]

You should be able to work with this;

[ipatch]

Sir, if I create a report out of that query you have given, it just show all the data of the table. how will I sort them in a report. Example total sales from January 6th to February 5th, February 6th to March 5th, and so on...

[burrina]

Your going to need a qry to Group by your date criteria. Personally, I would use a Start Date and a End Date for this so it could be flexible. Create a form to enter your dates and open the Report with that date criteria.

[ipatch]

Your going to need a qry to Group by your date criteria. Personally, I would use a Start Date and a End Date for this so it could be flexible. Create a form to enter your dates and open the Report with that date criteria.

doing that would only return one data for that given start and end date. what i need is the total sale on a monthly basis but not 1st to 30th, instead 6th to 5th. because i need to differentiate it with my electricity cut-off which is every 6th.

[burrina]

WRONG: Total Sales is one thing, date criteria is another. As suggested, if you use a Calendar form, it will simplify your problem. You have date criteria in your query that is based on your Date Criteria Form and then you can Open a Report that is based on that criteria.

Between [Forms]![frmDateCriteria]![txtstart] And [Forms]![frmDateCriteria]![txtend]

[burrina]

Here is an Example for you.

Good Luck With Your Project!