JULY TO JUNE FISCAL YEAR

[Guest]

Hello-
I need a query to find all entries within the current fiscal year (starting
July 1st to June 30th). So if the date I search for is July 30th I only want
it to find the results for that month. If I say June 30th. It would be for
the past 12 months.
Thanks in advance.

Zach K.

 

[James A. Fortune]

Hello-
I need a query to find all entries within the current fiscal year (starting
July 1st to June 30th). So if the date I search for is July 30th I only want
it to find the results for that month. If I say June 30th. It would be for
the past 12 months.
Thanks in advance.

Zach K.

ZZZK:

How about a function whose input is a date within the year and a test
date whose output is a boolean indicating whether or not the test date
is within the fiscal year. Then you could use the function in a query
with the criterion '= -1' to return only the records with a date during
that fiscal year. Then add the condition that the test date is on or
before the search date.

'pseudosql:
e.g., SELECT InFiscalYear(dtInFiscalYear, [InvoiceDate]) As ToSelect,
.... WHERE ToSelect = -1 AND [InvoiceDate] <= dtInFiscalYear;

'module air code:

Public Function InFiscalYear(dtInFiscalYear As Date, dtTest As Date) As
Boolean
Dim dtFYStart As Date
Dim dtFYEnd As Date

'July 1 to June 30
If Month(dtInFiscalYear) >= 7 Then
dtFYStart = DateSerial(Year(dtInFiscalYear), 7, 1)
dtFYEnd = DateSerial(Year(dtInFiscalYear) + 1, 6, 30)
Else
dtFYStart = DateSerial(Year(dtInFiscalYear) - 1, 7, 1)
dtFYEnd = DateSerial(Year(dtInFiscalYear), 6, 30)
End If
If dtTest >= dtFYStart And dtTest <= dtFYEnd Then
InFiscalYear = True
Else
InFiscalYear = False
End If
End Function

or using SQL only with a search date from a form:

'SQL air code

SELECT * FROM table WHERE [InvoiceDate] <=
Forms!frmMain!DateInFiscalYear AND
IIf(Month(Forms!frmMain!DateInFiscalYear) >= 7, [InvoiceDate] BETWEEN
DateSerial(Year(Forms!frmMain!DateInFiscalYear), 7, 1) AND
DateSerial(Year(Forms!frmMain!DateInFiscalYear) + 1, 6, 30),
[InvoiceDate] BETWEEN DateSerial(Year(Forms!frmMain!DateInFiscalYear) -
1, 7, 1) AND DateSerial(Year(Forms!frmMain!DateInFiscalYear), 6, 30)) = -1

These seem to do what you are asking for. Maybe a parameter in the
query would be better than referencing the form field five times.

James A. Fortune
(e-mail address removed)

 

[Guest]

ok I tried that a couple times, and a couple different ways and it works,
BUT. It errors out sometimes. I think its because the "invoice date" in your
sql I have being taken from results of another query, and it doesn't finish
the update fast enough when theres alot of results for it to be used. Does
this sound like what my problem is? And if so, how do I fix it.( I don't want
to have to type in the date for the query, thats why I pointed to a date
field in the other query) here is my sql.

SELECT BOOKINGS.[PT BOOKED], BOOKINGS.[MH BOOKED], BOOKINGS.DATE,
BOOKINGS.[TOTAL BOOKED], BOOKINGS.[PT TRANSFERED OUT], BOOKINGS.[MH
TRANSFERED OUT], BOOKINGS.[SHEAVES TRANSERED OUT], BOOKINGS.[TOTAL ORDERS],
BOOKINGS.[NUMBER PT ORDERS BOOKED], BOOKINGS.[NUMBER MH ORDERS BOOKED],
BOOKINGS.[TOTAL SHIPPED], BOOKINGS.[TOTAL INVOICE], BOOKINGS.MONTH,
BOOKINGS.YEAR, BOOKINGS.[MONTH NUMBER]
FROM BOOKINGS
WHERE (((BOOKINGS.DATE)<=[Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])
AND ((IIf(Month([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])>=7,[Date]
Between DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE]),7,1)
And DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST
DATE])+1,6,30),[DATE] Between DateSerial(Year([Forms]![BOOKINGS DAILY
REPORT]![THE LAST DATE])-1,7,1) And DateSerial(Year([Forms]![BOOKINGS DAILY
REPORT]![THE LAST DATE]),6,30)))=-1));

Hello-
I need a query to find all entries within the current fiscal year (starting
July 1st to June 30th). So if the date I search for is July 30th I only want
it to find the results for that month. If I say June 30th. It would be for
the past 12 months.
Thanks in advance.

Zach K.

ZZZK:

How about a function whose input is a date within the year and a test
date whose output is a boolean indicating whether or not the test date
is within the fiscal year. Then you could use the function in a query
with the criterion '= -1' to return only the records with a date during
that fiscal year. Then add the condition that the test date is on or
before the search date.

'pseudosql:
e.g., SELECT InFiscalYear(dtInFiscalYear, [InvoiceDate]) As ToSelect,
.... WHERE ToSelect = -1 AND [InvoiceDate] <= dtInFiscalYear;

'module air code:

Public Function InFiscalYear(dtInFiscalYear As Date, dtTest As Date) As
Boolean
Dim dtFYStart As Date
Dim dtFYEnd As Date

'July 1 to June 30
If Month(dtInFiscalYear) >= 7 Then
dtFYStart = DateSerial(Year(dtInFiscalYear), 7, 1)
dtFYEnd = DateSerial(Year(dtInFiscalYear) + 1, 6, 30)
Else
dtFYStart = DateSerial(Year(dtInFiscalYear) - 1, 7, 1)
dtFYEnd = DateSerial(Year(dtInFiscalYear), 6, 30)
End If
If dtTest >= dtFYStart And dtTest <= dtFYEnd Then
InFiscalYear = True
Else
InFiscalYear = False
End If
End Function

or using SQL only with a search date from a form:

'SQL air code

SELECT * FROM table WHERE [InvoiceDate] <=
Forms!frmMain!DateInFiscalYear AND
IIf(Month(Forms!frmMain!DateInFiscalYear) >= 7, [InvoiceDate] BETWEEN
DateSerial(Year(Forms!frmMain!DateInFiscalYear), 7, 1) AND
DateSerial(Year(Forms!frmMain!DateInFiscalYear) + 1, 6, 30),
[InvoiceDate] BETWEEN DateSerial(Year(Forms!frmMain!DateInFiscalYear) -
1, 7, 1) AND DateSerial(Year(Forms!frmMain!DateInFiscalYear), 6, 30)) = -1

These seem to do what you are asking for. Maybe a parameter in the
query would be better than referencing the form field five times.

James A. Fortune
(e-mail address removed)

 

[Guest]

Could I make the first query run....and have this query wait till its done,
then run.

ok I tried that a couple times, and a couple different ways and it works,
BUT. It errors out sometimes. I think its because the "invoice date" in your
sql I have being taken from results of another query, and it doesn't finish
the update fast enough when theres alot of results for it to be used. Does
this sound like what my problem is? And if so, how do I fix it.( I don't want
to have to type in the date for the query, thats why I pointed to a date
field in the other query) here is my sql.

SELECT BOOKINGS.[PT BOOKED], BOOKINGS.[MH BOOKED], BOOKINGS.DATE,
BOOKINGS.[TOTAL BOOKED], BOOKINGS.[PT TRANSFERED OUT], BOOKINGS.[MH
TRANSFERED OUT], BOOKINGS.[SHEAVES TRANSERED OUT], BOOKINGS.[TOTAL ORDERS],
BOOKINGS.[NUMBER PT ORDERS BOOKED], BOOKINGS.[NUMBER MH ORDERS BOOKED],
BOOKINGS.[TOTAL SHIPPED], BOOKINGS.[TOTAL INVOICE], BOOKINGS.MONTH,
BOOKINGS.YEAR, BOOKINGS.[MONTH NUMBER]
FROM BOOKINGS
WHERE (((BOOKINGS.DATE)<=[Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])
AND ((IIf(Month([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])>=7,[Date]
Between DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE]),7,1)
And DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST
DATE])+1,6,30),[DATE] Between DateSerial(Year([Forms]![BOOKINGS DAILY
REPORT]![THE LAST DATE])-1,7,1) And DateSerial(Year([Forms]![BOOKINGS DAILY
REPORT]![THE LAST DATE]),6,30)))=-1));

Hello-
I need a query to find all entries within the current fiscal year (starting
July 1st to June 30th). So if the date I search for is July 30th I only want
it to find the results for that month. If I say June 30th. It would be for
the past 12 months.
Thanks in advance.

Zach K.

ZZZK:

How about a function whose input is a date within the year and a test
date whose output is a boolean indicating whether or not the test date
is within the fiscal year. Then you could use the function in a query
with the criterion '= -1' to return only the records with a date during
that fiscal year. Then add the condition that the test date is on or
before the search date.

'pseudosql:
e.g., SELECT InFiscalYear(dtInFiscalYear, [InvoiceDate]) As ToSelect,
.... WHERE ToSelect = -1 AND [InvoiceDate] <= dtInFiscalYear;

'module air code:

Public Function InFiscalYear(dtInFiscalYear As Date, dtTest As Date) As
Boolean
Dim dtFYStart As Date
Dim dtFYEnd As Date

'July 1 to June 30
If Month(dtInFiscalYear) >= 7 Then
dtFYStart = DateSerial(Year(dtInFiscalYear), 7, 1)
dtFYEnd = DateSerial(Year(dtInFiscalYear) + 1, 6, 30)
Else
dtFYStart = DateSerial(Year(dtInFiscalYear) - 1, 7, 1)
dtFYEnd = DateSerial(Year(dtInFiscalYear), 6, 30)
End If
If dtTest >= dtFYStart And dtTest <= dtFYEnd Then
InFiscalYear = True
Else
InFiscalYear = False
End If
End Function

or using SQL only with a search date from a form:

'SQL air code

SELECT * FROM table WHERE [InvoiceDate] <=
Forms!frmMain!DateInFiscalYear AND
IIf(Month(Forms!frmMain!DateInFiscalYear) >= 7, [InvoiceDate] BETWEEN
DateSerial(Year(Forms!frmMain!DateInFiscalYear), 7, 1) AND
DateSerial(Year(Forms!frmMain!DateInFiscalYear) + 1, 6, 30),
[InvoiceDate] BETWEEN DateSerial(Year(Forms!frmMain!DateInFiscalYear) -
1, 7, 1) AND DateSerial(Year(Forms!frmMain!DateInFiscalYear), 6, 30)) = -1

These seem to do what you are asking for. Maybe a parameter in the
query would be better than referencing the form field five times.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Could I make the first query run....and have this query wait till its done,
then run.

:

ok I tried that a couple times, and a couple different ways and it works,
BUT. It errors out sometimes. I think its because the "invoice date" in your
sql I have being taken from results of another query, and it doesn't finish
the update fast enough when theres alot of results for it to be used. Does
this sound like what my problem is? And if so, how do I fix it.( I don't want
to have to type in the date for the query, thats why I pointed to a date
field in the other query) here is my sql.

SELECT BOOKINGS.[PT BOOKED], BOOKINGS.[MH BOOKED], BOOKINGS.DATE,
BOOKINGS.[TOTAL BOOKED], BOOKINGS.[PT TRANSFERED OUT], BOOKINGS.[MH
TRANSFERED OUT], BOOKINGS.[SHEAVES TRANSERED OUT], BOOKINGS.[TOTAL ORDERS],
BOOKINGS.[NUMBER PT ORDERS BOOKED], BOOKINGS.[NUMBER MH ORDERS BOOKED],
BOOKINGS.[TOTAL SHIPPED], BOOKINGS.[TOTAL INVOICE], BOOKINGS.MONTH,
BOOKINGS.YEAR, BOOKINGS.[MONTH NUMBER]
FROM BOOKINGS
WHERE (((BOOKINGS.DATE)<=[Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])
AND ((IIf(Month([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])>=7,[Date]
Between DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE]),7,1)
And DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST
DATE])+1,6,30),[DATE] Between DateSerial(Year([Forms]![BOOKINGS DAILY
REPORT]![THE LAST DATE])-1,7,1) And DateSerial(Year([Forms]![BOOKINGS DAILY
REPORT]![THE LAST DATE]),6,30)))=-1));

I'm not sure how you're putting everything together so try running the
queries one after another first. Note that Access is able to base one
query on another and that when you do that Access will wait for the
results of the initial query.

James A. Fortune
(e-mail address removed)

 

[Guest]

Ok i got that to work by changing which field it looked to so it didn't have
to wait for the query. Now I need the same thing but looking for the
previous year. I don't really understand the sql statment enough to try to
modify it. Thank you so much for all the help!!!!

Could I make the first query run....and have this query wait till its done,
then run.

:

ok I tried that a couple times, and a couple different ways and it works,
BUT. It errors out sometimes. I think its because the "invoice date" in your
sql I have being taken from results of another query, and it doesn't finish
the update fast enough when theres alot of results for it to be used. Does
this sound like what my problem is? And if so, how do I fix it.( I don't want
to have to type in the date for the query, thats why I pointed to a date
field in the other query) here is my sql.

SELECT BOOKINGS.[PT BOOKED], BOOKINGS.[MH BOOKED], BOOKINGS.DATE,
BOOKINGS.[TOTAL BOOKED], BOOKINGS.[PT TRANSFERED OUT], BOOKINGS.[MH
TRANSFERED OUT], BOOKINGS.[SHEAVES TRANSERED OUT], BOOKINGS.[TOTAL ORDERS],
BOOKINGS.[NUMBER PT ORDERS BOOKED], BOOKINGS.[NUMBER MH ORDERS BOOKED],
BOOKINGS.[TOTAL SHIPPED], BOOKINGS.[TOTAL INVOICE], BOOKINGS.MONTH,
BOOKINGS.YEAR, BOOKINGS.[MONTH NUMBER]
FROM BOOKINGS
WHERE (((BOOKINGS.DATE)<=[Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])
AND ((IIf(Month([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])>=7,[Date]
Between DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE]),7,1)
And DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST
DATE])+1,6,30),[DATE] Between DateSerial(Year([Forms]![BOOKINGS DAILY
REPORT]![THE LAST DATE])-1,7,1) And DateSerial(Year([Forms]![BOOKINGS DAILY
REPORT]![THE LAST DATE]),6,30)))=-1));

I'm not sure how you're putting everything together so try running the
queries one after another first. Note that Access is able to base one
query on another and that when you do that Access will wait for the
results of the initial query.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Ok i got that to work by changing which field it looked to so it didn't have
to wait for the query. Now I need the same thing but looking for the
previous year. I don't really understand the sql statment enough to try to
modify it. Thank you so much for all the help!!!!

:

Could I make the first query run....and have this query wait till its done,
then run.

:



ok I tried that a couple times, and a couple different ways and it works,
BUT. It errors out sometimes. I think its because the "invoice date" in your
sql I have being taken from results of another query, and it doesn't finish
the update fast enough when theres alot of results for it to be used. Does
this sound like what my problem is? And if so, how do I fix it.( I don't want
to have to type in the date for the query, thats why I pointed to a date
field in the other query) here is my sql.

SELECT BOOKINGS.[PT BOOKED], BOOKINGS.[MH BOOKED], BOOKINGS.DATE,
BOOKINGS.[TOTAL BOOKED], BOOKINGS.[PT TRANSFERED OUT], BOOKINGS.[MH
TRANSFERED OUT], BOOKINGS.[SHEAVES TRANSERED OUT], BOOKINGS.[TOTAL ORDERS],
BOOKINGS.[NUMBER PT ORDERS BOOKED], BOOKINGS.[NUMBER MH ORDERS BOOKED],
BOOKINGS.[TOTAL SHIPPED], BOOKINGS.[TOTAL INVOICE], BOOKINGS.MONTH,
BOOKINGS.YEAR, BOOKINGS.[MONTH NUMBER]

FROM BOOKINGS

WHERE (((BOOKINGS.DATE)<=[Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])
AND ((IIf(Month([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])>=7,[Date]
Between DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE]),7,1)
And DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST
DATE])+1,6,30),[DATE] Between DateSerial(Year([Forms]![BOOKINGS DAILY
REPORT]![THE LAST DATE])-1,7,1) And DateSerial(Year([Forms]![BOOKINGS DAILY
REPORT]![THE LAST DATE]),6,30)))=-1));

I'm not sure how you're putting everything together so try running the
queries one after another first. Note that Access is able to base one
query on another and that when you do that Access will wait for the
results of the initial query.

James A. Fortune
(e-mail address removed)

I think all you need to do is use something like DateAdd("m", -12,
dtInFiscalYear) instead of dtInFiscalYear. That should place the date
into the prior fiscal year. Then if the function returns True the test
date is in the prior fiscal year.

James A. Fortune
(e-mail address removed)

 

[Guest]

Thanks again so much for all your help. I think I have everything I need to
finish the project

Ok i got that to work by changing which field it looked to so it didn't have
to wait for the query. Now I need the same thing but looking for the
previous year. I don't really understand the sql statment enough to try to
modify it. Thank you so much for all the help!!!!

:

ZigZagZak wrote:

Could I make the first query run....and have this query wait till its done,
then run.

:



ok I tried that a couple times, and a couple different ways and it works,
BUT. It errors out sometimes. I think its because the "invoice date" in your
sql I have being taken from results of another query, and it doesn't finish
the update fast enough when theres alot of results for it to be used. Does
this sound like what my problem is? And if so, how do I fix it.( I don't want
to have to type in the date for the query, thats why I pointed to a date
field in the other query) here is my sql.

SELECT BOOKINGS.[PT BOOKED], BOOKINGS.[MH BOOKED], BOOKINGS.DATE,
BOOKINGS.[TOTAL BOOKED], BOOKINGS.[PT TRANSFERED OUT], BOOKINGS.[MH
TRANSFERED OUT], BOOKINGS.[SHEAVES TRANSERED OUT], BOOKINGS.[TOTAL ORDERS],
BOOKINGS.[NUMBER PT ORDERS BOOKED], BOOKINGS.[NUMBER MH ORDERS BOOKED],
BOOKINGS.[TOTAL SHIPPED], BOOKINGS.[TOTAL INVOICE], BOOKINGS.MONTH,
BOOKINGS.YEAR, BOOKINGS.[MONTH NUMBER]

FROM BOOKINGS

WHERE (((BOOKINGS.DATE)<=[Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])
AND ((IIf(Month([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE])>=7,[Date]
Between DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST DATE]),7,1)
And DateSerial(Year([Forms]![BOOKINGS DAILY REPORT]![THE LAST
DATE])+1,6,30),[DATE] Between DateSerial(Year([Forms]![BOOKINGS DAILY
REPORT]![THE LAST DATE])-1,7,1) And DateSerial(Year([Forms]![BOOKINGS DAILY
REPORT]![THE LAST DATE]),6,30)))=-1));

I'm not sure how you're putting everything together so try running the
queries one after another first. Note that Access is able to base one
query on another and that when you do that Access will wait for the
results of the initial query.

James A. Fortune
(e-mail address removed)

I think all you need to do is use something like DateAdd("m", -12,
dtInFiscalYear) instead of dtInFiscalYear. That should place the date
into the prior fiscal year. Then if the function returns True the test
date is in the prior fiscal year.

James A. Fortune
(e-mail address removed)