Search results

No problem! J Appreciate your help so far! I need the report to display all the months and year-to-date. Months and Year-to-date are in the same column – Period1 of the cross tab query which is also the column heading of the cross tab query. I have a criteria applied to the Period 1 column by...

I have a query which has a column called period. The period column contains months (mmm/yyyy) and year (yyyy YTD) for the data that the query generates. Jan-09 Feb-09 Mar-09 Apr-09 May-09 Jun-09 Jul-09 Aug-09 Sep-09 Oct-09 Nov-09 Dec-09 2009 YTD...

I got to work. I had to play around with the operators on the intX and conTotalCol variables. It works like I want it to. Thank you so much for the help! Also, I mentioned about the criteria in my previous reply – I will put that up on a separate thread because it is really not connected with...

The period column in the cross tab query contains the month and year in the format mm/yyyy. The criteria I have used is Between [Forms].[Form1]![StartDate] And [Forms].[Form1]![EndDate] StartDate and EndDate are specified by the user in the form Form1. What I don’t see is is the last month in...

Here is the db...

I could post it, but the tables are on the network so I cannot post them. I will try to make dummy tables and post the db.

Ok. I have corrected that. This is what I have. ' Constant for maximum number of columns CrossTab query would create is 14 scale_type+12months+Year Const conTotalColumns = 14 ' Variables for Database object and Recordset. Dim dbsReport As DAO.Database Dim rstReport As...

Here is the modified code: Now it leaves out the first and last columns ' Constant for maximum number of columns CrossTab query would create is 14 scale_type+12months+Year Const conTotalColumns = 14 ' Variables for Database object and Recordset. Dim dbsReport As DAO.Database Dim...

uhh...now it leaves out the first column...has that got something to do with the zero based property? how can it be corrected?

Works! Thank you so much!!

Sorry I forgot I had already posted it. I will delete the other post. :) I have no clue what is happening. What do you mean by it is starting at the second field? How do I make out where it's failing? Any clue on what might be causing it to fail at the end?

Sure. Here it is PARAMETERS [Forms].[Form1]![StartDate] Text ( 255 ), [Forms].[Form1]![EndDate] Text ( 255 ), [Forms].[Form1]![Year] Text ( 255 ); TRANSFORM Sum(Union.METRIC) AS SumOfMETRIC SELECT Union.ABUYR, Union.TYPE FROM [Union] WHERE (((Union.PERIOD1) Between [Forms].[Form1]![StartDate]...

I get your point, but the issue is that the YTD and Monthly values are in the same column. So it will have to be one criteria for that column.

I am using a form to enter the dates. I did not fully understand your suggestion, can you please explain it to me? I thought there would be a way ot modify the criteria that I have specified in the query Between [Forms].[Form1]![StartDate] And [Forms].[Form1]![EndDate] to include the year so...

I have a database that calculates monthly on hand inventory and YTD on hand inventory. Each month is displayed as 01/2009, 02/2009, 03/2009 ....etc and there is a YTD number e.g. 2009 The above query is a crosstab query in which the employee name is a row heading, period (mm/yyyy and yyyy)...

I am getting this error everytime I run this script. Can anyone tells what is causing the error? To give you a brief description of my database - I have got a database that consists of a query which calculates monthly inventory data (MIOH) A cross tab query then creates a cross tab such that...

Thanks for your reply. I used to have a group by but I took it out to simplify things I will probably add that step in a separate query. I made small changes and used the following code SELECT Input.Material AS Material, Input.Plant, ([Quantity]/[SUMREZ]) AS QTY_SUOM...

Yes, DCrake is right, I am sure that is the issue, but the spreadsheet is linked and is dynamic(updated on every run) so I will need a more permanent fix probably on access side?

Thanks David and Endre. I will try it out and let you know if it works!

Hi, I have a query which works off of objects I do not own. The field that I would like to join is in text format in one source while it is in the numerical format in the other source. I want to convert it into the same format so that I can join the two fields. Here is the SQL I am using SELECT*...