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

[vmauro]

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]

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]

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]

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]

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]

Sounds reasonable.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vmauro]

Thanks, will give it a go

 

[SkipVought]

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

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[milowu]

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]

@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!}