Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2015
    Posts
    3

    Unanswered: Query Date Range In An ODBC Linked Table

    I've got a table setup to an ODBC connection [CSAT]. The table is a linked table. The table has over a million records so i'm trying to filter out the unnecessary data by filtering by date.. When I set criteria on the date column, it ignores my criteria and excutes the queury.

    If import the data from the ODBC source into a local table [CSAT Import], the query executes flawlessly. Query the linked table and it ignores the criteria. I've tried several different was and it just ignores it.

    Working code on local table:
    SELECT [CSAT Import].Account, [CSAT Import].[Create Date/Time]
    FROM [CSAT Import]
    WHERE ((([CSAT Import].[Create Date/Time])>=#6/1/2015#));

    Non-Working code on linked table.
    SELECT CSAT.Account, CSAT.[Create Date/Time]
    FROM CSAT
    WHERE (((CSAT.[Create Date/Time])>=#6/1/2015#));

    I used the Year and Month functions to pull just the month and just the year of [Create Date/Time]. When I use the functions I can get it to return the month numbers. If I add the desired month number and desired year number to the criteria, it filters the data but not accurately. It went from over a million records to just under

    New Code
    SELECT CSAT.Account, CSAT.[Create Date/Time], Month([Create Date/Time]) AS [Month], Year([Create Date/Time]) AS [Year]
    FROM CSAT
    WHERE (((CSAT.Account)="Surgical Care Affiliates") AND ((Month([Create Date/Time]))=1) AND ((Year([Create Date/Time]))=2015));

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    It should work either way, Odbc or not.
    Instead of using date delimiters #, try string "
    If that don't work, try converting the date to its number equivalent.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ...getting odd results?
    well it could be down to using reserved words (or in your case reserved symbols) in your column names
    I can't remember but you may need to be consistent if you are delimiting table and column names. ie if you use square brackets for the table name, then use square brackets for the column name(s)

    ..one way of diagnosing f this is a fault with linked tables would be to create a query which aliases the table and column anems contianing reserved symbols
    Code:
    SELECT CSAT.Account, CSAT.[Create Date/Time] as create_date
    FROM CSAT
    then save that query, say as mynewquery, and apply you date limiter to a new query
    Code:
    SELECT Account, create_date
    FROM mynewquery
    WHERE create_date >= #6/1/2015#;
    it could be to the design of the underlying table... is the linked table using a datetime column datatype?
    its possible its becuase you are usign ODBC there is somethign odd going on int he interface


    you may need to explicitly cast the date
    Code:
    SELECT CSAT.Account, CSAT.[Create Date/Time]
    FROM CSAT
    WHERE (((cdate(CSAT.[Create Date/Time]))>=#6/1/2015#));
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2015
    Posts
    3
    Quote Originally Posted by ranman256 View Post
    It should work either way, Odbc or not.
    Instead of using date delimiters #, try string "
    If that don't work, try converting the date to its number equivalent.
    When I tried this, it is now telling me Data Type Mismatch.

  5. #5
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    not very forgiving is it?

  6. #6
    Join Date
    Jun 2015
    Posts
    3
    Unfortunately no mater what I tried, when applying the criteria it would not work. I did find another method to get the desired results.

    Instead of doing a select data query, I did a make data query. The query successfully created a new table with the desired criteria and columns.

    The only issue I have now is that I would like to run this query every month and have it not overwrite the previous query. Is there a way to make it prompt for a new table name when running this query? I know how to make it prompt for criteria to filter by each time but that's it. Also to be clear, i'm a novice with Access.

  7. #7
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    JJ,

    Instead of using the # as a delimiter for the date try single-quotes.

    WHERE cdate(CSAT.[Create Date/Time]) >= '6/1/2015';

    Wayne

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I would use the ISO format for the date and try:
    Code:
    SELECT [CSAT Import].Account, [CSAT Import].[Create Date/Time]
    FROM [CSAT Import]
    WHERE ((([CSAT Import].[Create Date/Time])>='2015-06-01'));
    If it does not work, I would force the conversion to a known format (this can be very slow!) and try:
    Code:
    SELECT [CSAT Import].Account, [CSAT Import].[Create Date/Time]
    FROM [CSAT Import]
    WHERE (((Format([CSAT Import].[Create Date/Time], 'yyyy-mm-dd'))>='2015-06-01'));
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •