Setting criteria from VB

[RFletch]

Hi,

I submitted this message yesterday and had a couple of replies, but none have been any use, so here goes again!

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?

 

[Maquis]

Hmmm, well I don't know what answers you received in your first posting, so this may or may not help.

Here's how I would do it. Assuming you know in which month your financial year starts you can place it in your function. (If this could be different for different parts of your database, then just store it in a table somewhere and read it as necessary.)

You can pass the begin date string and the end date string into the function and have the function return actual begin and end dates.

For example: Let's say your financial year starts in April. Your user enters 97/98 for the begin date and 01/02 for the end date. Pass these strings to your function and the function will return 04/01/97 and 03/31/02 back to you. You can then use those dates in a query. Maq
<insert witty signature here>

 

[RFletch]

Maquis,

Thanks for your reply. That won't actually work, because the actual values stored in the table are financial years, so I can't use specific dates.

 

[SgtJarrow]

Sounds to me like the answer would be in getting your Function getfinyears to give you the correct string value. How about giving us the code to view and see if we can't find the problem??? There is no I in team.

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[RFletch]

Sure,

Here it is. Note that the function (actually called calcfinyears) calls another function getynum, which I have also included. The getynum function returns a number which is relevant to the Year array. I hope this is apparent on inspection of the code.

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(&quot;97/98&quot;, &quot;98/99&quot;, &quot;99/00&quot;, &quot;00/01&quot;, &quot;01/02&quot;, &quot;02/03&quot;, &quot;03/04&quot
endstring = &quot;&quot;
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

Private Function getynum(y As String) As Integer
If y = &quot;97/98&quot; Then
getynum = 0
Else
If y = &quot;98/99&quot; Then
getynum = 1
Else
If y = &quot;99/00&quot; Then
getynum = 2
Else
If y = &quot;00/01&quot; Then
getynum = 3
Else
If y = &quot;01/02&quot; Then
getynum = 4
Else
If y = &quot;02/03&quot; Then
getynum = 5
Else
If y = &quot;03/04&quot; Then
getynum = 6
End If
End If
End If
End If
End If
End If
End If
End Function

 

[Maquis]

Fletch, add a variable to your function called dQuote.

Dim dquote as string
dquote = chr$(34) 'this is the double quote character

Next change your endstring definition from

If i <> endnum Then
   endstring = endstring & Year(i) & &quot; Or &quot;
Else
   endstring = endstring & Year(i)
End If

to

endstring = endstring & dquote & Year(i) & dquote
If i <> endnum Then
   endstring = endstring & &quot; Or &quot;
End If

Maq
<insert witty signature here>

 

[SgtJarrow]

RFletch,

The below code change should get the the value you need. I only included the section that needed to be changed. With the change, I had a consistent return of

&quot;97/98&quot; Or &quot;98/99&quot; Or &quot;99/00&quot; Or &quot;00/01&quot; Or &quot;01/02&quot; Or &quot;02/03&quot;

for CalcFinYears. I think this is the string value you need to make it all work. Good luck and please let me know if it worked! %-)



For i = startnum To endnum
If i <> endnum Then
endstring = endstring & &quot;&quot;&quot;&quot; & Year(i) & &quot;&quot;&quot;&quot; & &quot; Or &quot;
Else
endstring = endstring & &quot;&quot;&quot;&quot; & Year(i) & &quot;&quot;&quot;&quot;
End If
Next i
There is no I in team.

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[RFletch]

Thanks for your help there guys.
I used the &quot;&quot;&quot;&quot; idea from mstrmage1768 because it seemed to be slightly quicker to implement.
Although I do now consistently get
&quot;97/98&quot; Or &quot;98/99&quot; Or &quot;99/00&quot; Or &quot;00/01&quot; Or &quot;01/02&quot; Or &quot;02/03&quot;
returned when I test the function in the immediate window in VB, when I try it in the criteria box in my query, it still produces no records.
In the criteria box I put
CalcFinYears()
which should call the function and get the above. If I cut and paste the results from above into the criteria box instead, I get the required records output, so it's not a problem with the query design itself.
Am I missing something obvious here?
I think it's because the function returns a string, so I think it probably returning
&quot;&quot;97/98&quot; Or &quot;98/99&quot; Or &quot;99/00&quot; Or &quot;00/01&quot; Or &quot;01/02&quot; Or &quot;02/03&quot;&quot;
and therefore looking for the entire phrase.
What do you reckon?

 

[SgtJarrow]

Instead of putting the CalcFinYears() in the query (something I always try to avoid), create a hidden text box on you form where the dates are selected. Use an AfterUpdate property of the enddate box to populate this hidden text box...

Me![DateRange]=CalCFinYears()

This will populate the box with the string. Then in your query criteria, make it

=Forms![FormName]![DateRange]

The query will then grab this field string, and use it...

Should work fine. Let us know. There is no I in team.

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[RFletch]

Thanks Robert,
but no it doesn't!
The value in DateRange passed from CalcFinYears is &quot;&quot;98/99&quot; Or &quot;99/00&quot; Or &quot;00/01&quot; Or &quot;01/02&quot; Or &quot;02/03&quot;&quot;
having checked this in VB debugger.
I need some way of removing the opening and closing double quote marks.

 

[SgtJarrow]

We're getting closer.....using Me![HiddenField] = Mid(CalcFinYears, 2, Len(CalcFinYears) - 2) instead of Me![HiddenField] = CalcFinYear I was able to take off the lead and end quotes. But I can only get it to work if the date range is only one.....i.e. 96/97 and 97/98. If I make the range any lager, it doesn't work...... There is no I in team.

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[RFletch]

Hmm,

I tried that bit of code and it does seem to remove the start and end quote marks, but still I can't get it to work - even with a date range of 1!
I'm going home now for the weekend, so I'll tackle this problem again on Monday.
Have a good weekend!