Problem with Date PARAMETER 2

[villsthearcher]

Hi,

I am passing date as a Parameter to the query. I am using a date range by using the function BETWEEN.

Note: I have used actual values in the Square brackets just for understanding purposes.

The problem I was having with the query was that, if I run a query with the parameter value: BETWEEN [1995] and [1997], it pulls out all records in the years 1995 and 1996, but not 1997. So what I did was I used: BETWEEN [1995] and [1997] + 1. This worked perfectly fine, when I was directly working on the Query.

When I used a Form to pass the date range parameters to the same Query, it still pulls out records between 1995 and 1996, but not 1997. I wondering why this happens although the underlying Query works perfectly fine!



Thanks,
villsthearcher

 

[Tigerlili3]

How are you passing it from the form? Is it code, a combo box, or some other method?

 

[hneal98]

Not sure why it happens, I seem to remember a problem with an earlier version of Access where the BETWEEN did not include the last value. One way to overcome that would be to write your formula like this:

>=[1995] and <=[1997]

 

[lesw1433]

I think you are correct hneal98

1995 equates to 01/01/1995
1997 equates to 01/01/1997

therefore excludes records after 01/01/97 using the Between

I could be wrong, but I think it has to do with the format of the field being in date/time format. If the field was just a number field with 1997 then it would include all the records.

Someone correct me on this.

Thanks,

 

[villsthearcher]

Hi Tigerlili3, hneal98, lesw1433,

Thanks a lot guys !

Tigerlili3.. I use a text box in the FORM.

Lesw..I guess you are correct. I have it as a text field and thats the problem. I have to work with whatever data format, I have been given. Thats frustating but, adding "1" to it solves the problem. Like you said, if it were a number data type then it would have pulled all the records.

Thanks,
villsthearcher

 

[hneal98]

another way to write out the code I suggested above is like this:

>=cint([1995]) and <=cint9[1997])

And then on the query grid where it says "Field:", you would do the same thing with the field name, something like this:

FieldName:cint([FieldName])

That way everything is converted to Integer just for the sake of the query.