Print Page | Close Window

Query_AppendValuesViaSelectClause.mdb

Printed From: Roger's Access Library
Category: Other Download Libraries
Forum Name: Tejpal, A.D.
Forum Description: A.D. Tejpal's Library
URL: www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=68
Printed Date: 15 Dec 2017 at 2:37pm


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 
table. 
 
    Some advantages of using SELECT instead of VALUES clause are mentioned 
below:
    (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 
         concerned).
    (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 
respectively:
    (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: http://www.rogersaccesslibrary.com/forum/uploads/37/Query_AppendValuesViaSelectClause.zip - uploads/37/Query_AppendValuesViaSelectClause.zip



-------------
--

Email: mailto:adtejpal@gmail.com - adtejpal@gmail.com



Print Page | Close Window