displaying multiple types in a Text Box

[desklamp]

I'm a total Access newbie, please bear with me! Using Win2K/Access 2003.

I'm trying to create a table in which I can store IP addresses and other information. According to Microsoft, there is no native way of converting an IP address to an integer/long number (which is much more efficient to index and search). After searching, I found a function which is able to take either an IP address or integer and convert it to the other.

In the table, Address is type Number. My form has a Text Box for entering the IP address. But Access throws an error because it reads the dotted decimal as a String/Text type.

I would like to be able type a string into the form as a dotted decimal and have it _displayed_ as a string (easier for me to read), but _saved_ as a Number in the table (after calling this external function stored in a standard module). Also, it would be nice to have the form and/or report display the dotted decimal when reviewing previous entries.

Is this possible with a single Text Box? If so, could you please describe what I would need to do?

[Rabbit]

I'm a total Access newbie, please bear with me! Using Win2K/Access 2003.

I'm trying to create a table in which I can store IP addresses and other information. According to Microsoft, there is no native way of converting an IP address to an integer/long number (which is much more efficient to index and search). After searching, I found a function which is able to take either an IP address or integer and convert it to the other.

In the table, Address is type Number. My form has a Text Box for entering the IP address. But Access throws an error because it reads the dotted decimal as a String/Text type.

I would like to be able type a string into the form as a dotted decimal and have it _displayed_ as a string (easier for me to read), but _saved_ as a Number in the table (after calling this external function stored in a standard module). Also, it would be nice to have the form and/or report display the dotted decimal when reviewing previous entries.

Is this possible with a single Text Box? If so, could you please describe what I would need to do?

You can do this with the input mask but my question is why save it as a number when ip shouldn't be treated as a number unless it's in its binary format.

[desklamp]

You can do this with the input mask but my question is why save it as a number when ip shouldn't be treated as a number unless it's in its binary format.

Rabbit, thanks for your quick reply. I've looked into your suggestion. An input mask seems to define a format for displaying data, but not doing anything with the data. For example, a phone number input mask could be: [phone], specifying that digits are not required where the "9" is, plus and minuses are not allowed, but spaces are. A user could enter "(123) 555-" and that would be a valid entry for this input mask. Depending upon the option of the mask, Access could store "123555" or "(123) 555-".

It seems you are not aware that an IP address can be converted to a number. (Search for "inet_aton" and "inet_ntoa" .) In applications where one needs to search over a range of IP addresses, or see if a given IP comes before or after another, being able to convert them to numbers makes comparisons very easy. It is unfortunate that Microsoft chose not to include this functionality in Access.

What I want is to enter, for example, "127.0.0.1" , and store "2130706433 ".
( (127*256^3)+(0* 256^2)+(0*256^1 )+(1*256^0) = 2130706433. ) And when I view the form or report, I want Access to pull "2130706433 " from the record, and display "127.0.0.1" . Unless the input mask can call external functions, I don't see how the input mask would help me here.

Is there a binary type available? I only saw Byte type. If I'm reading the help file properly, I would guess that this actually only stores one bit of information ('yes' or 'no', 'off' or 'on', '1' or '0', etc.).

Best regards.

[Rabbit]

Rabbit, thanks for your quick reply. I've looked into your suggestion. An input mask seems to define a format for displaying data, but not doing anything with the data. For example, a phone number input mask could be: [phone], specifying that digits are not required where the "9" is, plus and minuses are not allowed, but spaces are. A user could enter "(123) 555-" and that would be a valid entry for this input mask. Depending upon the option of the mask, Access could store "123555" or "(123) 555-".

It seems you are not aware that an IP address can be converted to a number. (Search for "inet_aton" and "inet_ntoa" .) In applications where one needs to search over a range of IP addresses, or see if a given IP comes before or after another, being able to convert them to numbers makes comparisons very easy. It is unfortunate that Microsoft chose not to include this functionality in Access.

What I want is to enter, for example, "127.0.0.1" , and store "2130706433 ".
( (127*256^3)+(0* 256^2)+(0*256^1 )+(1*256^0) = 2130706433. ) And when I view the form or report, I want Access to pull "2130706433 " from the record, and display "127.0.0.1" . Unless the input mask can call external functions, I don't see how the input mask would help me here.

Is there a binary type available? I only saw Byte type. If I'm reading the help file properly, I would guess that this actually only stores one bit of information ('yes' or 'no', 'off' or 'on', '1' or '0', etc.).

Best regards.

I am aware it can be converted into a base 10 number. I was just under the impression you wanted to store 127.0.0.1 as 127001.

In this case you're going to need an intermediary unbound textbox. One text box will be where they enter in the ip address in the format you describe. In the after update event of that text box, that number will be converted and stored into the field. While in the On Current event of the form, it retrieves the number and converts it back and stores it to the text box.

[desklamp]

In this case you're going to need an intermediary unbound textbox. One text box will be where they enter in the ip address in the format you describe. In the after update event of that text box, that number will be converted and stored into the field. While in the On Current event of the form, it retrieves the number and converts it back and stores it to the text box.

Thank you for the description of the use of an intermediary text box. I'll attempt to implement this dual-text box set up today.

Best regards.

UPDATE:

After poking around the form builder, I find I need some further clarification.

Per your suggestion, I've added an unbound text box (UBox) taking a string/text type for the dotted decimal address. UBox's After Update event is defined as "[IP]![Address] = IPConvert ([Me].[UBox])". (I don't know if this is the correct syntax or not to assign the value typed into UBox to the field Address.)

I found the "On Current" event field for the form, but am confused as to how it updates a particular element on the form, i.e. my Address text box. Could you be more specific on how to use the form's On Current event to retrieve a value from the table, pass it as a parameter to a VB function, and put the returned value into an individual element of a form?

Thanks for your help.

[Rabbit]

Thank you for the description of the use of an intermediary text box. I'll attempt to implement this dual-text box set up today.

Best regards.

UPDATE:

After poking around the form builder, I find I need some further clarification.

Per your suggestion, I've added an unbound text box (UBox) taking a string/text type for the dotted decimal address. UBox's After Update event is defined as "[IP]![Address] = IPConvert ([Me].[UBox])". (I don't know if this is the correct syntax or not to assign the value typed into UBox to the field Address.)

I found the "On Current" event field for the form, but am confused as to how it updates a particular element on the form, i.e. my Address text box. Could you be more specific on how to use the form's On Current event to retrieve a value from the table, pass it as a parameter to a VB function, and put the returned value into an individual element of a form?

Thanks for your help.

I guess IP is the name of your table? But I assume that your table is also the record source of your form. In which case you can use Me.Address even though it's not a control on your form. So Me.Address = IPConvert(Me.UB ox).

As for the On Current event. You would do Me.UBox = ReverseConvert( Me.Address). Or whatever your reverse conversion function is called.

[desklamp]

I guess IP is the name of your table? But I assume that your table is also the record source of your form. In which case you can use Me.Address even though it's not a control on your form. So Me.Address = IPConvert(Me.UB ox).

As for the On Current event. You would do Me.UBox = ReverseConvert( Me.Address). Or whatever your reverse conversion function is called.

yes, IP is the name of the table (creative, isn't it? ;) ).

Thank you again, Rabbit, for the quick replies. Your advice was very helpful in making progress on this issue. I hope to be more clear in the future as I learn the lingo of Access.

Best regards!

[Rabbit]

Not a problem, good luck.