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

 

[n1a2v3i3n]

Hi guys,

I got your point and i know that it depends on the system format.

Well if it me i could change the system format on my machine and do it but if this application which i am making is to be used on another computer and that user is not aware of this then he ends up getting wrong dates when he retrives.
So the whole point of mine here is to avoid this.
like is there any way to change format of system date using VB?

 

[HarleyQuinn]

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?

I would be confident that an application I'd written to handle different localities would handle the user changing things (or I wouldn't release it), but I'm from a background where users (such as data entry clerks) only have permissions that they need on machines. Why would they need the ability to change it while in the same locality as the app was built (and if it was built to be used in other localitiers it would handle it)?

I'd be confident that my app was able to handle the situation should it arise but would I be confident that it wouldn't cause a problem to any other app/report/document on the machine? No.

Freedom is great for a lot of things, but not (IMO) for all users on company machines.

I agree with you that a DTPicker would be frustrating to use everytime that the user wanted a date, and also that, properly coded it is the devlopers perogative to take the date input almost any kind of (feasible) control that they wish (sorry, that was kind of inferred from your posts, not taken directly).

I think the most prominent thing to take from this portion of the thread is that we all do things differently, there is not a hard and fast UI standard for taking date input and as long as it's handled properly, results are correct and the users/developers are happy with it then that is the right way to do it

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]

After a bit of a jaunt off topic I'll have a go at getting back on it

So the whole point of mine here is to avoid this.
like is there any way to change format of system date using VB?

Yes there is, but I wouldn't do it that way. While changing the sytem date sounds an ideal solution, it's frowned upon generally as it would become increasingly frustrating to users having to work in different formats than they are used to.

The best way (IMO) is to allow your application to use the users current local settings for numbers/dates. There may be many ways to do this but the way I've always done it is to use the GetLocaleInfo() API, which can return a whole heap of information regarding the settings in use which you can then use in your application.

Generally if you're not distributing the application to places that might use a different locale then not much will change (in thoery), but it would be useful to stop any ambiguity and should lead to a much more robust code base.

Hope this helps

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.

 

[HughLerwill]

When using text boxes for entry of Dates it is difficult to ensure a Date entered is that intended by the user, if the user is given the freedom to enter the date in any format he wishes. However if the App insists the Date is entered in a specific format (which may be configurable in the App) the process is much easier.

So the Text box field label insists and says e.g. "Enter Date dd-mm-yy"
When the text box gains focus it should contain Format$(MyDate, "dd-mm-yy") where MyDate is a Date variable retrieved from a database or a default eg. Now().
When a Date string is entered/ changed and Focus leaves the Textbox
Check it is 8 characters long else message.
Check it is Like "##?##?##" else message.
Turn the ##s re their positions into integers representing day, month, year and check them for validity, as a set, using your own elementary calendar routines which may also convert yys into yyyys.
If all validation passes assign the integers into a proper Date variable using MyDate = DateSerial(Year,Month,Day)
Avoid use of IsDate and Format$ during the validation process.

Optionally and cosmetically
When focus leaves the Text box reassign its contents with Format$(MyDate,"dd mmm yyyy") or some other more desciptive format.

This approach should be independent of Windows Regional settings; however it could possibly be made to 'insist' that Dates are entered in the format specified in e.g. 'Short Date' on the host computer.

 

[SBerthold]

like is there any way to change format of system date using VB

n1a2v3i3n, do not do this automatically!

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

The problem here is the system date format is set to mm/dd/yyyy and you entered a date as dd/mm/yyyy.

27 is not a valid month, so it is being assumed that 27 it is a year in this case.
If you would have entered 27/01/32, it would work and assumes 27 is a day, because 32 can only be a year and 27 cannot be a month.

So, you must enter a date as mm/dd/yy.. which you haven't.
This was the standard country settings choosen when the OS was installed, or it was changed by some user.

If you as a user desire a different format and change the date format under system country settings to dd/mm/yyyy then you will not have a problem entering the date as desired and viewing the date as you would expect.

Another user with a different setting should also then not have a problem if the date is entered in their preferred format, if your code handles the date input correctly.

In your code, if Text1.Text is a valid date according to the system set format, then this

da = Format(Text1.Text, "dd/mm/yy")

may make the problem even worse, such as when the system is set to mm/dd/yyyy,
?Day(Format("06/08/09", "dd/mm/yy"))
returns 6
?Day(cdate("06/08/09"))
returns 8

and is not at all needed if Text1.text is in a proper date format.
Therefore, you only need this:
da= cdate(Text1.Text)

If Text1.Text is a proper input according to the system set format, then all that is needed to transfer the input to a date variable is:
da = CDate(Text1.Text)

 

[SkipVought]

The problem here is the system date format is set to mm/dd/yyyy and you entered a date as dd/mm/yyyy.

Touche!

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

 

[SBerthold]

>Touche!
???

You have got to be kidding!

How come I can type in dates in many programms, but just not yours?

or

Why does [insert favorite application, such as Word, Excel or MS Access table] format my dates wrong?
I insert 27/01/09 and it comes out showing 01/09/2027?

Could the answer be: "MS goofed, and should have used only cal. controls for date input so you had better get used to it?

What would the another answer be?

"our applications are better constructed, therefore we do not allow a user to type in a date"

No Touche.

Just a matter of preference, and what you can sell.

"our applications allow a user to type in a date or pick from a calendar."

 

[SkipVought]

I like...

"our applications will not allow a user to type in an incorrect date. They can pick from a calendar or a drop downs."

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