Date issue

I have a sql database that access is using to do some queries on, in the
queries there is a date/time field. I've used the datevalue() function to
remove the time values. In a different query the user will enter a begin
date and an end date when I run the query I get dates that do not belong,
such as 1/3/2008 to 1/6/2008 I will get records with 1/31/2008. Also the sql
database list the date/time field as 01/01/2008 access list it as 1/1/2008.

Thanks in advance

John

From your description, it almost sounds like the so-called "date" is
actually just a text value. Do you have a way to confirm the underlying
table/field data type?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
I have a sql database that access is using to do some queries on, in the
queries there is a date/time field. I've used the datevalue() function to
remove the time values. In a different query the user will enter a begin
date and an end date when I run the query I get dates that do not belong,
such as 1/3/2008 to 1/6/2008 I will get records with 1/31/2008. Also the
sql
database list the date/time field as 01/01/2008 access list it as
1/1/2008.

Thanks in advance

The sql database runs an application and in the application it does display
the date as 01/01/2008 12:00:00 PM

"Jeff Boyce" wrote:

John

From your description, it almost sounds like the so-called "date" is
actually just a text value. Do you have a way to confirm the underlying
table/field data type?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
I have a sql database that access is using to do some queries on, in the
queries there is a date/time field. I've used the datevalue() function to
remove the time values. In a different query the user will enter a begin
date and an end date when I run the query I get dates that do not belong,
such as 1/3/2008 to 1/6/2008 I will get records with 1/31/2008. Also the
sql
database list the date/time field as 01/01/2008 access list it as
1/1/2008.

Thanks in advance

John

Displayed value is not necessarily equal to stored value.

The application may display that, but what is the SQL-Server table's field's
data type.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
The sql database runs an application and in the application it does
display
the date as 01/01/2008 12:00:00 PM

"Jeff Boyce" wrote:

John

From your description, it almost sounds like the so-called "date" is
actually just a text value. Do you have a way to confirm the underlying
table/field data type?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
I have a sql database that access is using to do some queries on, in the
queries there is a date/time field. I've used the datevalue() function
to
remove the time values. In a different query the user will enter a
begin
date and an end date when I run the query I get dates that do not
belong,
such as 1/3/2008 to 1/6/2008 I will get records with 1/31/2008. Also
the
sql
database list the date/time field as 01/01/2008 access list it as
1/1/2008.

Thanks in advance

Jeff

The field type indicates that it is a date/time field.

"Jeff Boyce" wrote:

John

Displayed value is not necessarily equal to stored value.

The application may display that, but what is the SQL-Server table's field's
data type.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
The sql database runs an application and in the application it does
display
the date as 01/01/2008 12:00:00 PM

"Jeff Boyce" wrote:

John

From your description, it almost sounds like the so-called "date" is
actually just a text value. Do you have a way to confirm the underlying
table/field data type?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
I have a sql database that access is using to do some queries on, in the
queries there is a date/time field. I've used the datevalue() function
to
remove the time values. In a different query the user will enter a
begin
date and an end date when I run the query I get dates that do not
belong,
such as 1/3/2008 to 1/6/2008 I will get records with 1/31/2008. Also
the
sql
database list the date/time field as 01/01/2008 access list it as
1/1/2008.

Thanks in advance

Please post the SQL statement of the query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
Jeff

The field type indicates that it is a date/time field.

"Jeff Boyce" wrote:

John

Displayed value is not necessarily equal to stored value.

The application may display that, but what is the SQL-Server table's
field's
data type.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
The sql database runs an application and in the application it does
display
the date as 01/01/2008 12:00:00 PM

"Jeff Boyce" wrote:

John

From your description, it almost sounds like the so-called "date" is
actually just a text value. Do you have a way to confirm the
underlying
table/field data type?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
I have a sql database that access is using to do some queries on, in
the
queries there is a date/time field. I've used the datevalue()
function
to
remove the time values. In a different query the user will enter a
begin
date and an end date when I run the query I get dates that do not
belong,
such as 1/3/2008 to 1/6/2008 I will get records with 1/31/2008.
Also
the
sql
database list the date/time field as 01/01/2008 access list it as
1/1/2008.

Thanks in advance

here is the statement:

SELECT NeuropathyStore.OrderID, NeuropathyStore.CustomerID,
NeuropathyStore.OrderDate1, NeuropathyStore.ShipFirstName,
NeuropathyStore.ShipLastName, NeuropathyStore.ShipCompany,
NeuropathyStore.ShipAddress1, NeuropathyStore.ShipAddress2,
NeuropathyStore.ShipAddress3, NeuropathyStore.ShipCity,
NeuropathyStore.ShipStateProvinceCode, NeuropathyStore.ShipPostalCode,
dbo_OrderItems.UnitPrice, NeuropathyStore.OrderNumberPrefix,
NeuropathyStore.OrderNumberPostfix, NeuropathyStore.OrderNumberDisplay,
dbo_OrderItems.Name, dbo_OrderItems.Code, dbo_OrderItems.Quantity
FROM NeuropathyStore INNER JOIN dbo_OrderItems ON NeuropathyStore.OrderID =
dbo_OrderItems.OrderID
WHERE (((NeuropathyStore.OrderDate1)#1/18/2008# And
(NeuropathyStore.OrderDate1)#1/25/2008#));


I believe this is what you were asking for.

"Jeff Boyce" wrote:

Please post the SQL statement of the query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
Jeff

The field type indicates that it is a date/time field.

"Jeff Boyce" wrote:

John

Displayed value is not necessarily equal to stored value.

The application may display that, but what is the SQL-Server table's
field's
data type.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
The sql database runs an application and in the application it does
display
the date as 01/01/2008 12:00:00 PM

"Jeff Boyce" wrote:

John

From your description, it almost sounds like the so-called "date" is
actually just a text value. Do you have a way to confirm the
underlying
table/field data type?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
I have a sql database that access is using to do some queries on, in
the
queries there is a date/time field. I've used the datevalue()
function
to
remove the time values. In a different query the user will enter a
begin
date and an end date when I run the query I get dates that do not
belong,
such as 1/3/2008 to 1/6/2008 I will get records with 1/31/2008.
Also
the
sql
database list the date/time field as 01/01/2008 access list it as
1/1/2008.

Thanks in advance

John

Given the query/SQL you posted, I would expect to see records with
NeuropathyStore.OrderDate1 starting with 1/19/2008 and ending with
1/24/2008. Is this what you're getting when you run what you posted?

By the way, one of the fields in your OrderItems table is named "Name".
This is a reserved word in Access, and will only cause confusion for both
you and Access. Consider changing that fieldname to something other than
this reserved word.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
here is the statement:

SELECT NeuropathyStore.OrderID, NeuropathyStore.CustomerID,
NeuropathyStore.OrderDate1, NeuropathyStore.ShipFirstName,
NeuropathyStore.ShipLastName, NeuropathyStore.ShipCompany,
NeuropathyStore.ShipAddress1, NeuropathyStore.ShipAddress2,
NeuropathyStore.ShipAddress3, NeuropathyStore.ShipCity,
NeuropathyStore.ShipStateProvinceCode, NeuropathyStore.ShipPostalCode,
dbo_OrderItems.UnitPrice, NeuropathyStore.OrderNumberPrefix,
NeuropathyStore.OrderNumberPostfix, NeuropathyStore.OrderNumberDisplay,
dbo_OrderItems.Name, dbo_OrderItems.Code, dbo_OrderItems.Quantity
FROM NeuropathyStore INNER JOIN dbo_OrderItems ON NeuropathyStore.OrderID
=
dbo_OrderItems.OrderID
WHERE (((NeuropathyStore.OrderDate1)#1/18/2008# And
(NeuropathyStore.OrderDate1)#1/25/2008#));


I believe this is what you were asking for.

"Jeff Boyce" wrote:

Please post the SQL statement of the query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
Jeff

The field type indicates that it is a date/time field.

"Jeff Boyce" wrote:

John

Displayed value is not necessarily equal to stored value.

The application may display that, but what is the SQL-Server table's
field's
data type.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
The sql database runs an application and in the application it does
display
the date as 01/01/2008 12:00:00 PM

"Jeff Boyce" wrote:

John

From your description, it almost sounds like the so-called "date"
is
actually just a text value. Do you have a way to confirm the
underlying
table/field data type?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
I have a sql database that access is using to do some queries on,
in
the
queries there is a date/time field. I've used the datevalue()
function
to
remove the time values. In a different query the user will enter
a
begin
date and an end date when I run the query I get dates that do not
belong,
such as 1/3/2008 to 1/6/2008 I will get records with 1/31/2008.
Also
the
sql
database list the date/time field as 01/01/2008 access list it as
1/1/2008.

Thanks in advance

Jeff

Correct as it is now I get the results that I am looking for, however once I
put in the criteria, between [Enter Start Date] and [Enter End Date] I will
get dates outside the range. I have used the Datevalue() function to remove
the time but I still get dates outside the range.

"Jeff Boyce" wrote:

John

Given the query/SQL you posted, I would expect to see records with
NeuropathyStore.OrderDate1 starting with 1/19/2008 and ending with
1/24/2008. Is this what you're getting when you run what you posted?

By the way, one of the fields in your OrderItems table is named "Name".
This is a reserved word in Access, and will only cause confusion for both
you and Access. Consider changing that fieldname to something other than
this reserved word.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
here is the statement:

SELECT NeuropathyStore.OrderID, NeuropathyStore.CustomerID,
NeuropathyStore.OrderDate1, NeuropathyStore.ShipFirstName,
NeuropathyStore.ShipLastName, NeuropathyStore.ShipCompany,
NeuropathyStore.ShipAddress1, NeuropathyStore.ShipAddress2,
NeuropathyStore.ShipAddress3, NeuropathyStore.ShipCity,
NeuropathyStore.ShipStateProvinceCode, NeuropathyStore.ShipPostalCode,
dbo_OrderItems.UnitPrice, NeuropathyStore.OrderNumberPrefix,
NeuropathyStore.OrderNumberPostfix, NeuropathyStore.OrderNumberDisplay,
dbo_OrderItems.Name, dbo_OrderItems.Code, dbo_OrderItems.Quantity
FROM NeuropathyStore INNER JOIN dbo_OrderItems ON NeuropathyStore.OrderID
=
dbo_OrderItems.OrderID
WHERE (((NeuropathyStore.OrderDate1)#1/18/2008# And
(NeuropathyStore.OrderDate1)#1/25/2008#));


I believe this is what you were asking for.

"Jeff Boyce" wrote:

Please post the SQL statement of the query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
Jeff

The field type indicates that it is a date/time field.

"Jeff Boyce" wrote:

John

Displayed value is not necessarily equal to stored value.

The application may display that, but what is the SQL-Server table's
field's
data type.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
The sql database runs an application and in the application it does
display
the date as 01/01/2008 12:00:00 PM

"Jeff Boyce" wrote:

John

From your description, it almost sounds like the so-called "date"
is
actually just a text value. Do you have a way to confirm the
underlying
table/field data type?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
I have a sql database that access is using to do some queries on,
in
the
queries there is a date/time field. I've used the datevalue()
function
to
remove the time values. In a different query the user will enter
a
begin
date and an end date when I run the query I get dates that do not
belong,
such as 1/3/2008 to 1/6/2008 I will get records with 1/31/2008.
Also
the
sql
database list the date/time field as 01/01/2008 access list it as
1/1/2008.

Thanks in advance

John

I'm at a loss to explain why. If you can "hard-code" dates and get the
correct results, but are trying to use parameter prompts and getting
incorrect results, ...

Hmmm? Maybe Access doesn't know that what you are inputting in response to
the parameter prompt is a date value.

Try this ... Open the query in design view. Click on Query in the menu bar,
then Parameters. Enter the parameter prompts EXACTLY as you have them in
the query (without the square brackets []), then indicate that these are
Date/Time values. Do this once for each parameter.

This may tell Access enough to be looking only for dates.

If this doesn't work, I'd start over again on a new query, from scratch, and
do the above from the beginning.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
news
Jeff

Correct as it is now I get the results that I am looking for, however once
I
put in the criteria, between [Enter Start Date] and [Enter End Date] I
will
get dates outside the range. I have used the Datevalue() function to
remove
the time but I still get dates outside the range.

"Jeff Boyce" wrote:

John

Given the query/SQL you posted, I would expect to see records with
NeuropathyStore.OrderDate1 starting with 1/19/2008 and ending with
1/24/2008. Is this what you're getting when you run what you posted?

By the way, one of the fields in your OrderItems table is named "Name".
This is a reserved word in Access, and will only cause confusion for both
you and Access. Consider changing that fieldname to something other than
this reserved word.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
here is the statement:

SELECT NeuropathyStore.OrderID, NeuropathyStore.CustomerID,
NeuropathyStore.OrderDate1, NeuropathyStore.ShipFirstName,
NeuropathyStore.ShipLastName, NeuropathyStore.ShipCompany,
NeuropathyStore.ShipAddress1, NeuropathyStore.ShipAddress2,
NeuropathyStore.ShipAddress3, NeuropathyStore.ShipCity,
NeuropathyStore.ShipStateProvinceCode, NeuropathyStore.ShipPostalCode,
dbo_OrderItems.UnitPrice, NeuropathyStore.OrderNumberPrefix,
NeuropathyStore.OrderNumberPostfix, NeuropathyStore.OrderNumberDisplay,
dbo_OrderItems.Name, dbo_OrderItems.Code, dbo_OrderItems.Quantity
FROM NeuropathyStore INNER JOIN dbo_OrderItems ON
NeuropathyStore.OrderID
=
dbo_OrderItems.OrderID
WHERE (((NeuropathyStore.OrderDate1)#1/18/2008# And
(NeuropathyStore.OrderDate1)#1/25/2008#));


I believe this is what you were asking for.

"Jeff Boyce" wrote:

Please post the SQL statement of the query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
Jeff

The field type indicates that it is a date/time field.

"Jeff Boyce" wrote:

John

Displayed value is not necessarily equal to stored value.

The application may display that, but what is the SQL-Server
table's
field's
data type.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
The sql database runs an application and in the application it
does
display
the date as 01/01/2008 12:00:00 PM

"Jeff Boyce" wrote:

John

From your description, it almost sounds like the so-called
"date"
is
actually just a text value. Do you have a way to confirm the
underlying
table/field data type?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
I have a sql database that access is using to do some queries
on,
in
the
queries there is a date/time field. I've used the datevalue()
function
to
remove the time values. In a different query the user will
enter
a
begin
date and an end date when I run the query I get dates that do
not
belong,
such as 1/3/2008 to 1/6/2008 I will get records with
1/31/2008.
Also
the
sql
database list the date/time field as 01/01/2008 access list it
as
1/1/2008.

Thanks in advance