Loop through each column for a value and return the entire row when tr

vmauro · Mar 20, 2012

Hi All, I hope you could help me.

I have a table that has several columns, but i need to search 12 of them for a value and return the entire row when true, then contuine searching the column and procede to the next column and repeat.

columns heeading

data1 data2 data3 ---- data14

I need to search columns data3 - data14

for a value of 409.01 or 409*

once found, i would like it to return the row and all its data.

i search 'looping through columns in a record set" but just didnt find what i needed.

I know I need to do "for each" loop statement but I just having a hard time getting it started.

thanks inadvance

SkipVought · Mar 20, 2012

hi,

for a value of 409.01 or 409*

Would not 409* ALSO catch 409.01??? And if this is a numeric field, it would be better to use INT([Your Field]) = 409

Why do you need VBA? Why not just do a query?

HOWEVER, it seems that your table is not normalized, which compounds the difficulty of your task.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

vmauro · Mar 20, 2012

sorry, 408.01 or 409*

as for the table, i have no control of. i am currently doing 12 union quries to create the table of all records that meet the critera.

since the table has 12 columns that i need to seach through, i thought a VBA code (for each col(x) ) statement would work better. i also need to filter a data range and other fieldss in the record set,

I didnt know how to use just a query. since you a limited to critera rows

SkipVought · Mar 20, 2012

Code:

select *
from YourTable
Where data1 = 408.01
   or int(data1) = 409
   or data2 = 408.01
   or int(data2) = 409
   or data3 = 408.01
   or int(data3) = 409
......

there is no limit when you code the SQL directly.

TIP: Code your SQL in a text editor, rather than the SQL Window in Access, and PASTE into the SQL Window.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

vmauro · Mar 20, 2012

Thanks, so if I need to have a date range critera on a certain field, I would use the following

select *
from YourTable
Where (data1 = 408.01
or int(data1) = 409
or data2 = 408.01
or int(data2) = 409
or data3 = 408.01
or int(data3) = 409) and datefield (between #1/1/2012# and #2/29/2012) and somedatafield = "spec"
......

SkipVought · Mar 20, 2012

Sounds reasonable.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

vmauro · Mar 20, 2012

Thanks, will give it a go

SkipVought · Mar 20, 2012

The proof is in the putting the code to the test!

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

milowu · Aug 21, 2012

Hi, I have a very similar problem. However, I am working with 200 columns. Is there a more efficient way to loop through each columns? Thank you!

http://www.tek-tips.com/viewthread.cfm?qid=1678322

SkipVought · Aug 22, 2012

@milowu

Please post your own question in a new thread.

This thread should only contain posts relates to the OP's question.

FYI, If you have a table with 200 columns where columns contain similar data such as data1, data2, date3..., you should SERIOULSY consider 1) NORMALIZING your data and 2) preventing such database blunders from ever being perpetrated again.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Loop through each column for a value and return the entire row when tr

vmauro

Technical User

SkipVought

Programmer

vmauro

Technical User

SkipVought

Programmer

vmauro

Technical User

SkipVought

Programmer

vmauro

Technical User

SkipVought

Programmer

milowu

Technical User

SkipVought

Programmer

Similar threads

Part and Inventory Search

Sponsor