Access date criteria format with ODBC linked tables

Hello, I'm using Access to query SQL server tables, which are ODBC-linked to the .mdb file.  

I have a form where the user enters Begin and End  dates for a report.
 
This query works fine...
SELECT DateValue([ActualStartDateTime]) AS Expr1
FROM dbo_HhSchAppointment
WHERE (((dbo_HhSchAppointment.ActualStartDateTime) Between CDate([Forms]![BillingReports]![Begin]) And CDate([Forms]![BillingReports]![End])+#12/30/1899 23:59:59#));
If I modify it to  >= , it also returns results.

But I'm trying to get it that it will only query one date-- no date ranges, Between or Comparisons operators, and disregarding the time.

I would think the following below modification would work, but  it only returns a blank result.

SELECT DateValue([ActualStartDateTime]) AS Expr1
FROM dbo_HhSchAppointment
WHERE (((dbo_HhSchAppointment.ActualStartDateTime) = CDate([Forms]![BillingReports]![MyDate])+#12/30/1899 23:59:59#));

I've tried many different variations on the above, using Format, DateValue, CDate, but still having no luck. Any help would be appreciated. Thanks.
LVL 1
jbouzAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
>CDate([Forms]![BillingReports]![MyDate])+#12/30/1899 23:59:59#));
You'll need to use the DateAdd function to add date...

WHERE (((dbo_HhSchAppointment.ActualStartDateTime) = DateAdd("d", CDate([Forms]![BillingReports]![MyDate]), 1);
Jim HornMicrosoft SQL Server Data DudeCommented:
(slight correction)
WHERE (((dbo_HhSchAppointment.ActualStartDateTime) = DateAdd("d", 1, CDate([Forms]![BillingReports]![MyDate]));
jbouzAuthor Commented:
Hi,thanks, but it only pulls blank result. The solution does work with Between or >=, but not a single date.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Jim HornMicrosoft SQL Server Data DudeCommented:
>and disregarding the time.
I missed this one.   If these date columns have times as well, then if you want to search for just the date part you'll need
WHERE Format(dbo_HhSchAppointment.ActualStartDateTime, 'Short Date') = Format(DateAdd("d", 1, CDate([Forms]![BillingReports]![MyDate])), "Short Date")
jbouzAuthor Commented:
Hi, still not happening. This last one just kept running .
Gustav BrockCIOCommented:
Try this:

  SELECT
    [ActualStartDateTime]
  FROM
    dbo_HhSchAppointment
  WHERE
    DateValue([ActualStartDateTime]) = Format([Forms]![BillingReports]![MyDate], "\#m\/d\/yyyy\#");

/gustav
jbouzAuthor Commented:
Hi /gustav, when i run it I get the message...
"This expression is typed incorrectly, or it is too complex to be evaluated..."

I've tried to modify it a bit also, but still the same trouble.
Gustav BrockCIOCommented:
You could try one or both of these two changes:

  PARAMETERS [Forms]![BillingReports]![MyDate] Date;
  SELECT
    [ActualStartDateTime]
  FROM
    dbo_HhSchAppointment
  WHERE
    DateValue([ActualStartDateTime]) = Format([Forms]![BillingReports]![MyDate], "\'m\/d\/yyyy\'");

or, if [ActualStartDateTime] is of DateTime datatype:

  PARAMETERS [Forms]![BillingReports]![MyDate] Date;
  SELECT
    [ActualStartDateTime]
  FROM
    dbo_HhSchAppointment
  WHERE
    [ActualStartDateTime] = Format([Forms]![BillingReports]![MyDate], "\'m\/d\/yyyy\'");

/gustav
jbouzAuthor Commented:
I couldn't get either to work, still getting the "Too complex to be evaluated" error message. Don't know if I'm doing something wrong.

I realize if I take the one I know that works with Between...

 Between CDate([Forms]![BillingReports]![Begin]) And CDate([Forms]![BillingReports]![End])+#12/30/1899 23:59:59#));

I can just have the textbox [End] =[Begin] and  hide [End] on the Form, that way the user will still have the advantage of having only one date to type in, but it will "fool" the Access query.  Kind of poor man's way out, I know.

I appreciate  jimhorn's and  /gustav help on this.
I will leave question open for a little while, in case any other ideas in.

Gustav BrockCIOCommented:
Try this with a fixed date you know exists:

 SELECT
    [ActualStartDateTime]
  FROM
    dbo_HhSchAppointment
  WHERE
    [ActualStartDateTime]) = #3/23/2006#;

  SELECT
    [ActualStartDateTime]
  FROM
    dbo_HhSchAppointment
  WHERE
    [ActualStartDateTime]) = '3/23/2006';

If these still are "too complicated" something else is wrong.

/gustav
jbouzAuthor Commented:
Hi, thanks for your continued help.
this works...
SELECT
 [ActualStartDateTime]
  FROM
    dbo_HhSchAppointment
  WHERE
    [ActualStartDateTime]) = #3/23/2006#;

provided I have a 3/23/2006 (no time) in the table; but all these dates are time stamped. For instance, the following will produce a bunch of rows

SELECT
 [ActualStartDateTime]
  FROM
    dbo_HhSchAppointment
  WHERE
    [ActualStartDateTime]) = #3/23/2006 12:12:00 PM#;

but the obvious problem here is that a user would have to know the exact time and be able to input correctly, spaces and all.

I'm trying to get it that will pull everything that is 3/23/2006 (ignore the time) on a single input of #3/23/2006#.
Gustav BrockCIOCommented:
OK, then this could be used as DateValue rounds off the time:

  WHERE
    DateValue([ActualStartDateTime])) = #3/23/2006#;

/gustav
jbouzAuthor Commented:
For above, I get "Data type mismatch in criteria expression."

If I modify with the Select clause with DateValue , I get the same thing, such as
SELECT DateValue([ActualStartDateTime]) As Expr
FROM dbo_HhSchAppointment
WHERE DateValue([ActualStartDateTime])= #3/23/2006#;

If I surround it with  commas, I get the same message. If I start fomatting
it leads me back to where we started on the first post.












Gustav BrockCIOCommented:
This should do where Int() strips the time part:

SELECT DateValue([ActualStartDateTime]) As Expr
FROM dbo_HhSchAppointment
WHERE Int([ActualStartDateTime])= #3/23/2006#;

/gustav
jbouzAuthor Commented:
This is the message I get

ODBC call failed

[Microsoft][ODBC SQLServer Driver][SQL Server]Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query (#257)

I tried using CInt to see if that would satisfy the CONVERT, and get
"Invalid Use of Null"

I think I've caught just about every error:)
Gustav BrockCIOCommented:
Then you could try:

SELECT DateValue([ActualStartDateTime]) As Expr
FROM dbo_HhSchAppointment
WHERE CInt(Nz([ActualStartDateTime], 0))= #3/23/2006#;

/gustav
jbouzAuthor Commented:
This above produced an 'Overflow' error. I tried CLng as well and the query just kept running for too long.  I eventually had to break it.
It seems anything with Convert function, it has trouble.  

 
Gustav BrockCIOCommented:
Then you have many records and some have Null for date.

You'll have to follow the advice on using Convert.
An intro can be found here:

http://www.databasejournal.com/features/mssql/article.php/2197931

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jbouzAuthor Commented:
Thanks for all your help, /gustav.
Gustav BrockCIOCommented:
You are welcome.

/gustav
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.