?
Solved

Access date criteria format with ODBC linked tables

Posted on 2006-03-22
20
Medium Priority
?
430 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.
0
Comment
Question by:jbouz
  • 9
  • 8
  • 3
20 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 16260828
>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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 16260849
(slight correction)
WHERE (((dbo_HhSchAppointment.ActualStartDateTime) = DateAdd("d", 1, CDate([Forms]![BillingReports]![MyDate]));
0
 
LVL 1

Author Comment

by:jbouz
ID: 16261282
Hi,thanks, but it only pulls blank result. The solution does work with Between or >=, but not a single date.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 66

Expert Comment

by:Jim Horn
ID: 16261331
>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
 
LVL 1

Author Comment

by:jbouz
ID: 16261686
Hi, still not happening. This last one just kept running .
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 16267371
Try this:

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

/gustav
0
 
LVL 1

Author Comment

by:jbouz
ID: 16271283
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 16271758
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
 
LVL 1

Author Comment

by:jbouz
ID: 16273842
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 16278750
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
 
LVL 1

Author Comment

by:jbouz
ID: 16283037
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 16283173
OK, then this could be used as DateValue rounds off the time:

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

/gustav
0
 
LVL 1

Author Comment

by:jbouz
ID: 16284116
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 16287607
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
 
LVL 1

Author Comment

by:jbouz
ID: 16302370
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 16308045
Then you could try:

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

/gustav
0
 
LVL 1

Author Comment

by:jbouz
ID: 16313224
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
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 1500 total points
ID: 16313796
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
 
LVL 1

Author Comment

by:jbouz
ID: 16327601
Thanks for all your help, /gustav.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 16330257
You are welcome.

/gustav
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question