Date insertation using text box

[n1a2v3i3n]

I have tried inserting date using Combobox (which was posted previously) Now i would like to use text box to insert date and then retrive baxk from the access database. I tried as follows:

Dim Adodc1 As New ADODB.Recordset
Dim str As String
Dim da As Date
Dim ans As String
Dim d As String



str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
App.Path & "\Ecmdetail.mdb;Persist Security Info=False"
Adodc1.Open " select * from ecm ", str, adOpenDynamic, adLockOptimistic
da = Format(Text1.Text, "dd/mm/yy")

Adodc1.Find "ecmserial like '" & txtecmsno & "'"
If Adodc1.EOF Then
With Adodc1
.AddNew
MsgBox " Date is " & da
!daterem = da
MsgBox " Date retrived from database is " & !daterem
!vechno = txtvecno
!ecmserial = txtecmsno
!ECMpart = txtecmpno
!fault = txtflt
!Condition = Comcon

.Update
.Requery
End With
Here the date is being inserted for example i am entering 27/01/09, while it is saved as 01/09/2027.
Could any one tell wht is the problem

 

[SkipVought]

Hi,

Your "date" in the textbox is TEXT and not a date.

How can you be sure what 'format' or values the user enters in the textbox?

Why not use month, day and year combos? Then convert those values to a date using the DateSerial function, for instance.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, you cannot use TEXT in Format function. The Format function formats numbers. Date is a NUMBER.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

to clarify, you cannot format a string to produce a date using the Format function. The Format function returns a STRING.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[HarleyQuinn]

Skip,

Format() can format strings...

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Missed your caveat there mate

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[SkipVought]

Can format strings, but a string is not a date.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[HarleyQuinn]

Agreed.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[SBerthold]

If IsDate(Text1.Text) then

Dim da As Date
da = CDate(Text1.Text)
!daterem = da

'or
'!daterem = Format$(Text1.Text, "yyyy-mm-dd")
End if

 

[SkipVought]

then you get into the issue that the user entered 1/9/2009, which can be verified as a date using the IsDate function.

But did the user intend the date to be January 9 or September 1?

There is no UserIntent function.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

I would strongly suggest to avoid allowing user type date in the text box. You will never know if what they typed is what they actually wanted. Even if you show them the format of the date (MM/DD/YYYY) you may end up with wrong dates.

Use Calendar control, DTPicker or MonthView control so user can choose (pick) from the (hopefully familiar) calendar interface. And, as a bonus, no date validation is required, and your date is ALWAYS in correct format.

You may display it in the text box, or a label.


Have fun.

---- Andy

 

[SBerthold]

then you get into the issue that the user entered 1/9/2009, which can be verified as a date using the IsDate function.

But did the user intend the date to be January 9 or September 1?

There is no UserIntent function.

Yes there is.
It is set by the user in the country settings or user specified Date format setting there, and the default is their country date default settings.
If the user entered 1/9/2009 and the system is set to dd/mm/yyyy, then guess what?
Either it is correct, or they made a typo. It is not a different intent than what the system is set at.
And, they could just as well made a click error in the DTPicker.

This is the format that the user should be entering, because it is the format they have choosen.

If your application is built right, then whatever the user has determined their country and Date settings are to be, your application will still work, as long as the user enters it in that format. If they do not, then it isn't a valid date. Period.
This is all that matters.

Using IsDate(), Date variables, Command objects and parameters, and formatting dates as yyyy-mm-dd or yyyymmdd for use in SQL plain text strings should take care of most situations.

If they enter 1/9/2009 and their system settings are mm-dd-yyyy, then what they have entered is 9 Jan 2009.

If they enter 1/9/2009 and their system settings are dd-mm-yyyy, then what they have entered is 1 Sept 2009.

Not more, not less.

If they goof and enter 1/31/2009 when their system is set to dd-mm-yyyy, then the only logical thing they could mean is 31/1/2009, and the IsDate() works, and the Date variable and CDate() will take care of the rest for you.

This is the proper way of handling these dates, and therefore will work when using text boxes for dates.

My users would go bonkers if they had to use the DTPicker or similar each time, when they need to enter much much data, line after line, fast.

 

[SkipVought]

There is no UserIntent function.

Yes there is.
It is set by the user in the country settings or user specified Date format setting there, and the default is their country date default settings.

Well maybe for THAT user, but not for THIS user who does not know or care about THAT user's regional setting assumptions.

I've seen USA users "drive on the left" by using DMY formats. There goes THAT user's assumptions.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SBerthold]

Well maybe for THAT user, but not for THIS user who does not know or care about THAT user's regional setting assumptions

nope, strongly disagree.
THIS and THAT user are the same on the same machine logged in under the same user.
If not, then what ever the format settings are, then that is the only format to be accepted.

It is not the same as driving a car on the wrong side of the road. No it is not.

If for some reason, this log-in is used by several users, then if needed, the system set date format can be shown to the right of the edit box.

So, do you force all data entry clerks entering thousands of records a day to use a Date picker one or more times for each record?

 

[SBerthold]

And, if that be the case, what you are saying, what about currency settings and decimal settings?

If a user types 234.986, is that
two hundred thirty four decimal 986?

Or is that
two hundred thirty four thousand nine hundred eighty six?

Depending on these user country settings, it could be determine as either or.

It is no different. But it seems that the ones saying to use a DTPicker, never have a problem with the decimal separators and with using text boxes with numbers.

These are all settings each individual logged on user can set for their own preference, independent from the maching installed OS default.

 

[HarleyQuinn]

I think the currency/decimal point is a bit out of the scope of this to be honest (providing you're not developing apps that will be used in different areas likely to use those settings in which case it is very relevant).

It's quite feasible that a user in the same country will enter a date differently than another (saw it today) where as it's alot less likely that users will change there local settings to change the decimal seperator if in the same locality (at least it is here in the UK).

Also, if data input is the app we're using as an example I would generally think that the currency would be specified (which could easily change internationally) i.e. "GDP In Pounds Sterling (£)" in which case it wouldn't be a problem for the end user as they would only input the numbers.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Also, a data entry clerk? I would generally hope that the machine would be locked down sufficiently to stop them changing their own decimal seperators etc.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[SkipVought]

Never seen that one confused on this side of the pond.

But 123,45 just would not compute. It's there. Nothing has been CONVERTED.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SBerthold]

HarleyQuinn, your right about the currency, I was basically thinking numbers.

But 123,45 just would not compute. It's there. Nothing has been CONVERTED.

Sure it will compute. Change the separators for thousands and decimals around and 123.45 will turn to 12345, and 123,45 is 123decimal45.

I would generally hope that the machine would be locked down sufficiently to stop them changing their own decimal seperators

Why?
Because the application wasn't written to handle it, though written to be used by different people in different countries, or with different user settings as this?



Look, you people can do as wish.

I do not see where the problem is using a edit box.
If you have coded correctly to handle the user input, then there is no problem.

If you and your users are happy just using Picker controls, then fine.
But there is absolutely nothing wrong with using an edit box for date input either.

 

[JoeAtWork]

SBerthold - in a perfect world, all users would know that there is such a thing as regional settings, which includes a "short date" setting, and they would all be aware that it is this setting that determines how their date inputs will be interpreted by the operating system. But in reality, there are very few who do. "Users" rarely know or care about these details. If they did, they wouldn't be "users", they would be "techies".

I have used text boxes for date entry, but once the user leaves the control I have it format it as "dd-mmm-yyyy" - that leaves no ambigiouty as to how the date is going to be interpreted when they hit SAVE.