functions in criteria

Prev: Action Query (Append) didn't add records due to key violations.
Next: DSUM Date Criteria

From: seeker on 18 Mar 2010 15:10

---

The following phrase is in the criteria of a field;

Between criteriachange(1,[Begin Date]) And DateAdd("d",daynumber([Begin
Date]),criteriachange(1,[Begin Date]))

The functions are as follows;

Function daynumber(dtedate As Date) As Integer
Select Case DatePart("m", dtedate)
Case 1
daynumber = 30
Case 2
daynumber = 27
Case 3
daynumber = 30
Case 4
daynumber = 29
Case 5
daynumber = 30
Case 6
daynumber = 29
Case 7
daynumber = 30
Case 8
daynumber = 30
Case 9
daynumber = 29
Case 10
daynumber = 30
Case 11
daynumber = 29
Case 12
daynumber = 30
End Select
End Function
Function criteriachange(a As Integer, b As Date) As String
Select Case DatePart("m", b)
Case 1 'this represents if january is the month of intake
Select Case a
Case 1
'criteriachange = #1/1/2009#
criteriachange = "#1/1/" & Year(b) & "#"
Case 2
criteriachange = #2/1/2009#
Case 3
criteriachange = #3/1/2009#
Case 4
criteriachange = #4/1/2009#
Case 5
criteriachange = #5/1/2009#
Case 6
criteriachange = #6/1/2009#
End Select
Case 2
Select Case a
Case 1
criteriachange = #2/1/2009#
Case 2
criteriachange = #3/1/2009#
Case 3
criteriachange = #4/1/2009#
Case 4
criteriachange = #5/1/2009#
Case 5
criteriachange = #6/1/2009#
Case 6
criteriachange = #7/1/2009#
End Select
Case 3
Select Case a
Case 1
criteriachange = #3/1/2009#
Case 2
criteriachange = #4/1/2009#
Case 3
criteriachange = #5/1/2009#
Case 4
criteriachange = #6/1/2009#
Case 5
criteriachange = #7/1/2009#
Case 6
criteriachange = #8/1/2009#
End Select
Case 4
Select Case a
Case 1
criteriachange = #4/1/2009#
Case 2
criteriachange = #5/1/2009#
Case 3
criteriachange = #6/1/2009#
Case 4
criteriachange = #7/1/2009#
Case 5
criteriachange = #8/1/2009#
Case 6
criteriachange = #9/1/2009#
End Select
Case 5
Select Case a
Case 1
criteriachange = #5/1/2009#
Case 2
criteriachange = #6/1/2009#
Case 3
criteriachange = #7/1/2009#
Case 4
criteriachange = #8/1/2009#
Case 5
criteriachange = #9/1/2009#
Case 6
criteriachange = #10/1/2009#
End Select
Case 6
Select Case a
Case 1
criteriachange = #6/1/2008#
Case 2
criteriachange = #7/1/2008#
Case 3
criteriachange = #8/1/2008#
Case 4
criteriachange = #9/1/2008#
Case 5
criteriachange = #10/1/2008#
Case 6
criteriachange = #11/1/2008#
End Select
Case 7
Select Case a
Case 1
criteriachange = #7/1/2008#
Case 2
criteriachange = #8/1/2008#
Case 3
criteriachange = #9/1/2009#
Case 4
criteriachange = #10/1/2008#
Case 5
criteriachange = #11/1/2008#
Case 6
criteriachange = #12/1/2008#
End Select
Case 8
Select Case a
Case 1
criteriachange = #8/1/2008#
Case 2
criteriachange = #9/1/2008#
Case 3
criteriachange = #10/1/2008#
Case 4
criteriachange = #11/1/2008#
Case 5
criteriachange = #12/1/2008#
Case 6
criteriachange = #1/1/2009#
End Select
Case 9
Select Case a
Case 1
criteriachange = #9/1/2008#
Case 2
criteriachange = #10/1/2008#
Case 3
criteriachange = #11/1/2008#
Case 4
criteriachange = #12/1/2008#
Case 5
criteriachange = #1/1/2009#
Case 6
criteriachange = #2/1/2009#
End Select
Case 10
Select Case a
Case 1
criteriachange = #10/1/2008#
Case 2
criteriachange = #11/1/2008#
Case 3
criteriachange = #12/1/2008#
Case 4
criteriachange = #1/1/2009#
Case 5
criteriachange = #2/1/2009#
Case 6
criteriachange = #3/1/2009#
End Select
Case 11
Select Case a
Case 1
criteriachange = #11/1/2008#
Case 2
criteriachange = #12/1/2008#
Case 3
criteriachange = #1/1/2009#
Case 4
criteriachange = #2/1/2009#
Case 5
criteriachange = #3/1/2009#
Case 6
criteriachange = #4/1/2009#
End Select
Case 12
Select Case a
Case 1
criteriachange = #12/1/2008#
Case 2
criteriachange = #1/1/2009#
Case 3
criteriachange = #2/1/2009#
Case 4
criteriachange = #3/1/2009#
Case 5
criteriachange = #4/1/2009#
Case 6
criteriachange = #5/1/2009#
End Select
End Select
End Function

When I run the query it says that criteria is either typed incorrectly or
too complex. Can you tell me a simpler way or correct what is wrong. Thanks.

From: KARL DEWEY on 18 Mar 2010 19:35

---

What about an easier way --
Between DateSerial(Year(Date()),Month(DateAdd("m",-1,Date())),1) AND
DateSerial(Year(Date()), Month(Date()),0)
--
Build a little, test a little.


"seeker" wrote:

> The following phrase is in the criteria of a field;
>
> Between criteriachange(1,[Begin Date]) And DateAdd("d",daynumber([Begin
> Date]),criteriachange(1,[Begin Date]))
>
> The functions are as follows;
>
> Function daynumber(dtedate As Date) As Integer
> Select Case DatePart("m", dtedate)
> Case 1
> daynumber = 30
> Case 2
> daynumber = 27
> Case 3
> daynumber = 30
> Case 4
> daynumber = 29
> Case 5
> daynumber = 30
> Case 6
> daynumber = 29
> Case 7
> daynumber = 30
> Case 8
> daynumber = 30
> Case 9
> daynumber = 29
> Case 10
> daynumber = 30
> Case 11
> daynumber = 29
> Case 12
> daynumber = 30
> End Select
> End Function
> Function criteriachange(a As Integer, b As Date) As String
> Select Case DatePart("m", b)
> Case 1 'this represents if january is the month of intake
> Select Case a
> Case 1
> 'criteriachange = #1/1/2009#
> criteriachange = "#1/1/" & Year(b) & "#"
> Case 2
> criteriachange = #2/1/2009#
> Case 3
> criteriachange = #3/1/2009#
> Case 4
> criteriachange = #4/1/2009#
> Case 5
> criteriachange = #5/1/2009#
> Case 6
> criteriachange = #6/1/2009#
> End Select
> Case 2
> Select Case a
> Case 1
> criteriachange = #2/1/2009#
> Case 2
> criteriachange = #3/1/2009#
> Case 3
> criteriachange = #4/1/2009#
> Case 4
> criteriachange = #5/1/2009#
> Case 5
> criteriachange = #6/1/2009#
> Case 6
> criteriachange = #7/1/2009#
> End Select
> Case 3
> Select Case a
> Case 1
> criteriachange = #3/1/2009#
> Case 2
> criteriachange = #4/1/2009#
> Case 3
> criteriachange = #5/1/2009#
> Case 4
> criteriachange = #6/1/2009#
> Case 5
> criteriachange = #7/1/2009#
> Case 6
> criteriachange = #8/1/2009#
> End Select
> Case 4
> Select Case a
> Case 1
> criteriachange = #4/1/2009#
> Case 2
> criteriachange = #5/1/2009#
> Case 3
> criteriachange = #6/1/2009#
> Case 4
> criteriachange = #7/1/2009#
> Case 5
> criteriachange = #8/1/2009#
> Case 6
> criteriachange = #9/1/2009#
> End Select
> Case 5
> Select Case a
> Case 1
> criteriachange = #5/1/2009#
> Case 2
> criteriachange = #6/1/2009#
> Case 3
> criteriachange = #7/1/2009#
> Case 4
> criteriachange = #8/1/2009#
> Case 5
> criteriachange = #9/1/2009#
> Case 6
> criteriachange = #10/1/2009#
> End Select
> Case 6
> Select Case a
> Case 1
> criteriachange = #6/1/2008#
> Case 2
> criteriachange = #7/1/2008#
> Case 3
> criteriachange = #8/1/2008#
> Case 4
> criteriachange = #9/1/2008#
> Case 5
> criteriachange = #10/1/2008#
> Case 6
> criteriachange = #11/1/2008#
> End Select
> Case 7
> Select Case a
> Case 1
> criteriachange = #7/1/2008#
> Case 2
> criteriachange = #8/1/2008#
> Case 3
> criteriachange = #9/1/2009#
> Case 4
> criteriachange = #10/1/2008#
> Case 5
> criteriachange = #11/1/2008#
> Case 6
> criteriachange = #12/1/2008#
> End Select
> Case 8
> Select Case a
> Case 1
> criteriachange = #8/1/2008#
> Case 2
> criteriachange = #9/1/2008#
> Case 3
> criteriachange = #10/1/2008#
> Case 4
> criteriachange = #11/1/2008#
> Case 5
> criteriachange = #12/1/2008#
> Case 6
> criteriachange = #1/1/2009#
> End Select
> Case 9
> Select Case a
> Case 1
> criteriachange = #9/1/2008#
> Case 2
> criteriachange = #10/1/2008#
> Case 3
> criteriachange = #11/1/2008#
> Case 4
> criteriachange = #12/1/2008#
> Case 5
> criteriachange = #1/1/2009#
> Case 6
> criteriachange = #2/1/2009#
> End Select
> Case 10
> Select Case a
> Case 1
> criteriachange = #10/1/2008#
> Case 2
> criteriachange = #11/1/2008#
> Case 3
> criteriachange = #12/1/2008#
> Case 4
> criteriachange = #1/1/2009#
> Case 5
> criteriachange = #2/1/2009#
> Case 6
> criteriachange = #3/1/2009#
> End Select
> Case 11
> Select Case a
> Case 1
> criteriachange = #11/1/2008#
> Case 2
> criteriachange = #12/1/2008#
> Case 3
> criteriachange = #1/1/2009#
> Case 4
> criteriachange = #2/1/2009#
> Case 5
> criteriachange = #3/1/2009#
> Case 6
> criteriachange = #4/1/2009#
> End Select
> Case 12
> Select Case a
> Case 1
> criteriachange = #12/1/2008#
> Case 2
> criteriachange = #1/1/2009#
> Case 3
> criteriachange = #2/1/2009#
> Case 4
> criteriachange = #3/1/2009#
> Case 5
> criteriachange = #4/1/2009#
> Case 6
> criteriachange = #5/1/2009#
> End Select
> End Select
> End Function
>
> When I run the query it says that criteria is either typed incorrectly or
> too complex. Can you tell me a simpler way or correct what is wrong. Thanks.

From: John Spencer on 18 Mar 2010 20:11

---

What are you trying to accomplish? I am guessing that it is one of the two
criteria below. If not, please describe in words what you are trying to
accomplish.

This criteria will generate the 1st day of the month before begin date and the
next to the last day of the month before begin date.

Between DateSerial(Year([Begin Date]),Month([Begin Date])-1,1) and
DateSerial(Year([Begin Date]),Month([Begin Date]),1-2)

This criteria will generate the 1st day of the month of begin date and the
next to the last day of the month of begin date.

Between DateSerial(Year([Begin Date]),Month([Begin Date]),1) and
DateSerial(Year([Begin Date]),Month([Begin Date])+1 ,1-2)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

From: KARL DEWEY on 19 Mar 2010 10:38

---

BTW you will need a Case 13 to handle Leap Year. A Leap Year is evenly
divided by 4.

--
Build a little, test a little.


"KARL DEWEY" wrote:

> What about an easier way --
> Between DateSerial(Year(Date()),Month(DateAdd("m",-1,Date())),1) AND
> DateSerial(Year(Date()), Month(Date()),0)
> --
> Build a little, test a little.
>
>
> "seeker" wrote:
>
> > The following phrase is in the criteria of a field;
> >
> > Between criteriachange(1,[Begin Date]) And DateAdd("d",daynumber([Begin
> > Date]),criteriachange(1,[Begin Date]))
> >
> > The functions are as follows;
> >
> > Function daynumber(dtedate As Date) As Integer
> > Select Case DatePart("m", dtedate)
> > Case 1
> > daynumber = 30
> > Case 2
> > daynumber = 27
> > Case 3
> > daynumber = 30
> > Case 4
> > daynumber = 29
> > Case 5
> > daynumber = 30
> > Case 6
> > daynumber = 29
> > Case 7
> > daynumber = 30
> > Case 8
> > daynumber = 30
> > Case 9
> > daynumber = 29
> > Case 10
> > daynumber = 30
> > Case 11
> > daynumber = 29
> > Case 12
> > daynumber = 30
> > End Select
> > End Function
> > Function criteriachange(a As Integer, b As Date) As String
> > Select Case DatePart("m", b)
> > Case 1 'this represents if january is the month of intake
> > Select Case a
> > Case 1
> > 'criteriachange = #1/1/2009#
> > criteriachange = "#1/1/" & Year(b) & "#"
> > Case 2
> > criteriachange = #2/1/2009#
> > Case 3
> > criteriachange = #3/1/2009#
> > Case 4
> > criteriachange = #4/1/2009#
> > Case 5
> > criteriachange = #5/1/2009#
> > Case 6
> > criteriachange = #6/1/2009#
> > End Select
> > Case 2
> > Select Case a
> > Case 1
> > criteriachange = #2/1/2009#
> > Case 2
> > criteriachange = #3/1/2009#
> > Case 3
> > criteriachange = #4/1/2009#
> > Case 4
> > criteriachange = #5/1/2009#
> > Case 5
> > criteriachange = #6/1/2009#
> > Case 6
> > criteriachange = #7/1/2009#
> > End Select
> > Case 3
> > Select Case a
> > Case 1
> > criteriachange = #3/1/2009#
> > Case 2
> > criteriachange = #4/1/2009#
> > Case 3
> > criteriachange = #5/1/2009#
> > Case 4
> > criteriachange = #6/1/2009#
> > Case 5
> > criteriachange = #7/1/2009#
> > Case 6
> > criteriachange = #8/1/2009#
> > End Select
> > Case 4
> > Select Case a
> > Case 1
> > criteriachange = #4/1/2009#
> > Case 2
> > criteriachange = #5/1/2009#
> > Case 3
> > criteriachange = #6/1/2009#
> > Case 4
> > criteriachange = #7/1/2009#
> > Case 5
> > criteriachange = #8/1/2009#
> > Case 6
> > criteriachange = #9/1/2009#
> > End Select
> > Case 5
> > Select Case a
> > Case 1
> > criteriachange = #5/1/2009#
> > Case 2
> > criteriachange = #6/1/2009#
> > Case 3
> > criteriachange = #7/1/2009#
> > Case 4
> > criteriachange = #8/1/2009#
> > Case 5
> > criteriachange = #9/1/2009#
> > Case 6
> > criteriachange = #10/1/2009#
> > End Select
> > Case 6
> > Select Case a
> > Case 1
> > criteriachange = #6/1/2008#
> > Case 2
> > criteriachange = #7/1/2008#
> > Case 3
> > criteriachange = #8/1/2008#
> > Case 4
> > criteriachange = #9/1/2008#
> > Case 5
> > criteriachange = #10/1/2008#
> > Case 6
> > criteriachange = #11/1/2008#
> > End Select
> > Case 7
> > Select Case a
> > Case 1
> > criteriachange = #7/1/2008#
> > Case 2
> > criteriachange = #8/1/2008#
> > Case 3
> > criteriachange = #9/1/2009#
> > Case 4
> > criteriachange = #10/1/2008#
> > Case 5
> > criteriachange = #11/1/2008#
> > Case 6
> > criteriachange = #12/1/2008#
> > End Select
> > Case 8
> > Select Case a
> > Case 1
> > criteriachange = #8/1/2008#
> > Case 2
> > criteriachange = #9/1/2008#
> > Case 3
> > criteriachange = #10/1/2008#
> > Case 4
> > criteriachange = #11/1/2008#
> > Case 5
> > criteriachange = #12/1/2008#
> > Case 6
> > criteriachange = #1/1/2009#
> > End Select
> > Case 9
> > Select Case a
> > Case 1
> > criteriachange = #9/1/2008#
> > Case 2
> > criteriachange = #10/1/2008#
> > Case 3
> > criteriachange = #11/1/2008#
> > Case 4
> > criteriachange = #12/1/2008#
> > Case 5
> > criteriachange = #1/1/2009#
> > Case 6
> > criteriachange = #2/1/2009#
> > End Select
> > Case 10
> > Select Case a
> > Case 1
> > criteriachange = #10/1/2008#
> > Case 2
> > criteriachange = #11/1/2008#
> > Case 3
> > criteriachange = #12/1/2008#
> > Case 4
> > criteriachange = #1/1/2009#
> > Case 5
> > criteriachange = #2/1/2009#
> > Case 6
> > criteriachange = #3/1/2009#
> > End Select
> > Case 11
> > Select Case a
> > Case 1
> > criteriachange = #11/1/2008#
> > Case 2
> > criteriachange = #12/1/2008#
> > Case 3
> > criteriachange = #1/1/2009#
> > Case 4
> > criteriachange = #2/1/2009#
> > Case 5
> > criteriachange = #3/1/2009#
> > Case 6
> > criteriachange = #4/1/2009#
> > End Select
> > Case 12
> > Select Case a
> > Case 1
> > criteriachange = #12/1/2008#
> > Case 2
> > criteriachange = #1/1/2009#
> > Case 3
> > criteriachange = #2/1/2009#
> > Case 4
> > criteriachange = #3/1/2009#
> > Case 5
> > criteriachange = #4/1/2009#
> > Case 6
> > criteriachange = #5/1/2009#
> > End Select
> > End Select
> > End Function
> >
> > When I run the query it says that criteria is either typed incorrectly or
> > too complex. Can you tell me a simpler way or correct what is wrong. Thanks.

From: seeker on 19 Mar 2010 10:53

---

thanks guys. it seems to work

"John Spencer" wrote:

> What are you trying to accomplish? I am guessing that it is one of the two
> criteria below. If not, please describe in words what you are trying to
> accomplish.
>
> This criteria will generate the 1st day of the month before begin date and the
> next to the last day of the month before begin date.
>
> Between DateSerial(Year([Begin Date]),Month([Begin Date])-1,1) and
> DateSerial(Year([Begin Date]),Month([Begin Date]),1-2)
>
> This criteria will generate the 1st day of the month of begin date and the
> next to the last day of the month of begin date.
>
> Between DateSerial(Year([Begin Date]),Month([Begin Date]),1) and
> DateSerial(Year([Begin Date]),Month([Begin Date])+1 ,1-2)
>
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
> .
>

 |  Next  |  Last
Pages: 1 2
Prev: Action Query (Append) didn't add records due to key violations.
Next: DSUM Date Criteria