search any field on a table

[Mr. Capuchino]

Help please... how do you search for data using the select query? The things
is i have a table and by using the select query i want to type any field i
want to search for...
i've already used the select query but i was only able to specify one field
i wanted to search for...

 

[Brendan Reynolds]

SELECT * FROM TableName WHERE Field1 = Whatever OR Field2 = Whatever OR
Field3 = Whatever ...

Note that it very rarely, if ever, makes sense to search *all* fields - if
you're searching for a product name, for example, you're not going to find
it in the customer name field, and you're certainly not going to find it in
the order date field ...

 

[Mr. Capuchino]

Actually i have a table which have field names of check voucher numbers,
check numbers, payee and so on.. i think it would be easier if i have one
select query that i can input any of the two numbers or a payee name and it
would return the results...

 

[Brendan Reynolds]

SELECT * FROM tblTest WHERE ([CheckVoucherNumber] = [Check Voucher Number?]
OR [Check Voucher Number?] IS NULL) AND ([CheckNumber] = [Check Number?] OR
[Check Number?] IS NULL) AND ([PayeeName] = [Payee Name?] OR [Payee Name?]
IS NULL)

In this example, CheckVoucherNumber, CheckNumber and PayeeName are field
names, and [Check Voucher Number?], [Check Number?] and [Payee Name?] are
parameters.

 

[Mr. Capuchino]

Thanks i'll try this...

SELECT * FROM tblTest WHERE ([CheckVoucherNumber] = [Check Voucher Number?]
OR [Check Voucher Number?] IS NULL) AND ([CheckNumber] = [Check Number?] OR
[Check Number?] IS NULL) AND ([PayeeName] = [Payee Name?] OR [Payee Name?]
IS NULL)

In this example, CheckVoucherNumber, CheckNumber and PayeeName are field
names, and [Check Voucher Number?], [Check Number?] and [Payee Name?] are
parameters.

--
Brendan Reynolds (MVP)

Actually i have a table which have field names of check voucher numbers,
check numbers, payee and so on.. i think it would be easier if i have one
select query that i can input any of the two numbers or a payee name and
it
would return the results...

 

[Mr. Capuchino]

I still need help please... the code you gave me required me to enter all
three of the data before returning the results. i was wondering if i could at
least enter one of the three in a single select query and it would return the
results...

SELECT * FROM tblTest WHERE ([CheckVoucherNumber] = [Check Voucher Number?]
OR [Check Voucher Number?] IS NULL) AND ([CheckNumber] = [Check Number?] OR
[Check Number?] IS NULL) AND ([PayeeName] = [Payee Name?] OR [Payee Name?]
IS NULL)

In this example, CheckVoucherNumber, CheckNumber and PayeeName are field
names, and [Check Voucher Number?], [Check Number?] and [Payee Name?] are
parameters.

--
Brendan Reynolds (MVP)

Actually i have a table which have field names of check voucher numbers,
check numbers, payee and so on.. i think it would be easier if i have one
select query that i can input any of the two numbers or a payee name and
it
would return the results...

 

[Brendan Reynolds]

No, it doesn't. Just press the Enter key when prompted for the parameters
you want to ignore.

Alternatively, you could do it with just one parameter prompt - just compare
each of the three fields to the same parameter ...

SELECT * FROM tblTest WHERE [CheckVoucherNumber] = [Search Value?] OR
[CheckNumber] = [Search Value?] OR [PayeeName] = [Search Value?]

The difference is that the first example will allow you to search on any
combination of one, two, or three values, e.g. you could search for check
number = something and payee name = something else. The second example
prompts only once, but is therefore limited to always searching for just one
value.

--
Brendan Reynolds (MVP)

I still need help please... the code you gave me required me to enter all
three of the data before returning the results. i was wondering if i could
at
least enter one of the three in a single select query and it would return
the
results...

SELECT * FROM tblTest WHERE ([CheckVoucherNumber] = [Check Voucher
Number?]
OR [Check Voucher Number?] IS NULL) AND ([CheckNumber] = [Check Number?]
OR
[Check Number?] IS NULL) AND ([PayeeName] = [Payee Name?] OR [Payee
Name?]
IS NULL)

In this example, CheckVoucherNumber, CheckNumber and PayeeName are field
names, and [Check Voucher Number?], [Check Number?] and [Payee Name?] are
parameters.

--
Brendan Reynolds (MVP)

Actually i have a table which have field names of check voucher
numbers,
check numbers, payee and so on.. i think it would be easier if i have
one
select query that i can input any of the two numbers or a payee name
and
it
would return the results...

:

SELECT * FROM TableName WHERE Field1 = Whatever OR Field2 = Whatever
OR
Field3 = Whatever ...

Note that it very rarely, if ever, makes sense to search *all*
fields -
if
you're searching for a product name, for example, you're not going to
find
it in the customer name field, and you're certainly not going to find
it
in
the order date field ...

--
Brendan Reynolds (MVP)

message
Help please... how do you search for data using the select query?
The
things
is i have a table and by using the select query i want to type any
field i
want to search for...
i've already used the select query but i was only able to specify
one
field
i wanted to search for...