How to search all records on one field in sql

Question

Hi all
I want filter on 2 field but for field1 get input by textbox and FIELD2 search on ALL RECORDS but by where on 2 fields :

"Select * from tbl where field1="& textbox1.text &" and FIELD2=* "

But by * it not work
Please skip syntax4 error

Answer 1

Just leave it away?

SELECT f1, f2 FROM tbl
WHERE f1 = "x"
[optional: AND f2 != ""]
ORDER BY f1, f2;

Answer 2

Sandra Meyer
Ok Thank you thats right
If i want leave check box for two field to select optiinal user to 1 filter or 2 filter

in this case what is solution?
My Code:

field1 =field2 = "*"
If (Me.textf1= True) Then
field1 = Me.txtfield1
End If
If (Me.textf2= True) Then
field1 = Me.txtfield2
End If

Answer 3

Then just ask if either field 1 matches OR field 2:

SELECT f1, f2 FROM tbl
WHERE f1 = " textbox value 1"
OR f2 = " textbox value 2 "
ORDER BY f1, f2;

Result is every record, that matches one condition.

Answer 4

in this way if user select two checkbox , any record that is on first field match and on other field no match is will show and return

Answer 5

If you want to change they logic dependent from the input (f1 or f2 or (f1 and f2)), then you either have to implement it (possibly more user friendly) or to add it to your form. Use it as input:

SELECT f1, f2 FROM tbl
WHERE f1 = " textbox value 1"
" operator (AND | OR) "
f2 = " textbox value 2 "
ORDER BY f1, f2;

Answer 6

if user want select only one field1 to filter then above code is working?

Answer 7

You need something like a checkbox...
o AND
o OR
... and then have to use the label value of the chosen one or map it hard coded (easier). This is how such search applications work on many web pages.

Alternatively you can determine by code if both are filled > then use AND, otherwise use OR.