Code Not Showing Accurate rst.Fields.count 1

[joatmofn]

This multi-star problem is very perplexing to me

Overview:
Use a form to set beginning and ending date to supply a CROSS tab query with a date range. And use the cross tab query as the record source for a report setup to use a cross tab query.

When running the query alone, the query works fine. It has two parameters in it to fetch the start and stop dates from the form. Works fine.

My report is made to work with cross tab queries. It has several textboxes in the header and in the detail section. It has an open event that creates a record set of the supplied cross tab query. It is taken directly from the Getz, Litwin, and Gilbert Access book. It takes the results of the query and plugs them into the textboxes. Or does it??? This is the problem.

Actually, it does not. In the open event, there is a variable assignment - intColCount = rst.Fields.Count. that contains 0 after it runs.

I am not able to figure out why it contains 0. It should contain a number equal to the number of fields in the recordset. In this case, the crosstab query.

I hope this is clear enough to understand. Any help that you may be able to provide would be very much appreciated.

One more thing, in case you're wondering why a cross tab, the reason is we need to run a report for one or more employees and have the format list the employee name in the 1st column of the report and next to the name, a column for each day of the week, S thru Sat, with a total at the end. Like a spreadsheet. If you have an idea on how to do this without a crosstab query, I'm listening!

Thanks again.

 

[cmmrfrds]

There may not be a fields count if no rows are returned. That would be my guess. Put is a check where you do the field count for an EOF check also.

If rst.EOF then
msgbox " no records"
end if

Since there are a finite number of columns that correspond to 1 value the day of week, then an iif statement could determine the day.

Select employee, iif(datepart('d',yourdate)=1,"monday",null) as monday, iif(datepart('d',yourdate)=2,"tuesday",null) as tuesday, etc.......

This is the general idea.

 

[joatmofn]

Right, no records are being returned by the query when the query is the recordset for the report. Why does the query return records, but the report does not? It's got me stumped.

Not sure what you mean by the select employee, iif statement.

 

[Axoliien]

Sometimes the count will return an incorrect number due to how you have your recordset set up. Try changing the cursor type and see if that will fix your problem, it seemed to fix it when I had the same error.

 

[joatmofn]

I found that by hardcoding a date criteria such as between #3/1/2004# and #3/7/2004# in the query itself, the report works well.

When I have the criteria such as this: Between [forms]![frmReportWorkOrderLabor]![BeginningDate] And [forms]![frmReportWorkOrderLabor]![EndingDate], access complains that this not a valid expression.

However, by creating the following two query parameters: [forms]![frmReportWorkOrderLabor]![BeginningDate] and [forms]![frmReportWorkOrderLabor]![EndingDate], each as a date/time field, the error goes away and brings me right back to my original problem.

The report with its recordsource being the query returns no records. It has something to do with the reference to the form (which by the way is running with a valid date).

Overall, it works fine when I run the query, but not the report. I'm stumped!!

 

[joatmofn]

I tried changing the cursor type to each of 4 different cursors, but no help. same thing.

 

[cmmrfrds]

Paste in the code you have in the onopen event.

 

[RoyVidar]

Hm - just wondering if you perhaps would have to verify/evaluate those parameters thru the parameters collection of the command object, as one would do with stored queries with parameters (here's a sample of that thread709-819033).

Roy-Vidar

 

[joatmofn]

Roy - Beautiful! I followed your thread and copied your snippet into my open code, then referenced adox and it worked like a charm (fixing my parameter problem). I'm not exactly sure why it worked, so I'll be sure to dig up some help on your code. It must have been the eval call. Not sure really.

None the less, thanks a bunch. Here's a star for your excellent help. I tried to give you 2nd star, but am not sure if it actually went through. Again, thanks a bunch!