How to format Input Box Date

[PNGBill]

Hi Forum,
This input box

  LetterDate = InputBox("Please enter the Date The Lawyers Letter was Posted or Delivered")

Only works if I enter mm/dd/yyyy.
Otherwise it converts my dd/mm/yyyy input into US and of course, 1st Oct is now 10th Jan

Is there a way to format the Input Box so I can enter dd/mm/yyyy with out the "wheels" falling Off??

 

[SpentGeezer]

Hi Forum,
This input box

  LetterDate = InputBox("Please enter the Date The Lawyers Letter was Posted or Delivered")

Only works if I enter mm/dd/yyyy.
Otherwise it converts my dd/mm/yyyy input into US and of course, 1st Oct is now 10th Jan

Is there a way to format the Input Box so I can enter dd/mm/yyyy with out the "wheels" falling Off??

Greetings, what happens to letterdate after it is input? Whenever I need dates I use a combobox which on mousdown opens a calendar object. Then the user clicks a date and it is put into the combobox (and the field in the table). This stops the field being filled with useless formats.

 

[PNGBill]

Thanks SpentGeezer but this is one line in vba code where we need a date to be entered by the user. Not sure a ComboBox can be used but in any case, the inputbox assigns a value to a variable that is used in more then one place in the code.

 

[SpentGeezer]

Sorry dude, mis read you Q....

My solution to your problem. In the VBA instead of opening an inputbox make it open another form with the combobox and calendar object. Then you will always have dd/mm/yyyy. I have attahced a sample. Pretend the click button event is the line of code in your VBA that usually opens the input box.

 

[Dairy Farmer]

Assume Letterdate is an unbound TextBox.
Set the roperties of Letterdate to ShortDate

 

[PNGBill]

Thanks DairyFarmer. LetterDate is a variable in vba code.

The value is assigned from the Input box and used in the code later for sql etc.

At one point it is used as the value in an sql to append a record to a table.

No forms are used for data entry as apart from the date being required, all data is collected by the system.

 

[SpentGeezer]

Not bad, i still think that when dates are involved is far easier in the long run to use the calendar object. Once the form is setup then its only a few clicks of the mouse and your done. No errors! In your case a button labelled "Get Date" or something rather than the combobox! haha

 

[PNGBill]

I have resolved the issue

Left the InputBox to do what it wanted ie use US date.

Edited the codes sql as follows:

From this..

 SqlString = "UPDATE TblLetterSent SET TblLetterSent.LetterDate =#" & LetterDate & "#, TblLetterSent.LetterType = 11, TblLetterSent.LetterSubject = ""Lawyers Letter"" " & _
        "WHERE (((TblLetterSent.LetNumber)=" & LetNumber & "));"

To this.. where LetterDate is now formated as US so the vba code uses US date only. When it goes to the table, it is converted.

 SqlString = "UPDATE TblLetterSent SET TblLetterSent.LetterDate =#" & Format(LetterDate, "mm/dd/yyyy") & "#, TblLetterSent.LetterType = 11, TblLetterSent.LetterSubject = ""Lawyers Letter"" " & _
        "WHERE (((TblLetterSent.LetNumber)=" & LetNumber & "));"

The problem wasn't the actual update of the table but the code not using the same date throughout - I guess.