Print Page | Close Window


Printed From: Roger's Access Library
Category: Other Download Libraries
Forum Name: Tejpal, A.D.
Forum Description: A.D. Tejpal's Library
Printed Date: 24 Mar 2018 at 3:21am

Topic: Query_AppendValuesViaSelectClause.mdb
Posted By: A.D. Tejpal
Subject: Query_AppendValuesViaSelectClause.mdb
Date Posted: 08 Jul 2008 at 10:19pm
    This sample db demonstrates use of SELECT clause in preference to VALUES 
clause, for appending values held by form / subform controls into the destination 
    Some advantages of using SELECT instead of VALUES clause are mentioned 
    (a) No need to worry about specific delimiters while concatenating values for 
         various data types (e.g. ' for text type and # for date type data). Moreover, 
         there is no problem if the value held by any of the controls happens to be Null 
         (provided destination table is in a position to accept Null value in the field 
    (b) No need to fix any embedded quotes, whether single or double.
    (c) No risk of dates getting mis-interpreted (in the process of concatenation into 
         SQL string) when local system settings for short date are not as per US 
         settings (mm/dd/yyyy).
    Following three styles of SQL for append action are shown separately for values 
held by controls on an independent form as well as those on a subform. Three 
controls are involved in each case, holding text, date and number type data 
    (a) Use form/subform values directly.
    (b) Use form/subform values via recordset. Function Fn_rsApp() is used.
    (c) Use form/subform values via array. Function Fn_arApp() is used.
    A dummy table named T_Dummy with single field single record is used for 
facilitating adoption of SELECT clause instead of VALUES clause. Contents of this 
table are of no significance.
    Form/subform controls and correspondingly appended record in destination table 
get highlighted in special color. Simultaneously, the SQL actually used for the 
selected append action is also displayed for ready reference.
5 - Version: Access 2K/XP/2K3/2K7 (Access 2000 File Format)

Download Access 2K/XP/2K3/2K7: - uploads/37/


Email: -

Print Page | Close Window