Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Tejpal, A.D.
  New Posts New Posts RSS Feed - Query_AppendValuesViaSelectClause.mdb
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Please consider donating $1 per sample downloaded, (find out why here)


 Post Reply Post Reply
A.D. Tejpal View Drop Down
Microsoft MVP
Microsoft MVP

Joined: 30 Jun 2008
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote A.D. Tejpal Quote  Post ReplyReply Direct Link To This Post Topic: Query_AppendValuesViaSelectClause.mdb
    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/


Back to Top
Sponsored Links

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.