Searching on two fields from a register (1 Viewer)

[biofaku]

Hi, I have a Table that has two fields that can contain similar data. Let's call them "Name 1" and "Name 2".
I was thinking on making a Query that search for a name on either of those two fields, but I can't make it work.

The closest I came to achieving this was using this expression

Expr1: IFF([tbl_names_1].[Name 1]= "John";[tbl_names].[Name 1];SiInm([tbl_names_2].[Name 2]="John";[tbl_names_2].[Name 2]))
And on the criteria part I had to write "John" too, to filter all the registers that had "John" on either Name 1 or Name 2.

But that is really messy and also doesn't work if I want to search for names on that query from a Form.

Any idea on how can I achieve this?

 

[theDBguy]

Hi. Having two fields containing the same data (and named Name1 and Name2) sounds suspiciously non-normalized.

In any case, you could try using a WHERE clause like:

WHERE Name1="John" OR Name2="John"

 

[biofaku]

Oh, yes, I know its seems non-normalized, but I made it that way because those fields are reserved for people that are leading a project and at the moment. The thing is that some times the same name can appear on field 1 or others on field 2, I was looking for a way to see every register that has one or another on only one query field. I came to this solution.

I created a function that is named "Criteria", and takes the value of a combobox. Then when a I call that function from a query, it shows what I was looking for. I think is a nice patch.

 

[theDBguy]

Oh, yes, I know its seems non-normalized, but I made it that way because those fields are reserved for people that are leading a project and at the moment. The thing is that some times the same name can appear on field 1 or others on field 2, I was looking for a way to see every register that has one or another on only one query field. I came to this solution.

I created a function that is named "Criteria", and takes the value of a combobox. Then when a I call that function from a query, it shows what I was looking for. I think is a nice patch.

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[biofaku]

Lately I came for you looking for help and then even before you post something an idea comes to my head that mostly solves a problem that has been bugging me for hours. This forum is magical.

 

[theDBguy]

Lately I came for you looking for help and then even before you post something an idea comes to my head that mostly solves a problem that has been bugging me for hours. This forum is magical.

That just means you're getting better. Pretty soon, you'll be the one answering questions. Cheers!