Fields object syntax in VBA query

stepdev · March 2nd, 2006, 12:27 PM

I am new to ADO and trying to place a recordset field name in a vba query correctly. This works outside the query statement using double quotes with Debug.print and I get correct results. But inside the query statement shown below, I get an Error Invalid use of Syntax with Double or Single quotes. Everything works after the equal sign and up to the where statement.

.Open "SELECT Uparm, Aparm FROM WIST WHERE (Fields('Uparm').Value
= " & Me.Uparm & ")"

What is the correct syntax for this when placed after the WHERE statement? Documented text says it should be as it is typed with single quotes when in a query statement as above but it does not work. Fields('Uparm').Value

Debug.Print Fields["Uparm"].Value

I get the correct data when I use Debug.Print

Any help would be appreciated.

StepD

echovue · March 2nd, 2006, 12:41 PM

The tricky part is trying to distiniguish between VBA and SQL.

Try this...

.Open "SELECT Uparm, Aparm FROM WIST WHERE Uparm = " & Me.Uparm

if Uparm is not numeric you may need to add quotes

... WHERE Uparm = '" & Me.Uparm & "'"

Hope that helps,

Mike

Mike
EchoVue.com

stepdev · March 2nd, 2006, 01:27 PM

Tried it. I got "No value given for one or more required parameters"

Debug shows the right side as having a value but the left side no value. In my SELECT statement the 1st Uparm is from the table. the Uparm after the WHERE statement is referring to the recordset which is why I believe Uparm alone will not work.

Thanks for your response. I would be interested in any future solutions you may have.

StepD.

Bob Bedell · March 2nd, 2006, 01:39 PM

Hi StepD,

And don't forget about DateTime fields:

Code:

Sub Test()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strSQL As String

    Dim strCriteria As String
    Dim intCriteria As Integer
    Dim dtmCriteria As Date

    strCriteria = "Text1"    ' TextField
    intCriteria = 2          ' NumberField
    dtmCriteria = #1/2/2006# ' DateTimeField

    Set cnn = CurrentProject.Connection

    strSQL = "SELECT * FROM tblRecords WHERE TextField = '" & strCriteria & "'"
    ' strSQL = "SELECT * FROM tblRecords WHERE NumberField = " & intCriteria
    ' strSQL = "SELECT * FROM tblRecords WHERE DateTimeField = #" & dtmCriteria & "#"

    Set rst = New ADODB.Recordset
    rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly, adCmdText

    Debug.Print rst.GetString(adClipString, , ";")

End Sub

Also, ADO recordset object don't carry table schema information (keeps 'em light weight), so you can't get at a field's (or column's) Name through the Fields collection, only the field's Value.

All of the following are identical, returning the Value property (not the Name) of the EmployeeID field in the rstEmployees recordset object (assuming EmployeeID is the first field in the recordset).

The fields collection is 0-based.

rstEmployees.Fields("EmployeeID").Value
rstEmployees.Fields(0).Value
rstEmployees(0).Value
rstEmployees("EmployeeID").Value
rstEmployees!EmployeeID.Value
rstEmployees.Fields("EmployeeID")
rstEmployees.Fields(0)
rstEmployees(0)
rstEmployees("EmployeeID")
rstEmployees!EmployeeID

To get at the field's column name in the recordset's underlying table schema, you need to use either the OpenSchema method of a Connection object, or an ADOX Catalog object and its collections. See:

http://p2p.wrox.com/topic.asp?TOPIC_ID=39779

HTH,

Bob

stepdev · March 2nd, 2006, 02:05 PM

All correct, BUT none of those listed (tried them all) will work when placing them after the WHERE statement of a query. btw, I am looking for the field value, not the name.

Thanks Bob. All responses are welcome. Any addition help is ALWAYS appreciated.

StepD

Bob Bedell · March 2nd, 2006, 02:08 PM

You can't reference the Fields collection of a recordset object in the recordset objects Open method anyway, 'cause the recordset object hasn't been allocated yet (hence, the Fields collection hasn't been iinitialized yet).

Bob

stepdev · March 2nd, 2006, 02:51 PM

Bob,

It is not a question of if it's getting initialized, It's a question of If I can get all fields by not specify the WHERE why cant I get a specific field.

Thanks.

Bob Bedell · March 2nd, 2006, 02:55 PM

You could, however, reference the fields collection of an open recordset in the command argument used to open a second recorset, like:

Set rst2 = New ADODB.Recordset
strSQL2 = "Select * From tblRecords WHERE NumberField = " & rst.Fields("NumberField").Value
rst2.Open strSQL2, cnn, adOpenForwardOnly, adLockReadOnly, adCmdText

Bob

Bob Bedell · March 2nd, 2006, 03:04 PM

Mike was right with:

"SELECT Uparm, Aparm FROM WIST WHERE Uparm = " & Me.Uparm

Thats a perfectly valid SQL statement. Your error message is indicating that Me.Uparm isn't providing a value. What is Me.Uparm? Is it the name of a control on your form? The Me syntax suggests that you're trying to pull a value from a contol on your form. So your SQL should be:

"SELECT Uparm, Aparm FROM WIST WHERE Uparm = " & Me.TheNameOfYour Control

Bob

Bob Bedell · March 2nd, 2006, 03:19 PM

For example, if your control is a textbox bound to a text field in your table, use:

strSQL = "SELECT * FROM tblRecords WHERE TextField = '" & Me.txtTextField & "'"
rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly, adCmdText

If your control is a textbox bound to a number field in your table, use:

strSQL = "SELECT * FROM tblRecords WHERE NumberField = " & Me.txtNumberField
rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly, adCmdText

If your control is a textbox bound to a DateTime field in your table, use:

strSQL = "SELECT * FROM tblRecords WHERE DateTimeField = #" & Me.txtDateTimeField & "#"
rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly, adCmdText

Bob