[AccessD] Date Compare

Keith Williamson Kwilliamson at RTKL.com
Wed Jan 10 13:04:03 CST 2007


Thanks, Drew.  I'll give this a try.

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 12:31 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Date Compare

Oh, you are putting it in the FROM clause...ya, Access won't let you
edit
that in design view.  Try this:

SELECT Company, TransType, Period, Completed, PostSeq, EndDate, End
FROM tblPayPeriods AS T1, dbo_CFGPostControl
WHERE TransType="TS" AND ENDDate Between End AND Begin
ORDER BY EndDate

Drew

-----Original Message-----
From: Keith Williamson [mailto:Kwilliamson at rtkl.com] 
Sent: Wednesday, January 10, 2007 11:15 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Date Compare

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

-- 
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




More information about the AccessD mailing list