Wrong data pulled from parameter query

[Guest]

I need to filter 8 pay periods (PP) from user selected PP. For example if
user select PP 12, I need to show every entry between PP 5 and 12.

I have set up following criteria in fields CompYear and CompPeriod (I am
just showing the last part of the criteria, [Forms]![frmSelect] is omitted):
1. line: YearStart >=PeriodStart
2. line: YearEnd <=PeriodEnd

This works great when I query values for different years (2004/48 – 2005/3).
However, when I query values from the same year (e.g. PP 15) I get every
value from PP1, rather than PP 8. I am at the end of my ideas. What am I
doing wrong?

Thank you for your help.

 

[Guest]

Try to format your periods like 2005/03 instead of 2005/3, your periods
within the same year must be showing 2005/1, and 2005/15, if you change the
format then they will be 2005/01 and 2005/15 then going 8 months back it
should have 2005/08 as the start period

 

[Guest]

Thank you for the suggestion, but it did not work. It actually made things
worse since the original set up worked on different years, but not the same
years. By changing PP from integer to text I get all the PP from 2005, but
only selected PP in 2004; e.g. under the old set up when selecting 2005/2 I
get 8 PP - 2004/47 through 2005/3; under your suggestion I get 2004/47
through 2005/18 – latest PP).

It must be something else.

Try to format your periods like 2005/03 instead of 2005/3, your periods
within the same year must be showing 2005/1, and 2005/15, if you change the
format then they will be 2005/01 and 2005/15 then going 8 months back it
should have 2005/08 as the start period

I need to filter 8 pay periods (PP) from user selected PP. For example if
user select PP 12, I need to show every entry between PP 5 and 12.

I have set up following criteria in fields CompYear and CompPeriod (I am
just showing the last part of the criteria, [Forms]![frmSelect] is omitted):
1. line: YearStart >=PeriodStart
2. line: YearEnd <=PeriodEnd

This works great when I query values for different years (2004/48 – 2005/3).
However, when I query values from the same year (e.g. PP 15) I get every
value from PP1, rather than PP 8. I am at the end of my ideas. What am I
doing wrong?

Thank you for your help.

 

[John Vinson]

I need to filter 8 pay periods (PP) from user selected PP. For example if
user select PP 12, I need to show every entry between PP 5 and 12.

I have set up following criteria in fields CompYear and CompPeriod (I am
just showing the last part of the criteria, [Forms]![frmSelect] is omitted):
1. line: YearStart >=PeriodStart
2. line: YearEnd <=PeriodEnd

This works great when I query values for different years (2004/48 – 2005/3).
However, when I query values from the same year (e.g. PP 15) I get every
value from PP1, rather than PP 8. I am at the end of my ideas. What am I
doing wrong?

Thank you for your help.

What you're apparently doing wrong is storing two numeric values in
one Text field. As a text string, "2004/3" sorts AFTER "2004/28"
because the character 3 sorts after the character 2, and that's the
first place that the two strings differ.

Access has NO WAY to know that you intend these to be separate fields.
Solution? Store them as separate fields, say PayYear and PayPeriod.
Or, perhaps better, store them as Date/Time fields with the actual
date of the beginning (or end) of the pay period.

John W. Vinson[MVP]

 

[Guest]

The values are in 2 fields called CompYear and CompPeriod (both Integer). I
wrote the values (2004/3) just to simplify it. My error.

Here is part of my post (amended):

I have set up following criteria in fields CompYear and CompPeriod (I am
just showing the last part of the criteria, [Forms]![frmSelect] is omitted):

Field: CompYear CompPeriod
Criteria 1: YearStart >=PeriodStart
Criteria 2: YearEnd <=PeriodEnd

I still can’t get the values I need. Any suggestions?

Thank you.

Luke

I need to filter 8 pay periods (PP) from user selected PP. For example if
user select PP 12, I need to show every entry between PP 5 and 12.

I have set up following criteria in fields CompYear and CompPeriod (I am
just showing the last part of the criteria, [Forms]![frmSelect] is omitted):
1. line: YearStart >=PeriodStart
2. line: YearEnd <=PeriodEnd

This works great when I query values for different years (2004/48 – 2005/3).
However, when I query values from the same year (e.g. PP 15) I get every
value from PP1, rather than PP 8. I am at the end of my ideas. What am I
doing wrong?

Thank you for your help.

What you're apparently doing wrong is storing two numeric values in
one Text field. As a text string, "2004/3" sorts AFTER "2004/28"
because the character 3 sorts after the character 2, and that's the
first place that the two strings differ.

Access has NO WAY to know that you intend these to be separate fields.
Solution? Store them as separate fields, say PayYear and PayPeriod.
Or, perhaps better, store them as Date/Time fields with the actual
date of the beginning (or end) of the pay period.

John W. Vinson[MVP]

 

[John Vinson]

The values are in 2 fields called CompYear and CompPeriod (both Integer). I
wrote the values (2004/3) just to simplify it. My error.

Here is part of my post (amended):

I have set up following criteria in fields CompYear and CompPeriod (I am
just showing the last part of the criteria, [Forms]![frmSelect] is omitted):

Field: CompYear CompPeriod
Criteria 1: YearStart >=PeriodStart
Criteria 2: YearEnd <=PeriodEnd

If these are on separate rows of the query grid, you are using OR
logic: it will evaluate ALL of the criteria on the first row, and then
ALL of the criteria on the second row; if either evaluation is TRUE it
will return the record.

In this case you will return the record if CompYear is equal to
YearStart, and CompPeriod is greater than or equal to PeriodStart:

OR

if CompYear is equal to YearEnd and CompPeriod is less than or equaol
to PeriodEnd.

This probably isn't what you want!

Please open the query in SQL view and post the SQL text here. It may
look cryptic at first, but the SQL *IS* the query (the grid's just a
tool for building SQL). For multifield OR-logic queries it's often
easier to build the query in SQL. In your case, if you want all
records between YearStart/PeriodStart and YearEnd/PeriodEnd (spanning
multiple years) you'll need a fairly complex expression like

WHERE (CompYear = [YearStart] AND CompPeriod >= [PeriodStart])
OR (CompYear = [YearEnd] AND CompPeriod <= [PeriodEnd])
OR (CompYear > [YearStart] AND CompYear < [YearEnd])


John W. Vinson[MVP]

 

[Guest]

Thanks you for the suggestion, but it did not work. The original set up
works fine when used from one year to another e.g. 2004 and 47 to 2005 and 2
(8 PP range). It is when I use the same year that I get all the entries.

Therefore I have created a new query with the following set up that works
for the same year, but not different years (in different years I get no
values at all).

Here is the set up for the second query (same year, e.g. 2005):

Field: CompYear CompPeriod
Criteria 1: YearStart >=PeriodStart And <=PeriodEnd

I use it in the code and have created True/False statement based on year to
get the correct SQL statement to run. However, I should not need to do this,
as it complicates unnecessarily the update sequence and would like to replace
it with one query that works for both the previous/current year (2004/2005)
and current year (2005). I still do not know why it does not work. Plus I
like to learn

Luke

The values are in 2 fields called CompYear and CompPeriod (both Integer). I
wrote the values (2004/3) just to simplify it. My error.

Here is part of my post (amended):

I have set up following criteria in fields CompYear and CompPeriod (I am
just showing the last part of the criteria, [Forms]![frmSelect] is omitted):

Field: CompYear CompPeriod
Criteria 1: YearStart >=PeriodStart
Criteria 2: YearEnd <=PeriodEnd

If these are on separate rows of the query grid, you are using OR
logic: it will evaluate ALL of the criteria on the first row, and then
ALL of the criteria on the second row; if either evaluation is TRUE it
will return the record.

In this case you will return the record if CompYear is equal to
YearStart, and CompPeriod is greater than or equal to PeriodStart:

OR

if CompYear is equal to YearEnd and CompPeriod is less than or equaol
to PeriodEnd.

This probably isn't what you want!

Please open the query in SQL view and post the SQL text here. It may
look cryptic at first, but the SQL *IS* the query (the grid's just a
tool for building SQL). For multifield OR-logic queries it's often
easier to build the query in SQL. In your case, if you want all
records between YearStart/PeriodStart and YearEnd/PeriodEnd (spanning
multiple years) you'll need a fairly complex expression like

WHERE (CompYear = [YearStart] AND CompPeriod >= [PeriodStart])
OR (CompYear = [YearEnd] AND CompPeriod <= [PeriodEnd])
OR (CompYear > [YearStart] AND CompYear < [YearEnd])


John W. Vinson[MVP]

 

[John Vinson]

Thanks you for the suggestion, but it did not work. The original set up
works fine when used from one year to another e.g. 2004 and 47 to 2005 and 2
(8 PP range). It is when I use the same year that I get all the entries.

Therefore I have created a new query with the following set up that works
for the same year, but not different years (in different years I get no
values at all).

Here is the set up for the second query (same year, e.g. 2005):

Field: CompYear CompPeriod
Criteria 1: YearStart >=PeriodStart And <=PeriodEnd

Please post the SQL view of both queries. I can't "see" what's going
on.

I use it in the code and have created True/False statement based on year to
get the correct SQL statement to run.

I haven't a trace of a clue what you mean here. Could you explain?

However, I should not need to do this,
as it complicates unnecessarily the update sequence and would like to replace
it with one query that works for both the previous/current year (2004/2005)
and current year (2005). I still do not know why it does not work.

Me neither - since I don't know what your query actually does. Please
post the SQL, then we'll both know!

John W. Vinson[MVP]

 

[Guest]

Here is the SQL:

SELECT tblTransactions.SalesRepID, tblTransactions.CompYear,
tblTransactions.CompPeriod, tblTransactions.CompPaid
FROM tblTransactions
WHERE (((tblTransactions.CompYear)=[Forms]![frm_Reports]![txtYearStart]) AND
((tblTransactions.CompPeriod)>=[Forms]![frm_Reports]![txtPPStart])) OR
(((tblTransactions.CompYear)=[Forms]![frm_Reports]![txtYearEnd]) AND
((tblTransactions.CompPeriod)<=[Forms]![frm_Reports]![txtPPEnd]))
ORDER BY tblTransactions.SalesRepID;

Thank you for your help.

Luke