SELECT with WHERE with .FINDFIRST

[Paul Dennis]

I am trying to select all records from table T_Availability where ITEM =
E_ITEM, SEVERITY = E_SEVERITY, however I can't get the following to work. I
get problems on the WHERE. I get a "Data type conversion error", however both
ITEM & E_ITEM are both strings - If I remove the WHERE clause then it works
fine but then when the .FindFirst is done it returns nothing - help.

Dim rst As DAO.Recordset
Dim strItem As String
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset( _
"SELECT ITEM, SEVERITY, PROBLEM_ID " & _
"FROM T_Availability ORDER BY ITEM", _
"WHERE ITEM = E_ITEM", _
dbOpenSnapshot)

strItem = "ITEM = '" & E_ITEM & "'"
rst.FindFirst strItem

 

[Robert Morley]

Switch your ORDER BY and WHERE clauses around. I think that should work.
Also, be careful of the spacing in the command string, and adding the & 's
at the end of every line. So it should look like this when you're done:

Set rst = DB.OpenRecordset( _
"SELECT ITEM, SEVERITY, PROBLEM_ID " & _
"FROM T_Availability " & _
"WHERE ITEM = E_ITEM " & _
"ORDER BY ITEM", _
dbOpenSnapshot)


Rob

 

[Paul Dennis]

Still can't get the WHERE to work. I have now changed the code to that below
and while it all seems to work the fields all seem to be empty?


Function Availability(E_ITEM As String, E_SEVERITY As Integer, E_PROBLEM_ID
As String) As String
On Error GoTo Err_Availability

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim strItem As String
Dim strSQL As String
Dim DB As DAO.Database

Set DB = CurrentDb
strSQL = "SELECT * " & _
"FROM T_Availability " & _
"ORDER BY ITEM;"
Set rst = DBEngine(0)(0).OpenRecordset(strSQL)

strItem = "ITEM = '" & E_ITEM & "'"
rst.FindFirst strItem
If rst.NoMatch Then
MsgBox "No records found with " & strCountry & "."
End If
Availability = PROBLEM_ID

 

[Robert Morley]

Well, it looks okay to me, but my DAO's a little rusty. The first thing I
would check is the recordcount for the recordset.

After Set rst = DBEngine..., add

rst.MoveLast
MsgBox rst.RecordCount

See if that returns the expected number of records.

Oh, and I assume "strCountry" was a typo, and you'd want "strItem" in there,
or something of that nature?



Rob

 

[Clinton]

First of all, you have, the syntax for the OpenRecordset method wrong.

Set rst = DB.OpenRecordset( _
"SELECT ITEM, SEVERITY, PROBLEM_ID " & _
"FROM T_Availability ORDER BY ITEM", _
"WHERE ITEM = E_ITEM", _
dbOpenSnapshot)

Not only is the ORDER BY in the wrong position, but the WHERE clause is not
even in the recordset string - it is in the Recordset Type argument spot (you
have a comma in there).

OpenRecordset (Name As String, Type, Options, LockEdit)

So you are passing:

Name: "SELECT ITEM, SEVERITY, PROBLEM_ID FROM T_Availability ORDER BY ITEM"

Type: "WHERE ITEM = E_ITEM"

Options: dbOpenSnapshot

That will cause a Type conversion error because the recordset's Type needs
to be a Long (dbOpenSnapshot), and not your WHERE clause string.

It should corrected by putting the commas in the right place, and swapping
the WHERE/ORDER BY clauses:

Set rst = DB.OpenRecordset( _
"SELECT ITEM, SEVERITY, PROBLEM_ID " & _
" FROM T_Availability " _
& " WHERE ITEM = E_ITEM " _
& " ORDER BY ITEM", _
dbOpenSnapshot)


In your second try, using the FindFirst, you are trying to search for the
field value "E_ITEM". This cannot work, as the criteria value passed to the
FindFirst cannot be set to a field name, but has to be a hard coded value or
a value held with-in a Variable, or a value returned from a method/property
(such as a recordset's field value):

Dim strSearchFor As String

strSearchFor ="SomeThing"

strItem = "ITEM = '" & strSearchFor & "'"

You would have noticed that something is wrong here if you would have had
Option Explicit at the top of the module. This would have raised an error
because "E_ITEM" hasn't been dimmed as a variable.

These are basic problems for beginners, even though the problem with the
commas in the wrong position, or syntax errors can always happen when working
fast.
But the other things happen especially when not consulting the documentation
enough, and I would suggest that you get some reading material on this, or
use the VB Help files (plenty of examples) as to how to build sql statements,
open recordsets, and how to use the DAO methods properly, along with learning
good programming techniques, such as making sure all variables get declared
(Option Explicit).

 

[Clinton]

VB Help files

= VBA Help Files, Access Help files, or DAO/JET Help Files

For DAO/JET help see the JetSql40.chm, Dao351.chm, DAO35.Hlp, JetSql35.hlp

or simply put your curson on the method/property and hit F1