473,224 Members | 1,345 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

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

Jul 19 '05 #1
4 1283
On Mon, 5 Jan 2004 17:01:54 -0000, "Alistair"
<news@*remove*alistairb.co.uk> wrote:

"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:%2****************@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
Jul 19 '05 #2
> 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/
Jul 19 '05 #3
On Mon, 5 Jan 2004 14:25:03 -0500, "Aaron Bertrand - MVP"
<aa***@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
Jul 19 '05 #4
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***@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 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

Jul 19 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: theboss3 | last post by:
I am having trouble with accessing files with query strings. For example the code "require 'template.php?102932'" gives the following error: Warning: main(/www/blog/template.php?102932):...
23
by: Peter Row | last post by:
Hi, I am currently working on a VB.NET project that has been going for quite a while. In the past it has been developed with a lot of compatibility VB6 functions and constants, e.g Left(),...
6
by: Darrel | last post by:
*sigh*...I have gotten no where with my .net 2.0 project. ;o) I'm still stuck on db connection strings. I have a connection string in my web config: <appSettings> <add key="DBConn" value=""...
1
by: colleen1980 | last post by:
When I run my query it stuck is it possible that i can debug it SELECT TOP 1 tblCredits.OPR, Min(tbl_CRCDollars.SumOfAMOUNT) AS MinOfSumOfAMOUNT, Avg(tblCredits.Credit) AS AvgOfCredit,...
3
by: Nathan Guill | last post by:
I have an interface that works with an Access back-end. I would like to store and/or load user defined query strings per each user (i.e. no user can access another's queries). The idea I had was...
17
by: NeoAlchemy | last post by:
I am starting to find more web pages that are using a query parameters after the JavaScript file. Example can be found at www.opensourcefood.com. Within the source you'll see: <script...
5
by: Just_a_fan | last post by:
I tried to put an "on error" statement in a routine and got the message that I cannot user "on error" and a lamda or query expression in the same routine. Help does not list anything useful for...
13
by: canabatz | last post by:
i got for example this result from data base: user1 user1 user1 user2 user2 user1 user1
7
alpnz
by: alpnz | last post by:
I am trying to access data on a mySQL server, but due to my age and alzheimer's I seem to be overlooking the obvious mistake here. Anyone care to spare a couple of minutes to help. <? $host =...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.