Format Field

[Haytham]

Hi All...
I have a text field with input mask ----/--/--
I want to limit the entry in first four digits b/n. 1900 and 2050
second digits between 01 and 12 and last 2 digits from 07 to 25.
Will anybody help me in that please

 

[cpod]

You have to check field in the textbox. Something like this in the AfterUpdate event of your textbox:

Dim int1 As Integer, int2 As Integer, int3 As Integer
If IsNull(Me!YourTextboxName) Then Exit Sub
int1 = Left(Me!YourTextboxName, 4)
int2 = Mid(Me!YourTextboxName, 6, 2)
int3 = Right(Me!YourTextboxName, 2)
If int1 < 1900 Or int1 > 2050 Or int2 < 1 Or int2 > 12 Or int3 < 7 Or int3 > 25 Then
MsgBox "Field 1 must be between 1900 and 2050" & Chr(13) _
& "Field 2 must be between 1 and 12" & Chr(13) _
& "Field 3 must be between 7 and 25"
Me!YourTextboxName = Null
End If

 

[David R]

You're probably going to be happier in the long run if you make this a Date/Time field, formatted yyyy/mm/dd. This gives you access to the DatePart functions which will make your check a lot simpler. Look up "validate event procedure" on Access help, because you're going to have to use VBA code (or a Macro) in your validation rule.

This should get you started:

Private Sub DateField_BeforeUpdate(Cancel As Integer)
  Dim t as Integer

  t = DatePart("yyyy",[DateField])
    If ((t > 2050) Or (t < 1900)) Then
      MsgBox("Please enter a valid year.")
      Cancel = True
    End If
  
  t = DatePart("d",[DateField])
    If ((t > 7) Or (t < 25)) Then
      Msgbox("Please enter a valid date.")
      Cancel = True
    End If

End Sub

HTH,
David R

 

[David R]

If this is a bound form, AfterUpdate is going to be too late, because the field will already have been saved.

 

[Haytham]

cpod, Thank you for answer, your code helped me. It works well but not for the statement:
int2 = Mid(Me!ExpDate,6,2). If I put 12 for example it'll be Ok. But if I put 20 it accepts it as 2 which I don't want.. Any idea please..

David R, Thanks for answer, actually my field is supposed to be date field. But the problem is that I have to type it in a format which is not available in the Regional settings of my PC.
I'm restricted to a government paper which accept the date in such format yyyy/mm/dd.
That's why I decided to create a text field for that, but again I'll not be able to do any date calculation for that field.
Your code works well.
Ok, is it possible to make it date field and then display it in the given format..
i.e. yyyy/mm/dd
Very thankful for both of you for your great help.
Yours

 

[David R]

You have to type it in in a certain format? Man and I thought working for the City was rough...

Seriously though, you're not restricted to just the display formats under the drop down list. You can make your own format, i.e. in the table structure, Format: yyyy/mm/dd
Take off the input mask altogether. It's not needed, and gives you the flexibility to enter it however you want.
For instance, with just the format set as above, I just entered:
12/03/2002
12/3/2002
2002/12/03
2002/12/3

And they all came out the same: 2002/12/03

Cheers,
David R

P.S. My computer regional settings are M/d/yyyy, and I'm using Acc2k, Win2k.

[This message has been edited by David R (edited 01-16-2002).]

 

[Rich]

Only problem with that Dave is that 29/02/02 comes out as 2029/02/02 if somebody's entering as dd/mm/yy.

 

[David R]

Aren't Access' automatic sensing of date formats linked to your Regional settings? Because mine are American-based I wouldn't even try to put it in that way.

That is a good point though, and a case against entering two-digit dates. I couldn't get the input mask to work with the Format Rich, did you have another way to enforce yyyy/mm/dd?

 

[Haytham]

Still the Format required can't be achieved. I created a date field with no input mask.
In form how can I apply the format David Please

 

[Rich]

OK Set the Format to the date field to yyyy/mm/dd, set the input mask to 0000/99/99;0;_
Add the following er trapping
Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2279 Then
Me.txtDate.Undo
'Display Error Message
MsgBox "Please enter dates as yyyy/mm/dd"
Response = acDataErrContinue
'Prevent the standard error message from showing
txtDate.SetFocus
Else
If DataErr = 2113 Then
Me.txtDate.Undo
MsgBox "The value you have entered is not a valid date!"

Response = acDataErrContinue
Else
'If another error is applied then show the standard error message
Response = acDataErrDisplay
End If
End If
End Sub
Should cover most errors.
HTH

 

[Haytham]

A great help from a great person.It works like a charm.. Thank you Rich. And thank you all David R and cpod.

 

[Pat Hartman]

You do all realize I hope that neither the format or the mask have any effect at all on how the date is actually stored. Therefore, you should let the date be entered using the standard regional settings because that is what the users are most familiar with. You can format the date for export or display using any format that the feds require.

 

[Rich]

Surely someone developing a database for use world wide has more chance of trapping incorrect date entries by forcing a universally applied format then the hit and miss chance of users having the correct regional settings, how would one define an input mask?

 

[Haytham]

Hi Rich, I have something again please.
In my form, I have several Date Fields.So applying the code will act on all of them. The point is that when I out Me.FieldName.Undo, Should I specify all the fields name or Should I create a module and Call the function on all BeforeUpdate of every fields...
Help is expected
Thank you

 

[Rich]

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2279 Then
Screen.ActiveControl.Undo
MsgBox "Please enter dates as yyyy/mm/dd"
Response = acDataErrContinue
Else
If DataErr = 2113 Then
Screen.ActiveControl.Undo
MsgBox "The value you have entered is not a valid date!"
Response = acDataErrContinue
Else
Response = acDataErrDisplay
End If
End If
End Sub

 

[Haytham]

Hi Rich,
When I apply the code:
Screen.ActiveControl.Undo
It gives me
MsgBox "Please enter dates as yyyy/mm/dd"
for the first field date.For all other Date fields, it gives me:
MsgBox "The value you have entered is not a valid date!"

I think a minor trick is there.
But still, it works well.
Thanks a lot Rich.

 

[Rich]

I'm unable to reproduce the same problem here so I can't offer any suggestions sorry.

 

[Haytham]

Rich, Thank you for all your help and guidance.
Highly appreciated.