Another Date Error in linked tables query

[pguizze]

Hi,

I have a strange behavior with my Access 2003 database in date
calculation in a query.
My query is supposed to give me all the invoices between date X and
date Y from the INVOICE table.
The query is the following:

SELECT INVOICE.ID, INVOICE.DATE
FROM INVOICE
WHERE (((INVOICE.DATE)>=[startdate] And (INVOICE.DATE)<=[enddate]));

Access executes the query and asks me the 2 dates (startdate and
enddate) but the results are wrong. I receive less records than
expected and this only for the year 2005 !! With invoices from 2004, i
got them all. For the year 2005, I have less invoices than correct.

Example: startdate= 1st November 2005, enddate = 21st Nov 2005, i got
all the invoices with the date 1st Nov to 18th Nov and nothing for the
21 ! And if put 22nd Nov as the end date, I got all the invoice from
1st Nov to 21st Nov plus a few of 22nd but not all !

This is really strange and corrupt all my requests related to dates.
I've tried on several other PCs (XP) with exactly the same behavior.
What happen to my DB in 2005 ?

PLEASE HELP !

Patrick

 

[Brendan Reynolds]

Looks like your data includes a time component, e.g., your field may contain
the date 21 November 2005 9:30:00, which is greater than 21 November 2005
00:00:00, and therefore will not be included. (This time component may not
be immediately visible, as it may be hidden by formatting).

Try this instead ...

WHERE INVOICE.DATE >= [startdate] And INVOICE.DATE < ([enddate] - 1)

BTW: 'Date' is a reserved word, and can cause problems when used as a field
or control name.

 

[John Spencer]

Dear Brendan,

Should that be +1 instead of -1?

WHERE [INVOICE].[DATE] >= [startdate] And [INVOICE].[DATE] < ([enddate] + 1)


Sincerely,
John

Looks like your data includes a time component, e.g., your field may contain
the date 21 November 2005 9:30:00, which is greater than 21 November 2005
00:00:00, and therefore will not be included. (This time component may not
be immediately visible, as it may be hidden by formatting).

Try this instead ...

WHERE INVOICE.DATE >= [startdate] And INVOICE.DATE < ([enddate] - 1)

BTW: 'Date' is a reserved word, and can cause problems when used as a field
or control name.

--
Brendan Reynolds

Hi,

I have a strange behavior with my Access 2003 database in date
calculation in a query.
My query is supposed to give me all the invoices between date X and
date Y from the INVOICE table.
The query is the following:

SELECT INVOICE.ID, INVOICE.DATE
FROM INVOICE
WHERE (((INVOICE.DATE)>=[startdate] And (INVOICE.DATE)<=[enddate]));

Access executes the query and asks me the 2 dates (startdate and
enddate) but the results are wrong. I receive less records than
expected and this only for the year 2005 !! With invoices from 2004, i
got them all. For the year 2005, I have less invoices than correct.

Example: startdate= 1st November 2005, enddate = 21st Nov 2005, i got
all the invoices with the date 1st Nov to 18th Nov and nothing for the
21 ! And if put 22nd Nov as the end date, I got all the invoice from
1st Nov to 21st Nov plus a few of 22nd but not all !

This is really strange and corrupt all my requests related to dates.
I've tried on several other PCs (XP) with exactly the same behavior.
What happen to my DB in 2005 ?

PLEASE HELP !

Patrick

 

[Pat Hartman\(MVP\)]

When you need to extract only the date from a field that contains date and
time, use the DateValue() function:

WHERE DateValue(INVOICE.[DATE]) Between [startdate] And [enddate];

BTW, using DATE (or any other function or property name for that matter)
will sooner or later cause some problem with your application. Also avoid
special characters and embedded spaces. Search help and the Microsoft kb
for reserved words list. The next version of Access will warn you of these
poor practices but will not prevent their use to be compatible with earlier
versions.

 

[Brendan Reynolds]

Doh!

Thanks, John!

--
Brendan Reynolds

Dear Brendan,

Should that be +1 instead of -1?

WHERE [INVOICE].[DATE] >= [startdate] And [INVOICE].[DATE] < ([enddate] +
1)


Sincerely,
John

Looks like your data includes a time component, e.g., your field may
contain
the date 21 November 2005 9:30:00, which is greater than 21 November 2005
00:00:00, and therefore will not be included. (This time component may
not
be immediately visible, as it may be hidden by formatting).

Try this instead ...

WHERE INVOICE.DATE >= [startdate] And INVOICE.DATE < ([enddate] - 1)

BTW: 'Date' is a reserved word, and can cause problems when used as a
field
or control name.

--
Brendan Reynolds

Hi,

I have a strange behavior with my Access 2003 database in date
calculation in a query.
My query is supposed to give me all the invoices between date X and
date Y from the INVOICE table.
The query is the following:

SELECT INVOICE.ID, INVOICE.DATE
FROM INVOICE
WHERE (((INVOICE.DATE)>=[startdate] And (INVOICE.DATE)<=[enddate]));

Access executes the query and asks me the 2 dates (startdate and
enddate) but the results are wrong. I receive less records than
expected and this only for the year 2005 !! With invoices from 2004, i
got them all. For the year 2005, I have less invoices than correct.

Example: startdate= 1st November 2005, enddate = 21st Nov 2005, i got
all the invoices with the date 1st Nov to 18th Nov and nothing for the
21 ! And if put 22nd Nov as the end date, I got all the invoice from
1st Nov to 21st Nov plus a few of 22nd but not all !

This is really strange and corrupt all my requests related to dates.
I've tried on several other PCs (XP) with exactly the same behavior.
What happen to my DB in 2005 ?

PLEASE HELP !

Patrick

 

[Chris2]

Hi,

I have a strange behavior with my Access 2003 database in date
calculation in a query.
My query is supposed to give me all the invoices between date X and
date Y from the INVOICE table.
The query is the following:

SELECT INVOICE.ID, INVOICE.DATE
FROM INVOICE
WHERE (((INVOICE.DATE)>=[startdate] And (INVOICE.DATE)<=[enddate]));

Access executes the query and asks me the 2 dates (startdate and
enddate) but the results are wrong. I receive less records than
expected and this only for the year 2005 !! With invoices from 2004, i
got them all. For the year 2005, I have less invoices than correct.

Example: startdate= 1st November 2005, enddate = 21st Nov 2005, i got
all the invoices with the date 1st Nov to 18th Nov and nothing for the
21 ! And if put 22nd Nov as the end date, I got all the invoice from
1st Nov to 21st Nov plus a few of 22nd but not all !

This is really strange and corrupt all my requests related to dates.
I've tried on several other PCs (XP) with exactly the same behavior.
What happen to my DB in 2005 ?

PLEASE HELP !

Patrick

Patrick,

Tables:

Please forgive the dates appended to the table names.

Note: As noted in an earlier post, "Date" is a reserved word in MS
Access (and many other databases and programming languages). Using
it for things other than what it was meant for can cause problems.
If you have control of your database schema, strongly consider
re-naming this attribute. I have enclosed "Date" in [] as a result
of its appearance in the schema.

CREATE TABLE INVOICE_20051126_1
(ID INTEGER
,[Date] DATETIME
,CONSTRAINT pk_Invoice_20051126_1
PRIMARY KEY (ID)
)


Sample Data:

ID, [Date]
1, 10/31/2005
2, 11/01/2005
3, 11/02/2005
4, 11/10/2005
5, 11/17/2005
6, 11/18/2005
7, 11/19/2005
8, 11/20/2005
9, 11/21/2005
10, 11/22/2005


Query:

Note: A copy of the original above, re-aligned, and with table
aliases in place.


First Run:

SELECT I1.ID
,I1.[DATE]
FROM INVOICE_20051126_1 AS I1
WHERE (I1.[DATE] >= [startdate]
And I1.[DATE] <= [enddate]);

[startdate] = "11/01/2005"
[enddate] = "11/18/2005"


Results:

2, 11/01/2005
3, 11/02/2005
4, 11/10/2005
5, 11/17/2005
6, 11/18/2005
7, 11/19/2005
8, 11/20/2005
9, 11/21/2005

The above results are what what I expected given the sample data.


Second Run:

SELECT I1.ID
,I1.[DATE]
FROM INVOICE_20051126_1 AS I1
WHERE (I1.[DATE] >= [startdate]
And I1.[DATE] <= [enddate]);

[startdate] = "1st November 2005"
[enddate] = "21st November 2005"


Results:

The query returns an error: "The expression is incorrectly typed, or
it is too complex to be evaluated . . ."



When I run the query with standard MS Access date formats,
everything turns out ok.

When I enter the date literals you mentioned, it returns an error.


If you are not using US Date formats, see
http://allenbrowne.com/ser-36.html for more information.


Although meant for an sqlserver newsgroup, the
following link is still applicable for MS Access:
http://www.aspfaq.com/etiquette.asp?id=5006, and
is excellent when it comes to detailing how to
provide the information that will best enable
others to answer your questions.


Sincerely,

Chris O.

 

[pguizze]

Thanks a lot guys, but I still have the problem.
(for the DATE as a column name it was just for the sake of
understanding, the column is "date_invoice" in reality)

When I put what Pat Hartman(MVP) suggested :
WHERE DateValue(INVOICE.[DATE]) Between [startdate] And [enddate];

I got all the invoices !!! no matter what the date has been put for the
invoices...
It seems that Access does not understand DateValue ???

I'm using a French Access 2003. But I don't think it is related as I've
tested on a UK PC with same issues.
Doesn't seem to be a US format date problem either.

I'm lost...

 

[pguizze]

I just found a new strange behavior as well:

Sometimes I got invoice for the correct time period but not all of
them.

Example: startdate= 1st November 2005, enddate = 23th Nov 2005, i got
all the invoices with the date 1st Nov to 22th Nov and nothing for the
23, but only 3 invoices of 22 on 7 ! I've checked I really got 7
invoices for the 22th and not 3...

The idea from Brendan Reynolds and John Spencer is therefore not
enough. As only a portion of the last day is taken into account.

I really can't find any logic in Access here...

 

[Brendan Reynolds]

Do you have any other criteria in the query? Or any inner joins?

It might help if you posted the SQL. (The real SQL, copied and pasted from
the SQL View of the query, please. I understand the desire to simplify, but
sometimes if obscures the real source of the problem.)

 

[Chris2]

Thanks a lot guys, but I still have the problem.
(for the DATE as a column name it was just for the sake of
understanding, the column is "date_invoice" in reality)

When I put what Pat Hartman(MVP) suggested :
WHERE DateValue(INVOICE.[DATE]) Between [startdate] And [enddate];

I got all the invoices !!! no matter what the date has been put for the
invoices...
It seems that Access does not understand DateValue ???

pguizze,

Did you read http://allenbrowne.com/ser-36.html?

I'm using a French Access 2003. But I don't think it is related as I've
tested on a UK PC with same issues.
Doesn't seem to be a US format date problem either.

I'm lost...

My apologies, but I don't have enough information to know.

If you could post a full description of the structure of the tables
(DDL would be best), plus sample input data, and desired results, it
would help.

Although meant for an sqlserver newsgroup, the
following link is still applicable for MS Access:
http://www.aspfaq.com/etiquette.asp?id=5006, and
is excellent when it comes to detailing how to
provide the information that will best enable
others to answer your questions.


Sincerely,

Chris O.

 

[Pat Hartman\(MVP\)]

I was simply using the name you said you used. You need to translate my
suggestion to the REAL column name. Perhaps this will work:
WHERE DateValue([date_invoice]) Between [Enter Start Date] And [Enter End
Date];

substitute your REAL field name for date_invoice if that isn't correct. The
other two fields are parameters so I changed them to say something user
friendly. If the start and end dates are entered on a form, the SQL would
look something like:
WHERE DateValue([date_invoice]) Between Forms!YourFormName!StartDate And
Forms!YourFormName!EndDate;