Another Passing Variable To SQL Question 1

[Du2good]

I’ve read many of the posts from gurus PHV, RoyVidar, REmou and many others but have yet to understand a few basics in passing variables in SQL.
Where is some straightforward and simple info for how to pass the var and the why of the method(s) that are required.
So, how do I get the code to work?
Why does it require that method? Simple please..
Where is some straitforward teaching?

Thanks
DBW

Do I need to:
Dim db As DAO.Database ?
Dim rs As DAO.Recordset ?

Using DAO 3.6 Object Libray
Using Windows XP, Access 2003
Code is Private Sub behind button.
Code that works is:

Rep = ctlList.ItemData(varItem)
StrSql = "UPDATE SQ_Access_Temp SET SQ_Access_Temp.Rep = '" & Rep & "' " & _
"WHERE (((SQ_Access_Temp.ID)<=7)or (SQ_Access_Temp.ID)>=12);"
DoCmd.RunSQL StrSql

Code having a runtime error 3464 Data Type Mismatch in Criteria expression:
St1 is an upper limit in the WHERE portion of SQL and I will be adding another variable for the lower limit in the WHERE portion of SQL, once I get St1 to work correctly.

StrSql = "UPDATE SQ_Access_Temp SET SQ_Access_Temp.Rep = '" & Rep & "' " & _
"WHERE (((SQ_Access_Temp.ID)<=7 or (SQ_Access_Temp.ID)[b]>='" & St1 [/b] & "'));"
DoCmd.RunSQL StrSql

 

[PHV]

As ID is defined as numeric in SQ_Access_Temp you shouldn't test it against string literal value, so get rid of the single quotes around St1:
StrSql = "UPDATE SQ_Access_Temp SET Rep = '" & Rep & "' " & _
"WHERE ID<=7 OR ID>=" & St1 & ";"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RoyVidar]

Prefixing with library is one of the ways of ensuring unambiguous referencing, and it seems to be the preferred version.

There are two libraries used to manipulate table structure and contents programatically, DAO and ADO. Usually, when working on 2000+ version, you'll have both libraries checked/available, and you will need to disambiguate any declarations of these, so that Access/VBA will chose the correct object. This is necessary since both libraries contain objects, methods and properties with the same name, but being different (type mismatch when assigning an ADO recordset to a DAO recordset variable).

There are some who promote removing the ADO reference or toggling the order of references (place DAO on top), but in my view, that's probably going to come back and bite you at a later point of time - which might then, in the worst scenario, make you have to go through and rewrite all code declaring and using DAO objects ...

For passing values into SQL string, wehter they are form references or variables, the data type of the field on which the criterion is set, or to which a value is to be appended/updated, is what determines delimiters.
[tt]
Text fields: single quotes (')
Date/Time fields: hash (#) for access
Numeric fields: no delimiter[/tt]

For strings, you may sometimes need to deal with single quotes within the string, where you can use the technique of "double-quoting" when using DAO, for ADO, you'll need to replace single occurance of single quote with two. You should be able to find samples through search.

Depending on locale, and version you may also need to format the dates to an unambiguous format ("yyyy-mm-dd" or "mm\/dd\/yyyy") before appending to a string, and for some of us, who are using comma as decimalseparator, we need also to format numbers containing decimals. This, I think, is a pretty good reason to start looking into parameterized queries, or at least start utilizing the parameters collection, in stead of this "dynamic SQL mess" (see for instance my later post in the last reference, or chiph's faq faq709-1526)

Here are some other explanations thread705-700362, thread705-1028386

Roy-Vidar

 

[Du2good]

PHV
Thanks again for your help. I now realize not to test strings against numbers and the why of my problem. Duh, a simple error in a VBA environment
Thank you

RoyVidar
Thanks for your help as well. The information provided requires additional digestion and I may have additonal questions. Like how does one know when to use DAO or ADO? Which indicates on my part a lack of knowledge of the definitons.

Thanks
DBW