Between clause = form textboxes?

[Krullms]

Hello,

As of now, to make custom reports from XXXX Year to XXXX Year and a selected option I have VB code that changes the query to match what is entered in the year textboxes, then opens the report. I then remembered that I can set the years directly to the textboxes in the query itself.

The problem I am having is I can get the query to work for one year, and another option. But as soon as I do the Between =[Forms]![myForm]![FromYear] AND =[Forms]![myForm]![ToYear]. It won't find anything. Maybe I need to format it different if I am going to do it in the query? I was just wondering if this is possible to do (if it is, it would save me alot of time from copying and pasting and slightly changing VB code for 1 of many Reports)
Any tips would be appreciated!

Thankyou
Mike

 

[greely]

In access, dates are delimited with hashes (#)

in your sql, try:

BETWEEN #" & [Forms]![myForm]![FromYear] & "# AND #" & [Forms]![myForm]![ToYear] & "#"

You may also have to ensure the dates are properly formatted and/or use cdate([Forms]![myForm]![FromYear])

Hope this helps

 

[Eupher]

Hi, Krullms,

greely's suggestion is on the mark. Here's another option:

In your query, use an expression in one column that pulls the year from the field in question, and set your BETWEEN statement as the criteria. Something like this:

In the "Field" entry for one column:

MyYear: Year([[blue]MyDateField[/blue]])

And in the criteria for the same column:

Between [Forms]![[blue]frmMyForm[/blue]]![[blue]FromYear[/blue]] And [Forms]![[blue]frmMyForm[/blue]]![[blue]ToYear[/blue]]

Replace object names in blue above with YOUR names. Since the Year function in the expression returns an integer, no need to use the hash symbol as the date delimiter in the criteria.

HTH,

Ken S.

 

[Krullms]

Thankyou Eupher and Greely,

This will save me alot of time

Mike