Date BETWEEN

  • Thread starter Thread starter Steve Wagner
  • Start date Start date
S

Steve Wagner

Greetings,

I am relatively new to SQL and would appreciate help regarding finding
records between two dates. There are a number of SQL variances I've tried,
but let's start with this one:

"Select * from Table1 WHERE Date BETWEEN 39693.000000 AND 39693.999988"

The query always returns one record even though there are more. I'm using
CDaoDatabase in a Visual Studio application.

Thanks,

Steve Wagner
 
Seems that you are looking for dates on 9/2/2008? Are you sure that there are
records for that date?

Is the Date field actually date/time data type or something else like a
number or text field?

One thing that I did see what the word "Date" being used as a field name. As
there is a Date() function, Access could be getting confused. Try putting
square brackets around it like so: [date] .

http://support.microsoft.com/kb/286335/
 
Thank-you for the response.

The date in my posting is indeed 9/2/2008. I've attempted square brackets,
formatting to text such as "Sep-02-2008" and so on with the same result: we
get back one record.

Now, by some inspiration I tried adding an "ORDER BY" clause:

"Select * from Table1 WHERE Date >= 39693.000000 AND Date <= 39693.999988
ORDER BY Date ASC"

Finally, this returns all the records expected. Why is this?

Regards,

Steve



Jerry Whittle said:
Seems that you are looking for dates on 9/2/2008? Are you sure that there
are
records for that date?

Is the Date field actually date/time data type or something else like a
number or text field?

One thing that I did see what the word "Date" being used as a field name.
As
there is a Date() function, Access could be getting confused. Try putting
square brackets around it like so: [date] .

http://support.microsoft.com/kb/286335/

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Steve Wagner said:
Greetings,

I am relatively new to SQL and would appreciate help regarding finding
records between two dates. There are a number of SQL variances I've
tried,
but let's start with this one:

"Select * from Table1 WHERE Date BETWEEN 39693.000000 AND 39693.999988"

The query always returns one record even though there are more. I'm using
CDaoDatabase in a Visual Studio application.

Thanks,

Steve Wagner
Click to expand...
Click to expand...
 
Strange. Adding an Order By shouldn't make any difference to the number of
records returned (maybe with the exception of a Top statement and some ties).

I noticed that you've removed the Between and started using >= . Have you
tried this without the Order By?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Steve Wagner said:
Thank-you for the response.

The date in my posting is indeed 9/2/2008. I've attempted square brackets,
formatting to text such as "Sep-02-2008" and so on with the same result: we
get back one record.

Now, by some inspiration I tried adding an "ORDER BY" clause:

"Select * from Table1 WHERE Date >= 39693.000000 AND Date <= 39693.999988
ORDER BY Date ASC"

Finally, this returns all the records expected. Why is this?

Regards,

Steve



Jerry Whittle said:
Seems that you are looking for dates on 9/2/2008? Are you sure that there
are
records for that date?

Is the Date field actually date/time data type or something else like a
number or text field?

One thing that I did see what the word "Date" being used as a field name.
As
there is a Date() function, Access could be getting confused. Try putting
square brackets around it like so: [date] .

http://support.microsoft.com/kb/286335/

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Steve Wagner said:
Greetings,

I am relatively new to SQL and would appreciate help regarding finding
records between two dates. There are a number of SQL variances I've
tried,
but let's start with this one:

"Select * from Table1 WHERE Date BETWEEN 39693.000000 AND 39693.999988"

The query always returns one record even though there are more. I'm using
CDaoDatabase in a Visual Studio application.

Thanks,

Steve Wagner
Click to expand...
Click to expand...
Click to expand...
 
As Jerry mentioned, Date is a reserved word in Access, and should not be used
as a field name. If you do use it as a field name, you should wrap it in
brackets to help Access/Jet interpret it as a field, rather than the
function, so your query would look like:

"Select * from Table1 WHERE [Date] >= 39693.000000 AND Date <= 39693.999988

Personally, I prefer:

"Select * from Table1 WHERE DateValue([Date]) = #9/2/2008#"

Your [Date] field is probably storing time values as well as the date, so
that a query that looks like:

Select * from Table1 WHERE [Date] = #9/2/2008#"

would only return records where the [Date] field contained:

9/2/2008 00:00:00 (which is not very likely). By using the DateValue( )
function, you strip out the time portion of the date/time value. You could
also achieve this by:

Select * from Table1 WHERE INT([Date]) = 39693

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Steve Wagner said:
Thank-you for the response.

The date in my posting is indeed 9/2/2008. I've attempted square brackets,
formatting to text such as "Sep-02-2008" and so on with the same result: we
get back one record.

Now, by some inspiration I tried adding an "ORDER BY" clause:

"Select * from Table1 WHERE Date >= 39693.000000 AND Date <= 39693.999988
ORDER BY Date ASC"

Finally, this returns all the records expected. Why is this?

Regards,

Steve



Jerry Whittle said:
Seems that you are looking for dates on 9/2/2008? Are you sure that there
are
records for that date?

Is the Date field actually date/time data type or something else like a
number or text field?

One thing that I did see what the word "Date" being used as a field name.
As
there is a Date() function, Access could be getting confused. Try putting
square brackets around it like so: [date] .

http://support.microsoft.com/kb/286335/

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Steve Wagner said:
Greetings,

I am relatively new to SQL and would appreciate help regarding finding
records between two dates. There are a number of SQL variances I've
tried,
but let's start with this one:

"Select * from Table1 WHERE Date BETWEEN 39693.000000 AND 39693.999988"

The query always returns one record even though there are more. I'm using
CDaoDatabase in a Visual Studio application.

Thanks,

Steve Wagner
Click to expand...
Click to expand...
Click to expand...
 
Thanks Jerry; I have. All kinds of ways. From Dale's response I was
surprised to learn that "Date" is a reserved word. I added the brackets
around [Date] but I still need the "Order By". Anyways, you both have given
me something to pursue tomorrow. Thank-you so much for the responses.

Steve


Jerry Whittle said:
Strange. Adding an Order By shouldn't make any difference to the number of
records returned (maybe with the exception of a Top statement and some
ties).

I noticed that you've removed the Between and started using >= . Have you
tried this without the Order By?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Steve Wagner said:
Thank-you for the response.

The date in my posting is indeed 9/2/2008. I've attempted square
brackets,
formatting to text such as "Sep-02-2008" and so on with the same result:
we
get back one record.

Now, by some inspiration I tried adding an "ORDER BY" clause:

"Select * from Table1 WHERE Date >= 39693.000000 AND Date <=
39693.999988
ORDER BY Date ASC"

Finally, this returns all the records expected. Why is this?

Regards,

Steve



Jerry Whittle said:
Seems that you are looking for dates on 9/2/2008? Are you sure that
there
are
records for that date?

Is the Date field actually date/time data type or something else like a
number or text field?

One thing that I did see what the word "Date" being used as a field
name.
As
there is a Date() function, Access could be getting confused. Try
putting
square brackets around it like so: [date] .

http://support.microsoft.com/kb/286335/

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Greetings,

I am relatively new to SQL and would appreciate help regarding finding
records between two dates. There are a number of SQL variances I've
tried,
but let's start with this one:

"Select * from Table1 WHERE Date BETWEEN 39693.000000 AND
39693.999988"

The query always returns one record even though there are more. I'm
using
CDaoDatabase in a Visual Studio application.

Thanks,

Steve Wagner
Click to expand...
Click to expand...
Click to expand...
 
Back
Top