Douglas J. Steele said:
What do you mean by "directing the sql to a query"?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
As a test I created a table from [qry Query] (using make table) and
directed
my sql to it. This worked just fine. Unfortunately, I can't employ
this
solution and need to use the query. Can I force these fields to be
explicitly text somehow in the query?
:
The displayed sql looked fine. Here it is:
SELECT * FROM [qry Query] WHERE [X] = '9958228' AND [Y] = '23301' AND
[Z]
=
'S0001'
X and Y are formatted as text in their base table, but I am directing
the
sql to a query.
I'm not using Lookup. The error is attributed to the Set rs =
db.OpenRecordset(strSQL) statement.
Do I need to add a second parameter to the OpenRecordset call like
dbOpenSnapshot?
:
Okay, try:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM [qry Query] WHERE " & _
"[X] = '" & Me.MyX & "' AND " & _
"[Y] = '" & Me.MyY & "' AND " & _
"[Z] = '" & Me.MyZ & "'"
Debug.Print strSQL
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
Now, when the code runs (and fails), go the Immediate Window
(Ctrl-G)
and
look at what's printed there for the SQL statement.
Oh, and are you sure they're all text fields? Did you perhaps use
the
Lookup
Field misfeature, so that while text appears in the field, the field
is
actually storing a numeric value?
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM [qry Query] WHERE " & _
"[X] = '" & Me.MyX & "' AND " & _
"[Y] = '" & Me.MyY & "' AND " & _
"[Z] = '" & Me.MyZ & "'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
All fields are string.
:
What's the actual SQL statement you're using?
What are the data types of the fields?
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message
I got a error, "Too few parameters. Expected 2.".
:
You still haven't really explained what you're trying to do,
but
what
about
something like:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim varField4 As Variant
Dim varField5 As Variant
strSQL = "SELECT Field4, Field5 " & _
"FROM MyTable " & _
"WHERE Field1 = " & Criteria1 & _
" AND FIeld2 = '" & Criteria2 & "' " & _
"AND Field3 = " & Format(Criteria3, "\#yyyy\-mm\-dd\#")
Set db = CurrentDb()
Set rs = db.OpenRecords(strSQL)
If rs.EOF = True Then
' No matching record found
Else
varField4 = rs!Field4
varField5 = rs!Field5
End If
Here, I've assume Field1 is numeric, Field2 is text and Field3
is
a
date
(just to show you the different syntax requirements)
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
message
I suppose I could use the do loop to find the record I'm
looking
for,
but I
thought there would be a more direct way to do it
programmatically.
:
This is helpful, but I still need to position rs at a
particular
record
on
the basis of field criteria that I have. Say I have field
criteria
values of
Criteria1, Criteria2, and Criteria3 for rs fields Field1,
Field2,
and
Field3.
I need access to the field values Field4 and Field5 when
the
criteria
are
met.
I read ahead about your comment about DAO. I'm OK using
ADO,
but I
just
can't find decent enough resources to get a sufficient
critical
mass
of
knowledge. Any recommendations - books or sites???
Thanks!
:
You don't say exactly what you're doing, but the general
code
would
be
Dim rs As DAO.Recordset
Dim varField1 as String
Dim varField2 as String
Dim varField3 as String
Set db = CurrentDb
Set rs = db.OpenRecordset("YourQuery")
Do While Not rs.EOF
varField1 = rs!Field1
varField2 = rs!Field2
varField3 = rs!Field3
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
This assigns three fields to three variables, but you
could
assign
the
field
data to controls in your form. You could also include
conditional
If...Then
around the assignment lines, to pull values from a
particual
record
in
YourQuery.
