Query: Retrieve Invoices between certain dates

[richard05]

Hey was wondering if someone would be able to direct me down the right path

I am trying to tie invoices within a date range as so:
Year 08 = 1/4/08-31/3/09
Year 09 = 1/4/09-31/3/10
Year 10 = 1/4/10-31/3/11

So far have managed to match invoices within year 08 as can see from the "WHERE" statement below. But I am trying to get it fix so that It will prompt user for Year, they enter and shows the invoices within that date range

SELECT [Capital Expenditure].[Invoice Number], 
[Capital Expenditure].[Order Number], 
[Capital Expenditure].[Date of Order],
[Capital Expenditure].[Date of Delivery], 
[Capital Expenditure].[Delivery Note No], 
[Capital Expenditure].[Nett Value], 
[Capital Expenditure].Supplier, 
 
FROM [Capital Expenditure]
 
WHERE ((([Capital Expenditure].[Date of Order]) Between #4/1/2008# 
And #3/31/2009#));

Tried to consider if statement but no luck

IF ((([Capital Expenditure].[Date of Order]) Between #4/1/2008# 
And #3/31/2009#)) THEN Year 08
 
ELSE IF ((([Capital Expenditure].[Date of Order]) Between #4/1/2009# 
And #3/31/20010#)) THEN Year 09
 
ELSE IF ((([Capital Expenditure].[Date of Order]) Between #4/1/20010# 
And #3/31/20011#)) THEN Year 10

 

[DCrake]

Create a function

Public Function FinYear(AnyDate As Date) As String


If Month(AnyDate) < 4 Then
    FinYear = Year(DateAdd("yyyy", -1, AnyDate)) & "/" & Year(AnyDate)
Else
    FinYear = Year(AnyDate) & "/" & Year(DateAdd("yyyy", 1, AnyDate))
End If


End Function

This will return 08/09, 09/10, 10/11, etc

 

[richard05]

Thanks for posting back so quick, however the function statement is not going as expected.

I have also tried to use CASE statement as well however error message keeps appearing as syntax error

SELECT [Capital Expenditure].[Invoice Number], 
[Capital Expenditure].[Order Number], 
[Capital Expenditure].[Date of Order],  
[Capital Expenditure].[Date of Delivery], 
[Capital Expenditure].Supplier, 
 
CASE 
WHEN ([Capital Expenditure].[Date of Order] Between #4/1/2008# And #3/31/2009#) THEN "08"
WHEN ([Capital Expenditure].[Date of Order] Between #4/1/2009# And #3/31/2010#) THEN "09"
WHEN ([Capital Expenditure].[Date of Order] Between #4/1/2010# And #3/31/2011#) THEN "10"
END
 
FROM [Capital Expenditure];

 

[DCrake]

What was wrong with the function?

Go to the immediate window and enter

?FinYear(Date())

Should return

2008/2009

Also your usage of the Select Case is incorrect for Access

Select Case Statement Example
This example uses the Select Case statement to evaluate the value of a variable. The second Case clause contains the value of the variable being evaluated, and therefore only the statement associated with it is executed.

Dim Number
Number = 8    ' Initialize variable.
Select Case Number    ' Evaluate Number.
Case 1 To 5    ' Number between 1 and 5, inclusive.
    Debug.Print "Between 1 and 5"
' The following is the only Case clause that evaluates to True.
Case 6, 7, 8    ' Number between 6 and 8.
    Debug.Print "Between 6 and 8"
Case 9 To 10    ' Number is 9 or 10.
Debug.Print "Greater than 8"
Case Else    ' Other values.
    Debug.Print "Not between 1 and 10"
End Select

 

[richard05]

I did try to use the public function but the following error message kept coming up:
The SELECT Statement includes a reserved word or an argument name that is misspelled or missing, or punctuation is incorrect

Here is the code that I entered:

SELECT [Capital Expenditure].[Invoice Number], 
[Capital Expenditure].[Order Number], 
[Capital Expenditure].[Date of Order], 
[FinYear] AS Expr1
 
PUBLIC FUNCTION [FinYear]([Capital Expenditure].[Date of Order]) AS STRING
 
IF [Capital Expenditure].[Date of Order] <= #31/3/2009# THEN
   [FinYear] = "08"
ELSE IF [Capital Expenditure].[Date of Order] <= #31/3/2010# THEN
   [FinYear] = "09"
ELSE IF [Capital Expenditure].[Date of Order] <= #31/3/2011# THEN
   [FinYear] = "10"
END IF
END FUNCTION
 
FROM [Capital Expenditure];

 

[DCrake]

Why have you bastardised a perfectly good function? Did you follow the instructions I gave in the earlier post. I just can't work out how you are attempting to employ the functionality?

SQL:


SELECT [Invoice Number],
[Order Number],
[Date of Order],
FinYear([Date of Order]) As FinancialYear FROM [Capital Expenditure]