Strange result from query, '>' gives same result as '>='

  • Thread starter Thread starter Kjell Arne Johansen
  • Start date Start date
K

Kjell Arne Johansen

Hi

In a table a have a datetime field called ’Time’ and I query records before
or after a specific time.
Example, three records with the following values in the 'Time' field
‘22-02-2008 10:23:31’
‘22-02-2008 10:23:32’
‘22-02-2008 10:23:33’

This simple query (GetPrevious)

SELECT TOP 1 *
FROM events
WHERE
[time] < #22-02-2008 10:23:32#
ORDER BY
[time] DESC

gives the record with 'Time' field:
‘22-02-2008 10:23:31’
as excepted.

This query (GetNext)

SELECT *
FROM (
SELECT TOP 1 *
FROM events
WHERE
[time] > #22-02-2008 10:23:32#
ORDER BY
[time] ASC
) AS Next
ORDER BY
[time] DESC

gives the record with 'Time' field:
‘22-02-2008 10:23:32’
which I do not expect. I hoped for ‘22-02-2008 10:23:33’.

Why do I get ‘22-02-2008 10:23:32’ and not ‘22-02-2008 10:23:33’?

I’m a little confused.

Help will make me very happy :-)

Regards
Kjell Arne Johansen
 
Start by simplfying. What is returned when you do this?

SELECT TOP 1 *
FROM events
WHERE
[time] > #22-02-2008 10:23:32#
ORDER BY
[time] ASC ;

or

SELECT [time]
FROM events
WHERE
[time] > #22-02-2008 10:23:32#
ORDER BY
[time] ASC ;

Also when dealing with date and times, there are some standard things to
consider.

1. Is the time field an actual Date/Time datatype?

2. Sometimes SQL statements have problems with DD-MM-YYYY. It's an ANSII
standard where the 'A' stands for American. Therefore SQL likes MM-DD-YYYY
better.

3. The word "time" is reserved as it's a function. Therefore naming a field
or table 'time' could cause problems. You have square brackets around it so
it shouldn't be causing a problem; however, it is in a subquery so the []
might be getting removed somewhere along the line. For more on reserved
words, check out:
http://support.microsoft.com/kb/286335/
 
Hi

Thank you for the response.

I have tested all this stuff -also simplfying- before posting this request.
1. It is Date/Time type
2. I have tried the ‘MM-DD-YYYY’ way and it gives the same result
3. Instead of 'time' I have been using another name on the time field, same
result

There is some logic here which I don’t see.
Same type of query works very fine on same type of table in SQL Server.

Please give me some more advices, things to check if you have any idea.

Regards
Kjell Arne Johansen

Jerry Whittle said:
Start by simplfying. What is returned when you do this?

SELECT TOP 1 *
FROM events
WHERE
[time] > #22-02-2008 10:23:32#
ORDER BY
[time] ASC ;

or

SELECT [time]
FROM events
WHERE
[time] > #22-02-2008 10:23:32#
ORDER BY
[time] ASC ;

Also when dealing with date and times, there are some standard things to
consider.

1. Is the time field an actual Date/Time datatype?

2. Sometimes SQL statements have problems with DD-MM-YYYY. It's an ANSII
standard where the 'A' stands for American. Therefore SQL likes MM-DD-YYYY
better.

3. The word "time" is reserved as it's a function. Therefore naming a field
or table 'time' could cause problems. You have square brackets around it so
it shouldn't be causing a problem; however, it is in a subquery so the []
might be getting removed somewhere along the line. For more on reserved
words, check out:
http://support.microsoft.com/kb/286335/
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Kjell Arne Johansen said:
Hi

In a table a have a datetime field called ’Time’ and I query records before
or after a specific time.
Example, three records with the following values in the 'Time' field
‘22-02-2008 10:23:31’
‘22-02-2008 10:23:32’
‘22-02-2008 10:23:33’

This simple query (GetPrevious)

SELECT TOP 1 *
FROM events
WHERE
[time] < #22-02-2008 10:23:32#
ORDER BY
[time] DESC

gives the record with 'Time' field:
‘22-02-2008 10:23:31’
as excepted.

This query (GetNext)

SELECT *
FROM (
SELECT TOP 1 *
FROM events
WHERE
[time] > #22-02-2008 10:23:32#
ORDER BY
[time] ASC
) AS Next
ORDER BY
[time] DESC

gives the record with 'Time' field:
‘22-02-2008 10:23:32’
which I do not expect. I hoped for ‘22-02-2008 10:23:33’.

Why do I get ‘22-02-2008 10:23:32’ and not ‘22-02-2008 10:23:33’?

I’m a little confused.

Help will make me very happy :-)

Regards
Kjell Arne Johansen
Click to expand...
Click to expand...
 
What does the following return?

SELECT [time]
FROM events
WHERE [time] > #22-02-2008 10:23:32#
ORDER BY [time] ASC ;

Does it return 22-02-2008 10:23:33 ?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Kjell Arne Johansen said:
Hi

Thank you for the response.

I have tested all this stuff -also simplfying- before posting this request.
1. It is Date/Time type
2. I have tried the ‘MM-DD-YYYY’ way and it gives the same result
3. Instead of 'time' I have been using another name on the time field, same
result

There is some logic here which I don’t see.
Same type of query works very fine on same type of table in SQL Server.

Please give me some more advices, things to check if you have any idea.

Regards
Kjell Arne Johansen

Jerry Whittle said:
Start by simplfying. What is returned when you do this?

SELECT TOP 1 *
FROM events
WHERE
[time] > #22-02-2008 10:23:32#
ORDER BY
[time] ASC ;

or

SELECT [time]
FROM events
WHERE
[time] > #22-02-2008 10:23:32#
ORDER BY
[time] ASC ;

Also when dealing with date and times, there are some standard things to
consider.

1. Is the time field an actual Date/Time datatype?

2. Sometimes SQL statements have problems with DD-MM-YYYY. It's an ANSII
standard where the 'A' stands for American. Therefore SQL likes MM-DD-YYYY
better.

3. The word "time" is reserved as it's a function. Therefore naming a field
or table 'time' could cause problems. You have square brackets around it so
it shouldn't be causing a problem; however, it is in a subquery so the []
might be getting removed somewhere along the line. For more on reserved
words, check out:
http://support.microsoft.com/kb/286335/
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Kjell Arne Johansen said:
Hi

In a table a have a datetime field called ’Time’ and I query records before
or after a specific time.
Example, three records with the following values in the 'Time' field
‘22-02-2008 10:23:31’
‘22-02-2008 10:23:32’
‘22-02-2008 10:23:33’

This simple query (GetPrevious)

SELECT TOP 1 *
FROM events
WHERE
[time] < #22-02-2008 10:23:32#
ORDER BY
[time] DESC

gives the record with 'Time' field:
‘22-02-2008 10:23:31’
as excepted.

This query (GetNext)

SELECT *
FROM (
SELECT TOP 1 *
FROM events
WHERE
[time] > #22-02-2008 10:23:32#
ORDER BY
[time] ASC
) AS Next
ORDER BY
[time] DESC

gives the record with 'Time' field:
‘22-02-2008 10:23:32’
which I do not expect. I hoped for ‘22-02-2008 10:23:33’.

Why do I get ‘22-02-2008 10:23:32’ and not ‘22-02-2008 10:23:33’?

I’m a little confused.

Help will make me very happy :-)

Regards
Kjell Arne Johansen
Click to expand...
Click to expand...
Click to expand...
 
Hi

Answer to your question:
No, the query returned '22-02-2008 10:23:32'

I have done some more tests now, thinking of some other possibilities.
It looks to me that the problem could be related to converting an Access 97
database to Access 2000.
My test database was converted from 97 to 2000.

To check this I did as follows:
I exported the ‘Events’ table to a .csv file, emptied the table and imported
the .csv file.
Then I run the query again -and it worked as expected, returning '22-02-2008
10:23:33'

It looks to me that the problem is related to converting the database.
Maybe date/date format has changed in MS Access 2000?
What do you think?

Jerry Whittle said:
What does the following return?

SELECT [time]
FROM events
WHERE [time] > #22-02-2008 10:23:32#
ORDER BY [time] ASC ;

Does it return 22-02-2008 10:23:33 ?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Kjell Arne Johansen said:
Hi

Thank you for the response.

I have tested all this stuff -also simplfying- before posting this request.
1. It is Date/Time type
2. I have tried the ‘MM-DD-YYYY’ way and it gives the same result
3. Instead of 'time' I have been using another name on the time field, same
result

There is some logic here which I don’t see.
Same type of query works very fine on same type of table in SQL Server.

Please give me some more advices, things to check if you have any idea.

Regards
Kjell Arne Johansen

Jerry Whittle said:
Start by simplfying. What is returned when you do this?

SELECT TOP 1 *
FROM events
WHERE
[time] > #22-02-2008 10:23:32#
ORDER BY
[time] ASC ;

or

SELECT [time]
FROM events
WHERE
[time] > #22-02-2008 10:23:32#
ORDER BY
[time] ASC ;

Also when dealing with date and times, there are some standard things to
consider.

1. Is the time field an actual Date/Time datatype?

2. Sometimes SQL statements have problems with DD-MM-YYYY. It's an ANSII
standard where the 'A' stands for American. Therefore SQL likes MM-DD-YYYY
better.

3. The word "time" is reserved as it's a function. Therefore naming a field
or table 'time' could cause problems. You have square brackets around it so
it shouldn't be causing a problem; however, it is in a subquery so the []
might be getting removed somewhere along the line. For more on reserved
words, check out:
http://support.microsoft.com/kb/286335/
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hi

In a table a have a datetime field called ’Time’ and I query records before
or after a specific time.
Example, three records with the following values in the 'Time' field
‘22-02-2008 10:23:31’
‘22-02-2008 10:23:32’
‘22-02-2008 10:23:33’

This simple query (GetPrevious)

SELECT TOP 1 *
FROM events
WHERE
[time] < #22-02-2008 10:23:32#
ORDER BY
[time] DESC

gives the record with 'Time' field:
‘22-02-2008 10:23:31’
as excepted.

This query (GetNext)

SELECT *
FROM (
SELECT TOP 1 *
FROM events
WHERE
[time] > #22-02-2008 10:23:32#
ORDER BY
[time] ASC
) AS Next
ORDER BY
[time] DESC

gives the record with 'Time' field:
‘22-02-2008 10:23:32’
which I do not expect. I hoped for ‘22-02-2008 10:23:33’.

Why do I get ‘22-02-2008 10:23:32’ and not ‘22-02-2008 10:23:33’?

I’m a little confused.

Help will make me very happy :-)

Regards
Kjell Arne Johansen
Click to expand...
Click to expand...
Click to expand...
 
The format didn't change. Actually Access stores dates and times as a number
and formats them to look like dates. Try running the following query and see
what the numbers look like:

SELECT [time], CDbl([time]) as TimeNumber
FROM events
WHERE [time] > #20-02-2008#
ORDER BY [time] ASC ;

As the dates work after importing, I'm wondering if your database is
corrupt. Do a compact and repair. Maybe that could fix it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Kjell Arne Johansen said:
Hi

Answer to your question:
No, the query returned '22-02-2008 10:23:32'

I have done some more tests now, thinking of some other possibilities.
It looks to me that the problem could be related to converting an Access 97
database to Access 2000.
My test database was converted from 97 to 2000.

To check this I did as follows:
I exported the ‘Events’ table to a .csv file, emptied the table and imported
the .csv file.
Then I run the query again -and it worked as expected, returning '22-02-2008
10:23:33'

It looks to me that the problem is related to converting the database.
Maybe date/date format has changed in MS Access 2000?
What do you think?

Jerry Whittle said:
What does the following return?

SELECT [time]
FROM events
WHERE [time] > #22-02-2008 10:23:32#
ORDER BY [time] ASC ;

Does it return 22-02-2008 10:23:33 ?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Kjell Arne Johansen said:
Hi

Thank you for the response.

I have tested all this stuff -also simplfying- before posting this request.
1. It is Date/Time type
2. I have tried the ‘MM-DD-YYYY’ way and it gives the same result
3. Instead of 'time' I have been using another name on the time field, same
result

There is some logic here which I don’t see.
Same type of query works very fine on same type of table in SQL Server.

Please give me some more advices, things to check if you have any idea.

Regards
Kjell Arne Johansen

:

Start by simplfying. What is returned when you do this?

SELECT TOP 1 *
FROM events
WHERE
[time] > #22-02-2008 10:23:32#
ORDER BY
[time] ASC ;

or

SELECT [time]
FROM events
WHERE
[time] > #22-02-2008 10:23:32#
ORDER BY
[time] ASC ;

Also when dealing with date and times, there are some standard things to
consider.

1. Is the time field an actual Date/Time datatype?

2. Sometimes SQL statements have problems with DD-MM-YYYY. It's an ANSII
standard where the 'A' stands for American. Therefore SQL likes MM-DD-YYYY
better.

3. The word "time" is reserved as it's a function. Therefore naming a field
or table 'time' could cause problems. You have square brackets around it so
it shouldn't be causing a problem; however, it is in a subquery so the []
might be getting removed somewhere along the line. For more on reserved
words, check out:
http://support.microsoft.com/kb/286335/
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hi

In a table a have a datetime field called ’Time’ and I query records before
or after a specific time.
Example, three records with the following values in the 'Time' field
‘22-02-2008 10:23:31’
‘22-02-2008 10:23:32’
‘22-02-2008 10:23:33’

This simple query (GetPrevious)

SELECT TOP 1 *
FROM events
WHERE
[time] < #22-02-2008 10:23:32#
ORDER BY
[time] DESC

gives the record with 'Time' field:
‘22-02-2008 10:23:31’
as excepted.

This query (GetNext)

SELECT *
FROM (
SELECT TOP 1 *
FROM events
WHERE
[time] > #22-02-2008 10:23:32#
ORDER BY
[time] ASC
) AS Next
ORDER BY
[time] DESC

gives the record with 'Time' field:
‘22-02-2008 10:23:32’
which I do not expect. I hoped for ‘22-02-2008 10:23:33’.

Why do I get ‘22-02-2008 10:23:32’ and not ‘22-02-2008 10:23:33’?

I’m a little confused.

Help will make me very happy :-)

Regards
Kjell Arne Johansen
Click to expand...
Click to expand...
Click to expand...
 
Hi

Using your query there are differences between the table working and the
table not working. Repair did not help.

I feel that this is ok now and that it will not be a problem avoiding
converting the database.

Thank you for your valuable time.

Regards
Kjell Arne Johansen

Jerry Whittle said:
The format didn't change. Actually Access stores dates and times as a number
and formats them to look like dates. Try running the following query and see
what the numbers look like:

SELECT [time], CDbl([time]) as TimeNumber
FROM events
WHERE [time] > #20-02-2008#
ORDER BY [time] ASC ;

As the dates work after importing, I'm wondering if your database is
corrupt. Do a compact and repair. Maybe that could fix it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Kjell Arne Johansen said:
Hi

Answer to your question:
No, the query returned '22-02-2008 10:23:32'

I have done some more tests now, thinking of some other possibilities.
It looks to me that the problem could be related to converting an Access 97
database to Access 2000.
My test database was converted from 97 to 2000.

To check this I did as follows:
I exported the ‘Events’ table to a .csv file, emptied the table and imported
the .csv file.
Then I run the query again -and it worked as expected, returning '22-02-2008
10:23:33'

It looks to me that the problem is related to converting the database.
Maybe date/date format has changed in MS Access 2000?
What do you think?

Jerry Whittle said:
What does the following return?

SELECT [time]
FROM events
WHERE [time] > #22-02-2008 10:23:32#
ORDER BY [time] ASC ;

Does it return 22-02-2008 10:23:33 ?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Hi

Thank you for the response.

I have tested all this stuff -also simplfying- before posting this request.
1. It is Date/Time type
2. I have tried the ‘MM-DD-YYYY’ way and it gives the same result
3. Instead of 'time' I have been using another name on the time field, same
result

There is some logic here which I don’t see.
Same type of query works very fine on same type of table in SQL Server.

Please give me some more advices, things to check if you have any idea.

Regards
Kjell Arne Johansen

:

Start by simplfying. What is returned when you do this?

SELECT TOP 1 *
FROM events
WHERE
[time] > #22-02-2008 10:23:32#
ORDER BY
[time] ASC ;

or

SELECT [time]
FROM events
WHERE
[time] > #22-02-2008 10:23:32#
ORDER BY
[time] ASC ;

Also when dealing with date and times, there are some standard things to
consider.

1. Is the time field an actual Date/Time datatype?

2. Sometimes SQL statements have problems with DD-MM-YYYY. It's an ANSII
standard where the 'A' stands for American. Therefore SQL likes MM-DD-YYYY
better.

3. The word "time" is reserved as it's a function. Therefore naming a field
or table 'time' could cause problems. You have square brackets around it so
it shouldn't be causing a problem; however, it is in a subquery so the []
might be getting removed somewhere along the line. For more on reserved
words, check out:
http://support.microsoft.com/kb/286335/
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hi

In a table a have a datetime field called ’Time’ and I query records before
or after a specific time.
Example, three records with the following values in the 'Time' field
‘22-02-2008 10:23:31’
‘22-02-2008 10:23:32’
‘22-02-2008 10:23:33’

This simple query (GetPrevious)

SELECT TOP 1 *
FROM events
WHERE
[time] < #22-02-2008 10:23:32#
ORDER BY
[time] DESC

gives the record with 'Time' field:
‘22-02-2008 10:23:31’
as excepted.

This query (GetNext)

SELECT *
FROM (
SELECT TOP 1 *
FROM events
WHERE
[time] > #22-02-2008 10:23:32#
ORDER BY
[time] ASC
) AS Next
ORDER BY
[time] DESC

gives the record with 'Time' field:
‘22-02-2008 10:23:32’
which I do not expect. I hoped for ‘22-02-2008 10:23:33’.

Why do I get ‘22-02-2008 10:23:32’ and not ‘22-02-2008 10:23:33’?

I’m a little confused.

Help will make me very happy :-)

Regards
Kjell Arne Johansen
Click to expand...
Click to expand...
Click to expand...
 
Back
Top