same criteria for multiple field in a query

[Guest]

I need to filter the textbox content in the form( Serch Textbox) and it
should search textbox content in multiple field of the query.

 

[Jeff Boyce]

From what you posted (and what you didn't say), it sounds possible that you
have multiple fields in your table that could contain the text/string you
are searching for. If so, this is not a good use of the capabilities Access
can offer.

Can you post an example of the data you are trying to search?

 

[Guest]

Thanks for ur response,
I have multiple fields in my table. some fields contains text and some are
number in data type. If user type serach content on the text box from form it
should search all the field from the table/query and give matching field
result.

and
How to insert autonumber on the field(Already i stored many datas on field)
now i need to autoincrement to that field in next record. the format of that
field is "002569".
If i try to change the type of the field its showing its not possible. we
should start from first record. but i already store around 350 record. now i
can't able to type again all the records. please give me solution for thease.

thanks
imthiyaz

 

[Jeff Boyce]

See in-line comments below...

I have multiple fields in my table. some fields contains text and some are
number in data type. If user type serach content on the text box from form it
should search all the field from the table/query and give matching field
result.

As I mentioned in my first response, searching across all fields suggests
that your "database" would benefit from further normalization. Searching
multiple fields may not make good use of Access -- please post examples of
your data.

and
How to insert autonumber on the field(Already i stored many datas on field)
now i need to autoincrement to that field in next record. the format of that
field is "002569".

An Access "Autonumber" data type is NOT something you control - Access
determines the value. You can set the format (i.e., display) of a field (or
a control on a form or report) to match what you posted.

If i try to change the type of the field its showing its not possible. we
should start from first record. but i already store around 350 record. now i
can't able to type again all the records. please give me solution for

thease.

I don't understand. You have records, you want to control the "Autonumber"
value -- why? If you are displaying the autonumber value, reconsider.
Access Autonumbers are intended to be unique record identifiers, and can
help in relating tables. They are not generally fit for human consumption.

If you want an ID number you can better control, you'll have to create one
yourself.

 

[Guest]

Hi Thanks Jeff,
In Part no table(table Name) I have fields like class code, part no, Project
no... all are in number format. some are 3digits and others 6-8 digits. Other
fields like part description, project name etc., all are text field. the all
fields are in the same part no table.
If the user want to find from the find form(What i had created to find) In
side that i inserted text box and command button. when they enter on the text
box and press command button it should run the query. (This query is running
but not checking all the fields. its checking only first criteria what i had
given in query) But it should check all the fields. please send me some codes
to do this.

Thanks

Imthiyaz

 

[Jeff Boyce]

I do not have any code that does what you're describing. Perhaps another
newsgroup reader either has code or has time to create something like this.
Since this is an all-volunteer newsgroup, there may not be anyone ...

 

[Guest]

Hi,
Please help me to solve this
I had created find form it contains one text box and command button. same as
i have many query's depends on the search criteria.
I want to search the content of the find text box it should serach all the
fields of the table/query matching record it should display.
The table conatins different fields like part no, class code, description,
projetc name etc., some of the fields are numbers and some of them are text.

When i had created separate combo box for all (Like class code, part no,
project name etc.,) to find its related records. its sorting but only in
related to the that purticular fields. (like class code, part no). But i need
to place only one search box (any one of this. combo box, list box, text box)
it should search all the fields. atleast 3 fields. so that user can find
easily what they need.
please help me to solve this or give some refernce books or sites to solve.

Hope i will get reply for this.
Regards,
imthiyaz

 

[John Spencer]

It can be done. One method would be to get the value of the string. If the
string cannot be interpreted as a number, then the val function will return
Zero. If that is acceptable you can use.

Field: SomeNumberField
Criteria: Val(Forms!YourFormName!YourControlName)

Field: TextField
Criteria: Forms!YourFormName!YourControlName

If searching for Zero is not acceptable when inputting a string such as
"MilkShake", then post back with more details on your number field ranges.
You may be able to use the IsNumeric function and IIF to force a known
never-used value to search for.

Criteria:
IIF(IsNumeric(Forms!YourFormName!YourControlName),Val(Forms!YourFormName!YourControlName),-99999999)

However, you should note that if you have nulls in the fields you are
searching, this will ignore those. So to make your life more complex (and
your query more complex).

Criteria: IsNumeric(Forms!YourFormName!YourControlName)= False OR
Val(Forms!YourFormName!YourControlName)

 

[Guest]

Hi
Thanks for your reply.
My Search Criteria Sometimes "401ZMD001"(Its Project Name) and Some time
like "1024" (ClassCode). & 21198(Part No). They user if he type anything
related like i mentioned they query or the macro should run and give the
matching racord.

The Project Name, Class Code & Part No all the fields are one table (Part No
Log).

Waiting for ur response
Regards
Imthiyaz

 

[John Spencer]

The SQL for this would look something like the following

Parameters Forms!YourFormName!YourControlName Text(255);
SELECT *
FROM [Part No Log]
WHERE [Project Name] = Forms!YourFormName!YourControlName
OR [ClassCode] = Forms!YourFormName!YourControlName
OR [Part No] = Val(Forms!YourFormName!YourControlName)

That assumes that ClassCode is a string field and Part No is a number field.
The problem is that searching for 401ZMD001 will also find matches where Part No
= 401.

If you are not using a form to feed the value to the query, then try a parameter query.

Parameters [Find What?] Text(255);
SELECT *
FROM [Part No Log]
WHERE [Project Name] = [Find What?]
OR [ClassCode] = [Find What?]
OR [Part No] = Val([Find What?])