Re: Data type mismatch in criteria expression
- From: "Seth" <Seth@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 4 Aug 2005 13:17:31 -0700
Thanks Nikos.
Seth
"Nikos Yannacopoulos" wrote:
> Seth,
>
> My first rule in writing SQL expressions is "keep it simple"; this
> practically means, strip out anything unnecessary that Access puts in
> the SQL expression of a query. In this case, this includes (a) repeating
> the table name before the field names (not needed since there is only
> one table in the FROM clause), and (b) getting rid of redundant
> parentheses around criteria in the WHERE clause. Applying those, the SQL
> expression becomes quite simple to follow:
>
> strSQLDeleteStd = "DELETE * FROM tblTestRprtStds " _
> & "WHERE fldTestRprtStdID = '" & Me!txtTestRprtID & "' " _
> & "AND fldTestrprtStdRefNum= '" & intStdId & "'"
>
> Now, how it works:
>
> An SQL expression in VBA code is essentially a string expression; this
> is what a DoCmd.RunSQL or CurrentDb.Execute expects as an argument.
> Therefore, it is enclosed in double quotes, to denote the value assigned
> to the strSQLDeleteStd variable as such (string). In other words, double
> quotes denote String.
>
> An expression can be broken up into several lines for readability; to
> denote it is continued over to the next line, a " _" (space and
> underscore, without the quotes) is add ed at the end.
> A string can be made up of parts, concatenated with ambersands.
> The two previous points explain how space-underscores and ambersands are
> used to put together a string expression, made up of parts extending
> over several lines.
>
> Some parts of the expression are left outside the double quotes; this
> tells VBA that those are actually variables, functions or references to
> other database objects (form textboes, in this case) so VBA "reads"
> their value and concatenates the value into the string.
>
> The single quotes denote that the text between them within the overall
> string, is to be treated as a string on its own right; they are used
> where you would normally have double quotes in the query SQL. For
> instance, if part of) your query SQL was:
> WHERE CustName = "Smith"
> You would use single quotes around Smith in the SQL string in VBA:
> strSQL = "SELECT Somefield FROM Table1 WHERE CustName = 'Smith')
>
> With the above in mind, the syntax proposed at the beginning assumes
> that both fldTestRprtStdID and fldTestrprtStdRefNum are type Text;
> otherwise, the single quotes around the respective control references
> should be removed. For instance, if the latter is numeric, the
> expression should become:
>
> strSQLDeleteStd = "DELETE * FROM tblTestRprtStds " _
> & "WHERE fldTestRprtStdID = '" & Me!txtTestRprtID & "' " _
> & "AND fldTestrprtStdRefNum= " & intStdId
>
> HTH,
> Nikos
>
>
.
- References:
- Data type mismatch in criteria expression
- From: Seth
- Re: Data type mismatch in criteria expression
- From: Nikos Yannacopoulos
- Data type mismatch in criteria expression
- Prev by Date: Re: Getting Cartesian Product in query
- Next by Date: Re: Show count if zero
- Previous by thread: Re: Data type mismatch in criteria expression
- Next by thread: Data type mismatch in criteria expression
- Index(es):
Relevant Pages
|
