still stuck on query strings

[Alistair]

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..upperli mit 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

[Jeff Cochran]

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

"Aaron Bertrand - MVP" <aa***@TRASHasp faq.com> wrote in message
news:%2******* *********@TK2MS FTNGP09.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...gues s 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 Bertrand - MVP]

> 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/

[Jeff Cochran]

On Mon, 5 Jan 2004 14:25:03 -0500, "Aaron Bertrand - MVP"
<aa***@TRASHasp faq.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 Bertrand - MVP]

One solution: stop using Crapcess. :-)

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


"Jeff Cochran" <jc************ *@naplesgov.com > wrote in message
news:3f******** ********@msnews .microsoft.com. ..

On Mon, 5 Jan 2004 14:25:03 -0500, "Aaron Bertrand - MVP"
<aa***@TRASHasp faq.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 onlyreliable 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