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

[Jambi46n2]

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.

 

[Akuini]

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.

Sub tryDate()
Range("I4") = CDate(Range("I4"))
End Sub

 

[Akuini]

Wait, what happened if you use date format?

The format of the cell is text.

 

[Jambi46n2]

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.

 

[Akuini]

Did you try the code in post #2 ?

 

[Jambi46n2]

Did you try the code in post #2 ?

I tried it just now. Unfortunately it wasn't success.

Here was my result:

 

[Jambi46n2]

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.

<a href="https://ibb.co/PTvt2qV"><img src="https://i.ibb.co/4snJbh0/NOTworking-shot.png" alt="NOTworking-shot" border="0"></a>

 

[Jambi46n2]

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

Sub tryDate()
Range("I4") = CDate(Range("I4"))
Selection.NumberFormat = "yyyy-mm-dd"
End Sub

 

[Jambi46n2]

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.

<a href="https://ibb.co/m8rsHKj"><img src="https://i.ibb.co/tXwfb6G/working-shot.png" alt="working-shot" border="0"></a>