Can someone check my query please

[IanHem]

Can anyone tell me if I have made a mistake writing this query?
Using Docmd.RunSql it fails

SELECT
Tbl_BespokeInspectionAreas.BespokeID, Tbl_BespokeInspectionAreas.BespokeNumber, Tbl_BespokeInspectionAreas.BespokeInspectionArea
FROM Tbl_BespokeInspectionAreas WHERE Tbl_BespokeInspectionAreas.BespokeId IS NOT NULL;

Thanks

 

[Grumm]

Do you have any message error ?
Also i guess BespokeID is a primary key, so there shouldn't be any record in your table where it is null. The where is pretty useless in that case.

 

[IanHem]

No I don't get any error.

I agree that the where part of the clause is useless.

Strangely if you cut and paste it into the query designer wizard it does work.

 

[Grumm]

Ok, sorry I should have seen that you try to use Docmd.RunSql...
that command is only for query's that make changes to the database (insert/delete/update)

What you need is this :

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT 
Tbl_BespokeInspectionAreas.BespokeID, Tbl_BespokeInspectionAreas.BespokeNumber, Tbl_BespokeInspectionAreas.BespokeInspectionArea
FROM Tbl_BespokeInspectionAreas WHERE Tbl_BespokeInspectionAreas.BespokeId IS NOT NULL")

 

[IanHem]

Thanks for that

 

[Grumm]

Here is a more practical example :

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT Tbl_BespokeInspectionAreas.BespokeID, Tbl_BespokeInspectionAreas.BespokeNumber, Tbl_BespokeInspectionAreas.BespokeInspectionArea
FROM Tbl_BespokeInspectionAreas WHERE Tbl_BespokeInspectionAreas.BespokeId IS NOT NULL")

'Check to see if the recordset actually contains rows
If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst 'Unnecessary in this case, but still a good habit
    Do Until rs.EOF = True
        MsgBox rs!BespokeID & " " & rs!BespokeNumber & " " & rs!BespokeInspectionArea

        'Move to the next record. Don't ever forget to do this.
        rs.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If
MsgBox "Finished looping through records."

rs.Close 'Close the recordset
Set rs = Nothing 'Clean up