By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,222 Members | 1,730 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,222 IT Pros & Developers. It's quick & easy.

displaying multiple types in a Text Box

desklamp
P: 5
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?
Aug 10 '07 #1
Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,366
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.
Aug 10 '07 #2

desklamp
P: 5
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: (999) 999-9999, 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.
Aug 10 '07 #3

Rabbit
Expert Mod 10K+
P: 12,366
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: (999) 999-9999, 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.
Aug 10 '07 #4

desklamp
P: 5
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.
Aug 13 '07 #5

Rabbit
Expert Mod 10K+
P: 12,366
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.UBox).

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

desklamp
P: 5
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.UBox).

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!
Aug 14 '07 #7

Rabbit
Expert Mod 10K+
P: 12,366
Not a problem, good luck.
Aug 14 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.