Help me understand this

[inquiringMind]

I have not used Access except in making some tables when doing web
development. I have taken Intro to Oracle and leaned SQL. I have taken
VB.net and have not coded in VBA . As a intern in non IT dept, I am
referring to an old project for an application I am developing using
Access 2003. In the following sub routine, I do not understand about
the WHERE clause part of the SQL statement "SELECT * FROM
qryASPFindApplRec WHERE False" because what I learned in Oracle, there
should eb a field name in WHERE clause with a value for criteria.

The sub routine is

Private Sub Clear_Click()
'Clear controls in form header and remove records from subform


Dim strMySql as String
strMySql = "SELECT * FROM qryFindApplRec WHERE False"


'Clear serach text boxes
Me![txtID] = NULL
Me! = NULL
...
...
...
...


'Reset subform's RecordSource property to remove records displayed in
sub form.
Me![frmFindApplRecSub].Form.RecordSource=srtMySql


'Move insertion point to text box for ID
Me![txtID].SetFocus


End Sub


---------------------------------------------------------------------------­-----------

I looked at the sub form's RecordSource property and it is
SELECT * FROM qryFindApplRec WHERE False;


I looked at the Query builder and saw a table for qryFindApplRec which
has no field named "False" but in the bottom portion area, I saw
'False" as the field in the first column with Criteria vale of <>False


I looked at the SQL View and it is
SELECT *FROM qryASPFindApplRec
WHERE (((False)<>False));


So, can someome explain to me what the False in the WHERE statement of
the SQL statement "SELECT * FROM qryFindApplRec WHERE False" means? I
thought that there should be a column name from a table and the value
as criteria in WHERE statement.

Thanks for any hlep.

 

[Guest]

That SQL string will return an empty recordset. The recordset is updateable.
It is like creating a data entry form. You can add new records, but you
can't see existing records.

 

[Jerry Porter]

A Where clause always includes an expression that evaluates to True or
False. This usually includes references to fields, e.g. Where [State] =
'CA'. In this example, the expression is True for some records and
False for others, and the True ones are returned by the query.

In your example, the expression in the Where clause is always False, so
no records are returned. (If you used "Where True", it would return all
records). You can use this in a form when you want no records
displayed. This is better than leaving the RecordSource blank, which
would result in form displaying errors.

Jerry Porter

 

[Marshall Barton]

I have not used Access except in making some tables when doing web
development. I have taken Intro to Oracle and leaned SQL. I have taken
VB.net and have not coded in VBA . As a intern in non IT dept, I am
referring to an old project for an application I am developing using
Access 2003. In the following sub routine, I do not understand about
the WHERE clause part of the SQL statement "SELECT * FROM
qryASPFindApplRec WHERE False" because what I learned in Oracle, there
should eb a field name in WHERE clause with a value for criteria.

The sub routine is

Private Sub Clear_Click()
'Clear controls in form header and remove records from subform

Dim strMySql as String
strMySql = "SELECT * FROM qryFindApplRec WHERE False"

'Clear serach text boxes
Me![txtID] = NULL
Me! = NULL
..

'Reset subform's RecordSource property to remove records displayed in
sub form.
Me![frmFindApplRecSub].Form.RecordSource=srtMySql

'Move insertion point to text box for ID
Me![txtID].SetFocus
End Sub
---------------------------------------------------------------------------­-----------

I looked at the sub form's RecordSource property and it is
SELECT * FROM qryFindApplRec WHERE False;

I looked at the Query builder and saw a table for qryFindApplRec which
has no field named "False" but in the bottom portion area, I saw
'False" as the field in the first column with Criteria vale of <>False

I looked at the SQL View and it is
SELECT *FROM qryASPFindApplRec
WHERE (((False)<>False));

So, can someome explain to me what the False in the WHERE statement of
the SQL statement "SELECT * FROM qryFindApplRec WHERE False" means? I
thought that there should be a column name from a table and the value
as criteria in WHERE statement.

The WHERE clause is an expression that results in a value of
True or False. When True, the record is included in the
query's dataset, when False the record is not included.
Thinking about that means that WHERE False selects no
records (i.e. an empty dataset), which is what the comment
in the code says it's supposed to do.

 

[inquiringMind]

I really appreciate all the help.

 

[Pieter Wijnen]

Better would be (SQL-89 compliancy):
SELECT A.* FROM MyTable A WHERE 1=0

Pieter

I really appreciate all the help.

--