Multiple format (1 Viewer)

Mat1994

Registered User.
Local time
Today, 14:05
Joined
Nov 29, 2018
Messages
94
Hi All,

In my form, I have a field where I would like the user to comply with one of two format.

The fields name is : TargetAltitudeSID
The format possible are :
- FL and 1 to 3 numbers (e.g FL1 or FL12 or FL458 ...)
- The field has to be a number < to 99999

I haven't found much on the internet but I have tried a few things.

I tried :
- Input mask: "FL099" or "99999"
- After update event :
Me!TargetAltitude.format = "FL099"
or Me!TargetAltitude.format = "99999"

- After event :
Format([TagetAltitudeSID]) = Format(FL099) or Format (99999)


I'm open to any suggestion, advise.

Thank you for your help,
Mat
 

June7

AWF VIP
Local time
Yesterday, 17:05
Joined
Mar 9, 2014
Messages
5,423
Use the textbox BeforeUpdate event to validate input.

If you want to use this value for sorting records, don't save the FL text and make it a number field or use placeholder zeros. FL100 will sort before FL2. FL00002 will sort before FL00100.
 

Mat1994

Registered User.
Local time
Today, 14:05
Joined
Nov 29, 2018
Messages
94
Hi June,

If you want to use this value for sorting records

I don't want to sort the record. When the user inputs this field in the form I want to be sure the format is FL123 or a number (<10000).

Could I use me!field.format or a format([field]) = "the format I want"?

Mat
 

June7

AWF VIP
Local time
Yesterday, 17:05
Joined
Mar 9, 2014
Messages
5,423
This is tricky because you are allowing variable length and mixing text and numbers.

Are you saying values FL123, 457, 87143, FL32762 would be valid input?
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:05
Joined
Jan 20, 2009
Messages
12,849
The FL values are text and fundamentally different information from the numeric values. The two pieces of information should be stored in different fields.

FL would be a text field since a leading zero is significant. I probably wouldn't store the "FL" since it is common to all values.

Either way, put Validations on the fields in the table rather than the textbox. The FL field would validate one to three digits and the Altitude a numeric range. This ensures invalid data can never be stored, no matter how it is entered.

The data entry could still be done in a single textbox. Check for "FL" and manage the validation and update to the fields accordingly in VBA.
 

Mat1994

Registered User.
Local time
Today, 14:05
Joined
Nov 29, 2018
Messages
94
June,

Are you saying values FL123, 457, 87143, FL32762 would be valid input?
Yes for FL123, 457 and 87143. But no for FL32762. This fields indicates an altitude. In aeronautics, we can do that two differents ways. Or you indicate the altitude with a number (from 1 to 10 000 for my case) or we use a flight level (FL + 1 to 3 digits)


Galaxiom,
The two pieces of information should be stored in different fields.
Yes, it would be better to have two fields. it would be a lot easier to create the validation rule.
I can try to create to unbound fields (one for the FL and a second one for the numbers), then code, when one field is inputted, it populate the bound field (TargetAltitudeSID). And add to the code a function that when one field is inputted, it overwrites the data in the "TargetAltitudeSID" field and deletes the other unbound field to avoid confusion.
Do you think it's a good idea?

Mat
 

apr pillai

AWF VIP
Local time
Today, 06:35
Joined
Jan 20, 2005
Messages
735
Use the lost focus event of the earlier field to display an Inputbox() based menu of two choices 1. Flight Level 2. Altitude. Based on the selection of the User Enable/Disable appropriate field with the required Input Mask.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:05
Joined
Feb 28, 2001
Messages
27,001
Here's your problem. If you have one field that you intended to use with two possible fundamentally different formats, you cannot do that in Access. You cannot easily "overload" a table definition like you can in some languages that allow variant definitions.

You have to ask yourself what you will be doing with the inputs. If both are to be treated numerically but with different interpretations, then input everything as numbers and keep a flag that defines which way you interpret the input.

The comments you have gotten from June7, Galaxiom, and apr pillal are all based on the idea that a field in Access is not supposed to be multi-typed. In Excel, you can do this because each CELL in a spreadsheet carries around its own format information. However, in Access, formatting is the same for a given field across all records in that table.

Whether you store the number in one of two different fields or whether you store the two numbers in the same field and have a selection flag to go with it, you are trying to put a square peg in a round hole. Think a bit more about this design to take into account not only that you need to gather the data but you also have something to do with it afterwards.

Whenever you are in doubt, always to look to the real-world problem for design answers.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:05
Joined
Jan 20, 2009
Messages
12,849
And add to the code a function that when one field is inputted, it overwrites the data in the "TargetAltitudeSID" field and deletes the other unbound field to avoid confusion.
Do you think it's a good idea?

With two fields use a table validation to prevent the possibility of both fields being populated in the same record.

However as Doc intimates, it is less important which way you choose to store this data than it is to thoroughly ensure the data integrity. Ultimately, text can store anything.

Having said that, if you are going to do maths with the numbers then you aught to prioritise the need for numeric data and have two separate fields.
 

Mat1994

Registered User.
Local time
Today, 14:05
Joined
Nov 29, 2018
Messages
94
Thank you for all your explanations.
Has advised I rethought of how I was going to use this field. This field will be used after export in a software that can read FL + 3 digits or just digits.
However, when I questioned my supervisor about this. He thinks using FL+3 digits will define a altitude to low and therefore we could limit the field to a numeric field and set the correct interval. But this decision has to be discuss with the team. So I have to pause to problem for now.

However the solutions to this problem are :
- In the case the field can be a numeric (without FL), I will use a validation rule to define the interval.
- In the case the team wants both (FL + a numeric field), I will do 2 unbound field that will populate the field that will be exported. It will be a lot easier to setting the properties right with a validation rule.

Thank you for your help, I do understand more every time I seek your help/advise.
Mat
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:05
Joined
Jan 20, 2009
Messages
12,849
This field will be used after export in a software that can read FL + 3 digits or just digits.

You aren't limited to exporting the table. You could put it through a query:

Code:
ExportFieldName:IIF([FL] Is Null, [Altitude], "FL" & [FL])


However, when I questioned my supervisor about this. He thinks using FL+3 digits will define a altitude to low and therefore we could limit the field to a numeric field and set the correct interval.
I can imagine seeing this on "Air Crash Investigators" where some was plane at was 100 feet because of a data error.;)
 

Mat1994

Registered User.
Local time
Today, 14:05
Joined
Nov 29, 2018
Messages
94
Thanks,
Yeah, I don't want to be the cause of plane crash. My work is going to be checked by everyone in the team, then tested on the sofware, then checked again, then "certified" then used on the final sofware and only then will it be accepted.
 

essaytee

Need a good one-liner.
Local time
Today, 12:05
Joined
Oct 20, 2008
Messages
512
I assume this has something to do with flight planning. Many years ago I use to submit flight plans (in hardcopy form, personally handing to ATC, or faxing same).

I recall that FL (Flight level) was only used for altitudes above 10000' and all those under were represented directly by the full number (6500'). I agree with everyone, just record it as a number. Unless things have changed, I believe the 'FL' component doesn't change. The Number will be easy to validate, and again, I assume the valid numbers will be in ranges of 500', again easy to validate.

The display of the altitude for any reports or forms is a simple matter of string manipulation. As regards Form design, you don't have to lock yourself into the Viewing form is also your Editing form. If an edit is required, have a purpose Add/Edit form for the process. Just some food for thought.
 

Users who are viewing this thread

Top Bottom