Working with IP Address field in Access Database

[archangelos14]

Hi! I'm creating a database with an IP Address field. How do i handle this field? Field must accept only null or ip address ranging from 1.0.0.0 - 255.255.255.255? If rule is violated, msg box must appear stating "Input Values not Valid?" I'm quite new to access. Appreciate your help.

 

[revans]

I'd use an input mask on your form field

###\.###\.###\.###;0;"#"

Unfortunately this accepts spaces in addition to numbers so you'll need to fix up the spaces. In the after update event call something like this

Function FixIPAddress(str As String) As String
'trim out spaces in an ip address
Dim v As Variant
Dim temp As String
Dim i As Long
v = Split(str, ".")
For i = LBound(v) To UBound(v)
  temp = temp & Trim$(v(i)) & "."
Next i
If Len(temp) > 0 Then
  temp = Left$(temp, Len(temp) - 1)
End If
FixIPAddress = temp
End Function

Then you'll want to check that it's a valid ip address. Something like this might work

Function IsValidIPAddress(str As String) As Boolean
'return true if valid ip address
'assumes basic quad notation
Dim v As Variant
Dim i As Long
Dim temp As Boolean
v = Split(str, ".")
For i = LBound(v) To UBound(v)
  temp = (CInt(v(i)) >= 0 And CInt(v(i)) <= 255)
  If Not temp Then Exit For
Next i
IsValidIPAddress = temp
End Function

So, all put together you'd see something like this:

Private Sub Text1_AfterUpdate()
Me.Text1 = FixIPAddress(Me.Text1)
if not IsValidIPAddress(me.text1) then
  MsgBox "Input Values not Valid?" 
  me.Text1.SetFocus
end if
End Sub

 

[HiTechCoach]

Hi! I'm creating a database with an IP Address field. How do i handle this field? Field must accept only null or ip address ranging from 1.0.0.0 - 255.255.255.255? If rule is violated, msg box must appear stating "Input Values not Valid?" I'm quite new to access. Appreciate your help.

I like to use four fields. The first field validates with 1-255 and the others have a validation rule of 0 - 255

It is possible to store the data in a single field but use four text box controls on the form for data entry with validation.

 

[archangelos14]

I'd use an input mask on your form field

###\.###\.###\.###;0;"#"

Unfortunately this accepts spaces in addition to numbers so you'll need to fix up the spaces. In the after update event call something like this

Function FixIPAddress(str As String) As String
'trim out spaces in an ip address
Dim v As Variant
Dim temp As String
Dim i As Long
v = Split(str, ".")
For i = LBound(v) To UBound(v)
  temp = temp & Trim$(v(i)) & "."
Next i
If Len(temp) > 0 Then
  temp = Left$(temp, Len(temp) - 1)
End If
FixIPAddress = temp
End Function

Then you'll want to check that it's a valid ip address. Something like this might work

Function IsValidIPAddress(str As String) As Boolean
'return true if valid ip address
'assumes basic quad notation
Dim v As Variant
Dim i As Long
Dim temp As Boolean
v = Split(str, ".")
For i = LBound(v) To UBound(v)
  temp = (CInt(v(i)) >= 0 And CInt(v(i)) <= 255)
  If Not temp Then Exit For
Next i
IsValidIPAddress = temp
End Function

So, all put together you'd see something like this:

Private Sub Text1_AfterUpdate()
Me.Text1 = FixIPAddress(Me.Text1)
if not IsValidIPAddress(me.text1) then
  MsgBox "Input Values not Valid?" 
  me.Text1.SetFocus
end if
End Sub

i tried your method and it worked wondefully! the only thing i wanna change is to return the cursor @ the start of the IPAddress field rather than @ the start of the next field. How do i get around with it? Thank you very much!

 

[archangelos14]

I'd use an input mask on your form field

###\.###\.###\.###;0;"#"

Unfortunately this accepts spaces in addition to numbers so you'll need to fix up the spaces. In the after update event call something like this

Function FixIPAddress(str As String) As String
'trim out spaces in an ip address
Dim v As Variant
Dim temp As String
Dim i As Long
v = Split(str, ".")
For i = LBound(v) To UBound(v)
  temp = temp & Trim$(v(i)) & "."
Next i
If Len(temp) > 0 Then
  temp = Left$(temp, Len(temp) - 1)
End If
FixIPAddress = temp
End Function

Then you'll want to check that it's a valid ip address. Something like this might work

Function IsValidIPAddress(str As String) As Boolean
'return true if valid ip address
'assumes basic quad notation
Dim v As Variant
Dim i As Long
Dim temp As Boolean
v = Split(str, ".")
For i = LBound(v) To UBound(v)
  temp = (CInt(v(i)) >= 0 And CInt(v(i)) <= 255)
  If Not temp Then Exit For
Next i
IsValidIPAddress = temp
End Function

So, all put together you'd see something like this:

Private Sub Text1_AfterUpdate()
Me.Text1 = FixIPAddress(Me.Text1)
if not IsValidIPAddress(me.text1) then
  MsgBox "Input Values not Valid?" 
  me.Text1.SetFocus
end if
End Sub

And how do i deal with null values? Thanks!

 

[revans]

For nulls I'd check before you run the code like this

Private Sub Text1_AfterUpdate()
If Not IsNull(Me.Text1) Then
  Me.Text1 = FixIPAddress(Me.Text1)
  If Not IsValidIPAddress(Me.Text1) Then
    MsgBox "Input Values not Valid?"
    Me.Text1.SetFocus
  End If
Else
  'comment this out if we don't care about nulls
  MsgBox "Do we care that this is null?"
  Me.Text1.SetFocus
End If
End Sub

For returning the cursor at the beginning of the field, I read something long, long ago about cursor position within a text box but I'd have to find it again. Perhaps somebody will come up with it sooner because it'll take a while for me to root it out. I'll see what I can do but no promises.

hth,

Rich

 

[archangelos14]

For nulls I'd check before you run the code like this

Private Sub Text1_AfterUpdate()
If Not IsNull(Me.Text1) Then
  Me.Text1 = FixIPAddress(Me.Text1)
  If Not IsValidIPAddress(Me.Text1) Then
    MsgBox "Input Values not Valid?"
    Me.Text1.SetFocus
  End If
Else
  'comment this out if we don't care about nulls
  MsgBox "Do we care that this is null?"
  Me.Text1.SetFocus
End If
End Sub

For returning the cursor at the beginning of the field, I read something long, long ago about cursor position within a text box but I'd have to find it again. Perhaps somebody will come up with it sooner because it'll take a while for me to root it out. I'll see what I can do but no promises.

hth,

Rich

Thank you very much for your time in answering my question. You're a big help for a beginner like me. I'll try the additional code and will google about the other issue.

 

[archangelos14]

I'd use an input mask on your form field

###\.###\.###\.###;0;"#"

Unfortunately this accepts spaces in addition to numbers so you'll need to fix up the spaces. In the after update event call something like this

Function FixIPAddress(str As String) As String
'trim out spaces in an ip address
Dim v As Variant
Dim temp As String
Dim i As Long
v = Split(str, ".")
For i = LBound(v) To UBound(v)
  temp = temp & Trim$(v(i)) & "."
Next i
If Len(temp) > 0 Then
  temp = Left$(temp, Len(temp) - 1)
End If
FixIPAddress = temp
End Function

Then you'll want to check that it's a valid ip address. Something like this might work

Function IsValidIPAddress(str As String) As Boolean
'return true if valid ip address
'assumes basic quad notation
Dim v As Variant
Dim i As Long
Dim temp As Boolean
v = Split(str, ".")
For i = LBound(v) To UBound(v)
  temp = (CInt(v(i)) >= 0 And CInt(v(i)) <= 255)
  If Not temp Then Exit For
Next i
IsValidIPAddress = temp
End Function

So, all put together you'd see something like this:

Private Sub Text1_AfterUpdate()
Me.Text1 = FixIPAddress(Me.Text1)
if not IsValidIPAddress(me.text1) then
  MsgBox "Input Values not Valid?" 
  me.Text1.SetFocus
end if
End Sub

Got an ERROR: Runtime 13 Type Mismatch. This happens when i type spaces in the any of the octets (1"space"3."space"23.13"space".12"space" ) instead of the valid range 0f 0-255. When i debug it, it points to line "temp = (CInt(v(i)) >= 0 And CInt(v(i)) <= 255)" . I think i doesn't accept spaces as valid values. In cases like this, i wanna display a message like "Please input a valid value!". Thanks again.

 

[revans]

Hmm, my FixIPAddress merely trims spaces off the ends, and doesn't look for spaces in between. :/

Try this

Function FixIPAddress2(str As String) As String
'remove spaces in an ip address
FixIPAddress2 = Replace(str, " ", "")
End Function

Also, for setting the cursor to the beginning of text in a textbox there are two properties important for doing this. I haven't dealt with them since... Access 2 so I'll throw this out there for you to experiment with.

Me.Text1.SelStart=0
Me.Text1.SelLength=0

SelStart is the position of the cursor and SelLength is how much text the cursor has selected. You can set these to your taste either On Enter, On Got Focus, On Click, or whatever works.

hth,

Rich

 

[archangelos14]

i've got it all sorted out except to return the cursor @ the beginning of the field if input values are invalid. Selstart, Sellength as well as Tabindex don't seem to work. anything else you have in mind? thanks!