Author: A. D. Tejpal
SQL strings having embedded form based parameters do not readily lend themselves to creation of recordsets (Error 3061 - Too Few Parameters). Conventional approach involves concatenation of hard values represented by form controls into the SQL string.
Concatenation of hard values into SQL string suffers from the following drawbacks:
- (a) Proper delimiters need to be used for non-numeric data (Single quote for text type data and hash (#) for date type data).
- (b) If text data has embedded quotes, these need to be fixed before concatenation.
- (c) For date type data, the hard value has to be converted into "mm/dd/yyyy" format before undertaking the concatenation. This is meant to prevent inconsistent results, if the regional settings for short date on the local computer happens to be not as per U.S. date format.
- (d) Care needed in handling Null values.
The limitations brought out above, point to the desirability of devising a workable solution for utilizing SQL strings having embedded form parameters, as source for recordsets. Function Fn_FixFormParamsForRecordsets() is meant to fix such SQL strings, making them suitable for use with recordsets. This eliminates the problems outlined in preceding para.
You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=437
No comments:
Post a Comment