Specify Input Mask Format in Query

[jwgoerlich]

Hello group,

I have an existing Access 2000 database that with an odd quirk. The
table joins are based on numbers formatted with an input mask. Put
another way, the telephone numbers are stored as doubles, no decimal
places, with an input mask of &&&\-&&&\-&&&&.

Now as a favor I have written a data import query that does a SELECT
INTO to populate one table. This uses NewField: CINT(OldField) to
convert to an integer. However it wipes out the input mask on the
destination table. Then the table joins no longer work because, though
the numbers are the same, the format is different.

Is there a way to specify the decimal places and input mask within a
SELECT INTO query?

J Wolfgang Goerlich

 

[Jaci]

Hello group,

I have an existing Access 2000 database that with an odd quirk. The
table joins are based on numbers formatted with an input mask. Put
another way, the telephone numbers are stored as doubles, no decimal
places, with an input mask of &&&\-&&&\-&&&&.

Now as a favor I have written a data import query that does a SELECT
INTO to populate one table. This uses NewField: CINT(OldField) to
convert to an integer. However it wipes out the input mask on the
destination table. Then the table joins no longer work because, though
the numbers are the same, the format is different.

Is there a way to specify the decimal places and input mask within a
SELECT INTO query?

J Wolfgang Goerlich

 

[Steve Schapel]

Wolfgang,

I am not sure about the Input Mask aspect, as I have no experience with
this. But I imagine the reason the joins don't work is not related to
the input mask. It is probably related to the fact that the new table
has the field as an Integer and the old ones as Double, so the data
types do not match. I could not see the reason why you are using the
CInt function in the Make-Table query.

 

[gllincoln]

Hi JW,

Storing phones as double or integer or anything but text kind of offends me - it's bad design. OK, now that I have gotten that off my chest.

The input mask is just for show, display purposes. Numbers are stored as numbers and the 'mask' goes away.

Casting the phone numbers as integer will break the join because integers can't hold numbers like two billion. (A 10 digit phone number starts with the area code, which will be between 2 and 9 so the numeric value of a phone number will be in the range of two to ten billion. That's why the misguided author that created the original database used doubles.

The standard Cint cast will break at 32768 (16-bit border value), CLng will break when you get to the 215 area code or higher.

If you cannot convert the original database to text because too many things are dependent on the phones being stored the wrong way then you will need to re-export the data, this time leaving it as a double. We can't recast the integer because the data is gone.

Hope this helps...
Gordon












Hope this helps...
Gordon

 

[jwgoerlich]

Thank you for the responses. I agree that this is poor design.
Unfortunately much has already been built around storing and joining
the phone numbers this way. I am trying to simply add one query
without redoing the entire database.

The field for a telephone in the source table is a string (example:
"1235551212"). In the destination table, the field for telephone is a
double with [Decimal Places]=0 and [Input Mask]=&&&\-&&&\-&&&&.

I use a query similar to SELECT CDbl([SourcePhone]) AS [DestinPhone]
INTO DestinTable From SourceTable;. I have tried both CDbl() and
CInt().

The issue at hand is that query resets the field formatting in the
destination table. So say you create a destination table and set the
decimal place and input mask on the telephone field. Then you run the
SELECT INTO query. You can recheck the destination table and see that
the field has been reset to the defaults ([Decimal Places]=Auto and
[Input Mask]="").

Other tables in this Access database that store telephone numbers use
the input mask, and all queries and reports assume that the input mask
is in place, therefore the joins fail.

Can field defaults for decimal places and input mask be defined during
the SELECT INTO query?

J Wolfgang Goerlich

 

[Marcia Bunn]

Why in the world do you have telephone numbers stored as Doubles? In
truth,
they should be stored as text. Data should only be defined as Numeric if
you're actually going to do math with it. Since you already have it
defined
as Doubles, and presumably you only have 0-9 in there, go to the table and
change Double to Integer. Despite the warnings, there won't be any data
loss,
given the facts you've posted.