Forms Crosstab Query Report

H

hermie

Hello

I have a report that is called from a crosstabquery
The query field: Date_gestion have criteria: Between #1/1/2005# And
#1/31/2005#
This all works fine when I run the report.

Now I have made some changes.
First I have created a parameter form
I changed the query and now the report not run as expected?
I got the message cannot recognize [Forms]![Paraform]![txtFecha1]?

What goes wrong here?
Can you not run a report from a crosstabquery this way?
Below Info about the related table,forms and queries

Hope someone can help me

Herman

SQL query is:
SELECT Programa.Prog_plura, [HG-002 SERVICIO-APOYO].SEG_SOC_PART, [HG-002
SERVICIO-APOYO].CODIGO_SERVICIO, [HG-002 SERVICIO-APOYO].DATE_GESTION,
[HG-001 PARTICIPANTE].AREA_LOCAL, [HG-001 PARTICIPANTE].Tipoasist
FROM [HG-002 EXPERIENCIA TRABAJO] INNER JOIN (([HG-001 PARTICIPANTE] INNER
JOIN Programa ON [HG-001 PARTICIPANTE].PROGRAMA = Programa.Cod_Prog) INNER
JOIN [HG-002 SERVICIO-APOYO] ON [HG-001 PARTICIPANTE].SEG_SOC_PART = [HG-002
SERVICIO-APOYO].SEG_SOC_PART) ON ([HG-001 PARTICIPANTE].SEG_SOC_PART =
[HG-002 EXPERIENCIA TRABAJO].SEG_SOC_PART) AND ([HG-002 EXPERIENCIA
TRABAJO].DATE_GESTION = [HG-002 SERVICIO-APOYO].DATE_GESTION) AND ([HG-002
EXPERIENCIA TRABAJO].SEG_SOC_PART = [HG-002 SERVICIO-APOYO].SEG_SOC_PART)
WHERE ((([HG-002 SERVICIO-APOYO].DATE_GESTION) Between
[Forms]![Paramform]![txtFecha1] And [Forms]![Paramform]![txtFecha2]) AND
(([HG-001 PARTICIPANTE].AREA_LOCAL)=[Forms]![Paramform]![cboCons]) AND
(([HG-001 PARTICIPANTE].Tipoasist)=[Forms]![Paramform]![cboTipo]));

SQL Crosstab:
TRANSFORM Count([Apoyo X mes para crosstab].SEG_SOC_PART) AS
CountOfSEG_SOC_PART
SELECT [Apoyo X mes para crosstab].CODIGO_SERVICIO, Count([Apoyo X mes para
crosstab].SEG_SOC_PART) AS [Total Of SEG_SOC_PART]
FROM [Apoyo X mes para crosstab]
GROUP BY [Apoyo X mes para crosstab].CODIGO_SERVICIO
PIVOT [Apoyo X mes para crosstab].Prog_plura;

ParamForm selections:
txtFecha1
txtFecha2
cboTipo
cboCons
cmdOK on click event me.visible = false

Report events:
on open: DoCmd.OpenForm "Paramform", , , , , acDialog
on close: DoCmd.Close acForm, "Paramform"
 
A

Allen Browne

With crosstab queries, you must declare your parameters:

1. Open the crosstab in design view.

2. Choose Parameters from the Query menu. Access opens a dialog.

3. Enter this into the dialog:
[Forms]![Paraform]![txtFecha1] Date/Time
 
H

hermie

Hello Allen
Your suggestion works, but the result is not what I want?
I want that the crosstab query selects the data between 2 dates from field
Date_Gestion and it does not do this.
Other things i need to do?

Herman
 
H

hermie

Hello Allen

Stranger is that you give me advise on my problem I appreciate that, but now
I read on your website http://members.iinet.net.au/~allenbrowne/casu-08.html

that what I want to do not is possible?

Am I wrong?

Herman

Allen Browne said:
With crosstab queries, you must declare your parameters:

1. Open the crosstab in design view.

2. Choose Parameters from the Query menu. Access opens a dialog.

3. Enter this into the dialog:
[Forms]![Paraform]![txtFecha1] Date/Time

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

hermie said:
Hello

I have a report that is called from a crosstabquery
The query field: Date_gestion have criteria: Between #1/1/2005# And
#1/31/2005#
This all works fine when I run the report.

Now I have made some changes.
First I have created a parameter form
I changed the query and now the report not run as expected?
I got the message cannot recognize [Forms]![Paraform]![txtFecha1]?

What goes wrong here?
Can you not run a report from a crosstabquery this way?
Below Info about the related table,forms and queries

Hope someone can help me

Herman

SQL query is:
SELECT Programa.Prog_plura, [HG-002 SERVICIO-APOYO].SEG_SOC_PART, [HG-002
SERVICIO-APOYO].CODIGO_SERVICIO, [HG-002 SERVICIO-APOYO].DATE_GESTION,
[HG-001 PARTICIPANTE].AREA_LOCAL, [HG-001 PARTICIPANTE].Tipoasist
FROM [HG-002 EXPERIENCIA TRABAJO] INNER JOIN (([HG-001 PARTICIPANTE] INNER
JOIN Programa ON [HG-001 PARTICIPANTE].PROGRAMA = Programa.Cod_Prog) INNER
JOIN [HG-002 SERVICIO-APOYO] ON [HG-001 PARTICIPANTE].SEG_SOC_PART =
[HG-002
SERVICIO-APOYO].SEG_SOC_PART) ON ([HG-001 PARTICIPANTE].SEG_SOC_PART =
[HG-002 EXPERIENCIA TRABAJO].SEG_SOC_PART) AND ([HG-002 EXPERIENCIA
TRABAJO].DATE_GESTION = [HG-002 SERVICIO-APOYO].DATE_GESTION) AND ([HG-002
EXPERIENCIA TRABAJO].SEG_SOC_PART = [HG-002 SERVICIO-APOYO].SEG_SOC_PART)
WHERE ((([HG-002 SERVICIO-APOYO].DATE_GESTION) Between
[Forms]![Paramform]![txtFecha1] And [Forms]![Paramform]![txtFecha2]) AND
(([HG-001 PARTICIPANTE].AREA_LOCAL)=[Forms]![Paramform]![cboCons]) AND
(([HG-001 PARTICIPANTE].Tipoasist)=[Forms]![Paramform]![cboTipo]));

SQL Crosstab:
TRANSFORM Count([Apoyo X mes para crosstab].SEG_SOC_PART) AS
CountOfSEG_SOC_PART
SELECT [Apoyo X mes para crosstab].CODIGO_SERVICIO, Count([Apoyo X mes
para
crosstab].SEG_SOC_PART) AS [Total Of SEG_SOC_PART]
FROM [Apoyo X mes para crosstab]
GROUP BY [Apoyo X mes para crosstab].CODIGO_SERVICIO
PIVOT [Apoyo X mes para crosstab].Prog_plura;

ParamForm selections:
txtFecha1
txtFecha2
cboTipo
cboCons
cmdOK on click event me.visible = false

Report events:
on open: DoCmd.OpenForm "Paramform", , , , , acDialog
on close: DoCmd.Close acForm, "Paramform"
Click to expand...
Click to expand...
 
A

Allen Browne

You can get this to work with a crosstab query.

1. Open the crosstab in design view.

2. Under the date field, choose Where in the Group By line.

3. Declare both parameters (Query | Parameters), e.g.:
[Forms]![Paraform]![StartDate] Date/Time
[Forms]![Paraform]![EndDate] Date/Time

4. Save the query.

5. Open the form in design view.

6. Set the Format property of both text boxes to Short Date or similar.
Since they are unbound, this tells Access to interpret them as dates, and
prevents the user from entering values that are not valid dates.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

hermie said:
Hello Allen

Stranger is that you give me advise on my problem I appreciate that, but
now
I read on your website
http://members.iinet.net.au/~allenbrowne/casu-08.html

that what I want to do not is possible?

Am I wrong?

Herman

Allen Browne said:
With crosstab queries, you must declare your parameters:

1. Open the crosstab in design view.

2. Choose Parameters from the Query menu. Access opens a dialog.

3. Enter this into the dialog:
[Forms]![Paraform]![txtFecha1] Date/Time


hermie said:
Hello

I have a report that is called from a crosstabquery
The query field: Date_gestion have criteria: Between #1/1/2005# And
#1/31/2005#
This all works fine when I run the report.

Now I have made some changes.
First I have created a parameter form
I changed the query and now the report not run as expected?
I got the message cannot recognize [Forms]![Paraform]![txtFecha1]?

What goes wrong here?
Can you not run a report from a crosstabquery this way?
Below Info about the related table,forms and queries

Hope someone can help me

Herman

SQL query is:
SELECT Programa.Prog_plura, [HG-002 SERVICIO-APOYO].SEG_SOC_PART, [HG-002
SERVICIO-APOYO].CODIGO_SERVICIO, [HG-002 SERVICIO-APOYO].DATE_GESTION,
[HG-001 PARTICIPANTE].AREA_LOCAL, [HG-001 PARTICIPANTE].Tipoasist
FROM [HG-002 EXPERIENCIA TRABAJO] INNER JOIN (([HG-001 PARTICIPANTE] INNER
JOIN Programa ON [HG-001 PARTICIPANTE].PROGRAMA = Programa.Cod_Prog) INNER
JOIN [HG-002 SERVICIO-APOYO] ON [HG-001 PARTICIPANTE].SEG_SOC_PART =
[HG-002
SERVICIO-APOYO].SEG_SOC_PART) ON ([HG-001 PARTICIPANTE].SEG_SOC_PART =
[HG-002 EXPERIENCIA TRABAJO].SEG_SOC_PART) AND ([HG-002 EXPERIENCIA
TRABAJO].DATE_GESTION = [HG-002 SERVICIO-APOYO].DATE_GESTION) AND ([HG-002
EXPERIENCIA TRABAJO].SEG_SOC_PART = [HG-002 SERVICIO-APOYO].SEG_SOC_PART)
WHERE ((([HG-002 SERVICIO-APOYO].DATE_GESTION) Between
[Forms]![Paramform]![txtFecha1] And [Forms]![Paramform]![txtFecha2])
AND
(([HG-001 PARTICIPANTE].AREA_LOCAL)=[Forms]![Paramform]![cboCons]) AND
(([HG-001 PARTICIPANTE].Tipoasist)=[Forms]![Paramform]![cboTipo]));

SQL Crosstab:
TRANSFORM Count([Apoyo X mes para crosstab].SEG_SOC_PART) AS
CountOfSEG_SOC_PART
SELECT [Apoyo X mes para crosstab].CODIGO_SERVICIO, Count([Apoyo X mes
para
crosstab].SEG_SOC_PART) AS [Total Of SEG_SOC_PART]
FROM [Apoyo X mes para crosstab]
GROUP BY [Apoyo X mes para crosstab].CODIGO_SERVICIO
PIVOT [Apoyo X mes para crosstab].Prog_plura;

ParamForm selections:
txtFecha1
txtFecha2
cboTipo
cboCons
cmdOK on click event me.visible = false

Report events:
on open: DoCmd.OpenForm "Paramform", , , , , acDialog
on close: DoCmd.Close acForm, "Paramform"
Click to expand...
Click to expand...
Click to expand...
 
H

hermie

Hello Allen

I tried your advise but still get the jet database engine error that it not
recognize [Forms]![paramform][txtfecha1] as a valid field name or
expression.
Fyi Field Date_gestion is a medium date field.
The fields txtfecha1 and txtfecha2 are generated by a calandar control.

Here is the new SQL of the crosstab query
PARAMETERS [forms]![paramform]![cboCons] Text ( 255 ),
[forms]![paramform]![cbotipo] Text ( 255 );
TRANSFORM Count([Apoyo X mes para crosstab].SEG_SOC_PART) AS
CountOfSEG_SOC_PART
SELECT [Apoyo X mes para crosstab].CODIGO_SERVICIO, Count([Apoyo X mes para
crosstab].SEG_SOC_PART) AS [Total Of SEG_SOC_PART]
FROM [Apoyo X mes para crosstab]
WHERE ((([Apoyo X mes para
crosstab].DATE_GESTION)=[Forms]![Paramform]![txtFecha1] Or ([Apoyo X mes
para crosstab].DATE_GESTION)=[Forms]![Paramform]![txtFecha2]))
GROUP BY [Apoyo X mes para crosstab].CODIGO_SERVICIO
PIVOT [Apoyo X mes para crosstab].Prog_plura;

Not know why it not works?

Herman

Allen Browne said:
You can get this to work with a crosstab query.

1. Open the crosstab in design view.

2. Under the date field, choose Where in the Group By line.

3. Declare both parameters (Query | Parameters), e.g.:
[Forms]![Paraform]![StartDate] Date/Time
[Forms]![Paraform]![EndDate] Date/Time

4. Save the query.

5. Open the form in design view.

6. Set the Format property of both text boxes to Short Date or similar.
Since they are unbound, this tells Access to interpret them as dates, and
prevents the user from entering values that are not valid dates.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

hermie said:
Hello Allen

Stranger is that you give me advise on my problem I appreciate that, but
now
I read on your website
http://members.iinet.net.au/~allenbrowne/casu-08.html

that what I want to do not is possible?

Am I wrong?

Herman

Allen Browne said:
With crosstab queries, you must declare your parameters:

1. Open the crosstab in design view.

2. Choose Parameters from the Query menu. Access opens a dialog.

3. Enter this into the dialog:
[Forms]![Paraform]![txtFecha1] Date/Time


Hello

I have a report that is called from a crosstabquery
The query field: Date_gestion have criteria: Between #1/1/2005# And
#1/31/2005#
This all works fine when I run the report.

Now I have made some changes.
First I have created a parameter form
I changed the query and now the report not run as expected?
I got the message cannot recognize [Forms]![Paraform]![txtFecha1]?

What goes wrong here?
Can you not run a report from a crosstabquery this way?
Below Info about the related table,forms and queries

Hope someone can help me

Herman

SQL query is:
SELECT Programa.Prog_plura, [HG-002 SERVICIO-APOYO].SEG_SOC_PART, [HG-002
SERVICIO-APOYO].CODIGO_SERVICIO, [HG-002 SERVICIO-APOYO].DATE_GESTION,
[HG-001 PARTICIPANTE].AREA_LOCAL, [HG-001 PARTICIPANTE].Tipoasist
FROM [HG-002 EXPERIENCIA TRABAJO] INNER JOIN (([HG-001 PARTICIPANTE] INNER
JOIN Programa ON [HG-001 PARTICIPANTE].PROGRAMA = Programa.Cod_Prog) INNER
JOIN [HG-002 SERVICIO-APOYO] ON [HG-001 PARTICIPANTE].SEG_SOC_PART =
[HG-002
SERVICIO-APOYO].SEG_SOC_PART) ON ([HG-001 PARTICIPANTE].SEG_SOC_PART =
[HG-002 EXPERIENCIA TRABAJO].SEG_SOC_PART) AND ([HG-002 EXPERIENCIA
TRABAJO].DATE_GESTION = [HG-002 SERVICIO-APOYO].DATE_GESTION) AND ([HG-002
EXPERIENCIA TRABAJO].SEG_SOC_PART = [HG-002 SERVICIO-APOYO].SEG_SOC_PART)
WHERE ((([HG-002 SERVICIO-APOYO].DATE_GESTION) Between
[Forms]![Paramform]![txtFecha1] And [Forms]![Paramform]![txtFecha2])
AND
(([HG-001 PARTICIPANTE].AREA_LOCAL)=[Forms]![Paramform]![cboCons]) AND
(([HG-001 PARTICIPANTE].Tipoasist)=[Forms]![Paramform]![cboTipo]));

SQL Crosstab:
TRANSFORM Count([Apoyo X mes para crosstab].SEG_SOC_PART) AS
CountOfSEG_SOC_PART
SELECT [Apoyo X mes para crosstab].CODIGO_SERVICIO, Count([Apoyo X mes
para
crosstab].SEG_SOC_PART) AS [Total Of SEG_SOC_PART]
FROM [Apoyo X mes para crosstab]
GROUP BY [Apoyo X mes para crosstab].CODIGO_SERVICIO
PIVOT [Apoyo X mes para crosstab].Prog_plura;

ParamForm selections:
txtFecha1
txtFecha2
cboTipo
cboCons
cmdOK on click event me.visible = false

Report events:
on open: DoCmd.OpenForm "Paramform", , , , , acDialog
on close: DoCmd.Close acForm, "Paramform"
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

The 2 parameters in the query are the wrong type.
They are declared as Text. They need to be the Date/Time type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

hermie said:
Hello Allen

I tried your advise but still get the jet database engine error that it
not
recognize [Forms]![paramform][txtfecha1] as a valid field name or
expression.
Fyi Field Date_gestion is a medium date field.
The fields txtfecha1 and txtfecha2 are generated by a calandar control.

Here is the new SQL of the crosstab query
PARAMETERS [forms]![paramform]![cboCons] Text ( 255 ),
[forms]![paramform]![cbotipo] Text ( 255 );
TRANSFORM Count([Apoyo X mes para crosstab].SEG_SOC_PART) AS
CountOfSEG_SOC_PART
SELECT [Apoyo X mes para crosstab].CODIGO_SERVICIO, Count([Apoyo X mes
para
crosstab].SEG_SOC_PART) AS [Total Of SEG_SOC_PART]
FROM [Apoyo X mes para crosstab]
WHERE ((([Apoyo X mes para
crosstab].DATE_GESTION)=[Forms]![Paramform]![txtFecha1] Or ([Apoyo X mes
para crosstab].DATE_GESTION)=[Forms]![Paramform]![txtFecha2]))
GROUP BY [Apoyo X mes para crosstab].CODIGO_SERVICIO
PIVOT [Apoyo X mes para crosstab].Prog_plura;

Not know why it not works?

Herman

Allen Browne said:
You can get this to work with a crosstab query.

1. Open the crosstab in design view.

2. Under the date field, choose Where in the Group By line.

3. Declare both parameters (Query | Parameters), e.g.:
[Forms]![Paraform]![StartDate] Date/Time
[Forms]![Paraform]![EndDate] Date/Time

4. Save the query.

5. Open the form in design view.

6. Set the Format property of both text boxes to Short Date or similar.
Since they are unbound, this tells Access to interpret them as dates, and
prevents the user from entering values that are not valid dates.


hermie said:
Hello Allen

Stranger is that you give me advise on my problem I appreciate that,
but
now
I read on your website
http://members.iinet.net.au/~allenbrowne/casu-08.html

that what I want to do not is possible?

Am I wrong?

Herman

With crosstab queries, you must declare your parameters:

1. Open the crosstab in design view.

2. Choose Parameters from the Query menu. Access opens a dialog.

3. Enter this into the dialog:
[Forms]![Paraform]![txtFecha1] Date/Time


Hello

I have a report that is called from a crosstabquery
The query field: Date_gestion have criteria: Between #1/1/2005# And
#1/31/2005#
This all works fine when I run the report.

Now I have made some changes.
First I have created a parameter form
I changed the query and now the report not run as expected?
I got the message cannot recognize [Forms]![Paraform]![txtFecha1]?

What goes wrong here?
Can you not run a report from a crosstabquery this way?
Below Info about the related table,forms and queries

Hope someone can help me

Herman

SQL query is:
SELECT Programa.Prog_plura, [HG-002 SERVICIO-APOYO].SEG_SOC_PART,
[HG-002
SERVICIO-APOYO].CODIGO_SERVICIO, [HG-002 SERVICIO-APOYO].DATE_GESTION,
[HG-001 PARTICIPANTE].AREA_LOCAL, [HG-001 PARTICIPANTE].Tipoasist
FROM [HG-002 EXPERIENCIA TRABAJO] INNER JOIN (([HG-001 PARTICIPANTE]
INNER
JOIN Programa ON [HG-001 PARTICIPANTE].PROGRAMA = Programa.Cod_Prog)
INNER
JOIN [HG-002 SERVICIO-APOYO] ON [HG-001 PARTICIPANTE].SEG_SOC_PART =
[HG-002
SERVICIO-APOYO].SEG_SOC_PART) ON ([HG-001 PARTICIPANTE].SEG_SOC_PART =
[HG-002 EXPERIENCIA TRABAJO].SEG_SOC_PART) AND ([HG-002 EXPERIENCIA
TRABAJO].DATE_GESTION = [HG-002 SERVICIO-APOYO].DATE_GESTION) AND
([HG-002
EXPERIENCIA TRABAJO].SEG_SOC_PART = [HG-002
SERVICIO-APOYO].SEG_SOC_PART)
WHERE ((([HG-002 SERVICIO-APOYO].DATE_GESTION) Between
[Forms]![Paramform]![txtFecha1] And [Forms]![Paramform]![txtFecha2])
AND
(([HG-001 PARTICIPANTE].AREA_LOCAL)=[Forms]![Paramform]![cboCons]) AND
(([HG-001 PARTICIPANTE].Tipoasist)=[Forms]![Paramform]![cboTipo]));

SQL Crosstab:
TRANSFORM Count([Apoyo X mes para crosstab].SEG_SOC_PART) AS
CountOfSEG_SOC_PART
SELECT [Apoyo X mes para crosstab].CODIGO_SERVICIO, Count([Apoyo X mes
para
crosstab].SEG_SOC_PART) AS [Total Of SEG_SOC_PART]
FROM [Apoyo X mes para crosstab]
GROUP BY [Apoyo X mes para crosstab].CODIGO_SERVICIO
PIVOT [Apoyo X mes para crosstab].Prog_plura;

ParamForm selections:
txtFecha1
txtFecha2
cboTipo
cboCons
cmdOK on click event me.visible = false

Report events:
on open: DoCmd.OpenForm "Paramform", , , , , acDialog
on close: DoCmd.Close acForm, "Paramform"
Click to expand...
Click to expand...
Click to expand...
 
H

hermie

Allen

I declared the parameters for txtFecha1 and txtFecha2 as short date in the
crosstab query and also in the Paramform, but I not see this in the sql
view? And still same error.

Herman

Allen Browne said:
The 2 parameters in the query are the wrong type.
They are declared as Text. They need to be the Date/Time type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

hermie said:
Hello Allen

I tried your advise but still get the jet database engine error that it
not
recognize [Forms]![paramform][txtfecha1] as a valid field name or
expression.
Fyi Field Date_gestion is a medium date field.
The fields txtfecha1 and txtfecha2 are generated by a calandar control.

Here is the new SQL of the crosstab query
PARAMETERS [forms]![paramform]![cboCons] Text ( 255 ),
[forms]![paramform]![cbotipo] Text ( 255 );
TRANSFORM Count([Apoyo X mes para crosstab].SEG_SOC_PART) AS
CountOfSEG_SOC_PART
SELECT [Apoyo X mes para crosstab].CODIGO_SERVICIO, Count([Apoyo X mes
para
crosstab].SEG_SOC_PART) AS [Total Of SEG_SOC_PART]
FROM [Apoyo X mes para crosstab]
WHERE ((([Apoyo X mes para
crosstab].DATE_GESTION)=[Forms]![Paramform]![txtFecha1] Or ([Apoyo X mes
para crosstab].DATE_GESTION)=[Forms]![Paramform]![txtFecha2]))
GROUP BY [Apoyo X mes para crosstab].CODIGO_SERVICIO
PIVOT [Apoyo X mes para crosstab].Prog_plura;

Not know why it not works?

Herman

Allen Browne said:
You can get this to work with a crosstab query.

1. Open the crosstab in design view.

2. Under the date field, choose Where in the Group By line.

3. Declare both parameters (Query | Parameters), e.g.:
[Forms]![Paraform]![StartDate] Date/Time
[Forms]![Paraform]![EndDate] Date/Time

4. Save the query.

5. Open the form in design view.

6. Set the Format property of both text boxes to Short Date or similar.
Since they are unbound, this tells Access to interpret them as dates, and
prevents the user from entering values that are not valid dates.


Hello Allen

Stranger is that you give me advise on my problem I appreciate that,
but
now
I read on your website
http://members.iinet.net.au/~allenbrowne/casu-08.html

that what I want to do not is possible?

Am I wrong?

Herman

With crosstab queries, you must declare your parameters:

1. Open the crosstab in design view.

2. Choose Parameters from the Query menu. Access opens a dialog.

3. Enter this into the dialog:
[Forms]![Paraform]![txtFecha1] Date/Time


Hello

I have a report that is called from a crosstabquery
The query field: Date_gestion have criteria: Between #1/1/2005# And
#1/31/2005#
This all works fine when I run the report.

Now I have made some changes.
First I have created a parameter form
I changed the query and now the report not run as expected?
I got the message cannot recognize [Forms]![Paraform]![txtFecha1]?

What goes wrong here?
Can you not run a report from a crosstabquery this way?
Below Info about the related table,forms and queries

Hope someone can help me

Herman

SQL query is:
SELECT Programa.Prog_plura, [HG-002 SERVICIO-APOYO].SEG_SOC_PART,
[HG-002
SERVICIO-APOYO].CODIGO_SERVICIO, [HG-002 SERVICIO-APOYO].DATE_GESTION,
[HG-001 PARTICIPANTE].AREA_LOCAL, [HG-001 PARTICIPANTE].Tipoasist
FROM [HG-002 EXPERIENCIA TRABAJO] INNER JOIN (([HG-001 PARTICIPANTE]
INNER
JOIN Programa ON [HG-001 PARTICIPANTE].PROGRAMA = Programa.Cod_Prog)
INNER
JOIN [HG-002 SERVICIO-APOYO] ON [HG-001 PARTICIPANTE].SEG_SOC_PART =
[HG-002
SERVICIO-APOYO].SEG_SOC_PART) ON ([HG-001
Click to expand...
PARTICIPANTE].SEG_SOC_PART
=
[HG-002 EXPERIENCIA TRABAJO].SEG_SOC_PART) AND ([HG-002 EXPERIENCIA
TRABAJO].DATE_GESTION = [HG-002 SERVICIO-APOYO].DATE_GESTION) AND
([HG-002
EXPERIENCIA TRABAJO].SEG_SOC_PART = [HG-002
SERVICIO-APOYO].SEG_SOC_PART)
WHERE ((([HG-002 SERVICIO-APOYO].DATE_GESTION) Between
[Forms]![Paramform]![txtFecha1] And [Forms]![Paramform]![txtFecha2])
AND
(([HG-001 PARTICIPANTE].AREA_LOCAL)=[Forms]![Paramform]![cboCons]) AND
(([HG-001 PARTICIPANTE].Tipoasist)=[Forms]![Paramform]![cboTipo]));

SQL Crosstab:
TRANSFORM Count([Apoyo X mes para crosstab].SEG_SOC_PART) AS
CountOfSEG_SOC_PART
SELECT [Apoyo X mes para crosstab].CODIGO_SERVICIO, Count([Apoyo X mes
para
crosstab].SEG_SOC_PART) AS [Total Of SEG_SOC_PART]
FROM [Apoyo X mes para crosstab]
GROUP BY [Apoyo X mes para crosstab].CODIGO_SERVICIO
PIVOT [Apoyo X mes para crosstab].Prog_plura;

ParamForm selections:
txtFecha1
txtFecha2
cboTipo
cboCons
cmdOK on click event me.visible = false

Report events:
on open: DoCmd.OpenForm "Paramform", , , , , acDialog
on close: DoCmd.Close acForm, "Paramform"
Click to expand...
Click to expand...
Click to expand...
 
H

hermie

Allen
Many,many thanks It works now After some puzzling i found the problem indeed
you need to declare the parameters for the date.

I have learned again something

Herman

Allen Browne said:
The 2 parameters in the query are the wrong type.
They are declared as Text. They need to be the Date/Time type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

hermie said:
Hello Allen

I tried your advise but still get the jet database engine error that it
not
recognize [Forms]![paramform][txtfecha1] as a valid field name or
expression.
Fyi Field Date_gestion is a medium date field.
The fields txtfecha1 and txtfecha2 are generated by a calandar control.

Here is the new SQL of the crosstab query
PARAMETERS [forms]![paramform]![cboCons] Text ( 255 ),
[forms]![paramform]![cbotipo] Text ( 255 );
TRANSFORM Count([Apoyo X mes para crosstab].SEG_SOC_PART) AS
CountOfSEG_SOC_PART
SELECT [Apoyo X mes para crosstab].CODIGO_SERVICIO, Count([Apoyo X mes
para
crosstab].SEG_SOC_PART) AS [Total Of SEG_SOC_PART]
FROM [Apoyo X mes para crosstab]
WHERE ((([Apoyo X mes para
crosstab].DATE_GESTION)=[Forms]![Paramform]![txtFecha1] Or ([Apoyo X mes
para crosstab].DATE_GESTION)=[Forms]![Paramform]![txtFecha2]))
GROUP BY [Apoyo X mes para crosstab].CODIGO_SERVICIO
PIVOT [Apoyo X mes para crosstab].Prog_plura;

Not know why it not works?

Herman

Allen Browne said:
You can get this to work with a crosstab query.

1. Open the crosstab in design view.

2. Under the date field, choose Where in the Group By line.

3. Declare both parameters (Query | Parameters), e.g.:
[Forms]![Paraform]![StartDate] Date/Time
[Forms]![Paraform]![EndDate] Date/Time

4. Save the query.

5. Open the form in design view.

6. Set the Format property of both text boxes to Short Date or similar.
Since they are unbound, this tells Access to interpret them as dates, and
prevents the user from entering values that are not valid dates.


Hello Allen

Stranger is that you give me advise on my problem I appreciate that,
but
now
I read on your website
http://members.iinet.net.au/~allenbrowne/casu-08.html

that what I want to do not is possible?

Am I wrong?

Herman

With crosstab queries, you must declare your parameters:

1. Open the crosstab in design view.

2. Choose Parameters from the Query menu. Access opens a dialog.

3. Enter this into the dialog:
[Forms]![Paraform]![txtFecha1] Date/Time


Hello

I have a report that is called from a crosstabquery
The query field: Date_gestion have criteria: Between #1/1/2005# And
#1/31/2005#
This all works fine when I run the report.

Now I have made some changes.
First I have created a parameter form
I changed the query and now the report not run as expected?
I got the message cannot recognize [Forms]![Paraform]![txtFecha1]?

What goes wrong here?
Can you not run a report from a crosstabquery this way?
Below Info about the related table,forms and queries

Hope someone can help me

Herman

SQL query is:
SELECT Programa.Prog_plura, [HG-002 SERVICIO-APOYO].SEG_SOC_PART,
[HG-002
SERVICIO-APOYO].CODIGO_SERVICIO, [HG-002 SERVICIO-APOYO].DATE_GESTION,
[HG-001 PARTICIPANTE].AREA_LOCAL, [HG-001 PARTICIPANTE].Tipoasist
FROM [HG-002 EXPERIENCIA TRABAJO] INNER JOIN (([HG-001 PARTICIPANTE]
INNER
JOIN Programa ON [HG-001 PARTICIPANTE].PROGRAMA = Programa.Cod_Prog)
INNER
JOIN [HG-002 SERVICIO-APOYO] ON [HG-001 PARTICIPANTE].SEG_SOC_PART =
[HG-002
SERVICIO-APOYO].SEG_SOC_PART) ON ([HG-001
Click to expand...
PARTICIPANTE].SEG_SOC_PART
=
[HG-002 EXPERIENCIA TRABAJO].SEG_SOC_PART) AND ([HG-002 EXPERIENCIA
TRABAJO].DATE_GESTION = [HG-002 SERVICIO-APOYO].DATE_GESTION) AND
([HG-002
EXPERIENCIA TRABAJO].SEG_SOC_PART = [HG-002
SERVICIO-APOYO].SEG_SOC_PART)
WHERE ((([HG-002 SERVICIO-APOYO].DATE_GESTION) Between
[Forms]![Paramform]![txtFecha1] And [Forms]![Paramform]![txtFecha2])
AND
(([HG-001 PARTICIPANTE].AREA_LOCAL)=[Forms]![Paramform]![cboCons]) AND
(([HG-001 PARTICIPANTE].Tipoasist)=[Forms]![Paramform]![cboTipo]));

SQL Crosstab:
TRANSFORM Count([Apoyo X mes para crosstab].SEG_SOC_PART) AS
CountOfSEG_SOC_PART
SELECT [Apoyo X mes para crosstab].CODIGO_SERVICIO, Count([Apoyo X mes
para
crosstab].SEG_SOC_PART) AS [Total Of SEG_SOC_PART]
FROM [Apoyo X mes para crosstab]
GROUP BY [Apoyo X mes para crosstab].CODIGO_SERVICIO
PIVOT [Apoyo X mes para crosstab].Prog_plura;

ParamForm selections:
txtFecha1
txtFecha2
cboTipo
cboCons
cmdOK on click event me.visible = false

Report events:
on open: DoCmd.OpenForm "Paramform", , , , , acDialog
on close: DoCmd.Close acForm, "Paramform"
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

So what does your SQL statement look like now?

It should begin like this:
PARAMETERS [forms]![paramform]![cboCons] DateTime,
[forms]![paramform]![cbotipo] DateTime;
TRANSFORM ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

hermie said:
Allen

I declared the parameters for txtFecha1 and txtFecha2 as short date in the
crosstab query and also in the Paramform, but I not see this in the sql
view? And still same error.

Herman

Allen Browne said:
The 2 parameters in the query are the wrong type.
They are declared as Text. They need to be the Date/Time type.


hermie said:
Hello Allen

I tried your advise but still get the jet database engine error that it
not
recognize [Forms]![paramform][txtfecha1] as a valid field name or
expression.
Fyi Field Date_gestion is a medium date field.
The fields txtfecha1 and txtfecha2 are generated by a calandar control.

Here is the new SQL of the crosstab query
PARAMETERS [forms]![paramform]![cboCons] Text ( 255 ),
[forms]![paramform]![cbotipo] Text ( 255 );
TRANSFORM Count([Apoyo X mes para crosstab].SEG_SOC_PART) AS
CountOfSEG_SOC_PART
SELECT [Apoyo X mes para crosstab].CODIGO_SERVICIO, Count([Apoyo X mes
para
crosstab].SEG_SOC_PART) AS [Total Of SEG_SOC_PART]
FROM [Apoyo X mes para crosstab]
WHERE ((([Apoyo X mes para
crosstab].DATE_GESTION)=[Forms]![Paramform]![txtFecha1] Or ([Apoyo X
mes
para crosstab].DATE_GESTION)=[Forms]![Paramform]![txtFecha2]))
GROUP BY [Apoyo X mes para crosstab].CODIGO_SERVICIO
PIVOT [Apoyo X mes para crosstab].Prog_plura;

Not know why it not works?

Herman

You can get this to work with a crosstab query.

1. Open the crosstab in design view.

2. Under the date field, choose Where in the Group By line.

3. Declare both parameters (Query | Parameters), e.g.:
[Forms]![Paraform]![StartDate] Date/Time
[Forms]![Paraform]![EndDate] Date/Time

4. Save the query.

5. Open the form in design view.

6. Set the Format property of both text boxes to Short Date or
similar.
Since they are unbound, this tells Access to interpret them as dates, and
prevents the user from entering values that are not valid dates.


Hello Allen

Stranger is that you give me advise on my problem I appreciate that,
but
now
I read on your website
http://members.iinet.net.au/~allenbrowne/casu-08.html

that what I want to do not is possible?

Am I wrong?

Herman

With crosstab queries, you must declare your parameters:

1. Open the crosstab in design view.

2. Choose Parameters from the Query menu. Access opens a dialog.

3. Enter this into the dialog:
[Forms]![Paraform]![txtFecha1] Date/Time


Hello

I have a report that is called from a crosstabquery
The query field: Date_gestion have criteria: Between #1/1/2005# And
#1/31/2005#
This all works fine when I run the report.

Now I have made some changes.
First I have created a parameter form
I changed the query and now the report not run as expected?
I got the message cannot recognize
[Forms]![Paraform]![txtFecha1]?

What goes wrong here?
Can you not run a report from a crosstabquery this way?
Below Info about the related table,forms and queries

Hope someone can help me

Herman

SQL query is:
SELECT Programa.Prog_plura, [HG-002 SERVICIO-APOYO].SEG_SOC_PART,
[HG-002
SERVICIO-APOYO].CODIGO_SERVICIO, [HG-002
SERVICIO-APOYO].DATE_GESTION,
[HG-001 PARTICIPANTE].AREA_LOCAL, [HG-001 PARTICIPANTE].Tipoasist
FROM [HG-002 EXPERIENCIA TRABAJO] INNER JOIN (([HG-001 PARTICIPANTE]
INNER
JOIN Programa ON [HG-001 PARTICIPANTE].PROGRAMA = Programa.Cod_Prog)
INNER
JOIN [HG-002 SERVICIO-APOYO] ON [HG-001
PARTICIPANTE].SEG_SOC_PART =
[HG-002
SERVICIO-APOYO].SEG_SOC_PART) ON ([HG-001 PARTICIPANTE].SEG_SOC_PART
=
[HG-002 EXPERIENCIA TRABAJO].SEG_SOC_PART) AND ([HG-002 EXPERIENCIA
TRABAJO].DATE_GESTION = [HG-002 SERVICIO-APOYO].DATE_GESTION) AND
([HG-002
EXPERIENCIA TRABAJO].SEG_SOC_PART = [HG-002
SERVICIO-APOYO].SEG_SOC_PART)
WHERE ((([HG-002 SERVICIO-APOYO].DATE_GESTION) Between
[Forms]![Paramform]![txtFecha1] And [Forms]![Paramform]![txtFecha2])
AND
(([HG-001
PARTICIPANTE].AREA_LOCAL)=[Forms]![Paramform]![cboCons])
AND
(([HG-001 PARTICIPANTE].Tipoasist)=[Forms]![Paramform]![cboTipo]));

SQL Crosstab:
TRANSFORM Count([Apoyo X mes para crosstab].SEG_SOC_PART) AS
CountOfSEG_SOC_PART
SELECT [Apoyo X mes para crosstab].CODIGO_SERVICIO, Count([Apoyo
X
mes
para
crosstab].SEG_SOC_PART) AS [Total Of SEG_SOC_PART]
FROM [Apoyo X mes para crosstab]
GROUP BY [Apoyo X mes para crosstab].CODIGO_SERVICIO
PIVOT [Apoyo X mes para crosstab].Prog_plura;

ParamForm selections:
txtFecha1
txtFecha2
cboTipo
cboCons
cmdOK on click event me.visible = false

Report events:
on open: DoCmd.OpenForm "Paramform", , , , , acDialog
on close: DoCmd.Close acForm, "Paramform"
Click to expand...
Click to expand...
Click to expand...
 
H

hermie

Alleen
As already mentioned i found it out :)
Here is the working SQL

PARAMETERS [forms]![paramform]![txtfecha1] DateTime,
[forms]![paramform]![txtfecha2] DateTime, [forms]![paramform]![cboCons] Text
( 255 ), [forms]![Paramform]![cboTipo] Text ( 255 );
TRANSFORM Count([Apoyo X mes para crosstab].SEG_SOC_PART) AS
CountOfSEG_SOC_PART
SELECT [Apoyo X mes para crosstab].CODIGO_SERVICIO, Count([Apoyo X mes para
crosstab].SEG_SOC_PART) AS [Total Of SEG_SOC_PART]
FROM [Apoyo X mes para crosstab]
WHERE ((([Apoyo X mes para crosstab].DATE_GESTION) Between
[Forms]![Paramform]![txtFecha1] And [Forms]![Paramform]![txtFecha2]) AND
(([Apoyo X mes para crosstab].AREA_LOCAL)=[Forms]![Paramform]![cboCons]) AND
(([Apoyo X mes para crosstab].Tipoasist)=[Forms]![Paramform]![cboTipo]))
GROUP BY [Apoyo X mes para crosstab].CODIGO_SERVICIO
PIVOT [Apoyo X mes para crosstab].Prog_plura;

Herman

Allen Browne said:
So what does your SQL statement look like now?

It should begin like this:
PARAMETERS [forms]![paramform]![cboCons] DateTime,
[forms]![paramform]![cbotipo] DateTime;
TRANSFORM ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

hermie said:
Allen

I declared the parameters for txtFecha1 and txtFecha2 as short date in the
crosstab query and also in the Paramform, but I not see this in the sql
view? And still same error.

Herman

Allen Browne said:
The 2 parameters in the query are the wrong type.
They are declared as Text. They need to be the Date/Time type.


Hello Allen

I tried your advise but still get the jet database engine error that it
not
recognize [Forms]![paramform][txtfecha1] as a valid field name or
expression.
Fyi Field Date_gestion is a medium date field.
The fields txtfecha1 and txtfecha2 are generated by a calandar control.

Here is the new SQL of the crosstab query
PARAMETERS [forms]![paramform]![cboCons] Text ( 255 ),
[forms]![paramform]![cbotipo] Text ( 255 );
TRANSFORM Count([Apoyo X mes para crosstab].SEG_SOC_PART) AS
CountOfSEG_SOC_PART
SELECT [Apoyo X mes para crosstab].CODIGO_SERVICIO, Count([Apoyo X mes
para
crosstab].SEG_SOC_PART) AS [Total Of SEG_SOC_PART]
FROM [Apoyo X mes para crosstab]
WHERE ((([Apoyo X mes para
crosstab].DATE_GESTION)=[Forms]![Paramform]![txtFecha1] Or ([Apoyo X
mes
para crosstab].DATE_GESTION)=[Forms]![Paramform]![txtFecha2]))
GROUP BY [Apoyo X mes para crosstab].CODIGO_SERVICIO
PIVOT [Apoyo X mes para crosstab].Prog_plura;

Not know why it not works?

Herman

You can get this to work with a crosstab query.

1. Open the crosstab in design view.

2. Under the date field, choose Where in the Group By line.

3. Declare both parameters (Query | Parameters), e.g.:
[Forms]![Paraform]![StartDate] Date/Time
[Forms]![Paraform]![EndDate] Date/Time

4. Save the query.

5. Open the form in design view.

6. Set the Format property of both text boxes to Short Date or
similar.
Since they are unbound, this tells Access to interpret them as
Click to expand...
dates,
and
prevents the user from entering values that are not valid dates.


Hello Allen

Stranger is that you give me advise on my problem I appreciate that,
but
now
I read on your website
http://members.iinet.net.au/~allenbrowne/casu-08.html

that what I want to do not is possible?

Am I wrong?

Herman

With crosstab queries, you must declare your parameters:

1. Open the crosstab in design view.

2. Choose Parameters from the Query menu. Access opens a dialog.

3. Enter this into the dialog:
[Forms]![Paraform]![txtFecha1] Date/Time


Hello

I have a report that is called from a crosstabquery
The query field: Date_gestion have criteria: Between
Click to expand...
#1/1/2005#
And
#1/31/2005#
This all works fine when I run the report.

Now I have made some changes.
First I have created a parameter form
I changed the query and now the report not run as expected?
I got the message cannot recognize
[Forms]![Paraform]![txtFecha1]?

What goes wrong here?
Can you not run a report from a crosstabquery this way?
Below Info about the related table,forms and queries

Hope someone can help me

Herman

SQL query is:
SELECT Programa.Prog_plura, [HG-002 SERVICIO-APOYO].SEG_SOC_PART,
[HG-002
SERVICIO-APOYO].CODIGO_SERVICIO, [HG-002
SERVICIO-APOYO].DATE_GESTION,
[HG-001 PARTICIPANTE].AREA_LOCAL, [HG-001 PARTICIPANTE].Tipoasist
FROM [HG-002 EXPERIENCIA TRABAJO] INNER JOIN (([HG-001 PARTICIPANTE]
INNER
JOIN Programa ON [HG-001 PARTICIPANTE].PROGRAMA = Programa.Cod_Prog)
INNER
JOIN [HG-002 SERVICIO-APOYO] ON [HG-001
PARTICIPANTE].SEG_SOC_PART =
[HG-002
SERVICIO-APOYO].SEG_SOC_PART) ON ([HG-001 PARTICIPANTE].SEG_SOC_PART
=
[HG-002 EXPERIENCIA TRABAJO].SEG_SOC_PART) AND ([HG-002 EXPERIENCIA
TRABAJO].DATE_GESTION = [HG-002 SERVICIO-APOYO].DATE_GESTION) AND
([HG-002
EXPERIENCIA TRABAJO].SEG_SOC_PART = [HG-002
SERVICIO-APOYO].SEG_SOC_PART)
WHERE ((([HG-002 SERVICIO-APOYO].DATE_GESTION) Between
[Forms]![Paramform]![txtFecha1] And [Forms]![Paramform]![txtFecha2])
AND
(([HG-001
PARTICIPANTE].AREA_LOCAL)=[Forms]![Paramform]![cboCons])
AND
(([HG-001
Click to expand...
PARTICIPANTE].Tipoasist)=[Forms]![Paramform]![cboTipo]));

SQL Crosstab:
TRANSFORM Count([Apoyo X mes para crosstab].SEG_SOC_PART) AS
CountOfSEG_SOC_PART
SELECT [Apoyo X mes para crosstab].CODIGO_SERVICIO, Count([Apoyo
X
mes
para
crosstab].SEG_SOC_PART) AS [Total Of SEG_SOC_PART]
FROM [Apoyo X mes para crosstab]
GROUP BY [Apoyo X mes para crosstab].CODIGO_SERVICIO
PIVOT [Apoyo X mes para crosstab].Prog_plura;

ParamForm selections:
txtFecha1
txtFecha2
cboTipo
cboCons
cmdOK on click event me.visible = false

Report events:
on open: DoCmd.OpenForm "Paramform", , , , , acDialog
on close: DoCmd.Close acForm, "Paramform"
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

spelling 6

Top