How to select beyond the date range?

[janue]

hi, how do I select my data where the date is beyond the date range specified? For instance, I wan to select all the rows except the rows that are between 3/22/2005 to 10/17/2005. Is it possible to select the query? I tried the condition 'where date < #3/22/2005# and date > #10/17/2005#', but it return no rows at all. Does anyone knows how to do smth lidat??

 

[Rabbie]

Date is a reserved word in Access and is a function that returns the current date. I would recommend changinging that Name to something else. That should let you select the desired records

 

[janue]

that columnname for my date field is logtime, but thx for telling me that, i appreciated your reply

 

[MStef]

In qry criteria put: <#3/22/2005# OR >#10/17/2005#

 

[janue]

thank you, but issit possible to select all the rows out of 2 date range?

 

[Rabbie]

MStef's solution should give you all records where Logtime is not between 3/22/2005 and 10/17/2005. This is what I think you said you want.

 

[janue]

yea but im currently developing a software that can select the records when logtime is not between 3/22/2005 and 10/17/2005, but also between 1/1/2006 and 2/25/2006.

I know this is troublesome, but I tried 'logtime<#3/22/2005# or logtime>#10/17/2005# or logtime< #1/1/2006# or #2/25/2006#', but it doesnt leave out the dates between 1/1/2006 and 2/25/2006. Do you guys know how to solve it? My project is now handling multi-range of time already =\

 

[Rabbie]

I know this is troublesome, but I tried 'logtime<#3/22/2005# or logtime>#10/17/2005# or logtime< #1/1/2006# or #2/25/2006#', but it doesnt leave out the dates between 1/1/2006 and 2/25/2006. Do you guys know how to solve it? My project is now handling multi-range of time already =\

Problem is that all records that match any one of you criteria will be selected. So any date in 2006 will be included because it will be greater than 10/17/2005. So thats why it isnt working for you. Try this

'where (logtime Not between #3/22/2005# and #10/17/2005#) or (logtime not between #1/1/2006# and #2/25/2006#').

Hope this helps

Rob

 

[janue]

thx rabbie, but the working solution is 'where (logtime Not between #3/22/2005# and #10/17/2005#) AND (logtime not between #1/1/2006# and #2/25/2006#')'. I tried your solution but it doesnt exclude the records in the 2nd range, so i tried my luk by replacing the OR keyword by AND inbetween the 2 ranges

 

[Rabbie]

Glad to here you got it working. Having seen the the original And changed correctly to an OR I missed that it needed changing back. Sorry. Still it was good for you to spot that and get it working.

 

[janue]

thx rabbite, but i got a new problem again =\

Please have a look at the query below:

SELECT *

FROM(
SELECT username, DateValue(logtime) AS Date_Clocked
FROM NGAC_SYSTEMLOG
WHERE (logtime BETWEEN #9/1/2005# AND #9/30/2005#)
GROUP BY username, DateValue(logtime))

WHERE (Date_Clocked < #9/22/2005# OR Date_Clocked > #9/23/2005#)
;

In this query, I try to select a query from the subquery and I try to select the Date_Clocked column which I rename that column in the subquery, but it complains an error saying smth like they cannot find Date_Clocked and also mentioned that the table must be alrdy exist. Date_Clocked is there but why can't they notice it?? so I tried this way:

SELECT username, DateValue(logtime) AS Date_Clocked,
FROM
[SELECT username, logtime FROM NGAC_SYSTEMLOG WHERE logtime BETWEEN #9/1/2005# AND #9/30/2005#]
WHERE (logtime<#9/23/2005# Or logtime>#9/25/2005#)
GROUP BY username, DateValue(logtime);

First I subquery all records in September 2005, and den I query from it to leave out all records between 9/23/2005 and 9/25/2005, but it still complain the same error! FYI, logtime is an existing feild in an existing table, why does it still complain about it??? =[ I hope someone help me out i would greatly appreciate it

 

[Rabbie]

Have you tried

SELECT * 

FROM(
SELECT username, DateValue(logtime) AS Date_Clocked
FROM NGAC_SYSTEMLOG
WHERE (logtime BETWEEN #9/1/2005# AND #9/30/2005#)
GROUP BY username, DateValue(logtime))

WHERE (DateValue(logtime)< #9/22/2005# OR DateValue(logtime)> #9/23/2005#);

Let me know how you get on.

 

[janue]

I had tried it be4, cannot =[

I use the [] brackets to indicate subqueries

 

[Rabbie]

I had tried it be4, cannot =[

I use the [] brackets to indicate subqueries

See this link for info about sub-queries. You sould enclose the subquery in () parentheses not [] as far as I can see.

 

[janue]

i tried both () and [] brackets, but the same error still occur, sad

 

[janue]

omg thanks for that link Rabbie!!! it really helps!

SELECT username, DateValue(logtime) AS Date_Clocked
FROM NGAC_SYSTEMLOG
WHERE logtime IN
(SELECT logtime FROM NGAC_SYSTEMLOG WHERE logtime BETWEEN #9/1/2005# AND #9/30/2005#)
AND (logtime < #9/23/2005# OR logtime > #9/25/2005#)
GROUP BY username, DateValue(logtime);

It is also my first time encountering the EXIST keyword. LOL

 

[MStef]

Hello janue!

I see you have a big problem, try this:

<#3/22/2005# Or >#10/17/2005# AND <#1/1/2006# Or >#2/25/2006#

Ok I see you solved the problem.

 

[janue]

Thanks MStef! But I tried that method b4, I cannot work. But thx for making an effort to generate a solution for me though!

 

[Jon K]

IN (a subquery) is inefficient. If the table is large, you will be better off going back to the earlier two Between...And... approach.
.