Field constraint

  • Thread starter Thread starter gsnidow via AccessMonster.com
  • Start date Start date
G

gsnidow via AccessMonster.com

Greetings all. I need help making sure text entered in a certain field is in
the correct form. I am working with a batch number that is simply the date,
including hour and minutes, with no special characters. For example the
batch number for September 24 2007 10:30 am would be 0924071030.
The following is a double click event of a datasheet where the user double
clicks the "batch" field, and a popup opens with the batch from the datasheet
displayed in the patch field. I want to make sure that a valid batch number
is entered, but I do not know how to exclude letters, for example, or a
batch like 1301011200, where the '13' would not be a valid month. Any ideas?
I am using Access 2003 ADP with SQL Server 2000. Thank you.

Private Sub BATCH__DblClick(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rstblreel_notes As ADODB.Recordset
Dim strSQL As String
Dim strSQLI As String
Dim boolDupId As Boolean

'SQL to check if batch number already exists in the reel_notes table
strSQL = "Select batch FROM tblreel_notes where batch = '" & Me.
BATCH_.Value & "'"
'SQL to insert the record into the reel_notes table if it does not
already exist
strSQLI = "INSERT INTO tblreel_notes (reel_num,batch,reel_id) " & _
"VALUES ('" & Me.REEL.Value & "','" & Me.BATCH_.Value & "',
'" & Me.id.Value & "') "

boolDupId = False
Set rstblreel_notes = New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
rstblreel_notes.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic

'============== Need Help here ===========================

'Right now I can only see if the batch is 10 digits long,
'but I would like to make sure it is valid in that it is a
'valid date and minute combination. Is this possible?
If Len("" & Me.BATCH_) <> 10 Then
MsgBox "Enter a valid batch#", vbOKCancel
Else
'============== the rest is good =========================

'Insert the record
If rstblreel_notes.EOF Then
boolDupId = False
DoCmd.RunSQL strSQLI
Else
'Open the form
If Not CurrentProject.AllForms("frmCable_Reel_Notes2").
IsLoaded Then
stDocName = "frmCable_Reel_Notes2"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End If
Forms!frmCable_Reel_Notes2.Requery
If CurrentProject.AllForms("frmCable_Reel_History").IsLoaded Then
Forms!frmCable_Reel_History.Requery
End If
End If
End Sub
 
Greetings all. I need help making sure text entered in a certain field is in
the correct form. I am working with a batch number that is simply the date,
including hour and minutes, with no special characters. For example the
batch number for September 24 2007 10:30 am would be 0924071030.
The following is a double click event of a datasheet where the user double
clicks the "batch" field, and a popup opens with the batch from the datasheet
displayed in the patch field. I want to make sure that a valid batch number
is entered, but I do not know how to exclude letters, for example, or a
batch like 1301011200, where the '13' would not be a valid month. Any ideas?
I am using Access 2003 ADP with SQL Server 2000. Thank you.

Private Sub BATCH__DblClick(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rstblreel_notes As ADODB.Recordset
Dim strSQL As String
Dim strSQLI As String
Dim boolDupId As Boolean

'SQL to check if batch number already exists in the reel_notes table
strSQL = "Select batch FROM tblreel_notes where batch = '" & Me.
BATCH_.Value & "'"
'SQL to insert the record into the reel_notes table if it does not
already exist
strSQLI = "INSERT INTO tblreel_notes (reel_num,batch,reel_id) " & _
"VALUES ('" & Me.REEL.Value & "','" & Me.BATCH_.Value & "',
'" & Me.id.Value & "') "

boolDupId = False
Set rstblreel_notes = New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
rstblreel_notes.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic

'============== Need Help here ===========================

'Right now I can only see if the batch is 10 digits long,
'but I would like to make sure it is valid in that it is a
'valid date and minute combination. Is this possible?
If Len("" & Me.BATCH_) <> 10 Then
MsgBox "Enter a valid batch#", vbOKCancel
Else
'============== the rest is good =========================

'Insert the record
If rstblreel_notes.EOF Then
boolDupId = False
DoCmd.RunSQL strSQLI
Else
'Open the form
If Not CurrentProject.AllForms("frmCable_Reel_Notes2").
IsLoaded Then
stDocName = "frmCable_Reel_Notes2"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End If
Forms!frmCable_Reel_Notes2.Requery
If CurrentProject.AllForms("frmCable_Reel_History").IsLoaded Then
Forms!frmCable_Reel_History.Requery
End If
End If
End Sub
Click to expand...

isNumeric( ) will test to see if the whole number is really a number.
Val(Mid(me.Batch,1,2)<13 will test the first two digits to see if they
are less than 13.
 
Thanks OldPro. Both work great, and I think I can elaborate on the Val(Mid
(me.Batch,1,2)) example to give my form some good constraints. Thanks for
the quick reply.
Greetings all. I need help making sure text entered in a certain field is in
the correct form. I am working with a batch number that is simply the date,
Click to expand...
[quoted text clipped - 59 lines]
isNumeric( ) will test to see if the whole number is really a number.
Val(Mid(me.Batch,1,2)<13 will test the first two digits to see if they
are less than 13.
Click to expand...
 
I need help making sure text entered in a certain field is in
the correct form. I am working with a batch number that is simply the date,
including hour and minutes, with no special characters. For example the
batch number for September 24 2007 10:30 am would be 0924071030.
Click to expand...

I suggest you parse you input string into an unambiguous date format
(e.g. yyyy-mm-dd hh:nn:ss) and test it using the IsDate() expression
e.g.

ISDATE(
'2001-'
& MID(test_value, 1, 2)
& '-'
& MID(test_value, 3, 2)
& ' '
& MID(test_value, 7, 2)
& ':'
& MID(test_value, 9, 2)
& ':00')

I've hard-coded the year because I further suggest that you make a
separate test for the input year: all values between '00' and '99'
inclusive are valid years, so it's pointless including this element in
the above rule, but not all years may be valid as regards your
business rules e.g. all possible years for the value '55' (1955, 2055,
etc) may be out of scope for you. PS ever heard of the Y2K issue? :)

Jamie.

--
 
Back
Top