Search the whole table and retrieve a record set

[BrucePerry]

I have a table with a list of fields (1-2-3-4-5...). Each piece of data entered in a field is a unique four diget number.
And i have a number of individual records with a unique id; so it's not just a columb of feilds there is actual rows too.
I would like to be able to create a form with a text box and a command button where the user enters a
four diget number in the text and selects the command button and then the form finds that record with
that nunber in it. I only find help/examples of searches for a specific feild. I want to search the whole table
and retrieve the individual record.



Maybe records; because there should not be more than one unique number.

[Micron]

Run your query with the button click. The sql would be like SELECT * FROM tblName WHERE [1] = Forms!frmMain.txtFourDigitNum OR [2] = Forms!frmMain.txtFourDigitNum OR [3] = Forms!frmMain.txtFourDigitNum.....
Tell me you did not actually name your fields 1,2,3 etc.?

[orange]

You could try my search sample at https://www.accessforums.net/showthread.php?t=43055

[BrucePerry]

Thanks for the help. I was able to get the statement to run on a limited number of fields.
MySQLstring = """SELECT * FROM [MS-NUMBER ONLY])"
MySQLstring = MySQLstring & "WHERE (([MS SOURCE REFERENCE] Like ""*" & strsearch & "*"")"
MySQLstring = MySQLstring & " OR ([MS-1] Like ""*" & strsearch & "*"")"
MySQLstring = MySQLstring & " OR ([MS-2] Like ""*" & strsearch & "*""))"""
Debug.Print MySQLstring
'Task = MySQLstring
Task = "SELECT * FROM [MS-NUMBER ONLY] WHERE (([MS SOURCE REFERENCE] Like ""*" & strsearch & "*"") OR ([MS-1] Like ""*" & strsearch & "*"") OR ([MS-2] Like ""*" & strsearch & "*""))"
Me.RecordSource = Task
'Me.RecordSource = MySQLstring
Me.txtSearch.BackColor = vbWhite

Task was the SQL statement; I then tried to break it down so that when I add more fields it would be easier to manage.
I could get the original statement in Task to break properly; so I tried adding values to a string and then ran the string
as an SQL........
But I couldn't get it to run even after looking at the string. They looked exactly the same but.

[Micron]

I would not expect it to run as you have it - too many double quotes, which I believe are causing early termination of sections of it. One can always take the output and dump it into the sql view of a new query and try to run it (or go to datasheet view if it's an action query you don't want to execute). Issues are often correctly highlighted, although not 100% of the time. Also, watch your lack of spaces at the end (or beginning) of a concatenation line, lest you end up with something that won't be properly evaluated. For this type of thing, I keep the variable name short, but feel free to revert to what you're doing. Note that the nested single quotes won't terminate the concatenation as your syntax will:

strSql = "SELECT * FROM [MS-NUMBER ONLY] WHERE (([MS SOURCE REFERENCE] Like '*'" & strsearch & "'*') "
strSql = strSql & "OR ([MS-1] Like '*'" & strsearch & "'*') OR ([MS-2] Like '*'" & strsearch & "'*'))"

You really should learn more about proper naming conventions for fields and the like. MS-2 is not good, either for the minus sign or the digit.