In a button click event, you would need code something like the following.
Private Sub btnRunSQL_Click()
Dim strSQL as String
Dim strWhere as String
StrSQL = "SELECT * FROM tblData"
IF Not IsNull([Forms]![FrmSearchproj]![TxStreet]) THEN
strWHERE = strWhere & " AND Street = """ & _
[Forms]![FrmSearchproj]![TxStreet] & """"
End if
IF Not IsNull([Forms]![FrmSearchproj]![TxProj]) THEN
strWHERE = strWhere & " AND ProjectNo = """ & _
[Forms]![FrmSearchproj]![TxProj] & """"
End if
IF Not IsNull([Forms]![FrmSearchproj]![TxNo]) THEN
strWHERE = strWhere & " AND [No]= """ & _
[Forms]![FrmSearchproj]![TxNo] & """"
End if
StrSQL = StrSQL & " WHERE " & MID(5,strWhere)
Me.RecordSource = strSQL
End Sub
Also, change the Source query for the form to something that will return no
records
SELECT * FROM tblData WHERE 1 = 2
or
SELECT * FROM tblData WHERE False
Or
SELECT * FROM tblData WHERE txNo = "AZZISSZZZ"
Allen Browne may have an example on his site that you can look at.
AllenBrowne.com
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
John now works just as expected. Thank you for your help. You would use VBA
to build the SQL statement on the fly and control how exactly does this
works? You are probably right because in opening the form when the connection
is slow it takes forever ... and I guess because the query try to run on
opening the form already... where I only need to retrieve records once the
criteris has been set. Wold you mind to guide me in this process since it is
new to me?
the query that way.
:
Try changing the SQL statement to the following.
SELECT *
FROM tblData
WHERE (((IIf([Forms]![FrmSearchproj]![TxStreet] Is Null
And [Forms]![FrmSearchproj]![TxNo] Is Null
And [Forms]![FrmSearchproj]![TxProj] Is Null,False
,[Street] Like [Forms]![FrmSearchproj]![TxStreet] & "*"
And ([projectNo]=[Forms]![FrmSearchproj]![TxProj] or
[Forms]![FrmSearchproj]![TxProj] IS Null)
And [No]=Nz([Forms]![FrmSearchproj]![TxNo],[No])))<>False));
Personally, I would use VBA to build the SQL statement on the fly and control
the query that way.
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Silvio wrote:
Jeff, the project number field may or may not have a number in the table. If
a number is entered in my form then only records matching my entry matching
the form control/s will be retrieved from the table. In short, I am using a
form as search engine, which it has a subform that will display only record
matching the user seach string. If all the controls are left blank in the
main form, then no record will be retrieved at all. Use can enter any
combination of the 3 fields which are:
Streen number
Street Name
Project number
All the record in my database have a street number and a street name in it,
but only few have a project number in it. I have tested renaiming the [No] to
[StNo] and I am still having the same problem.
Thank you for your help on this
:
Are you checking for "null" AND checking for "zero-length string" ("")?
Regards
Jeff Boyce
Microsoft Office/Access MVP
I have a different search form only with the street number and street name
and works just fine. Also, if I enter a number in the project field of the
table the syntax below works just fine. So it appears to me the Nz is not
doing the work.
:
Not sure if this is what's affecting your situation, but "No" is probably
a
reserved word in Access (think "yes"/"no"). What happens if you change
the
name of that field on the table and in your query?
Regards
Jeff Boyce
Microsoft Office/Access MVP
I have 3 fields in my search form
1. No
2. Street
3. Project
If all the fields are left blank then no record will be retrieved... so
far
so good,... however, if the "Project number" in table my table is blank
the
then the nz does not appear to work and no record will display. My
records
will always have a street number and name but not a project number. My
code
is below, what's wrong with it?
SELECT *
FROM tblData
WHERE (((IIf([Forms]![FrmSearchproj]![TxStreet] Is Null And
[Forms]![FrmSearchproj]![TxNo] Is Null And
[Forms]![FrmSearchproj]![TxProj]
Is Null,False,[Street] Like [Forms]![FrmSearchproj]![TxStreet] & "*"
And
[projectNo]=Nz([Forms]![FrmSearchproj]![TxProj],[projectno]) And
[No]=Nz([Forms]![FrmSearchproj]![TxNo],[No])))<>False));
Thank you folks!
