Funny Between AND Query

  • Thread starter Thread starter Torie
  • Start date Start date
T

Torie

Hi All,

I have query that searches through a range of numbers. I
am using the simple criteria "Between [#] AND [#}".
However, I am getting funny results.... For example, when
I do "Between 1 AND 4" - I am getting back numbers
like "10", "11", "12, "20", "21", "30", "40" - all these
numbers start with 1, 2, 3 or 4. Does anyone know what I
am doing wrong? I just want the numbers between 1-4 (not
the ones starting with those numbers).

Thanks
 
Hi All,

I have query that searches through a range of numbers. I
am using the simple criteria "Between [#] AND [#}".
However, I am getting funny results.... For example, when
I do "Between 1 AND 4" - I am getting back numbers
like "10", "11", "12, "20", "21", "30", "40" - all these
numbers start with 1, 2, 3 or 4. Does anyone know what I
am doing wrong? I just want the numbers between 1-4 (not
the ones starting with those numbers).

Thanks
Click to expand...

Sounds like the column you're selecting is text, not numeric. In an
alpha sort, "20" *is* between "1" and "4". Try converting your
selected column to a numeric format using a function like "CInt".

Peter
 
-----Original Message-----
Hi All,

I have query that searches through a range of numbers. I
am using the simple criteria "Between [#] AND [#}".
However, I am getting funny results.... For example, when
I do "Between 1 AND 4" - I am getting back numbers
like "10", "11", "12, "20", "21", "30", "40" - all these
numbers start with 1, 2, 3 or 4. Does anyone know what I
am doing wrong? I just want the numbers between 1-4 (not
the ones starting with those numbers).

Thanks
Click to expand...

Sounds like the column you're selecting is text, not numeric. In an
alpha sort, "20" *is* between "1" and "4". Try converting your
selected column to a numeric format using a function like "CInt".

Peter
.
Click to expand...

Actually my field is a date/time field. I am using the day
function to search between days. For example I want to be
able to search between Dec 1-4.
 
Actually my field is a date/time field. I am using the day
function to search between days. For example I want to be
able to search between Dec 1-4.
Click to expand...

A Date/Time field is stored as a Double Float number, a count of days
and fractions of a day since midnight, December 30, 1899. The format
is irrelevant.

If you want to search between two specific dates, you need to search
between *two specific dates*. 4 is not a date! 12/4 is a date (Access
will assume that you mean the current year, so this will actually be
12/04/2003; if you run the query next January it'll be 12/04/2004).
 
Back
Top