report from vba ado recordset HELP please! :)

[Spyridon]

This was moved from the VBA Modules forum as I think it's starting to apply more here, maybe.

What I'm wanting is to have a report field based off a value passed through VBA. The field, a textbox, is in the detail section, and at present the code that I have written populates it with the last recordset number. If the table the recordset has 4 records in it with values of 101,202,303, and 404, the field presents 404. What I'm wanting is for it to display all of them, just as any regular report based off a query would. Here's my problem. I have a field that contains different types of sales. There are 16 different sales types, each with it's own number. Some of those types are two sales added together. For example, an order is placed for a pair jeans, and that order number is 100. An order could have been placed for a shirt, and that would be number 5. At the same time, both of them could have been bought, and the number would be 105.

So I need a report to list something like this:

Sales Order Jeans Shirts
1111 1 0
2222 0 1
3333 1 1

The underlying query has one column, SalesType, that has the values listed as :100,5,105

So I basically need it to extract all the 100's and count them, and then do the same for the 5's, but then take the 105's and populate two different fields. The only way that I've been able to do it in the report is with the IIF function. The problem with that is that in the report footer the fiels are summed, so Jeans would = 2, Shirts = 2,
and then Total Sales would = 4. It won't sum a value from an IIF function (or I can't get it to), so I'm left with code to do my work. Any ideas? Here's the code that I have so far :



Dim salesNum As Integer
Dim Sales As Integer
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strsql As String


strsql = "SELECT * FROM Sales"

cnn.Open CurrentProject.Connection

rst.Open strsql, cnn

Do While Not rst.EOF
Sales = rst.Fields("SalesType&quot.Value
Me.listb.Value = "=" & Sales & ""

rst.MoveNext
Loop

 

[grtammi]

Technically, what you have said is correct : 2 sales of jeans, and 2 sales of shirts is correct. But the problem you're running into is that only 3 transactions took place, and you're looking to obtain only the total # of transactions? Did I understand your question correctly?

If so, then all you would really need to do is get a sum of all the 100 transactions, add it to the 5 transactions, and subtract the 105 transactions from that total.

Using your example above:

2 jeans transactions + 2 shirt transactions - 1 dual transaction = 3 total transactions.

HTH (actually, hope I understood this right ... I've got a huge combination of ASP, HTML, and Javascript floating in my head still from work today - cause blurry vision ya know?)



Greg Tammi, IT Design & Consultation
Work:

http://www.atsbell.com

Home:

http://members.shaw.ca/gregandliz

 

[dpc123]

Hi,

I am using AS400 with DB2/400 running JD Edwards 7.3. Crystal Reports ver 8 is installed on my windows PC.
I want to print a report which gives the cash book status of banks. I have a field called "opening balance" and another field called "closing balance". I want the report to contain a field called "Total Amount" which contains the difference of the two initially mentioned fields i.e 'opening balance' and 'closing balance'. Can anyone show me the way. I am very new to reports.

Thanks

DPC