Date Error in linked tables query

[Guest]

I am using access to link to tables within the database (SQL) of our company
software package, when writing a query I am unsuccessful when using a date
range as criteria. I am using "Between #14/11/2005# And #20/11/2005#"
-(Australian date standard) and receive data outside this date range.
If I import the table and run the query using the same date criteria, the
date range of the data returned is correct, but if I link tables only the
date range is incorrect, why would this be so?

 

[Allen Browne]

Literal dates in SQL statements need to be in US format.

See:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

 

[Guest]

Allen, I checked the query criteria in SQL view and Access had corrected the
date format to US anyway but the data returned was still incorrect.
--
Regards,
Keith in Australia

Literal dates in SQL statements need to be in US format.

See:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

 

[Allen Browne]

Okay.

1. What is the data type of the field in the table. Is it a Date/Time field?

2. Are you getting too many records, or too few?

If only the records on the last date that are missing, it could be due to a
time component in the field (even if not displayed due to formatting.)

Is it related to the day of the month (e.g. records before 12th are wrong,
becuase the value is being interpreted as a month instead of a day)?

Presumably these are literal values in the Criteria row (as per your
example), and not parameters or text boxes on a form.

 

[Guest]

1. Field is Date/Time
2. Getting too many records
I am getting records back with dates such as 08/07/2005, 31/07/2005,
20/11/2005, 09/10/2005
These are literal values in the criteria row, simply written with query in
design view.

 

[Allen Browne]

Sorry, Keith. That makes no sense to me either.

Post the full SQL statement if you like, but the thing that bothers me is
that you say you get the right results if you use an Access table.

When you view the query results, is the field displayed left-aligned (like
text) or right-aligned (like a number or date)?

 

[Guest]

SELECT dbo_PayCostDetail.ClientNo, dbo_Client.CompanyName,
dbo_PayCostDetail.OfficeCode, dbo_PayCostDetail.PeriodEndDate,
dbo_PayCostDetail.ChargeAmount
FROM dbo_Client INNER JOIN dbo_PayCostDetail ON dbo_Client.ClientNo =
dbo_PayCostDetail.ClientNo
WHERE (((dbo_PayCostDetail.OfficeCode)=200) AND
((dbo_PayCostDetail.PeriodEndDate) Between #11/14/2005# And #11/20/2005#));

TempByWeek PeriodEndDate
31/07/2005
22/05/2005
08/05/2005
20/11/2005
09/10/2005
06/03/2005
13/11/2005
21/11/2004
23/01/2005
15/05/2005
31/07/2005
26/06/2005
21/08/2005
21/08/2005
31/07/2005
20/11/2005
07/08/2005
20/11/2005
30/09/2005
07/08/2005
20/11/2005
20/11/2005
20/11/2005
20/11/2005

Allen,

SQL Statement is at top with resulting records below, records are right
aligned, results shown are the only date field within the query.

 

[Allen Browne]

Keith, I'm really at a loss to understand how Access could get that simple
query statement wrong.

If there is an index on the PeriodEndDate field, it is possible the index is
gone bad, but very unlikely it could be *that* bad.

Not sure what the "TempByWeek" is.

Possible workarounds (grasping at straws):
1. You say this is SQL Server back end?
Could you try it as a Pass Through query?

2. Alternatively, create a View in SQL Server, and link the view?

3. Force JET to typecast the field and see it it then interprets it
correctly, i.e.:
.... WHERE ((dbo_PayCostDetail.OfficeCode = 200)
AND (CDate(dbo_PayCostDetail.PeriodEndDate)
Between #11/14/2005# And #11/20/2005#));

4. It might be informative to type this expression into the Field row in the
query:
InRange: (dbo_PayCostDetail.PeriodEndDate Between #11/14/2005# And
#11/20/2005#)
Does this return -1 (true) on the rows that are out of range?

Perhaps someone else has another suggestion that could help.

 

[Allen Browne]

Another thought: Make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General
Then compact the database to get rid of this stuff.

I have seen cases where Access applies the criteria on the wrong field when
there has been a name change. I doubt this applies to your situation, but
worth doing anyway. More info on the Name AutoCorrupt problems:
http://allenbrowne.com/bug-03.html

 

[Chris2]

"Keith in Australia" <[email protected]>
wrote in message

SELECT dbo_PayCostDetail.ClientNo, dbo_Client.CompanyName,
dbo_PayCostDetail.OfficeCode, dbo_PayCostDetail.PeriodEndDate,
dbo_PayCostDetail.ChargeAmount
FROM dbo_Client INNER JOIN dbo_PayCostDetail ON dbo_Client.ClientNo =
dbo_PayCostDetail.ClientNo
WHERE (((dbo_PayCostDetail.OfficeCode)=200) AND
((dbo_PayCostDetail.PeriodEndDate) Between #11/14/2005# And

#11/20/2005#));


Keith,

SQL Server's takes string date literals for DATETIME values as
yyyy-mm-dd (oh, it will do its best to "intepret", but it's best to
avoid that).

When using MS Access via linked tables:

This line:

((dbo_PayCostDetail.PeriodEndDate) Between #11/14/2005# And

#11/20/2005#));

Will not retrive the same data as this line:

((dbo_PayCostDetail.PeriodEndDate) Between #2005/11/14# And
#2005/11/20#));


Sincerely,

Chris O.