Using VB functions to set criteria for queries

[RFletch]

I have a form which allows a user to select two financial years - one is the beginning financial year and the other is the end which feeds into a query which outputs all records between the two financial years.
Because Access has no data type for storing financial years, I have to store them as strings (eg "97/98&quot. This obviously means I cannot use the "Between" function in the query to bring up all the intervening years (eg Between "97/98" And "01/02&quot.
To try and get around this I have a function called "getfinyears" that returns to the criteria box in the query the correct expression (in the example above the expression returned should be:
"97/98" Or "98/99" Or "99/00" Or "00/01" Or "01/02"
which would return the required records.
Unfortunately because the "getfinyears" function returns a string, the actual expression returned is:
"97/98 Or 98/99 Or 99/00 Or 00/01 Or 01/02"
meaning that Access looks for the entire string, rather than each separate string separated by Or clauses.
Can anyone help?

 

[JoanneM]

hello

Are you concatenating a string to get your criteria?

Perhaps you would copy getfinyears fx so I can see how you are developing your string

regards

Jo

 

[braindead2]

If I understand correctly you are using a function to build the string. Just Modify the function so it builds the string correctly like this:
"97/98" & Or & "98/99" & Or& "99/00" & Or & "00/01" & Or & "01/02"

 

[RFletch]

Yes, it is a string concatenation. Here is the function (actually called CalcFinYears()). Note that getynum calls a function which returns the relevant number to use in the Year array.

Public Function CalcFinYears()
Dim yfrom As String
Dim yto As String
Dim Year
Dim endstring
Dim i, startnum, endnum, temp As Integer
yfrom = [Forms]![Grant Discounts]![From fin]
yto = [Forms]![Grant Discounts]![To fin]
Year = Array("97/98", "98/99", "99/00", "00/01", "01/02", "02/03", "03/04&quot
endstring = ""
startnum = getynum(yfrom)
endnum = getynum(yto)
If startnum > endnum Then
temp = endnum
endnum = startnum
startnum = temp
End If
i = startnum
For i = startnum To endnum
If i <> endnum Then
endstring = endstring & Year(i) & &quot; Or &quot;
Else
endstring = endstring & Year(i)
End If
Next i
CalcFinYears = endstring
'Debug.Print CalcFinYears
End Function