Re: Data type mismatch in criteria expression
- From: Nikos Yannacopoulos <nyannacoREMOVETHISBIT@xxxxx>
- Date: Thu, 04 Aug 2005 11:36:53 +0300
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= " & intStdIdHTH, Nikos
.
- Follow-Ups:
- References:
- Data type mismatch in criteria expression
- From: Seth
- Data type mismatch in criteria expression
- Prev by Date: Re: Key violation and lock violation stop delete query!!
- Next by Date: Re: Subreport with union query
- Previous by thread: Data type mismatch in criteria expression
- Next by thread: Re: Data type mismatch in criteria expression
- Index(es):
Relevant Pages
|
