• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 53
  • Last Modified:

Defining a String Value using SQL

In MS Access VBA, is it possible to define the value for a string using SQL? I keep getting errors trying to get this to work. I've got a bit rusty in the past 4 years... Here is an example of what I have been attempting:

Private Sub Command1_Click()

Dim strCCN as String, strDESCR as String

strCCN = " & FRM_MAIN.CCN & "
strDESCR = DoCmd.RunSQL("SELECT DESCR FROM TBL_INV_REPORT WHERE TBL_INV_REPORT.CCN = ' " & strCCN & " ' ; )
0
Stephen Daugherty
Asked:
Stephen Daugherty
  • 2
1 Solution
 
BembiCEOCommented:
strCCN = FRM_MAIN.CCN (assuming this is a text field)
 strDESCR = DoCmd.RunSQL("SELECT DESCR FROM TBL_INV_REPORT WHERE TBL_INV_REPORT.CCN = ' " & strCCN & " ' "; )
0
 
Gustav BrockCIOCommented:
You can use:

strDESCR = Nz(DLookup("DESCR", "TBL_INV_REPORT", "CCN = '" & FRM_MAIN.CCN & "'"))

Open in new window

/gustav
1
 
Fabrice LambertFabrice LambertCommented:
Hi,

Building an SQL query with user input as strings can be a source of troubles:
What if the user enter something with quotes ? Double quotes ? Or a mix of both ?
Your query will become invalid, and MS Access will complain (by raising an unexpected and unpleasant error, or not running the query).

to prevent this, you can use an advanced technique: Parameterized query:
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim strSQL As String

    '// First, define your SQL query, with parameters:
strSQL = vbNullString
strSQL = strSQL & "PARAMETERS CNNvalue Text(255); & vbcrlf
strSQL = strSQL & "SELECT DESCR" & vbcrlf
strSQL = strSQL & "FROM TBL_INV_REPORT" & vbcrlf
strSQL = strSQL & "WHERE TBL_INV_REPORT.CCN = [CNNvalue];"

    '// 2nd, create a temporary querydef object
    '// (name must be unique)
Set db = CurrentDb
Set qd = db.CreateQueryDef("tmpQry", strSQL)

    '// 3rd, fillup querydef parameters:
qd.Parameters("CNNvalue").Value = strCCN

    '// 4th, execute the querydef
qd.Execute dbFailOnError

    '// 5th: properly destroy objects
qd.Close
db.QueryDefs.Delete qd.Name
Set qd = Nothing
Set db = Nothing

Open in new window

Additional bonus: It protect from SQL injection.

Side notes:
Prefer using the Execute() function when running sql queries, so MS Access won't prompt you when running "Action queries", and you won't have to mess up with docmd.SetWarnings. Plus it can raise an error in case of troubles.
0
 
Gustav BrockCIOCommented:
It was a select task, not an action task.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now