We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Access date criteria format with ODBC linked tables

jbouz
jbouz asked
on
Medium Priority
472 Views
Last Modified: 2011-10-03
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.
Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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 HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
(slight correction)
WHERE (((dbo_HhSchAppointment.ActualStartDateTime) = DateAdd("d", 1, CDate([Forms]![BillingReports]![MyDate]));

Author

Commented:
Hi,thanks, but it only pulls blank result. The solution does work with Between or >=, but not a single date.
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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")

Author

Commented:
Hi, still not happening. This last one just kept running .
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Try this:

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

/gustav

Author

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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Author

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.

CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Author

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#.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
OK, then this could be used as DateValue rounds off the time:

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

/gustav

Author

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.












CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

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

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

/gustav

Author

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:)
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Then you could try:

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

/gustav

Author

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.  

 
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thanks for all your help, /gustav.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome.

/gustav
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.