Date format in SQL Expression within VBA

[Statistician]

Hi all,

I am using Excel VBA to create a query in Access but am having problems with the date field which is a required input in Excel via a InputBox statement.

Is there a way to set the format of the inputbox data entered by the user within an SQL statement in Excel VBA?

Basically I want to set the format of the date entered by the user to a short date format.

Can anyone help me with this?

Thanks for any support

Dee

 

[mincefish]

I hope I've got the right end of the stick here.

You could try formatting the returned value from the input box - I assume you have dim'd the value it returns as a Variant?

Dim dteReponse as Variant

dteResponse = InputBox "Blah, Blah"....

dteResponse = format(dteResponse, "dd/mm/yyyy")

You can specify pretty much any date format in there - "DDMMMYY", etc....

 

[Statistician]

Thanks for your help. I might give that a go when I get more time. I actually resolved the problem by defining the date as long date and that sorted the problem out.

Perhaps it has something to do with Access 97 and date conventions. Who knows..... but many thanks for responding!

Dee