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

Jambi46n2

Active Member
Joined
May 24, 2016
Messages
260
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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:
Upvote 0
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.
 
Upvote 0
Did you try the code in post #2 ?
 
Last edited:
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top