isdate() in a controls properties

[darbid]

I do not understand why I cannot get this.

Can I use the isdate() function in text box the ValidationRule property?

If yes how? For example a text box named Text0

=isdate([Text0])

 

[JANR]

As far as I know you can't but this in the validationrule of yor control. If you want to chech for valid dates, either set the format property to date/time or set an beforeUpdate event to chech for valid dates.

Private Sub txt1_BeforeUpdate(Cancel As Integer)
    If IsDate(Me.txt1) Then
        MsgBox "it's a date"
        Else
        MsgBox "not a date"
    End If
End Sub

If you set the format property unvalid entries wil trigger an error.

JR

 

[neileg]

I'm confused. If your control is bound to a date/time field then it will only accept a valid date. No validation required.

Isdate() will return a boolean value, true or false. As JANR suggests this can be used in an If() statment.

 

[darbid]

I'm confused.

Safety in numbers

Ok I will extend my thoughts to see what you guys thinks I should do.

I have an existing MDB. I have made it a front end and just now connected it to a SQL Server. It is used by computers with German/US/Japanese/Chinese/UK regional settings.

eg

DE - dd.mm.yyyy
US - mm/dd/yyyy
JP - yyyy/mm/dd
CN - yyyy-mm-dd
UK - dd/mm/yyyy

originally when a date text box got focus a little text helper label was made visible with "dd/mm/yyyy" to help users.

Further there was an inputmask = 99/00/0000;0;_

My solution to this was as follows

I read with the help of an API Call the regional setting of the client. Depending on this I display the appropriate text helper label. Also in the on open event all these controls receive the appropriate inputmask. I do this with the help of an .ini file.

I thought that I should make sure that it is actually a date but wanted to make sure that I do not step on any of the dateformat differences.

As I am really a beginner I just now realised that none of the above stops a user from entering 23/23/2009 or 2009/23/23 or where ever they are in the world.

I think that setting the format property to date/time will work as as far as I can test access takes its formating from the OS regional settings.

 

[neileg]

A date/time value in Access is stored as a decimal number. The integer part gives the number of days since 31 December 1899 and the fractional part is the time represented as a fraction of a day. So as you can see, the stored value is completely independent of the format applied or the regional setting.

However, you are using SQL Server for staorage and that uses a different convention. SQL Server takes into account a system base date of January 1st, 1900. SQL Server stores the datetime data type internally as two 4 byte integers and smalldatetime as two 2 byte integers. The first integer in both the cases stores the number of day difference from the base date. The second integer part stores the number of milliseconds/minutes since midnight.

So I would have been confident of saying that you can let the regional setting deal with how the date is presented if the back end was Access, I don't know if you can do this with SQL.

Sorry, it's just beyond my experience or knowledge.

 

[darbid]

Sorry, it's just beyond my experience or knowledge.

Your telling me. Thanks heaps for letting me know what you know. I am just waiting and seeing what happens. I am testing by changing my computers regional settings to Japanese/Chinese/US/German etc and testing and all seems to be ok. BUt with testing comes questions like What if they do this or what if they do that????? This is where I came up with the problem of dates. What if in one of the unbound search criteria boxes they enter something that is not a date.

Anyway at this stage I just have to make sure that whenever I pass a control or variable to an SQL string either through access linked tables or directly to the sql server that I format it.

 

[JANR]

What if in one of the unbound search criteria boxes they enter something that is not a date.

That's the problem with user's, you can't You could force a strict format as in Inputmask, but as you said how to choose which format to use.

On way could be to create an inputmask table with different type mask's and assign the mask at runtime depending on the systemsettings.

JR

 

[darbid]

You could force a strict format as in Inputmask, but as you said how to choose which format to use. JR

That I have got sorted.
As I said above I have input masks based on the regional settings. Here are the keys from my ini file

usinputdatetype=99/00/0000;0;_
deinputdatetype=99.00.0000;0;_
jpinputdatetype=0000/00/99;0;_
cninputdatetype=0000-00-99;0;_
otherinputdatetype=99/00/0000;0;_

 

[neileg]

Two ways to cheat:
1- use three separate controls to capture day, month, year and concatenate the results how you want them
2- use a calendar control as a date picker