Exploring Microsoft Access 2016 Series Editor Mary Anne

Exploring Microsoft® Access® 2016 Series Editor Mary Anne Poatsy Cameron |Williams Series Created by Dr. Robert T. Grauer

Chapter 5 Data Validation and Data Analysis Reducing Errors and Extracting Better Information Copyright © 2017 Pearson Education, Inc.

Objectives • Establish Data Validation • Control the Format of Data Entry • Control Input with a Lookup Field • Customize Output Based on User Input • Use Advanced Functions Copyright © 2017 Pearson Education, Inc.

Objective 1: Establish Data Validation Skills: Require a Field Add a Default Field Value Add a Validation Rule with Validation Text Copyright © 2017 Pearson Education, Inc.

Establish Data Validation Selected field Select Yes Required field Copyright © 2017 Pearson Education, Inc.

Establish Data Validation Resulting error message No name entered Copyright © 2017 Pearson Education, Inc.

Establish Data Validation Selected field Default Copyright © 2017 Pearson Education, Inc.

Establish Data Validation Cat Copyright © 2017 Pearson Education, Inc.

Establish Data Validation • Validation rule—limits data values entered into a field • Validation text—custom error message • Comparison operators: §Equal to: = §Greater than: > §Less than: < §Between §In §Like §Not equal to: <> §Greater than or equal to: >= §Less than or equal to: <= Copyright © 2017 Pearson Education, Inc.

Establish Data Validation Selected field Validation Rule Validation Text Copyright © 2017 Pearson Education, Inc.

Establish Data Validation Erroneous data entry Validation Text Copyright © 2017 Pearson Education, Inc.

Objective 2: Control the Format of Data Entry Skills: Create an Input Mask Copyright © 2017 Pearson Education, Inc.

Control the Format of Data Entry Phone number input mask Copyright © 2017 Pearson Education, Inc.

Control the Format of Data Entry Selected field Click Input Mask Click ellipses Copyright © 2017 Pearson Education, Inc.

Control the Format of Data Entry Select mask Change or leave placeholder Copyright © 2017 Pearson Education, Inc.

Control the Format of Data Entry Input mask Copyright © 2017 Pearson Education, Inc.

Objective 3: Control Input with a Lookup Field Skills: Create a Lookup Field Modify a Lookup Field Copyright © 2017 Pearson Education, Inc.

Control Input with a Lookup Field Animals table Animal. Type options Copyright © 2017 Pearson Education, Inc.

Control Input with a Lookup Field Animals table Select field Select Lookup Wizard Copyright © 2017 Pearson Education, Inc.

Control Input with a Lookup Field Animals table Table with lookup options Make your changes here Copyright © 2017 Pearson Education, Inc.

Objective 4: Customize Output Based on User Input Skills: Create a Parameter Query Create a Parameter Report Copyright © 2017 Pearson Education, Inc.

Customize Output Based on User Input User prompt for search criterion Copyright © 2017 Pearson Education, Inc.

Customize Output Based on User Input User prompt for search criterion Copyright © 2017 Pearson Education, Inc.

Objective 5: Use Advanced Functions Skills: Use the Date Function Use the Round Function Use the IIf Function Use the Is. Null Function Use the Date. Part Function Copyright © 2017 Pearson Education, Inc.

Use Advanced Functions No parameters Double-click Functions Click Built-In Functions Click Expression Category Double-click Date Copyright © 2017 Pearson Education, Inc.

Use Advanced Functions Two parameters Double-click Functions Click Built-In Functions Click Expression Category Double-click Round Copyright © 2017 Pearson Education, Inc.

Use Advanced Functions One decimal place Copyright © 2017 Pearson Education, Inc.

Use Advanced Functions Three parameters Double-click IIf Click Program Flow Copyright © 2017 Pearson Education, Inc.

Use Advanced Functions Within the last 30 days More than 30 days ago Copyright © 2017 Pearson Education, Inc.

Use Advanced Functions One parameter Double-click Is. Null Click Inspection Copyright © 2017 Pearson Education, Inc.

Use Advanced Functions The is a Date. Of Adoption so Is. Null is 0 (False) The is no Date. Of Adoption so Is. Null is -1 (True) Copyright © 2017 Pearson Education, Inc.

Use Advanced Functions Four parameters Double-click Date. Part Click Date/Time Copyright © 2017 Pearson Education, Inc.

Use Advanced Functions Month numbers are displayed Copyright © 2017 Pearson Education, Inc.

Summary • Access offers several features to simplify and verify data entry • Access allows users to determine what they want to see • Access provides advanced functions that can be used to further customize queries Copyright © 2017 Pearson Education, Inc.

Questions ? Copyright © 2017 Pearson Education, Inc.

Copyright All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Printed in the United States of America. Copyright © 2017 Pearson Education, Inc.