Parametrized crosstab query report problem... 1

[vtuda]

(First of all, sorry because my English)

Here's my problem:

I have a crosstab query working fine. It asks for a range of dates entered in a form. But I'm not able to generate a report. Using wizard report or design mode report generator once I select my crosstab query on the list, there's no available fields displayed. Any clue?...

(Not asking for any parameters then report generator works ok).

THANKS IN ADVANCE,

Vianey

 

[kjv1611]

It is probably going to be due to the parameter fields, as it sounds like you've already guessed. Is there a way to create a separate query which would then be based on the results of the crosstab query, or either create a table from the crosstab query, or create a recordset from the crosstab query, and set the report to that in code?

Another option: Take the SQL from the query, and adjust it to your needs, and create a SQL statement in VBA code, which would add in the values for the parameter fields via variables.

 

[vtuda]

kjv1611,

Thank you so so much for advice!

Actually I was considering some of those ideas but not the last one which is the most interesting to me and sure I'm going to try it.

But let me say something pretty weird is happening now. I already was able to design this report. It was on design mode. It was asking me for parameters' value for about 4 times. When I canceled it, suddenly it showed me the crosstab query fields. Well, I was excited and everything seemed to be OK.... I ran the report and put the first day of this month on the range and it worked fine. Then I put the second day and then I got the message of: "The Microsoft Jet database engine does not recognize " as a valid field name or expression." It was the same for the third day of the month.

What's going on?. How is it possible to be working just for the first day of the month and not for the rest?... There's not big deal because this report will be generated for a range of dates of course but it could be useful also for a same date.

Here's SQL statement:

PARAMETERS [Formularios]![RANGO]![DEL] DateTime, [Formularios]![RANGO]![AL] DateTime;
TRANSFORM Sum([ENCABEZADO VENTAS DIARIAS Consulta].IMPORTE) AS SumaDeIMPORTE
SELECT [ENCABEZADO VENTAS DIARIAS Consulta].[CATALOGO DE VENDEDORES].NOMBRE, [ENCABEZADO VENTAS DIARIAS Consulta].FECHA, Sum([ENCABEZADO VENTAS DIARIAS Consulta].IMPORTE) AS [Total de IMPORTE]
FROM [ENCABEZADO VENTAS DIARIAS Consulta]
WHERE ((([ENCABEZADO VENTAS DIARIAS Consulta].FECHA) Between [Formularios]![RANGO]![DEL] And [Formularios]![RANGO]![AL]))
GROUP BY [ENCABEZADO VENTAS DIARIAS Consulta].[CATALOGO DE VENDEDORES].NOMBRE, [ENCABEZADO VENTAS DIARIAS Consulta].FECHA, [ENCABEZADO VENTAS DIARIAS Consulta].FECHA
PIVOT [ENCABEZADO VENTAS DIARIAS Consulta].DESCRIPCION;

*Note of sintaxis: Formularios=Forms

Crosstab query works well when I enter the same date on that range of dates. It drives me nuts

 

[vtuda]

It's me again!

Please don't bother to answer my last question. I GOT IT!. Because report is designed including all fields from crosstab query, it only works if every field contains data. Otherwise, obviously Microsoft Database engine doesn't find the name of the field when it tries to fit onto report.

Now I guess it's better to generate a table from the crosstab query output. I'm going to do that. I guess I was asking pretty much about Access automatized report generator.

Thanks for your help.

Vianey

 

[kjv1611]

Glad to hear it all worked out!