Date issue

[John]

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 Boyce]

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]

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]

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]

Jeff

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

 

[Jeff Boyce]

Please post the SQL statement of the query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John]

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]

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]

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]

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

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.

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