A common variable for many queries

  • Thread starter Thread starter Céline Brien
  • Start date Start date
C

Céline Brien

Hi everybody,

14 queries execute one after the other via a macro.

A common field : Payroll date.

I would like to add the possibility to specify which payroll date.

But of course, I don't want to be ask 14 times which payroll date.

How do I add a common variable for many queries ????

Thank you for your help and have a good day,

Céline
 
Hi everybody,
Hi Arvin,
Thank you so much for your answer.
I created a table SousTableNoPeriodePaye with a text field NoPeriodePaye.
With that table I create a form FNoPeriodePaye with the text fied of the
table.
I added the WHERE line to my SQL codes.
When I execute the query a dialog box is asking to type in the parameter of
Formulaires!FNoPeriodePaye!NoPeriodePaye.
What has to be corrected ???
Below are my codes.
Many thanks again,
Céline
-------------------------------------------
INSERT INTO Historique ( NoEmploye, NoPeriode, [Date], Semaine, NoDep,
NoClass, Heures, Taux, NoCode, Anciennete, [Note] )
SELECT [FeuilleDeTemps].[NoEmploye], [FeuilleDeTemps].[NoPeriode],
[FeuilleDeTemps].[DateDebut], "Sem 1" AS Semaine,
[FeuilleDeTemps].[NoDepartement], [FeuilleDeTemps].[NoClassification],
[FeuilleDeTemps].[DIM1], IIf([Taux
2]=True,[Taux2],IIf([RetourTaux2]=True,[Taux2],[Taux1])) AS Taux,
[FeuilleDeTemps].[NoCodeDim1], IIf([NoStatut]=1 And [SiDim1]=-1 And
[NoCodeDim1]=19,2,IIf([SiDim1]=-1,1,0)) AS Anciennete,
[FeuilleDeTemps].[NoteDim1]
FROM (Employes INNER JOIN FeuilleDeTemps ON
[Employes].[NoEmploye]=[FeuilleDeTemps].[NoEmploye]) INNER JOIN
SousTableDepartement ON
[FeuilleDeTemps].[NoDepartement]=[SousTableDepartement].[NoAutoDep]
WHERE NoPeriode=[Forms]![FNoPeriodePaye]![NoPeriodePaye];
 
Is your form open when you run the query? Access will not open the form just
because you're referencing it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Céline Brien said:
Hi everybody,
Hi Arvin,
Thank you so much for your answer.
I created a table SousTableNoPeriodePaye with a text field NoPeriodePaye.
With that table I create a form FNoPeriodePaye with the text fied of the
table.
I added the WHERE line to my SQL codes.
When I execute the query a dialog box is asking to type in the parameter
of Formulaires!FNoPeriodePaye!NoPeriodePaye.
What has to be corrected ???
Below are my codes.
Many thanks again,
Céline
-------------------------------------------
INSERT INTO Historique ( NoEmploye, NoPeriode, [Date], Semaine, NoDep,
NoClass, Heures, Taux, NoCode, Anciennete, [Note] )
SELECT [FeuilleDeTemps].[NoEmploye], [FeuilleDeTemps].[NoPeriode],
[FeuilleDeTemps].[DateDebut], "Sem 1" AS Semaine,
[FeuilleDeTemps].[NoDepartement], [FeuilleDeTemps].[NoClassification],
[FeuilleDeTemps].[DIM1], IIf([Taux
2]=True,[Taux2],IIf([RetourTaux2]=True,[Taux2],[Taux1])) AS Taux,
[FeuilleDeTemps].[NoCodeDim1], IIf([NoStatut]=1 And [SiDim1]=-1 And
[NoCodeDim1]=19,2,IIf([SiDim1]=-1,1,0)) AS Anciennete,
[FeuilleDeTemps].[NoteDim1]
FROM (Employes INNER JOIN FeuilleDeTemps ON
[Employes].[NoEmploye]=[FeuilleDeTemps].[NoEmploye]) INNER JOIN
SousTableDepartement ON
[FeuilleDeTemps].[NoDepartement]=[SousTableDepartement].[NoAutoDep]
WHERE NoPeriode=[Forms]![FNoPeriodePaye]![NoPeriodePaye];



Arvin Meyer said:
Add a form with an unbound textbox on it. Fill in the data and use the
form's textbox as a criteria in each of the 14 queries:

Select * From Whatever
Where PayrollDate = Forms!FormName!txtBoxName
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
Click to expand...
Click to expand...
 
Hi everybody,
Hi Douglas,
Thank you for your answer.
It was not open. I opened it and it made no diffenrence.
Any other suggestion ???
Thanks again,
Céline

Douglas J. Steele said:
Is your form open when you run the query? Access will not open the form
just because you're referencing it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Céline Brien said:
Hi everybody,
Hi Arvin,
Thank you so much for your answer.
I created a table SousTableNoPeriodePaye with a text field NoPeriodePaye.
With that table I create a form FNoPeriodePaye with the text fied of the
table.
I added the WHERE line to my SQL codes.
When I execute the query a dialog box is asking to type in the parameter
of Formulaires!FNoPeriodePaye!NoPeriodePaye.
What has to be corrected ???
Below are my codes.
Many thanks again,
Céline
-------------------------------------------
INSERT INTO Historique ( NoEmploye, NoPeriode, [Date], Semaine, NoDep,
NoClass, Heures, Taux, NoCode, Anciennete, [Note] )
SELECT [FeuilleDeTemps].[NoEmploye], [FeuilleDeTemps].[NoPeriode],
[FeuilleDeTemps].[DateDebut], "Sem 1" AS Semaine,
[FeuilleDeTemps].[NoDepartement], [FeuilleDeTemps].[NoClassification],
[FeuilleDeTemps].[DIM1], IIf([Taux
2]=True,[Taux2],IIf([RetourTaux2]=True,[Taux2],[Taux1])) AS Taux,
[FeuilleDeTemps].[NoCodeDim1], IIf([NoStatut]=1 And [SiDim1]=-1 And
[NoCodeDim1]=19,2,IIf([SiDim1]=-1,1,0)) AS Anciennete,
[FeuilleDeTemps].[NoteDim1]
FROM (Employes INNER JOIN FeuilleDeTemps ON
[Employes].[NoEmploye]=[FeuilleDeTemps].[NoEmploye]) INNER JOIN
SousTableDepartement ON
[FeuilleDeTemps].[NoDepartement]=[SousTableDepartement].[NoAutoDep]
WHERE NoPeriode=[Forms]![FNoPeriodePaye]![NoPeriodePaye];



Arvin Meyer said:
Add a form with an unbound textbox on it. Fill in the data and use the
form's textbox as a criteria in each of the 14 queries:

Select * From Whatever
Where PayrollDate = Forms!FormName!txtBoxName
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi everybody,

14 queries execute one after the other via a macro.

A common field : Payroll date.

I would like to add the possibility to specify which payroll date.

But of course, I don't want to be ask 14 times which payroll date.

How do I add a common variable for many queries ????

Thank you for your help and have a good day,

Céline
Click to expand...
Click to expand...
Click to expand...
 
The only thing that occurs to me is to double check that you didn't misspell
the name of the form and/or text box.

You're apparently using a French version of Access (or Windows, or both).
It's possible you need to put [Formulaires]![FNoPeriodePaye]![NoPeriodePaye]
rather than [Forms]![FNoPeriodePaye]![NoPeriodePaye], but that would
surprise me...

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Céline Brien said:
Hi everybody,
Hi Douglas,
Thank you for your answer.
It was not open. I opened it and it made no diffenrence.
Any other suggestion ???
Thanks again,
Céline

Douglas J. Steele said:
Is your form open when you run the query? Access will not open the form
just because you're referencing it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Céline Brien said:
Hi everybody,
Hi Arvin,
Thank you so much for your answer.
I created a table SousTableNoPeriodePaye with a text field
NoPeriodePaye. With that table I create a form FNoPeriodePaye with the
text fied of the table.
I added the WHERE line to my SQL codes.
When I execute the query a dialog box is asking to type in the parameter
of Formulaires!FNoPeriodePaye!NoPeriodePaye.
What has to be corrected ???
Below are my codes.
Many thanks again,
Céline
-------------------------------------------
INSERT INTO Historique ( NoEmploye, NoPeriode, [Date], Semaine, NoDep,
NoClass, Heures, Taux, NoCode, Anciennete, [Note] )
SELECT [FeuilleDeTemps].[NoEmploye], [FeuilleDeTemps].[NoPeriode],
[FeuilleDeTemps].[DateDebut], "Sem 1" AS Semaine,
[FeuilleDeTemps].[NoDepartement], [FeuilleDeTemps].[NoClassification],
[FeuilleDeTemps].[DIM1], IIf([Taux
2]=True,[Taux2],IIf([RetourTaux2]=True,[Taux2],[Taux1])) AS Taux,
[FeuilleDeTemps].[NoCodeDim1], IIf([NoStatut]=1 And [SiDim1]=-1 And
[NoCodeDim1]=19,2,IIf([SiDim1]=-1,1,0)) AS Anciennete,
[FeuilleDeTemps].[NoteDim1]
FROM (Employes INNER JOIN FeuilleDeTemps ON
[Employes].[NoEmploye]=[FeuilleDeTemps].[NoEmploye]) INNER JOIN
SousTableDepartement ON
[FeuilleDeTemps].[NoDepartement]=[SousTableDepartement].[NoAutoDep]
WHERE NoPeriode=[Forms]![FNoPeriodePaye]![NoPeriodePaye];



"Arvin Meyer [MVP]" <[email protected]> a écrit dans le message de (e-mail address removed)...
Add a form with an unbound textbox on it. Fill in the data and use the
form's textbox as a criteria in each of the 14 queries:

Select * From Whatever
Where PayrollDate = Forms!FormName!txtBoxName
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi everybody,

14 queries execute one after the other via a macro.

A common field : Payroll date.

I would like to add the possibility to specify which payroll date.

But of course, I don't want to be ask 14 times which payroll date.

How do I add a common variable for many queries ????

Thank you for your help and have a good day,

Céline
Click to expand...
Click to expand...
Click to expand...
 
I triple check the spelling of the name of the form and the text box (the
name of the control and the source are the same)
Formulaires à la place de Forms does not work
I get the same dialog box asking to type in the parameter
Formulaires!FNoPeriodePaye!NoPeriodePaye.
Any other suggestion ???
Thanks again,
Céline

Douglas J. Steele said:
The only thing that occurs to me is to double check that you didn't
misspell the name of the form and/or text box.

You're apparently using a French version of Access (or Windows, or both).
It's possible you need to put
[Formulaires]![FNoPeriodePaye]![NoPeriodePaye] rather than
[Forms]![FNoPeriodePaye]![NoPeriodePaye], but that would surprise me...

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Céline Brien said:
Hi everybody,
Hi Douglas,
Thank you for your answer.
It was not open. I opened it and it made no diffenrence.
Any other suggestion ???
Thanks again,
Céline

Douglas J. Steele said:
Is your form open when you run the query? Access will not open the form
just because you're referencing it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi everybody,
Hi Arvin,
Thank you so much for your answer.
I created a table SousTableNoPeriodePaye with a text field
NoPeriodePaye. With that table I create a form FNoPeriodePaye with the
text fied of the table.
I added the WHERE line to my SQL codes.
When I execute the query a dialog box is asking to type in the
parameter of Formulaires!FNoPeriodePaye!NoPeriodePaye.
What has to be corrected ???
Below are my codes.
Many thanks again,
Céline
-------------------------------------------
INSERT INTO Historique ( NoEmploye, NoPeriode, [Date], Semaine, NoDep,
NoClass, Heures, Taux, NoCode, Anciennete, [Note] )
SELECT [FeuilleDeTemps].[NoEmploye], [FeuilleDeTemps].[NoPeriode],
[FeuilleDeTemps].[DateDebut], "Sem 1" AS Semaine,
[FeuilleDeTemps].[NoDepartement], [FeuilleDeTemps].[NoClassification],
[FeuilleDeTemps].[DIM1], IIf([Taux
2]=True,[Taux2],IIf([RetourTaux2]=True,[Taux2],[Taux1])) AS Taux,
[FeuilleDeTemps].[NoCodeDim1], IIf([NoStatut]=1 And [SiDim1]=-1 And
[NoCodeDim1]=19,2,IIf([SiDim1]=-1,1,0)) AS Anciennete,
[FeuilleDeTemps].[NoteDim1]
FROM (Employes INNER JOIN FeuilleDeTemps ON
[Employes].[NoEmploye]=[FeuilleDeTemps].[NoEmploye]) INNER JOIN
SousTableDepartement ON
[FeuilleDeTemps].[NoDepartement]=[SousTableDepartement].[NoAutoDep]
WHERE NoPeriode=[Forms]![FNoPeriodePaye]![NoPeriodePaye];



"Arvin Meyer [MVP]" <[email protected]> a écrit dans le message de (e-mail address removed)...
Add a form with an unbound textbox on it. Fill in the data and use the
form's textbox as a criteria in each of the 14 queries:

Select * From Whatever
Where PayrollDate = Forms!FormName!txtBoxName
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi everybody,

14 queries execute one after the other via a macro.

A common field : Payroll date.

I would like to add the possibility to specify which payroll date.

But of course, I don't want to be ask 14 times which payroll date.

How do I add a common variable for many queries ????

Thank you for your help and have a good day,

Céline
Click to expand...
Click to expand...
Click to expand...
 
Try renaming the control, just in case, and refer to the control, not the
source. (It shouldn't matter, but you never know...)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Céline Brien said:
I triple check the spelling of the name of the form and the text box (the
name of the control and the source are the same)
Formulaires à la place de Forms does not work
I get the same dialog box asking to type in the parameter
Formulaires!FNoPeriodePaye!NoPeriodePaye.
Any other suggestion ???
Thanks again,
Céline

Douglas J. Steele said:
The only thing that occurs to me is to double check that you didn't
misspell the name of the form and/or text box.

You're apparently using a French version of Access (or Windows, or both).
It's possible you need to put
[Formulaires]![FNoPeriodePaye]![NoPeriodePaye] rather than
[Forms]![FNoPeriodePaye]![NoPeriodePaye], but that would surprise me...

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Céline Brien said:
Hi everybody,
Hi Douglas,
Thank you for your answer.
It was not open. I opened it and it made no diffenrence.
Any other suggestion ???
Thanks again,
Céline

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> a écrit dans le
message de news: (e-mail address removed)...
Is your form open when you run the query? Access will not open the form
just because you're referencing it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi everybody,
Hi Arvin,
Thank you so much for your answer.
I created a table SousTableNoPeriodePaye with a text field
NoPeriodePaye. With that table I create a form FNoPeriodePaye with the
text fied of the table.
I added the WHERE line to my SQL codes.
When I execute the query a dialog box is asking to type in the
parameter of Formulaires!FNoPeriodePaye!NoPeriodePaye.
What has to be corrected ???
Below are my codes.
Many thanks again,
Céline
-------------------------------------------
INSERT INTO Historique ( NoEmploye, NoPeriode, [Date], Semaine, NoDep,
NoClass, Heures, Taux, NoCode, Anciennete, [Note] )
SELECT [FeuilleDeTemps].[NoEmploye], [FeuilleDeTemps].[NoPeriode],
[FeuilleDeTemps].[DateDebut], "Sem 1" AS Semaine,
[FeuilleDeTemps].[NoDepartement], [FeuilleDeTemps].[NoClassification],
[FeuilleDeTemps].[DIM1], IIf([Taux
2]=True,[Taux2],IIf([RetourTaux2]=True,[Taux2],[Taux1])) AS Taux,
[FeuilleDeTemps].[NoCodeDim1], IIf([NoStatut]=1 And [SiDim1]=-1 And
[NoCodeDim1]=19,2,IIf([SiDim1]=-1,1,0)) AS Anciennete,
[FeuilleDeTemps].[NoteDim1]
FROM (Employes INNER JOIN FeuilleDeTemps ON
[Employes].[NoEmploye]=[FeuilleDeTemps].[NoEmploye]) INNER JOIN
SousTableDepartement ON
[FeuilleDeTemps].[NoDepartement]=[SousTableDepartement].[NoAutoDep]
WHERE NoPeriode=[Forms]![FNoPeriodePaye]![NoPeriodePaye];



"Arvin Meyer [MVP]" <[email protected]> a écrit dans le message de (e-mail address removed)...
Add a form with an unbound textbox on it. Fill in the data and use
the
form's textbox as a criteria in each of the 14 queries:

Select * From Whatever
Where PayrollDate = Forms!FormName!txtBoxName
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi everybody,

14 queries execute one after the other via a macro.

A common field : Payroll date.

I would like to add the possibility to specify which payroll date.

But of course, I don't want to be ask 14 times which payroll date.

How do I add a common variable for many queries ????

Thank you for your help and have a good day,

Céline
Click to expand...
Click to expand...
Click to expand...
 
HI !

I finally decided to use this table as a join to limit the query to that pay
period.

The codes below.

It work well.

Thank you for suggesting to create this table feed by a form.

Céline

---------------
INSERT INTO Historique ( NoEmploye, NoPeriode, [Date], Semaine, NoDep,
NoClass, Heures, Taux, NoCode, Anciennete, [Note] )
SELECT FeuilleDeTemps.NoEmploye, FeuilleDeTemps.NoPeriode,
FeuilleDeTemps.DateDebut, "Sem 1" AS Semaine, FeuilleDeTemps.NoDepartement,
FeuilleDeTemps.NoClassification, FeuilleDeTemps.DIM1, IIf([Taux
2]=True,[Taux2],IIf([RetourTaux2]=True,[Taux2],[Taux1])) AS Taux,
FeuilleDeTemps.NoCodeDim1, IIf([NoStatut]=1 And [SiDim1]=-1 And
[NoCodeDim1]=19,2,IIf([SiDim1]=-1,1,0)) AS Anciennete,
FeuilleDeTemps.NoteDim1
FROM ((Employes INNER JOIN FeuilleDeTemps ON Employes.NoEmploye =
FeuilleDeTemps.NoEmploye) INNER JOIN SousTableDepartement ON
FeuilleDeTemps.NoDepartement = SousTableDepartement.NoAutoDep) INNER JOIN
SousTableNoPeriodePaye ON FeuilleDeTemps.NoPeriode =
SousTableNoPeriodePaye.NoPeriodePaye;

Douglas J. Steele said:
Try renaming the control, just in case, and refer to the control, not the
source. (It shouldn't matter, but you never know...)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Céline Brien said:
I triple check the spelling of the name of the form and the text box (the
name of the control and the source are the same)
Formulaires à la place de Forms does not work
I get the same dialog box asking to type in the parameter
Formulaires!FNoPeriodePaye!NoPeriodePaye.
Any other suggestion ???
Thanks again,
Céline

Douglas J. Steele said:
The only thing that occurs to me is to double check that you didn't
misspell the name of the form and/or text box.

You're apparently using a French version of Access (or Windows, or
both). It's possible you need to put
[Formulaires]![FNoPeriodePaye]![NoPeriodePaye] rather than
[Forms]![FNoPeriodePaye]![NoPeriodePaye], but that would surprise me...

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi everybody,
Hi Douglas,
Thank you for your answer.
It was not open. I opened it and it made no diffenrence.
Any other suggestion ???
Thanks again,
Céline

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> a écrit dans le
message de news: (e-mail address removed)...
Is your form open when you run the query? Access will not open the
form just because you're referencing it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi everybody,
Hi Arvin,
Thank you so much for your answer.
I created a table SousTableNoPeriodePaye with a text field
NoPeriodePaye. With that table I create a form FNoPeriodePaye with
the text fied of the table.
I added the WHERE line to my SQL codes.
When I execute the query a dialog box is asking to type in the
parameter of Formulaires!FNoPeriodePaye!NoPeriodePaye.
What has to be corrected ???
Below are my codes.
Many thanks again,
Céline
-------------------------------------------
INSERT INTO Historique ( NoEmploye, NoPeriode, [Date], Semaine,
NoDep, NoClass, Heures, Taux, NoCode, Anciennete, [Note] )
SELECT [FeuilleDeTemps].[NoEmploye], [FeuilleDeTemps].[NoPeriode],
[FeuilleDeTemps].[DateDebut], "Sem 1" AS Semaine,
[FeuilleDeTemps].[NoDepartement],
[FeuilleDeTemps].[NoClassification], [FeuilleDeTemps].[DIM1],
IIf([Taux 2]=True,[Taux2],IIf([RetourTaux2]=True,[Taux2],[Taux1])) AS
Taux, [FeuilleDeTemps].[NoCodeDim1], IIf([NoStatut]=1 And [SiDim1]=-1
And [NoCodeDim1]=19,2,IIf([SiDim1]=-1,1,0)) AS Anciennete,
[FeuilleDeTemps].[NoteDim1]
FROM (Employes INNER JOIN FeuilleDeTemps ON
[Employes].[NoEmploye]=[FeuilleDeTemps].[NoEmploye]) INNER JOIN
SousTableDepartement ON
[FeuilleDeTemps].[NoDepartement]=[SousTableDepartement].[NoAutoDep]
WHERE NoPeriode=[Forms]![FNoPeriodePaye]![NoPeriodePaye];



"Arvin Meyer [MVP]" <[email protected]> a écrit dans le message de (e-mail address removed)...
Add a form with an unbound textbox on it. Fill in the data and use
the
form's textbox as a criteria in each of the 14 queries:

Select * From Whatever
Where PayrollDate = Forms!FormName!txtBoxName
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi everybody,

14 queries execute one after the other via a macro.

A common field : Payroll date.

I would like to add the possibility to specify which payroll date.

But of course, I don't want to be ask 14 times which payroll date.

How do I add a common variable for many queries ????

Thank you for your help and have a good day,

Céline
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

Add a field to Query 2
combining queries 2
Formatting Result of Date AND Time Difference 3
payroll weeks / varying first day and week of the year 2
Query for Obtaining Detail Records 6
querying unrelated tables 7
reformat data field in a query? 1
Is there a formula? 4

Back
Top