Keith Williamson
Kwilliamson at RTKL.com
Wed Jan 10 11:14:58 CST 2007
Here is my query: SELECT DISTINCTROW dbo_CFGPostControl.Company, dbo_CFGPostControl.TransType, dbo_CFGPostControl.Period, dbo_CFGPostControl.Completed, dbo_CFGPostControl.PostSeq, dbo_CFGPostControl.EndDate, tblPayPeriods.End FROM tblPayPeriods INNER JOIN dbo_CFGPostControl ON (dbo_CFGPostControl.EndDate between tblPayPeriods.End and tblPayPeriods.Begin) WHERE (((dbo_CFGPostControl.TransType)="TS")) Order by dbo_CFGPostControl.EndDate; It runs....ONCE. If I try to sort on a field, I get an error: ---------------------------------- Between Operator without And in query expression 'dbo_CFGPostcontrol.EndDate betweentblPayPeriods.End'. ---------------------------------- Huh?? There is an "And" in there. ?? If I try to view in Design View, I get the error: ---------------------------------- Microsoft Access can't represent the join expression dbo_CFGPostControl.EndDate between tblPayPeriods.End and tblPayPeriods.Begin in Design view. *One of more fields may have been deleted or renamed. *The name of one or more fields or tables specified in the join expression may be misspelled. *The join may use an operator that isn't supported in Design view, such as > or<. -------------------------------- Grrrrrr!!!!!! Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland 21231-3305 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka Sent: Wednesday, January 10, 2007 11:24 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Date Compare No, should be able to see it in design view too, it just won't have a 'join' line. Drew -----Original Message----- From: Keith Williamson [mailto:Kwilliamson at rtkl.com] Sent: Wednesday, January 10, 2007 10:15 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Date Compare Oops! Nevermind. :) I figured out how to get the between operator to work in the "Select" statement. It appears, though, that once you use this....you can no longer view the query in Design View...only in SQL view???? Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland 21231-3305 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith Williamson Sent: Wednesday, January 10, 2007 10:58 AM To: Access Developers discussion and problem solving Subject: [AccessD] Date Compare Hi all, Anyone have a solution for how to link two tables with different dates? I have a Payroll Paid table, with field: DatePaid I also have a Timesheet table, with field: TimesheetEndDate The DatePaid field is a bi-weekly date. The TimesheetEndDate field is usually a weekly date, but occasionally there is a supplemental timesheet date. What I am trying to do is link the Timesheet table for all dates that fall within the 13 days prior to, and including, the DatePaid. So, for DatePaid of 11/29/06.....I want to link all Timesheets with a TimesheetEndDate falling 13 days prior to, and including 11/29/06. Anyone have a solution for that? Thanks kindly, for any help. Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland 21231-3305 410-537-6098 direct | 410-276-2136 fax | www.rtkl.com <http://www.rtkl.com/> -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com