OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Query Date Between 2 dates

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
heymish
General User
General User


Joined: 08 Mar 2007
Posts: 5
Location: Christchurch, New Zealand

PostPosted: Wed Apr 25, 2007 7:52 pm    Post subject: Query Date Between 2 dates Reply with quote

I have 2 tables one with the StartDate and EndDate and another table with a Date. I want to find if the date is between one of the StartDate,EndDate pairs. I have tried 2 different querys which both give me a table not found error.

I have this query

Code:
SELECT "Tax"."StartDate", "Tax"."EndDate", "Query_Paid_Total"."Date" FROM "Query_Paid_Total", "Tax" WHERE (("Query_Paid_Total"."Date" > "Tax"."StartDate") AND ("Query_Paid_Total"."Date" < "Tax"."EndDate"))


For which I get a table not found error

I have also tried

Code:
SELECT "Tax"."StartDate", "Tax"."EndDate", "Query_Paid_Total"."Date" FROM "Query_Paid_Total", "Tax" WHERE (("Query_Paid_Total"."Date" BETWEEN "Tax"."StartDate" AND "Tax"."EndDate"))


for which I also get a talbe not found error.

If I replace Start Date and EndDate with dates rather than table references it works

Code:
SELECT "Tax"."StartDate", "Tax"."EndDate", "Query_Paid_Total"."Date" FROM "Query_Paid_Total", "Tax" WHERE (("Query_Paid_Total"."Date" BETWEEN '2006-01-01'AND '2007-01-01'))


I am not sure if this is also going to be a problem but the dates could fall within more than one date pair.
Anyway to get around this would be nice thanks

Would there be away using datediff

Code:
Datediff(d,'Startdate','EndDate') > Datediff(d,'Startdate','Date')


I just do not see how to add this into the query
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Thu Apr 26, 2007 5:44 am    Post subject: Reply with quote

Any chance you could show me what Query_Paid_Total query looks like?
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
heymish
General User
General User


Joined: 08 Mar 2007
Posts: 5
Location: Christchurch, New Zealand

PostPosted: Thu Apr 26, 2007 11:22 am    Post subject: Reply with quote

Here it is
Code:

SELECT "Query_Paid"."InvoiceNumber", "Query_Invoice_Totals"."Amount", "Query_Paid"."Date" FROM "Query_Invoice_Totals", "Query_Paid" WHERE ( "Query_Invoice_Totals"."InvoiceNumber" = "Query_Paid"."InvoiceNumber" )


Is there anything else you would like to see

Does the Query need a nested WHERE to pick out hte Startdate and end date pairs


Last edited by heymish on Thu Apr 26, 2007 11:31 am; edited 1 time in total
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Thu Apr 26, 2007 11:29 am    Post subject: Reply with quote

no that should be enough. As I recall there was a problem with BETWEEN and dates in the past but I thought that cleared up. Let me try a couple of things here and I'll put something up later this evening.
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group