5 Replies - 5690 Views - Last Post: 26 July 2013 - 04:34 AM

#1 pharaon  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 79
  • Joined: 18-October 07

Missing ), ], or Item in query expression

Posted 05 July 2013 - 08:45 AM

I want to filter data in table between two dates accorded to months and years only
this the select statement

"select * from [names] where currentconfess between ((month(@startdate) and (year(@startdate)) AND ((month(@enddate) and (year(@enddate))"



and I get this error

Missing ), ], or Item in query expression 'currentconfess between ((month(@startdate) and (year(@startdate)) AND ((month(@enddate) and (year(@enddate))'.

Is This A Good Question/Topic? 0
  • +

Replies To: Missing ), ], or Item in query expression

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9059
  • View blog
  • Posts: 34,014
  • Joined: 12-June 08

Re: Missing ), ], or Item in query expression

Posted 05 July 2013 - 08:50 AM

Typically 'between' takes full date/times... I do not believe those are full date/times.
Was This Post Helpful? 0
  • +
  • -

#3 pharaon  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 79
  • Joined: 18-October 07

Re: Missing ), ], or Item in query expression

Posted 05 July 2013 - 08:59 AM

I'm using access database accdb the column I'm filtering is date type
what I want to exactly is filtering the data lets say between 4/2013 to 5/2013
I want to do it by months and years to avoid days defining.. which mean it will filter from 1/4/2013 till 31/5/2013...so how to do that in the select statement
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3331
  • View blog
  • Posts: 11,275
  • Joined: 12-December 12

Re: Missing ), ], or Item in query expression

Posted 05 July 2013 - 09:32 AM

I think it is easier to create Dates that represent the first of the start-date, and the last of the month for the end-date, and use these in the sql statement:

Assuming you have variables startdate and enddate:

startdate = DateAdd("m",DateSerial(Year(startdate), Month(startdate), 1)
enddate = DateAdd("m",DateSerial(Year(enddate), Month(enddate), 1) - 1

"select * from [names] where currentconfess between @startdate and @enddate"

The enddate is constructed by moving to the first of its month, then adding 1 month (which is the first of the following month) then coming back a day.

If you want to keep the original Date variables then you can create two new variables (startdate1 and enddate_last, perhaps).

This post has been edited by andrewsw: 05 July 2013 - 09:35 AM

Was This Post Helpful? 0
  • +
  • -

#5 torind_2000  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 57
  • View blog
  • Posts: 288
  • Joined: 22-August 11

Re: Missing ), ], or Item in query expression

Posted 25 July 2013 - 10:16 AM

View Postpharaon, on 05 July 2013 - 11:45 AM, said:

Missing ), ], or Item in query expression 'currentconfess between ((month(@startdate) and (year(@startdate)) AND ((month(@enddate) and (year(@enddate))'.


I think you are short a couple closing parens
Was This Post Helpful? 0
  • +
  • -

#6 depricated  Icon User is online

  • DLN-000

Reputation: 655
  • View blog
  • Posts: 2,258
  • Joined: 13-September 08

Re: Missing ), ], or Item in query expression

Posted 26 July 2013 - 04:34 AM

break it out!

'currentconfess between 
(
   (month
      (@startdate) 
   and 
   (year
      (@startdate)
   ) 
   AND 
   (
      (month
         (@enddate)
         and
         (year
            (@enddate)
         )


try
'currentconfess between (month(@startdate) and year(@startdate)) AND (month(@enddate) and year(@enddate))

looks like you had extra OPENING parens in a couple spots

This post has been edited by depricated: 26 July 2013 - 04:37 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1