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?
 
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
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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);
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(slight correction)
WHERE (((dbo_HhSchAppointment.ActualStartDateTime) = DateAdd("d", 1, CDate([Forms]![BillingReports]![MyDate]));
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
jbouzAuthor Commented:
Hi,thanks, but it only pulls blank result. The solution does work with Between or >=, but not a single date.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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")
0
 
jbouzAuthor Commented:
Hi, still not happening. This last one just kept running .
0
 
Gustav BrockCIOCommented:
Try this:

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

/gustav
0
 
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.
0
 
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
0
 
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.

0
 
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
0
 
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#.
0
 
Gustav BrockCIOCommented:
OK, then this could be used as DateValue rounds off the time:

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

/gustav
0
 
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.












0
 
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
0
 
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:)
0
 
Gustav BrockCIOCommented:
Then you could try:

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

/gustav
0
 
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.  

 
0
 
jbouzAuthor Commented:
Thanks for all your help, /gustav.
0
 
Gustav BrockCIOCommented:
You are welcome.

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.