functions in criteria

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.
 
K

KARL DEWEY

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...
 
J

John Spencer

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
 
K

KARL DEWEY

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...
 
J

John Spencer

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...
 

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

Maintaining cell 'variable' values even after the the macro has en 1
number of records per week 2
Dates, Conversions, and Histograms Questions 2
Create query 1
3rd Business Day 4
Column headings in a crosstab query 6
Find Top n values in each Name 6
Running a T/SQL in MS Access 8

Top