Expression or SQL to count query columns (1 Viewer)

[Obe]

I'm looking for an expression or SQL for use in a query that will count the number of columns in another query. I do not need to count the records, I just need to know how many columns. I can't seem to locate a reference to a column count - everything points me to record count.

Thank you for your time in advance.

 

[namliam]

no (easy) way to count columns, not in SQL anyways, perhaps with some VBA, i.e. count the number of comma's in the Select .... from part
or open the query and loop for each column....

But as far as I know, no easy way to count columns.

 

[CJ_London]

try looking for a field count rather than column count.

You will want something like

FldCount = CurrentDb.QueryDefs("YourQueryName").Fields.Count

 

[Obe]

Thanks CJ, I'll give it a try and let you know how it turns out.

 

[namliam]

Hmz, learned something today afterall , its still VBA, but still easy(er) than I had anticipated

 

[Obe]

Thank Worked CJ! Thanks. I'm not sure why I wasn't thinking about the field count, must not have had enough coffee this morning. I'll tweak it with some criteria and then I move on.

Dim FldCount As String
FldCount = CurrentDb.QueryDefs("qrytest").Fields.Count
Me.txtTestItemNumber = FldCount

 

[CJ_London]

minor point - count is numeric so fldcount should be as well - particularly if you want to use it in a calculation

 

[Obe]

Mailman; if it were easy, everybody would be doing it!

 

[Obe]

Good point CJ, thanks again. I corrected that as it will be used in a calc.

 

[CJ_London]

@namliam

Hmz, learned something today afterall

Nice to be ahead of the game occasionally, can use it on any recordset (tho' not sure about ADODB)

 

[Obe]

I've discovered another way to count fields, this method only counts the fields in a query that have a number value (can be modified for Null or other data types). I created the following module and then use RCount ([fieldnames],[fieldnames]...) in a query expression. This allows me to use this method on multiple queries as needed.

Function RCount(ParamArray FieldValues()) As Variant
'-------------------------------------------------
' Function RCount() will accept a variable number of arguments,
' and returns a count of arguments containing numbers.
'-------------------------------------------------
Dim lngCount As Long
Dim varArg As Variant
For Each varArg In FieldValues
If IsNumeric(varArg) Then
lngCount = lngCount + 1
End If
Next
RCount = lngCount
End Function

 

[CJ_London]

won't this potentially return different values for each row if there is variation in the data for example a telephone number:

record1 '680123458' will return true
record2 '001 456 5344' will return false

Also, why do you return rcount as a variant - shouldn't it be integer or long?

 

[Obe]

You are correct CJ, but that is ok for my application as it is returning the data I need. I'm building a test item analysis db (analyzing results from individual questions); multiple exams with multiple versions and revisions for multiple courses (over a hundred courses) with different amount of participants. The value I need for statistical calculation is simply the amount of questions on an exam, for which I reference the examination result data (a comparison between individual participant responses and the answer key - returns a 1 for correct and 0 for incorrect). The examinations can and will have different amount of questions (from 10 to 50).

Once an exam answer key is entered (by course, version, and revision), the data entry clerk can select the course, exam version, exam revision, course date, and instructor followed by the participant results. A report can then be generated to view the statistical analysis of individual examination items (questions) and the selected answers (mean, standard deviation, KR-20, standard error of measurement, min/max discriminating power, etc). Exam date can be used as filters to analyze a particular exam for one class or many (increasing statistical accuracy). Same with the instructor.

 

[CJ_London]

OK - just wasn't what you were asking for in your original post