functions in criteria

  • Thread starter Thread starter seeker
  • Start date Start date
S

seeker

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.
 
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 said:
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.
Click to expand...
 
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
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
Glad it seems to work for you. I still would like to know what you were
trying to accomplish.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
thanks guys. it seems to work

John Spencer said:
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
.
Click to expand...
Click to expand...
 
Back
Top