Using Data Validation Drop Down List on a Workbook With ODC SQL Connection
Results 1 to 9 of 9

Thread: Using Data Validation Drop Down List on a Workbook With ODC SQL Connection
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular Jambi46n2's Avatar
    Join Date
    May 2016
    Location
    USA
    Posts
    224
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Using Data Validation Drop Down List on a Workbook With ODC SQL Connection

    I'm working with a workbook that allows users to enter a Start Date, and End Date in two different cells.

    My objective is to display a list with Data Validation to select the date range without having to key it in manually.

    The problem I'm having is because the workbook is connected to an ODC SQL Query,
    Excel converts the dates into a format that the Server doesn't recognize causing the Query to fail.

    When typing in the dates manually it works fine. The format of the cell is text.

    Any thoughts on getting this to work and avoiding the change after a selection is made noted in the screen shot below?




    This is what happens AFTER a date is chosen from the Drop Down List.

    Last edited by Jambi46n2; Aug 16th, 2019 at 12:55 PM.
    Using Microsoft Office 365 ProPlus on Windows 10

  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,949
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Using Data Validation Drop Down List on a Workbook With ODC SQL Connection

    First, try this code below to change the result in I4 to date format, see whether it works.
    If it works then we will need to use it in 'Private Sub Worksheet_Change', so it will be done automatically. Let me know if you need help to do that.

    Code:
    Sub tryDate()
    Range("I4") = CDate(Range("I4"))
    End Sub
    Last edited by Akuini; Aug 16th, 2019 at 06:48 PM.

  3. #3
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,949
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Using Data Validation Drop Down List on a Workbook With ODC SQL Connection

    Wait, what happened if you use date format?
    The format of the cell is text.
    Last edited by Akuini; Aug 16th, 2019 at 07:19 PM.

  4. #4
    Board Regular Jambi46n2's Avatar
    Join Date
    May 2016
    Location
    USA
    Posts
    224
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Data Validation Drop Down List on a Workbook With ODC SQL Connection

    Quote Originally Posted by Akuini View Post
    Wait, what happened if you use date format?
    It appears it just spins into the eternal wheel of death when it's under the date format.

    I believe the TEXT format in the cell is required with the ODC connection to pull the query correctly.

    Thanks for responding! I'm open to any other suggestions.
    Using Microsoft Office 365 ProPlus on Windows 10

  5. #5
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,949
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Using Data Validation Drop Down List on a Workbook With ODC SQL Connection

    Did you try the code in post #2 ?
    Last edited by Akuini; Aug 19th, 2019 at 11:42 AM.

  6. #6
    Board Regular Jambi46n2's Avatar
    Join Date
    May 2016
    Location
    USA
    Posts
    224
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Data Validation Drop Down List on a Workbook With ODC SQL Connection

    Quote Originally Posted by Akuini View Post
    Did you try the code in post #2 ?
    I tried it just now. Unfortunately it wasn't success.

    Here was my result:

    Last edited by Jambi46n2; Aug 19th, 2019 at 12:30 PM.
    Using Microsoft Office 365 ProPlus on Windows 10

  7. #7
    Board Regular Jambi46n2's Avatar
    Join Date
    May 2016
    Location
    USA
    Posts
    224
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Data Validation Drop Down List on a Workbook With ODC SQL Connection

    It did initially change the format, but the perimeters must be in "YYYY-MM-DD" format.
    The code you provided changed it to "M-D-YYYY" format.

    NOTworking-shot
    Using Microsoft Office 365 ProPlus on Windows 10

  8. #8
    Board Regular Jambi46n2's Avatar
    Join Date
    May 2016
    Location
    USA
    Posts
    224
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Data Validation Drop Down List on a Workbook With ODC SQL Connection

    Tried this to see if it would work but the Format did not change.

    Code:
    Sub tryDate()
    Range("I4") = CDate(Range("I4"))
    Selection.NumberFormat = "yyyy-mm-dd"
    End Sub
    Using Microsoft Office 365 ProPlus on Windows 10

  9. #9
    Board Regular Jambi46n2's Avatar
    Join Date
    May 2016
    Location
    USA
    Posts
    224
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Data Validation Drop Down List on a Workbook With ODC SQL Connection

    Got it to work!

    I was experimenting with my Validation Selection, and created an additional column converting the dates to TEXT in the "YYYY-MM-DD" format.

    Now it works perfectly.

    I appreciate your responses, it helped me come to a conclusion that otherwise wouldn't have happened.

    working-shot
    Using Microsoft Office 365 ProPlus on Windows 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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