Format vs validation: Exactly How records of a particular fields are stored

[larrywang]

Hello Experts,
Just curious how access stores records. Say, data type of Text, field name "FullName" when entering data via forms normally we use formatting or enforce data entry with validation rule to check say if user typed in propercase or cases that we desire to see. So far so good. When text format masks is applied for instance > or < , does the actual data or the formatted data is passed to the table layer. Alternatively, table layer format vs form layer format which one of the methods dictate what exactly is stored under the table?



Thank you for your attention

[CJ_London]

no - the table will store exactly as typed - format is a property stating how it is displayed.

however the format function will return a string e.g.

newstring=format(oldstring,">")

[Micron]

Generally speaking, formatting does not affect the value stored in a table field. Some would argue this point with respect to upper vs. lower case, but in truth, they are not the same if you look at the ASCII character values for U vs u. Yet the upper case or lower case text will be stored as you wish. However, the database Compare statement (Database, Binary or Text) could come in to play with respect to how things function when dealing with text. How data is stored can also depend on what is input or retrieved. So if you type "sam" without any coercion to upper case, you will store sam as long as the field data type allows text and doesn't violate any other field properties such as count of characters. As another example, if a data source contains date/time fields with time, things don't work the same if you query this data as =01/01/2015 as it will if youi use >01/01/2015 regardless of how you format the criteria. Even if you format the table field as short date, the stored value is like 01/01/2015 12:31:15 regardless of how it looks, if that is what the query returns. That being said, I think the second part of a mask build stipulates whether or not the embedded mask characters are stored with the data. I must say though, I have a strong disliking for masks and can't remember the last time I used one. Not to say they don't have a purpose in some environments.

[larrywang]

@Micron, great response. To sum up, formats are for display, and cosmetic purpose records are not actually stored on the table that way. Input masks are another way of formatting except they allow whether or not the embedded masks are stored with the data. Is this understanding correct. Just out of curiosity, how do you force access to store embedded masks. Thank you so much

[larrywang]

no - the table will store exactly as typed - format is a property stating how it is displayed.however the format function will return a string e.g.newstring=format(oldstring,">")

Hello Ajax, I didn't quite get the format function. Would you kindly elaborate. Did you mean with the help of this function, access would actually retrieve on what was on format display.

[CJ_London]

Did you mean with the help of this function, access would actually retrieve on what was on format display.

not exactly - format is a function which returns a string value so lets say in a table have fld1 as 'abc'

in a table, query or control on a form or report you can format its display using the format property so it 'appears as' ABC

or in a query you can assign the value as value formatted as required e.g.

SELECT format(fld1,">")
FROM
myTable

If you wanted to update all your values to uppercase in a table you would have a query something like this

UPDATE myTable Set Fld1=Format(Fld1,">")

The Format function (and property) has many uses - you can format dates to appear the way you want them, numbers as currency etc

This link explains the format function

https://msdn.microsoft.com/en-us/lib.../gg251755.aspx

[Micron]

not exactly - format is a function which returns a string value

Ajax, I think I know what you mean, I just disagree with your wording on the quoted text. I recognize that you worded it differently in your first post, which I agree with entirely. So this is mostly for others.

Format is not a function that returns a string value, per se. It is a function that controls how any data type to be displayed by using the format you specify (unlesss there are data types it won't work with, which I do not know if there are any). I agree that "returns" is an accepted term for this. As you probably know, you can format dates, currrency, time, etc. and these are not strings, which is a term generally accepted in programming circles to refer to text, special characters and numbers stored as text. For example, the following values do not appear to be the same: 42210.47303; 7/25/2015; 7/25/2015 11:21 The first is the actual value stored for a date and time (the date serial numer). The rest are representations of the number which is formatted to look like dates according to my system date/time settings.

larrywang: there really is no such thing as a "cosmetic purpose record" although I think your understanding is fundamentally correct with respect to formatting. Do not make this assumption for conversion functions, whose purpose is to actually convert the value to a different type. CDate(42210) should convert the value to a date data type, but from what? Whether or not 42210 is a string or a number depends on how you are dealing with it elsewhere. UCase is a conversion function.

As for how to store characters from input masks, I do not know since I don't use them, but you could check out support.office.com if this link fails
https://support.office.com/en-us/art...2-4a47832de8da. I believe the sole purpose of a mask is to enforce data entry to meet certain requirements, but it is not a formatting tool. For example, you can enforce a date to be entered as mm/dd/yy or yyyy-dd-mm but it will be stored like 42210.47303 and be returned/displayed as formatted by the table field settings, vba code, query field formatting, etc.

[CJ_London]

@Micron

I was quite clear about the difference between the format function and the format property - so I can't agree with what you say here

Format is not a function that returns a string value, per se. It is a function that controls how any data type to be displayed

if you replaced the word function with the word property, I would agree with you

see the link I provided in post#6 where it states

Format Function (Visual Basic for Applications)

Office 2013

Other Versions

• Office 2010

Last modified: March 09, 2015
Applies to: Office 2013 | VBA

Description
Returns a Variant (String) containing an expression formatted according to instructions contained in a format expression.

[Micron]

I did read those before I posted. What I wrote was influenced by the part that says it returns a variant... so I guess I took that too literally and would have to now believe that the function returns a variant of string (not fixed length string) data type.
`

you can format its display using the format property so it 'appears as' ABC

I have never tried to control format properties in code, preferring to use the object's property sheet, so I had to look this up. I could not find anything on how to control a format property setting to corerce abc to ABC using either of these methods. There are several examples, such as Me!myCtrl.Format ="Long Time", but I found nothing that would format the property as upper case. Could you please direct us to info that shows how to do this?

In summary, I think I have learned something about the format function and format property thanks to you, but haven't changed my mind about it with respect to dates at least.
Thanks.

[CJ_London]

Not quite sure what you are saying

preferring to use the object's property sheet

yes - the format property

I found nothing that would format the property as upper case.

I presume you mean format the control value as upper case? Can't see why you would want to format a property

To format as uppercase the format string is > and for lowercase it is <

The link I provided in post #6 documents it under the string section

As far as using code - I don't have a need to use it very often, but occasionally you have data that is always displayed uppercase (e.g. postcodes, passport number, etc) so rather than force the user to ensure they enter in upper case, or needing to remember to format as uppercase when displaying I simply have a bit of code in the control afterupdate event to store it in uppercase

ctrl=format(ctrl,">")

[Micron]

I was going to post a link but figured you would comprehend what I was saying because you seemed to have a better grasp of the format function vs format property than me. Here is a link about the format property as it applies to a textbox control: https://msdn.microsoft.com/en-us/lib.../ff823190.aspx

I presume you mean format the control value as upper case? Can't see why you would want to format a property

my impression is that you were the one advocating this when you wrote:

in a table, query or control on a form or report you can format its display using the format property so it 'appears as' ABC

The link I provided in post #6 documents it under the string section

I am aware of this. I just never use it, preferring to use the Ucase function to convert and store the conversion. At this point, I have no idea if by using either of these methods there is a difference in the actual stored value. I do see that they look the same at the table level.

[CJ_London]

I have no idea if by using either of these methods there is a difference in the actual stored value

your link clearly says

....are displayed and printed

So the answer is there is no difference in the actual stored value.

You can check this fairly easily

have a table with a text field with the format property set to > (all upper case)
in the table enter a value - say abc (all lower case) - this will be displayed in uppercase ABC
write a query and use the following calculated column (change myfield to the name of your text column)

InStr(1,[myfield],"B",0) this is the binarycompare - it will return 0 (because there is not a 'B' in the field value)
InStr(1,[myfield],"b",0) will return 2 (because there is a 'b' in the field value)

[Micron]

Thanks for the info. I Googled but didn't find a way to compare the stored value to the displayed value to see if they were the same. I had already tried putting the > in the table format property sheet but didn't know how to make the comparision reliably.

[CJ_London]

look at using the strcomp function

https://msdn.microsoft.com/en-us/lib...(v=vs.90).aspx

note this does not return a boolean result

Good luck