still stuck on query strings

I should just get a hat with a big D on it and then everyone would recognise
me..

anyway...

I have a DB full of people and their details..

I'm trying to perform a query based on age.

so I have a form with two values..upperlimit and lowerlimit ( eg aged
between 25 and 30, but worked out in years so that upper limit gives a birth
year of 1974 and lowerlimit gives a birth year of 1979)

my query string looks like

strSQL = "SELECT * FROM users where ( (right(dob,4)) =>" & lowerlimit & "
AND " & " (right(dob,4)) =<" & upperlimit & ")order by key"

so..dob is the DB field for date of birth, I'm just taking the year from
this hence the right(dob,4) and looking for entries where the year of dob is
more than AND less than the search criteria

the error message I get is

Syntax error (missing operator) in query expression

I must be missing something obvious...

on a lighter note...I'm certainly getting there as I've managed to get most
of the queries working..just not this one....lol

thanks again

Aaron
Mon Jan 05 10:45:18 CST 2004

> strSQL = "SELECT * FROM users where ( (right(dob,4)) =>" & lowerlimit &
"
> AND " & " (right(dob,4)) =<" & upperlimit & ")order by key"

Why do you think right(dob,4) is valid for a date? You realize that dates
aren't stored how they are presented, right? And that you shouldn't be
treating dates as strings? Also, what operations are >= and =< ?

Try

strSQL = "SELECT * FROM users WHERE dob >= #" & lowerlimit & "-01-01# AND
dob < #" & upperlimit+1 & "-01-01# ORDER BY key"

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Curt_C
Mon Jan 05 10:54:04 CST 2004

this
strSQL = "SELECT * FROM users where ( (right(dob,4)) =>" & lowerlimit & "
AND " & " (right(dob,4)) =<" & upperlimit & ")order by key"

should be
strSQL = "SELECT * FROM users where ( (right(dob,4)) => " & lowerlimit &
"
AND (right(dob,4)) =< " & upperlimit & ")order by key"


--
Curt Christianson
Owner/Lead Developer, DF-Software
www.Darkfalz.com


"Alistair" <news@*remove*alistairb.co.uk> wrote in message
news:vvj4n2ns6md1fa@corp.supernews.com...
> I should just get a hat with a big D on it and then everyone would
recognise
> me..
>
> anyway...
>
> I have a DB full of people and their details..
>
> I'm trying to perform a query based on age.
>
> so I have a form with two values..upperlimit and lowerlimit ( eg aged
> between 25 and 30, but worked out in years so that upper limit gives a
birth
> year of 1974 and lowerlimit gives a birth year of 1979)
>
> my query string looks like
>
> strSQL = "SELECT * FROM users where ( (right(dob,4)) =>" & lowerlimit &
"
> AND " & " (right(dob,4)) =<" & upperlimit & ")order by key"
>
> so..dob is the DB field for date of birth, I'm just taking the year from
> this hence the right(dob,4) and looking for entries where the year of dob
is
> more than AND less than the search criteria
>
> the error message I get is
>
> Syntax error (missing operator) in query expression
>
> I must be missing something obvious...
>
> on a lighter note...I'm certainly getting there as I've managed to get
most
> of the queries working..just not this one....lol
>
> thanks again
>
>
>

Aaron
Mon Jan 05 10:55:51 CST 2004

> strSQL = "SELECT * FROM users where ( (right(dob,4)) => " & lowerlimit &
> "
> AND (right(dob,4)) =< " & upperlimit & ")order by key"

What did you change? Also, what are => and =< ? :-)

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Alistair
Mon Jan 05 11:01:54 CST 2004


"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:%23YN3Mu60DHA.1736@TK2MSFTNGP09.phx.gbl...
> > strSQL = "SELECT * FROM users where ( (right(dob,4)) =>" & lowerlimit
&
> "
> > AND " & " (right(dob,4)) =<" & upperlimit & ")order by key"
>
> Why do you think right(dob,4) is valid for a date? You realize that dates
> aren't stored how they are presented, right? And that you shouldn't be
> treating dates as strings? Also, what operations are >= and =< ?
>
> Try
>
> strSQL = "SELECT * FROM users WHERE dob >= #" & lowerlimit & "-01-01# AND
> dob < #" & upperlimit+1 & "-01-01# ORDER BY key"
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>

thanks for that Aaron, I know what you mean about strings and
numbers...guess I got confused as right(dob,4)) gave me 1965 or whatever
and although your string works perfectly, I don't understand how it works.
whats the # and 01-01 for?

is there a page I can look this up?...I hate using something I don't
understand ("stick to gardening" I hear you cry)

thanks again

Curt_C
Mon Jan 05 11:08:37 CST 2004

Took out some extra & and ", but upon review they weren't hurting it really,
just un-necessary.

and I was figuring the => was meant to be >= and just a typo... but who
knows :}


--
Curt Christianson
Owner/Lead Developer, DF-Software
www.Darkfalz.com


"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:u9YGG060DHA.2528@TK2MSFTNGP09.phx.gbl...
> > strSQL = "SELECT * FROM users where ( (right(dob,4)) => " & lowerlimit
&
> > "
> > AND (right(dob,4)) =< " & upperlimit & ")order by key"
>
> What did you change? Also, what are => and =< ? :-)
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>

Aaron
Mon Jan 05 11:11:18 CST 2004

> thanks for that Aaron, I know what you mean about strings and
> numbers...guess I got confused as right(dob,4)) gave me 1965 or whatever
> and although your string works perfectly, I don't understand how it works.
> whats the # and 01-01 for?

It's treating the date column as it's supposed to be treated, as a date.
And if you're lucky, it will use an index on the date column (assuming you
have one) for a more efficient range search. Using the string methodology,
you have to scan the entire table and perform that operation on every single
row. Yuck. This would be just like using LIKE '%1965' which, as you can
see in http://www.aspfaq.com/2312, is not a great idea. The article was
written for SQL Server, but is equally applicable to any database. Dates
are dates; they are not "strings" until they are displayed in HTML.

If you response.write strSQL, you should see exactly what it is doing...
give me all rows where the dob >= 1975-01-1 and dob < 1980-01-01. The # are
how Access expects dates to be delimited (most normal databases use ' -- see
http://www.aspfaq.com/2023).

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


>
> is there a page I can look this up?...I hate using something I don't
> understand ("stick to gardening" I hear you cry)
>
> thanks again
>
>

jcochran
Mon Jan 05 13:20:20 CST 2004

On Mon, 5 Jan 2004 17:01:54 -0000, "Alistair"
<news@*remove*alistairb.co.uk> wrote:

>
>"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
>news:%23YN3Mu60DHA.1736@TK2MSFTNGP09.phx.gbl...
>> > strSQL = "SELECT * FROM users where ( (right(dob,4)) =>" & lowerlimit
>&
>> "
>> > AND " & " (right(dob,4)) =<" & upperlimit & ")order by key"
>>
>> Why do you think right(dob,4) is valid for a date? You realize that dates
>> aren't stored how they are presented, right? And that you shouldn't be
>> treating dates as strings? Also, what operations are >= and =< ?
>>
>> Try
>>
>> strSQL = "SELECT * FROM users WHERE dob >= #" & lowerlimit & "-01-01# AND
>> dob < #" & upperlimit+1 & "-01-01# ORDER BY key"
>>
>> --
>> Aaron Bertrand
>> SQL Server MVP
>> http://www.aspfaq.com/
>>
>>
>
>thanks for that Aaron, I know what you mean about strings and
>numbers...guess I got confused as right(dob,4)) gave me 1965 or whatever
>and although your string works perfectly, I don't understand how it works.
>whats the # and 01-01 for?

# is the Microsoft Access delimiter for dates. The -01-01 is month
01, day 01 for whatever year. As in YYYY-MM-DD notation, which is how
date formats are handled. (In the US at least).

>is there a page I can look this up?...I hate using something I don't
>understand ("stick to gardening" I hear you cry)

Gardening? My gardening skill amounts to "Plant it. If it dies,
plant something else."

FWIW, I was converting dates to strings for a long time to handle
them, though doing so outside the query, using DatePart and so on.
SQL Books Online has a bunch of date stuff, as does MSDN, but in
Access you have a really pretty decent help system built in. Go to
Help, and search for "Dates in Queries" or the like and you'll get a
decent reference.

Plus, you can build the query in Access, then look at the queyr in SQL
view and that's pretty much the code you need in your strSQL, with the
exception of single vs. double quotes.

Jeff

Aaron
Mon Jan 05 13:25:03 CST 2004

> 01, day 01 for whatever year. As in YYYY-MM-DD notation, which is how
> date formats are handled. (In the US at least).

Actually, it's one of the ISO date formatting standards. Pity that the only
reliable standard in Access (YYYY-MM-DD) is not the same as the only
reliable format in SQL Server (YYYYMMDD).

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

jcochran
Mon Jan 05 16:09:51 CST 2004

On Mon, 5 Jan 2004 14:25:03 -0500, "Aaron Bertrand - MVP"
<aaron@TRASHaspfaq.com> wrote:

>> 01, day 01 for whatever year. As in YYYY-MM-DD notation, which is how
>> date formats are handled. (In the US at least).
>
>Actually, it's one of the ISO date formatting standards. Pity that the only
>reliable standard in Access (YYYY-MM-DD) is not the same as the only
>reliable format in SQL Server (YYYYMMDD).

Especially since I switch between SQL and Access, and get bit every
single time. :)

Jeff

Aaron
Mon Jan 05 16:08:49 CST 2004

One solution: stop using Crapcess. :-)

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/




"Jeff Cochran" <jcochran.nospam@naplesgov.com> wrote in message
news:3ff9e085.292992440@msnews.microsoft.com...
> On Mon, 5 Jan 2004 14:25:03 -0500, "Aaron Bertrand - MVP"
> <aaron@TRASHaspfaq.com> wrote:
>
> >> 01, day 01 for whatever year. As in YYYY-MM-DD notation, which is how
> >> date formats are handled. (In the US at least).
> >
> >Actually, it's one of the ISO date formatting standards. Pity that the
only
> >reliable standard in Access (YYYY-MM-DD) is not the same as the only
> >reliable format in SQL Server (YYYYMMDD).
>
> Especially since I switch between SQL and Access, and get bit every
> single time. :)
>
> Jeff