Anyone proficient with Access?

Count rows, convert text phrase to array, construct SQL query using array

[dougmcc1]

I'm trying to write a module in Access '97 and I need help with the following:

1. Counting the number of rows in a table and assigning the value to a variable. The macro would loop this many times.
2. This table only has one column and each row in this column contains a text phrase. I need to put each word into an array such as array("text", "phrase").
3. Using the array, I need to construct a query that uses "And Like" to search another table for all phrases which contain all the words in the array.

Any help is very much appreciated. Thanks in advance.

[aspdaddy]

Hi,

1. isnt this just - select count(*) from [Table]
2 - Look at the vbScript functions split() , getRows() and redim preserve.

You can select the whole table with a select *, convert to a 2D array with getRows() and then loop over the array, calling split() on each row and populating additional array columns, redimming as necessary when you reach ubound().

3 - If you achieve 2, this should just be a simple loop, something like this:

strSQL="SELECT * FROM [Table] WHERE TRUE "
For row = 0 To Ubound(arr,2) 
For col = 0 To Ubound(arr,1)
strSQL=strSQL & " AND [field] LIKE'" & arr(row,col) & "'"
Next
Next 

HTH

[dougmcc1]

Thanks for that, aspdaddy. How do I assign the result of the count rows query to a variable?

[mattglet]

SELECT COUNT(*) as the_count FROM [Table]

Now when you call it, you will need to do this:

myVar = rs("the_count")

[dougmcc1]

Thanks and how do I run the constructed query (strSQL) when it's assigned to a variable?