VBA Code help

[aamer]

Any help available?

[pbaldy]

I'm on the road. Hopefully Steve can follow up.

[ssanfu]

I looked at your dB. The problem (well, not so much a problem - more the totals queries work a little different that a standard query) is that you are using totals queries.
With a totals query, all fields in one or more records have to match to be grouped together to have one record (line) unless you have a different option for the Total line.
Having a column "PurDate" as "Group By" causes two lines for Feb 2014 when "PurCompanyName" equals "AK Transport" and "Purchase Description" equals "TR Charges": 2/14/2014 and 2/24/2014.

I changed the form/query and the code to open the report. The numbers from the report matches your picture. (See "Sample 01D")

[aamer]

Thank you Paul & Steve for your help and guidence.
With your help I am able to get the reports on time otherwise I would have been stuck with lot of paper work.
One request, I am attaching the SAMPLE 01E please see if I have done it correctly as you mentioned.
Untill now I had never worked with crosstab querys, but now I made a Query and a report. But am failed to understand why in crosstab querys formula's dont work in criteria.
I tried to filter the report based on combo boxes on a form. Simple filteration is working but when I try to generate report by Account Title, Item Description & Units. It does not work. If you have time can you please have a look at the sample 01E and the FORM is DialogMonYrCross.
Thank you again for your kind help.


Attachment 23817

[ssanfu]

Untill now I had never worked with crosstab querys

I have never had occasion to use a crosstab query, so I won't be much help at this point.

Maybe Paul will know.

I kind of "roll my own" crosstabs. No one told me it couldn't be done, but it works for what I need. If I get some free time, I will take a look.

[aamer]

Thanks Steve
I appreciate

[pbaldy]

I get an error trying to download the attachment in post 19.

[aamer]

I have uploaded 01F - SAMPLE again.

01F - SAMPLE.zip

[pbaldy]

I'm not clear on what you're trying to do. You've got a crosstab based on another crosstab? There is no criteria. The error when running the second crosstab is because the field you specified doesn't exist:

MonYrCross.MonYrCross.Yr

The first crosstab returns a year, not the yr field. In other words, 2014 & 2015, not yr.

[aamer]

I guess you are looking at Query: MonYrCrossUnits
This was suppose to be deleted. I was testing this.

If you open the form DialogMonYrCross on this form I have highlighted with red lables.
When I open the report it shows other units also where as I only wanted it to show the values by month where Kg is used.

I tried to put the Criteria [Forms]![DialogMonYrCross].[CboITRC] under Purchase Units in crosstab query MonYrCrossUnits but it does nt accept.

[pbaldy]

Well, you've got a wherecondition that doesn't include that field:

strCriteria = "[Purchase Description] = """ & Me.cboCustomers & """"

To use a query criteria, you have to set the parameters:

http://allenbrowne.com/ser-67.html

[aamer]

Thanks a million Paul & Steve.

Because of both of you my life has become easy. specially the link http://allenbrowne.com/ser-67.html Paul sent me is very informative & helpful.
I am able to fix the 01F - SAMPLE and got the desired result. This concludes the project for the time being.

Attached is the 01G - SAMPLE.

Thank you both once again.

01G - SAMPLE.zip

[pbaldy]

Happy to help!

[ssanfu]

What Paul said.



Ready to mark this solved?