+ Reply to Thread
Results 1 to 10 of 10

Selection of data using MS Query

  1. #1
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Selection of data using MS Query

    I have a large table of data in random order each row of which is dated. I need to be able to extract from this items which have dates between two givens. I was hoping to do this using MSQuery.
    I can find the table in query and set the date range as the criteria. If I directly type the dates into the criterion area it works. However if I set the criteria using parameters (which I will need to do) I get an error " Invalid Character value for cast specification."
    This error persists whatever format I use for the dates.
    What am I doing wrong?
    John

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Selection of data using MS Query

    Make sure you are entering the date parameter in the right format. For example, in Oracle, unless otherwise specified, it expects dates to be in 'dd-mmm-yyyy' format. Access seems to be a bit more understanding and accept mm/dd/yy or mm/dd/yyyy (on the left side of the pond at least).

    Experiment with different date formats.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: Selection of data using MS Query

    dflak,
    Thanks for your interest. I have become aware of the vagaries of date format requirements. Sometimes MSQuery seems to accept any format, at others it requires yyyy/mm/dd. I have actually tried all reasonable formats for the dates, but always with the same result if these dates were entered as parameters.
    I don't think that this is the prime cause of my problem.
    I realise that I had not made it clear before that the table I wish to query is in Excell. in fact in the same workbook as I am working in. I believe that until a few versions ago it was not possible to use MSQuery in this way with Excel, perhaps there is still a limitation?
    John

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Selection of data using MS Query

    Hi,

    Is there a reason for not using an autofilter and copy/paste? It can be automated of course.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  5. #5
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: Selection of data using MS Query

    xlnitwit,
    There is no reason beyond the fact that I had forgotten that possibility. Thanks fir reminding me
    John

  6. #6
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: Selection of data using MS Query

    xlnitwit
    I have tried automating the autofilter with this code
    Please Login or Register  to view this content.
    It works in the sense that an autofilter happens, but there are no results are selected. I think the Criteria line is wrong, can you help please?
    I have attached a workbook
    John
    Attached Files Attached Files

  7. #7
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Selection of data using MS Query

    Please try this
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: Selection of data using MS Query

    xlnitwit,#Many thanks for your help. Your code did the trick.
    #John

  9. #9
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Selection of data using MS Query

    You're most welcome.

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Selection of data using MS Query

    I agree with the suggestion of copying and pasting a filtered list as preferable to using MS-Query. But FYI: you can use MS-Query against an Excel Spreadsheet including referencing itself.

    Perhaps a different solution would be to define the data either as a table or named dynamic range and use a pivot table. These can be done in a tabular form and with 2010 and later, you can repeat the row headers so it should look like the filtered list.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. With Range Selection Sort Key Query
    By timbo1957 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-08-2014, 05:41 AM
  2. Data Query - How to Change Drop down Selection from Website
    By mattjac1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-13-2013, 04:41 PM
  3. [SOLVED] Range Selection Query
    By timbo1957 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-23-2011, 06:42 AM
  4. How to Query with Date Selection Condition
    By antoni in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-26-2011, 05:22 AM
  5. Combobox Selection To Run Query
    By Mooseman60 in forum Access Tables & Databases
    Replies: 11
    Last Post: 05-13-2011, 07:37 AM
  6. Sheet Selection Query
    By Belgarath75 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-13-2011, 06:02 AM
  7. team selection by web query
    By excellentexcel in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-09-2009, 02:43 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1