Solved

Access Table - Input Mask

Posted on 2016-07-20
11
61 Views
Last Modified: 2016-07-22
Good afternoon,

I've created an Input Mask but have fallen short on the Format Results.

This is the Mask "PROGRAM AREA: "9" @ "9,999" NSF";;# which works great if I have 1000+ but I also have cases where the value maybe 100+

PROGRAM AREA: 3 @ 1,250 NSF - Works
PROGRAM AREA: 1 @ 0,660 NSF - Does not Work

How do I modify the input mask to get the following results PROGRAM AREA: 1 @ 660 NSF

Thanks for your time and guidance
0
Comment
Question by:Aubie91
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 41721407
I don't have time to look it up but try using # or 0 instead of 9 for the first three digits.  Leave the last one as a 9 if you always want to show at least one digit.  Either the # or the 0 is the optional numeric character.
0
 
LVL 57
ID: 41721445
Part of the issue too is that your table design is incorrect.

From the way that's formatted, you have two quantities in that field.

Jim.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41721566
Good point.  You probably can't have a 9 followed by the optional character and ending with a 9 since that wouldn't make sense.
0
 

Author Comment

by:Aubie91
ID: 41721629
What other options are available to allow easy data input that is consistent in the look and feel
0
 
LVL 57
ID: 41721716
Give an example of the way it should look using a label to the right of the control, then verify the formatting and correct if it's wrong (if possible) in the BeforeUpdate event of the control.

In other words, you'll need to do it on your own as I doubt you'll get anything built-in to work the way you want it.

again, if this is a qty and another qty (or price), then those should be in two separate fields.  You'll find that built-in validation then will work better.   You can then display those two things any way you want (even combined as your showing).   But for entry and storage, you should be dealing with them separately.

Right now, you're going to have a devil of a time checking either one of those numbers.  For example, can they be negative?   Must either be a whole number?  Your not going to be able to easily check any of that.

Jim.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 41722167
To expand on what Jim stated...

Also consider that your input mask seems to contain redundant text.
    "PROGRAM AREA: "9" @ "9,999" NSF";;#
So "PROGRAM AREA" will appear in *every* record?
As Jim Stated, ...the name of the *field* should be "ProgramArea", ...and the value in that field should just be a single number.

The same would seem to go for "NSF".

So perhaps instead of trying to workaround an incorrect design, ...you should reconsider how your data is normalized, ...
Then (AFAICT) no input mask would be needed at all.

Finally, ...if you still needed to display the value as:
PROGRAM AREA: 3 @ 1,250 NSF
PROGRAM AREA: 1 @ 660 NSF

You could build a concatenated text field in a query:
SELECT YourTable.ID, YourTable.ProgramArea, YourTable.NSF, "PROGRAM AREA: " & [ProgramArea] & " @ " & Format([NSF],"#,000") & " NSF" AS DisplayAs
FROM YourTable;
ScenarioSample db is attached

JeffCoachman
0
 
LVL 57
ID: 41722868
and if that makes sense, make sure you select Jeff's comment as the solution, which explained very well in detail what I was suggesting.

Jim.
0
 

Author Closing Comment

by:Aubie91
ID: 41722953
Thanks to all for your guidance and setting me on the correct path that will allow the users to input the data and keep the output with a consistent look and feel
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 41723446
Actually,...
The "Design limitations" line of reasoning, was Jim's idea, ...I just added to it, ...so a split of the points might be a better resolution...
;-)

A final tidbit would be that it will be near impossible to do any "Aggregation" (Average NSF, ...for example) on your field in its current state

On a side note, ...I will say that a great many of the "issues" here have to do with inefficient designs.
A Rock solid design should always come first.
(before forms, queries, Reports, Inputmasks, ...or code, ...etc)

So most first question(s) should be along the lines of:
    This is what I need to do, ..._________________
     How do I design my database (Tables) to accomplish this?

So when you frame the question like so:
"This is what I have...It doesn't work, ...can you make it work?"
This boxes us in, as far as what we can offer as a solution.

Don't take it personally, things like this happens to a lot of members here.
...its just that "Making" a database is easy (especially with the tools Access provides)
But "designing" a database is more of a science.

Do a web search for some good beginner books on Database/Table design and "Normalization".

Then take a good look at your current db and possibly post questions here on its Purpose and structure.

;-)

JeffCoachman
0
 
LVL 57
ID: 41724866
Jeff's wasn't trying to be harsh, he was just trying to show you how you can get more mileage out asking a question on EE and get your project  off to a good start.

 As he said, many times a problem is simply a result of coming at it from the wrong direction.  We see this a lot for example with someone that has worked with Excel tries to use Access.  But if you apply an Excel approach to Access, it fails miserably.   Access requires a different mindset than Excel when you approach things.

 So it's best in the beginning if you make your questions about the goal rather than something specific.  Once your more familiar with Access, then you can get into specifics right off.   That's all he was saying.

We're here to help and will do that in any way we can.

Jim.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 41725458
Aubie91,

No problem buddy,
We were all rookies at one time
;-)


As you can see I provided lots of information for you...
Sample files, ...SQL, Screenshots, ...etc
..so as Jim says:
We're here to help and will do that in any way we can.

;-)

Jeff
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now