12
APONTAMENTOS –ACCESS_02 Metodologia Ler cada questão do enunciado Interpretar o que é pedido Analisar a base de dados fornecida como exemplo Ler os apontamentos relativos à questão Ver os vídeos (se disponíveis) Ouvir atentamente as demonstrações práticas Tentar responder mentalmente às diversas questões colocadas 1) 2) Criar tabela na vista de estrutura: · Inserir o nome dos campos tendo em atenção as regras de nomenclatura, nomeadamente: - Utilizar uma nomenclatura consistente; - Atribuir nomes significativos nos vários itens a criar; - Tabelas: Plural; - Campos: singular; - Sem espaços - Com acentuação; - Maiúscula inicial em cada palavra; · Utilizar a área de transferência para aumentar a produtividade (Ctrl+C » Ctrl+V) · Definir a chave primária: Campo que não permite repetição de dados (Menu de contexto » Chave primária) 3) Tipos de dados: Texto Alfanumérico, comprimento limite de 255 carateres (Para inserir texto) Memo Alfanumérico, comprimento limite de 65535 carateres Para inserir texto sem limitações de tamanho Permite inserir quebras de linha Número Numérico Data/Hora Data e/ou hora, ex. dd-mm-aaaa hh:mm:ss Moeda ex. €uro Sim/Não Caixa de marcação (CheckBox) Na disciplina iremos utilizar apenas códigos numéricos (Os campos de código podem ter outros tipos de dados) Tamanho do campo Comprimento (Texto) ou subtipo numérico (Número) Subtipo numérico Byte Inteiro Positivo 0 a 255 1 byte Inteiro Inteiro Negativos e Positivos -32768 a 32767 2 bytes Inteiro Longo Inteiro Negativos e Positivos até 2147483647 4 bytes Simples Decimal Negativos e Positivos até 3,4E38 4 bytes Exemplos: Byte 162 cm (Altura cm) Inteiro 1000 Kg Inteiro longo 100000 € Simples 1,62 m (Altura m)

Access_02_Apontamentos_1_01.pdf

Embed Size (px)

Citation preview

Page 1: Access_02_Apontamentos_1_01.pdf

APONTAMENTOS – ACCESS_02Metodologia Ler cada questão do enunciado

Interpretar o que é pedido

Analisar a base de dados fornecida como exemplo

Ler os apontamentos relativos à questão

Ver os vídeos (se disponíveis)

Ouvir atentamente as demonstrações práticasTentar responder mentalmente às diversas questões colocadas

1)2) Criar tabela na vista de estrutura:

· Inserir o nome dos campos tendo em atenção as regras de nomenclatura,nomeadamente:- Utilizar uma nomenclatura consistente;- Atribuir nomes significativos nos vários itens a criar;- Tabelas: Plural;- Campos: singular;- Sem espaços- Com acentuação;- Maiúscula inicial em cada palavra;

· Utilizar a área de transferência para aumentar a produtividade (Ctrl+C »Ctrl+V)

· Definir a chave primária: Campo que não permite repetição de dados(Menu de contexto » Chave primária)

3) Tipos de dados: Texto Alfanumérico, comprimento limite de 255 carateres

(Para inserir texto) Memo Alfanumérico, comprimento limite de 65535 carateres

Para inserir texto sem limitações de tamanhoPermite inserir quebras de linha

Número Numérico Data/Hora Data e/ou hora, ex. dd-mm-aaaa hh:mm:ss Moeda ex. €uro Sim/Não Caixa de marcação (CheckBox)Na disciplina iremos utilizar apenas códigos numéricos (Os campos de códigopodem ter outros tipos de dados)

Tamanho do campoComprimento (Texto) ou subtipo numérico (Número)Subtipo numéricoByte Inteiro Positivo 0 a 255 1 byteInteiro Inteiro Negativos e Positivos -32768 a 32767 2 bytesInteiro Longo Inteiro Negativos e Positivos até 2147483647 4 bytesSimples Decimal Negativos e Positivos até 3,4E38 4 bytes

Exemplos:Byte 162 cm (Altura cm)Inteiro 1000 KgInteiro longo 100000 €Simples 1,62 m (Altura m)

Page 2: Access_02_Apontamentos_1_01.pdf

4) Propriedades (Utilizar a ajuda em cada propriedade) Formatar

Atua após a inserção de dados (Efeito visual)Número: 0000 Formata número com pelo menos 4 dígitos

Casas decimaisAplica-se quando o subtipo de dados não é inteiro

Máscara de introduçãoAtua aquando da inserção de dadosControla o que o utilizador pode ou deve inserirNúmero: 0000 Obrigatória a inserção de 4 dígitosNúmero: 9990 três dígitos facultativos + 1 dígito obrigatório

LegendaTexto que aparece como nome do campo

Valor predefinidoValor que aparece, por defeito, para cada novo registo

Regra de validaçãoRegra matemática.Ex. >1900 E <2010

Texto de validaçãoTexto que aparece se a regra não for cumprida.Deve informar o que o utilizador deve inserir

NecessárioIndica se é obrigatório o seu preenchimento

IndexadoPor defeito o valor é: NãoNas chaves primárias o valor deverá ser: Sim (Duplicação não autorizada)

5) Relações· As tabelas devem estar fechadas para que quaisquer alterações à estrutura da

BD possam ser validadas com sucesso· Adicionar tabelas· Dispor as tabelas· Identificar as tabelas simples· Identificar as chaves externas: Campos que precisam de "ir buscar" dados a

outra tabela· Os campos a ligar têm de ser do mesmo Tipo de dados;· Os campos numéricos têm de ter o mesmo Tamanho do campo (Subtipo numérico).· Arrastar as chaves externas para a chave primária respetiva (ou em sentido

inverso)· Organizar as tabelas para que as relações oo--1 fiquem no mesmo sentidoImpor integridade referencial:· Impede a utilização de um código que não exista;· Impede a eliminação de um código que esteja a ser utilizadoPropagar a atualização dos campos relacionados:· Atualiza qualquer alteração de dados

6) Caixas de combinação· Na Vista de estrutura· Identificar as chaves externas: Campos que precisam de "ir buscar" dados a

outra tabela ()· Apenas se configuram caixas de combinação nas chaves externas· Posicionar o cursor na Chave externa: Pesquisa » Caixa de combinação Origem da linha

Selecionar uma das tabelas existentes na lista Coluna vinculada

Ordem do campo chave da tabela definida como origem da linha: 1ª, 2ª,etc.).

7) Iniciar o preenchimento pelas tabelas secundárias

Page 3: Access_02_Apontamentos_1_01.pdf

8) Consultas Nomenclatura

. Con_

. Atribuir nomes significativos às consultas: Con_##_Descrição

. ex: Con_01_Todos os filmes Pensar sobre os campos que queremos visualizar Adicionar apenas as tabelas necessárias

Evitar a utilização dos campos que estabelecem as relações Adicionar o campos pretendidos

. O asterisco (*) significa adicionar todos os campos de uma dada tabelaNão permite utilizar critérios (condições de procura)

não permite utilizar critérios de ordenação. Apenas as caixas (checkbox) marcadas serão visíveis na consulta

Desmarcam-se os campos que possam aparecer repetidos. Eliminar quaisquer tabelas sem campos utilizados na consulta Critérios de consulta:. Estão relacionados com o tipo de dados do campo em causa

ex: >=2001, para um campo numérico"Ação" para um campo de texto

. Os dados alfanuméricos (Texto) ficam entre aspas (")

. As datas ficam entre cardinais (#)

. Condições na mesma linha »»» condição ETêm de verificar-se todas e em simultâneo para o mesmo registo

. Condições em linha diferentes »»» condição OU

Critérios de ordenação:. Os critérios são aplicados da esquerda para a direita. Quando não se pretende alterar a ordem dos campos, podemos:

- Adicionar um campo repetido- Desmarcar a checkbox para não aparecerem campos repetidos- Definir o critério de ordenação: ascendente ou descendente

9) Consultas parametrizadas. Con_Par_. Substituir dados por questões entre parêntesis retos. Como – Aplicado ao tipo de dados Texto e Data/Hora. Permite utilizar *

10) Formulários. For_Tab_ (...). Utilizar o assistente. Utilizar Tabelas. Utilizar os campos de ligação que contêm caixas de combinação (chaves

externas). Modelos disponíveis: Colunas, Tabela, folha de dados e justificado. Formulário com Subformulário: Incluir 2 tabelas ligadasRelatórios

. Rel_Con_ (...)

. Utilizar o assistente

. Utilizar consultas

. Não utilizar os campos de ligação que contêm caixas de combinação (chavesexternas)

Page 4: Access_02_Apontamentos_1_01.pdf

CONSULTAS – OPERADORES E PARÂMETROS· Operadores:

>; <; >=; <=; COMO; E (Conjunção); OU (Disjunção)· Tipo de dados TEXTO

> “B”“Suspense”Como “*Rock*”

· Tipo de dados NÚMERO<= 2002> 2000 E < 2005<= 2002 OU > 2005

· Tipo de dados DATA/HORA>= #01-01-2000#>= #01-01-2000# E <= #31-12-2000#< #01-01-1999# OU > #31-12-1999#

CONSULTAS PARAMETRIZADAS Neste tipo de consultas é solicitado ao utilizador um valor a inserir; O texto entre parêntesis retos será personalizado tendo em conta o

contexto; Se os [Campos] tiverem o mesmo nome a questão apenas é apresentada uma

única vez.· Tipo de dados TEXTO, NÚMERO, DATA/HORA

= [Questão a colocar]> [Questão a colocar]> [Questão a colocar] E <= [Questão a colocar]< [Questão a colocar] OU >= [Questão a colocar]

· Utilização do caráter universal (*) O asterisco significa “Qualquer caráter ou nulo”; É necessária a utilização do termo Como.Como [Questão a colocar]

Neste caso será utilizada exatamente a expressão inserida peloutilizador

Como "*" & [Questão a colocar] & "*" Será utilizada a expressão inserida pelo utilizador podendo conter

carateres antes ou depois (Valores contendo ...)Como "*" & [Questão a colocar]

Será utilizada a expressão inserida pelo utilizador podendo contercarateres antes (Valores terminados em ...)

Como [Questão a colocar] & "*" Será utilizada a expressão inserida pelo utilizador podendo conter

carateres depois (Valores começados por ...)Exemplo:

Consulta de datas de um dado ano:>= ”01/01/” & [Pesquisa de datas relativas ao ano] E <=”31/12/” &[Pesquisa de datas relativas ao ano]

Page 5: Access_02_Apontamentos_1_01.pdf

DATATYPE PROPERTY (TIPO DE DADOS) 5FIELDSIZE PROPERTY (TAMANHO DO CAMPO) 6FORMAT PROPERTY (FORMATAR) 6FORMAT PROPERTY - DATE/TIME DATA TYPE (FORMATAR) 7FORMAT PROPERTY - NUMBER AND CURRENCY DATA TYPES (FORMATAR) 8FORMAT PROPERTY - TEXT AND MEMO DATA TYPES (FORMATAR) 9FORMAT PROPERTY - YES/NO DATA TYPE (FORMATAR) 10TEXTBOX.INPUTMASK PROPERTY (MÁSCARA DE INTRODUÇÃO) 10TEXTBOX.VALIDATIONRULE PROPERTY (REGRA DE VALIDAÇÃO) 12TEXTBOX.VALIDATIONTEXT PROPERTY (TEXTO DE VALIDAÇÃO) 12REQUIRED PROPERTY (NECESSÁRIO) 13

DATATYPE PROPERTY (TIPO DE DADOS)

You can use the DataType property to specify the type of data stored in a table field. Each field can store data consisting of only asingle data type.SettingThe DataType property uses the following settings.Setting Type of data Size

Text(Default) Text or combinations of text and numbers,as well as numbers that don't require calculations,such as phone numbers.

Up to 255 characters or the length set by the FieldSize property,whichever is less. Microsoft Access does not reserve space forunused portions of a text field.

Memo Lengthy text or combinations of text and numbers.

Up to 63,999 characters. (If the Memo field is manipulated throughDAO and only text and numbers [not binary data] will be stored in it,then the size of the Memo field is limited by the size of thedatabase.)

NumberNumeric data used in mathematical calculations.For more information on how to set the specificNumber type, see the FieldSize property topic.

1, 2, 4, or 8 bytes (16 bytes if the FieldSize property is set toReplication ID).

Date/TimeDate and time values for the years 100 through9999.

8 bytes.

Currency

Currency values and numeric data used inmathematical calculations involving data with oneto four decimal places. Accurate to 15 digits on theleft side of the decimal separator and to 4 digits onthe right side.

8 bytes.

AutoNumber

A unique sequential (incremented by 1) number orrandom number assigned by Microsoft Accesswhenever a new record is added to a table.AutoNumber fields can't be updated. For moreinformation, see the NewValues property topic.

4 bytes (16 bytes if the FieldSize property is set to Replication ID).

Yes/NoYes and No values and fields that contain only oneof two values (Yes/No, True/False, or On/Off).

1 bit.

OLE Object

An object (such as a Microsoft Excel spreadsheet, aMicrosoft Word document, graphics, sounds, orother binary data) linked to or embedded in aMicrosoft Access table.

Up to 1 gigabyte (limited by available disk space)

Hyperlink

Text or combinations of text and numbers stored astext and used as a hyperlink address. A hyperlinkaddress can have up to four parts:text to display — the text that appears in a field orcontrol.address — the path to a file (UNC path) or page(URL).subaddress — a location within the file or page.screentip — the text displayed as a tooltip.

Each part of the parts of a Hyperlink data type can contain up to2048 characters.

Attachment Any supported type of file

You can attach images, spreadsheet files, documents, charts, andother types of supported files to the records in your database, muchlike you attach files to e-mail messages. You can also view and editattached files, depending on how the database designer sets up theAttachment field. Attachment fields provide greater flexibility thanOLE Object fields, and they use storage space more efficientlybecause they don't create a bitmap image of the original file.

Page 6: Access_02_Apontamentos_1_01.pdf

LookupWizard

Creates a field that allows you to choose a valuefrom another table or from a list of values by usinga list box or combo box. Clicking this option startsthe Lookup Wizard, which creates a Lookup field.After you complete the wizard, Microsoft Accesssets the data type based on the values selected inthe wizard.

The same size as the primary key field used to perform the lookup,typically 4 bytes.

FIELDSIZE PROPERTY (TAMANHO DO CAMPO)

You can use the FieldSize property to set the maximum size for data stored in a field set to the Text, Number, or AutoNumberdata type.SettingIf the DataType property is set to Text, enter a number from 0 to 255. The default setting is 50.If the DataType property is set to AutoNumber, the FieldSize property can be set to Long Integer or Replication ID.If the DataType property is set to Number, the FieldSize property settings and their values are related in the following way.Setting Description Decimal precision Storage size

Byte Stores numbers from 0 to 255 (no fractions). None 1 byte

DecimalStores numbers from –10^38–1 through 10^38–1 (.adp)Stores numbers from –10^28–1 through 10^28–1 (.mdb, .accdb)

28 2 bytes

Integer Stores numbers from –32,768 to 32,767 (no fractions). None 2 bytes

Long Integer (Default) Stores numbers from –2,147,483,648 to 2,147,483,647 (no fractions). None 4 bytes

Single

Stores numbers from–3.402823E38 to –1.401298E–45for negative values and from1.401298E–45 to 3.402823E38 for positive values.

7 4 bytes

Double

Stores numbers from–1.79769313486231E308 to –4.94065645841247E–324for negative values and from4.94065645841247E–324 to 1.79769313486231E308 for positive values.

15 8 bytes

Replication ID Globally unique identifier (GUID) N/A 16 bytes

FORMAT PROPERTY (FORMATAR)

You can use the Format property to customize the way numbers, dates, times, and text are displayed and printed. Read/writeString.Syntaxexpression.Formatexpression A variable that represents a TextBox object.RemarksYou can use one of the predefined formats or you can create a custom format by using formatting symbols.The Format property uses different settings for different data types. For information about settings for a specific data type, seeone of the following topics:

Date/Time Data TypeNumber and Currency Data TypesText and Memo Data TypesYes/No Data TypeYou can use the following symbols in custom formats for any data type.

Symbol Meaning

(space) Display spaces as literal characters.

"ABC" Display anything inside quotation marks as literal characters.

! Force left alignment instead of right alignment.

* Fill available space with the next character.

\ Display the next character as a literal character. You can also display literal characters by placing quotation marks around them.

[color]Display the formatted data in the color specified between the brackets. Available colors: Black, Blue, Green, Cyan, Red, Magenta,Yellow, White.

FORMAT PROPERTY - DATE/TIME DATA TYPE (FORMATAR)

You can set the Format property to predefined date and time formats or use custom formats for the Date/Time data type.SettingPredefined Formats

Page 7: Access_02_Apontamentos_1_01.pdf

The following table shows the predefined Format property settings for the Date/Time data type.Setting Description

GeneralDate

(Default) If the value is a date only, no time is displayed; if the value is a time only, no date is displayed. This setting is acombination of the Short Date and Long Time settings.Examples: 4/3/93, 05:34:00 PM, and 4/3/93 05:34:00 PM.

Long DateSame as the Long Date setting in the regional settings of Windows.Example: Saturday, April 3, 1993.

MediumDate

Example: 3-Apr-93.

ShortDate

Same as the Short Date setting in the regional settings of Windows.Example: 4/3/93.WARNING The Short Date setting assumes that dates between 1/1/00 and 12/31/29 are twenty-first century dates (that is, theyears are assumed to be 2000 to 2029). Dates between 1/1/30 and 12/31/99 are assumed to be twentieth century dates (thatis, the years are assumed to be 1930 to 1999).

Long TimeSame as the setting on the Time tab in the regional settings of Windows.Example: 5:34:23 PM.

MediumTime

Example: 5:34 PM.

ShortTime

Example: 17:34.

Custom FormatsYou can create custom date and time formats by using the following symbols.Symbol Description

: (colon) Time separator. Separators are set in the regional settings of Windows.

/ Date separator.

c Same as the General Date predefined format.

d Day of the month in one or two numeric digits, as needed (1 to 31).

dd Day of the month in two numeric digits (01 to 31).

ddd First three letters of the weekday (Sun to Sat).

dddd Full name of the weekday (Sunday to Saturday).

ddddd Same as the Short Date predefined format.

dddddd Same as the Long Date predefined format.

w Day of the week (1 to 7).

ww Week of the year (1 to 53).

m Month of the year in one or two numeric digits, as needed (1 to 12).

mm Month of the year in two numeric digits (01 to 12).

mmm First three letters of the month (Jan to Dec).

mmmm Full name of the month (January to December).

q Date displayed as the quarter of the year (1 to 4).

y Number of the day of the year (1 to 366).

yy Last two digits of the year (01 to 99).

yyyy Full year (0100 to 9999).

h Hour in one or two digits, as needed (0 to 23).

hh Hour in two digits (00 to 23).

n Minute in one or two digits, as needed (0 to 59).

nn Minute in two digits (00 to 59).

s Second in one or two digits, as needed (0 to 59).

ss Second in two digits (00 to 59).

ttttt Same as the Long Time predefined format.

AM/PM Twelve-hour clock with the uppercase letters "AM" or "PM", as appropriate.

am/pm Twelve-hour clock with the lowercase letters "am" or "pm", as appropriate.

A/P Twelve-hour clock with the uppercase letter "A" or "P", as appropriate.

a/p Twelve-hour clock with the lowercase letter "a" or "p", as appropriate.

AMPM Twelve-hour clock with the appropriate morning/afternoon designator as defined in the regional settings of Windows.

Custom formats are displayed according to the settings specified in the regional settings of Windows. Custom formats inconsistentwith the settings specified in the regional settings of Windows are ignored.

Page 8: Access_02_Apontamentos_1_01.pdf

Note

If you want to add a comma or other separator to a custom format, enclose the separator in quotation marks as follows: mmm d", "yyyy.

ExampleThe following are examples of custom date/time formats.Setting Display

ddd", "mmm d", "yyyy Mon, Jun 2, 1997

mmmm dd", "yyyy June 02, 1997

"This is week number "ww This is week number 22

"Today is "dddd Today is Tuesday

FORMAT PROPERTY - NUMBER AND CURRENCY DATA TYPES (FORMATAR)

You can set the Format property to predefined number formats or custom number formats for the Number and Currency datatypes.SettingPredefined FormatsThe following table shows the predefined Format property settings for numbers.Setting Description

GeneralNumber

(Default) Display the number as entered.

CurrencyUse the thousand separator; follow the settings specified in the regional settings of Windows for negative amounts,decimal and currency symbols, and decimal places.

Euro Use the euro symbol ( ), regardless of the currency symbol specified in the regional settings of Windows.

FixedDisplay at least one digit; follow the settings specified in the regional settings of Windows for negative amounts, decimaland currency symbols, and decimal places.

StandardUse the thousand separator; follow the settings specified in the regional settings of Windows for negative amounts,decimal symbols, and decimal places.

PercentMultiply the value by 100 and append a percent sign (%); follow the settings specified in the regional settings of Windowsfor negative amounts, decimal symbols, and decimal places.

Scientific Use standard scientific notation.

Custom FormatsCustom number formats can have one to four sections with semicolons (;) as the list separator. Each section contains the formatspecification for a different type of number.Section Description

First The format for positive numbers.

Second The format for negative numbers.

Third The format for zero values.

Fourth The format for Null values.

For example, you could use the following custom Currency format:$#,##0.00[Green];($#,##0.00)[Red];"Zero";"Null"This number format contains four sections separated by semicolons and uses a different format for each section.If you use multiple sections but don't specify a format for each section, entries for which there is no format will either displaynothing or default to the formatting of the first section.You can create custom number formats by using the following symbols.Symbol Description

. (period) Decimal separator. Separators are set in the regional settings in Windows.

,(comma)

Thousand separator.

0 Digit placeholder. Display a digit or 0.

# Digit placeholder. Display a digit or nothing.

$ Display the literal character "$".

% Percentage. The value is multiplied by 100 and a percent sign is appended.

E– or e–Scientific notation with a minus sign (–) next to negative exponents and nothing next to positive exponents. This symbol mustbe used with other symbols, as in 0.00E–00 or 0.00E00.

E+ or e+Scientific notation with a minus sign (–) next to negative exponents and a plus sign (+) next to positive exponents. This symbolmust be used with other symbols, as in 0.00E+00.

Page 9: Access_02_Apontamentos_1_01.pdf

RemarksYou can use the DecimalPlaces property to override the default number of decimal places for the predefined format specified forthe Format property.The predefined currency and euro formats follow the settings in the regional settings of Windows. You can override these byentering your own currency format.ExampleThe following are examples of the predefined number formats.Setting Data Display

General Number3456.789–3456.789$213.21

3456.789–3456.789$213.21

Currency3456.789–3456.789

$3,456.79($3,456.79)

Fixed3456.789–3456.7893.56645

3456.79–3456.793.57

Standard 3456.789 3,456.79

Percent30.45

300%45%

Scientific3456.789–3456.789

3.46E+03–3.46E+03

The following are examples of custom number formats.Setting Description

0;(0);;"Null" Display positive values normally; display negative values in parentheses; display the word "Null" if the value is Null.

+0.0;–0.0;0.0 Display a plus (+) or minus (–) sign with positive or negative numbers; display 0.0 if the value is zero.

FORMAT PROPERTY - TEXT AND MEMO DATA TYPES (FORMATAR)

You can use special symbols in the setting for the Format property to create custom formats for Text and Memo fields.SettingYou can create custom text and memo formats by using the following symbols.Symbol Description

@ Text character (either a character or a space) is required.

& Text character is not required.

< Force all characters to lowercase.

> Force all characters to uppercase.

Custom formats for Text and Memo fields can have up to two sections. Each section contains the format specification for differentdata in a field.Section Description

First Format for fields with text.

Second Format for fields with zero-length strings and Null values.

For example, if you have a text box control in which you want the word "None" to appear when there is no string in the field, youcould type the custom format @;"None" as the control's Format property setting. The @ symbol causes the text from the field tobe displayed; the second section causes the word "None" to appear when there is a zero-length string or Null value in the field.

Note

You can use the Format function to return one value for a zero-length string and another for a Null value, and you can similarly use theFormat property to automatically format fields in table Datasheet view or controls on a form or report.

ExampleThe following are examples of text and memo custom formats.Setting Data Display

@@@-@@-@@@@ 465043799 465-04-3799

@@@@@@@@@465-04-3799465043799

465-04-3799465043799

>davolioDAVOLIODavolio

DAVOLIODAVOLIODAVOLIO

<davolioDAVOLIODavolio

davoliodavoliodavolio

Page 10: Access_02_Apontamentos_1_01.pdf

@;"Unknown" Null value Unknown

Zero-length string Unknown

Any text Same text as entered is displayed

FORMAT PROPERTY - YES/NO DATA TYPE (FORMATAR)

You can set the Format property to the Yes/No, True/False, or On/Off predefined formats or to a custom format for the Yes/Nodata type.SettingMicrosoft Access uses a check box control as the default control for the Yes/No data type. Predefined and custom formats areignored when a check box control is used. Therefore, these formats apply only to data that is displayed in a text box control.Predefined FormatsYes, True, and On are equivalent, as are No, False, and Off. If you specify one predefined format and then enter an equivalentvalue, the predefined format of equivalent value will be displayed. For example, if you enter True or On in a text box control withits Format property set to Yes/No, the value is automatically converted to Yes.Custom FormatsThe Yes/No data type can use custom formats containing up to three sections.Section Description

First This section has no effect on the Yes/No data type. However, a semicolon (;) is required as a placeholder.

Second The text to display in place of Yes, True, or On values.

Third The text to display in place of No, False, or Off values.

ExampleThe following example shows a custom yes/no format for a text box control. The control displays the word "Always" in blue text forYes, True, or On, and the word "Never" in red text for No, False, or Off.;"Always"[Blue];"Never"[Red]

TEXTBOX.INPUTMASK PROPERTY (MÁSCARA DE INTRODUÇÃO)

You can use the InputMask property to make data entry easier and to control the values users can enter in a text boxcontrol.Read/write String.Syntaxexpression.InputMaskexpression A variable that represents a TextBox object.RemarksInput masks are helpful for data-entry operations such as an input mask for a Phone Number field that shows you exactly how toenter a new number: (___) ___-____. It is often easier to use the Input Mask Wizard to set the property for you.The InputMask property can contain up to three sections separated by semicolons (;).Section Description

FirstSpecifies the input mask itself; for example, !(999) 999-9999. For a list of characters you can use to define the input mask, see thefollowing table.

SecondSpecifies whether Microsoft Access stores the literal display characters in the table when you enter data. If you use 0 for thissection, all literal display characters (for example, the parentheses in a phone number input mask) are stored with the value; ifyou enter 1 or leave this section blank, only characters typed into the control are stored.

ThirdSpecifies the character that Microsoft Access displays for the space where you should type a character in the input mask. For thissection, you can use any character; to display an empty string, use a space enclosed in quotation marks (" ").

When you create an input mask, you can use special characters to require that certain data be entered (for example, the area codefor a phone number) and that other data be optional (such as a telephone extension). These characters specify the type of data,such as a number or character, that you must enter for each character in the input mask.You can define an input mask by using the following characters.Character Description

0 Digit (0 to 9, entry required, plus [+] and minus [–] signs not allowed).

9 Digit or space (entry not required, plus and minus signs not allowed).

#Digit or space (entry not required; spaces are displayed as blanks while in Edit mode, but blanks are removed when data issaved; plus and minus signs allowed).

L Letter (A to Z, entry required).

? Letter (A to Z, entry optional).

A Letter or digit (entry required).

a Letter or digit (entry optional).

& Any character or a space (entry required).

C Any character or a space (entry optional).

Page 11: Access_02_Apontamentos_1_01.pdf

. , : ; - /Decimal placeholder and thousand, date, and time separators. (The actual character used depends on the settings in theRegional Settings Properties dialog box in Windows Control Panel).

< Causes all characters to be converted to lowercase.

> Causes all characters to be converted to uppercase.

!Causes the input mask to display from right to left, rather than from left to right. Characters typed into the mask always fill itfrom left to right. You can include the exclamation point anywhere in the input mask.

\ Causes the character that follows to be displayed as the literal character (for example, \A is displayed as just A).

Note

Setting the InputMask property to the word "Password" creates a password-entry control. Any character typed in the control is stored asthe character but is displayed as an asterisk (*). You use the Password input mask to prevent displaying the typed characters on the screen.

When you type data in a field for which you've defined an input mask, the data is always entered in Overtype mode. If you use theBACKSPACE key to delete a character, the character is replaced by a blank space.If you move text from a field for which you've defined an input mask onto the Clipboard, the literal display characters are copied,even if you have specified that they not be saved with data.When you've defined an input mask and set the Format property for the same field, the Format property takes precedence whenthe data is displayed. This means that even if you've saved an input mask, the input mask is ignored when data is formatted anddisplayed. The data in the underlying table itself isn't changed; the Format property affects only how the data is displayed.The following table shows some useful input masks and the type of values you can enter in them.Input mask Sample values

(000) 000-0000 (206) 555-0248

(999) 999-9999 (206) 555-0248

( ) 555-0248

(000) AAA-AAAA (206) 555-TELE

#999 –20

2000

>L????L?000L0 GREENGR339M3

MAY R 452B7

>L0L 0L0 T2F 8M4

00000-9999 98115-

98115-3007

>L<?????????????? Maria

Brendan

SSN 000-00-0000 SSN 555-55-5555

>LL00000-0000 DB51392-0493

TEXTBOX.VALIDATIONRULE PROPERTY (REGRA DE VALIDAÇÃO)

You can use the ValidationRule property to specify requirements for data entered into a record, field, or control. When data isentered that violates the ValidationRule setting, you can use the ValidationText property to specify the message to bedisplayed to the user. Read/write String.Syntaxexpression.ValidationRuleexpression A variable that represents a TextBox object.RemarksEnter an expression for the ValidationRule property setting and text for the ValidationText property setting. The maximumlength for the ValidationRule property setting is 2048 characters. The maximum length for the ValidationText property settingis 255 characters.For controls, you can set the ValidationRule property to any valid expression. For field and record validation rules, the expressioncan't contain user-defined functions, domain aggregate or aggregate functions, the Eval function, or CurrentUser method, orreferences to forms, queries, or tables. In addition, field validation rules can't contain references to other fields. For records,expressions can include references to fields in that table.For table fields and records, you can also set these properties in Visual Basic by using the DAO ValidationRule property.Microsoft Access automatically validates values based on a field's data type; for example, Microsoft Access doesn't allow text in anumeric field. You can set rules that are more specific by using the ValidationRule property.If you set the ValidationRule property but not the ValidationText property, Microsoft Access displays a standard error messagewhen the validation rule is violated. If you set the ValidationText property, the text you enter is displayed as the error message.For example, when a record is added for a new employee, you can enter a ValidationRule property requiring that the value in theemployee's StartDate field fall between the company's founding date and the current date. If the date entered isn't in this range,you can display the ValidationText property message: "Start date is incorrect."If you create a control by dragging a field from the field list, the field's validation rule remains in effect, although it isn't displayedin the control's ValidationRule property box in the property sheet. This is because a field's validation rule is inherited by a controlbound to that field.Control, field, and record validation rules are applied as follows:

Page 12: Access_02_Apontamentos_1_01.pdf

Validation rules you set for fields and controls are applied when you edit the data and the focus leaves the field orcontrol.

Validation rules for records are applied when you move to another record.If you create validation rules for both a field and a control bound to the field, both validation rules are applied when you

edit data and the focus leaves the control.The following table contains expression examples for the ValidationRule and ValidationText properties.ValidationRule property ValidationText property

<> 0 Entry must be a nonzero value.

> 1000 Or Is Null Entry must be blank or greater than 1000.

Like "A????" Entry must be 5 characters and begin with the letter "A".

>= #1/1/96# And <#1/1/97# Entry must be a date in 1996.

DLookup("CustomerID", "Customers", "CustomerID =Forms!Customers!CustomerID") Is Null

Entry must be a unique CustomerID (domain aggregate functions areallowed only for form-level validation).

If you create a validation rule for a field, Microsoft Access doesn't normally allow a Null value to be stored in the field. If you wantto allow a Null value, add "Is Null" to the validation rule, as in "<> 8 Or Is Null" and make sure the Required property is set toNo.You can't set field or record validation rules for tables created outside Microsoft Access (for example, dBASE, Paradox, or SQLServer). For these kinds of tables, you can create validation rules for controls only.

TEXTBOX.VALIDATIONTEXT PROPERTY (TEXTO DE VALIDAÇÃO)

Use the ValidationText property to specify a message to be displayed to the user when data is entered that violates aValidationRule setting for a record, field, or control. Read/write String.The following table contains expression examples for theValidationRule and ValidationText properties.ValidationRule property ValidationText property

<> 0 Entry must be a nonzero value.

> 1000 Or Is Null Entry must be blank or greater than 1000.

Like "A????" Entry must be 5 characters and begin with the letter "A".

>= #1/1/96# And <#1/1/97# Entry must be a date in 1996.

DLookup("CustomerID", "Customers", "CustomerID =Forms!Customers!CustomerID") Is Null

Entry must be a unique CustomerID (domain aggregate functions areallowed only for form-level validation).

REQUIRED PROPERTY (NECESSÁRIO)

You can use the Required property to specify whether a value is required in a field. If this property is set to Yes, when you enterdata in a record, you must enter a value in the field or in any control bound to the field, and the value cannot be Null. Forexample, you might want to be sure that a LastName control has a value for each record. When you want to permit Null values ina field, you must not only set the Required property to No but, if there is a ValidationRule property setting, it must alsoexplicitly state "validationrule Or Is Null".

Note

The Required property doesn't apply to AutoNumber fields.

SettingThe Required property uses the following settings.Setting Visual Basic Description

Yes True (–1) The field requires a value.

No False (0) (Default) The field doesn't require a value.