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

[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

 

[Jerry Whittle]

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/

 

[Kjell Arne Johansen]

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

 

[Jerry Whittle]

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

 

[Kjell Arne Johansen]

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?

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

 

[Jerry Whittle]

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.

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?

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

 

[Kjell Arne Johansen]

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

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.

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?

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