Date US/UK format problem

[Les Isaacs]

Hi All
What do I do with this

Dim startdate As Date
    startdate = InputBox("Please enter the start date:")
    DoCmd.OpenReport "rpt_OrdersAnalysis5", acViewPreview, , "[InvoiceDate] > #" & startdate & "#"

to get access to accept that startdate has been entered in UK format?
I've checked that regional settings in control panel are set to UK format.
Hope someone can help - because I've tried everything I can think of (formatting startdate as "dd/mm/yyyy", declaring it as a string, various other delimeters, etc etc), but all to no avail and now this is driving me nuts :banghead:
Thanks for any help.
Les

 

[Minty]

If this is from a form, I would ask for the Startdate in an unbound textbox on the form, and format it to short date. If it's on a form then your date will be interpreted correctly in the local regional settings. It will also allow users to use the datepicker which an input box can't do.

To refer to it assuming your unbound text box was call txtStartDate then do the following;

    Dim startdate As Date
    startdate = Me.txtStartDate

    DoCmd.OpenReport "rpt_OrdersAnalysis5", acViewPreview, , "[InvoiceDate] > #" & startdate & "#"

 

[Ranman256]

UK format , may be just that, the format but not the actual date.
input boxs, nor queries may not know this, so you may need to parse it into US...
try converting it:

sub MySub
Dim startdate As Date
    startdate = InputBox("Please enter the start date:")
    
    vUsDate = CvtUSDate(startdate)
    DoCmd.OpenReport "rpt_OrdersAnalysis5", acViewPreview, , "[InvoiceDate] > #" & vUsDate & "#
end sub


function CvtUSDate(pvUkDate)
 if isNull(pvUkDate) then
    CvtUSDate= pvukdate 
 else
    CvtUSDate= mid(startdate,2) & "/" & Left(startdate,2) & "/" & right(startdate,4)
 endif
end function

 

[Les Isaacs]

Hi Guys

Thanks for your replies, but:

Minty - the date is acquired via an inputbox. I agree that this doesn't allow for a datepicker, but for the purpose I have a form would not be preferable;

Ranman256 - I did wonder whether I'd need to create a function to convert the date, but the problem is that if the user enters, say, 25/4/17, access seems to recognise that this must be in UK format - and so of course the conversion function wouldn't work. Neither would it work if the user enters say 5/4/17, as without the preceding 0s the positions of the /s etc are altered so the Mid function goes haywire!

How can this be so difficult - surely this is a pretty basic requirement?
Thanks again for any help.
Les

 

[Minty]

It is a common problem - book mark and have a read here http://allenbrowne.com/ser-36.html

 

[Les Isaacs]

Hi Minty
I did come across that article from Allen Browne, and tried to implement his suggestions, but still couldn't get it to work!
I have to leave the office now, and won't be back until next week - but will check back here then for any further help!
Thanks all.
Les