Passing dates as criteria in queries

[TraceyB]

I am trying to use Excel to create criteria for a query
one of my criteria is a date;
I have at present the following formulae in the cell:
="[Response History.ResponseDate] = "&C1&C6&C1
Where cell C1 contains quote marks "
and Cell C6 contains a date "10/12/2007" (picked by user from a picklist)

the formulae returns the following value:
[Response History.ResponseDate] = "39426"

when what I really need it to return is:
[Response History.ResponseDate] = "10/12/2007"
so that I can use it with my software addin to pull back required data from the datasource.

can anyone assist with me getting the value i need please
I am happy to build any functions into VBA code if it is not possible in just excel formulae.

Thanks in advance

 

[DonkeyOte]

removed original post -- not VBA.

You could try

=CHAR(34)&TEXT(C6,"DD/MM/YYYY")&CHAR(34)

 

[TraceyB]

Thank you, this is just what I was looking for
I have spent hours on this today and have tried everything except this!!!
thanks again
Tracey