Function Help

[turtle]

Background:
I have a table of fields each field representing a month (Jan, Feb,
Mar...). These are not a date field but more of a bucket to hold
labor actuals. Along with the months their is another field for
year. Looking at the table you can see which month and which year the
hours belong to but since the month buckets are not actual dates it is
hard to write certain criteria's that represent dates. I have linked
to a suppliers database and that is why it is this way (not something
i created).

Problem:
What i need is to write a parameter function that would allow me to
see only the dates that are greater than a parameter value.

My not so good VB skills solution (need help finishing):
I thought that i could do a select case that would look something like
this. I would have a combo box for month (cboMonth) and combo box for
year (cboYear).

Case1: Jan+Feb+Mar+Apr...
Case2: Feb+Mar+Apr+May...
Case3: Mar+Apr+May+Jun...
Case4: Apr+May+Jun+Jul...

If CboMonth = "Jan" then
Case1 where [Year] >= cboYear
If CboMonth = "Feb" then
Case2 where year = cboYear + Case1 where [year] cboYear
If CboMonth = "Mar" then
Case3 where year =cboYear + Case1 where [year] cboYear
....

First question - how close am i on the code? Am i on the right
track?
Second Question - If i save this as a function and call it in a query
how does it work to get the parameters that are on my form? Really i
need someone to fill the gaps of how to do this. i really suck at the
VB side of Access.

Any help would be awesome.

KO

[Technolust]

On Dec 12, 10:32 am, turtle <[e-mail]:

Background:
I have a table of fields each field representing a month (Jan, Feb,
Mar...). These are not a date field but more of a bucket to hold
labor actuals. Along with the months their is another field for
year. Looking at the table you can see which month and which year the
hours belong to but since the month buckets are not actual dates it is
hard to write certain criteria's that represent dates. I have linked
to a suppliers database and that is why it is this way (not something
i created).

Problem:
What i need is to write a parameter function that would allow me to
see only the dates that are greater than a parameter value.

My not so good VB skills solution (need help finishing):
I thought that i could do a select case that would look something like
this. I would have a combo box for month (cboMonth) and combo box for
year (cboYear).

Case1: Jan+Feb+Mar+Apr...
Case2: Feb+Mar+Apr+May...
Case3: Mar+Apr+May+Jun...
Case4: Apr+May+Jun+Jul...

If CboMonth = "Jan" then
Case1 where [Year] >= cboYear
If CboMonth = "Feb" then
Case2 where year = cboYear + Case1 where [year] cboYear
If CboMonth = "Mar" then
Case3 where year =cboYear + Case1 where [year] cboYear
...

First question - how close am i on the code? Am i on the right
track?
Second Question - If i save this as a function and call it in a query
how does it work to get the parameters that are on my form? Really i
need someone to fill the gaps of how to do this. i really suck at the
VB side of Access.

Any help would be awesome.

KO

Maybe u need something like this?...

Public Function GetStartDate() As Date
Dim iMonth%, iYr%
Dim dtStartDate As Date

On Error GoTo Err_GetStartDate

iYr = CInt(cboYear.Value)
iMonth = GetMonthNumFromSelection()

' Add 1 to month to determine the
' last day of the selected month later on...
iMonth = iMonth + 1
dtStartDate = CDate((CStr(iMonth) & "/01/" & CStr(iYr)))

' Now, subtract 1 day to get the
' last day of the month...
dtStartDate = DateAdd("d", -1, dtStartDate)

' return results
GetStartDate = dtStartDate
Exit_GetStartDate:
Exit Function

Err_GetStartDate:
Dim iResp%

iResp = MsgBox("Error #" & Err.Number & vbCrLf & _
Err.Description, vbRetryCancel + vbExclamation)
If iResp = vbRetry Then Resume
Resume Exit_GetStartDate
End Function

Public Function GetMonthNumFromSelection() As Integer
Select Case cboMonth.Value
Case "Jan"
GetMonthNumFromSelection = 1
Case "Feb"
GetMonthNumFromSelection = 2
Case "Mar"
GetMonthNumFromSelection = 3
Case "Apr"
GetMonthNumFromSelection = 4
Case "May"
GetMonthNumFromSelection = 5
Case "Jun"
GetMonthNumFromSelection = 6
Case "Jul"
GetMonthNumFromSelection = 7
Case "Aug"
GetMonthNumFromSelection = 8
Case "Sep"
GetMonthNumFromSelection = 9
Case "Oct"
GetMonthNumFromSelection = 10
Case "Nov"
GetMonthNumFromSelection = 11
Case "Dec"
GetMonthNumFromSelection = 12
Case Else
GetMonthNumFromSelection = 0
End Select
End Function


Use GetStartDate() in your criteria - (Ex: dtRcvd_Date >
GetStartDate())

[Salad]

turtle wrote:

Background:
I have a table of fields each field representing a month (Jan, Feb,
Mar...). These are not a date field but more of a bucket to hold
labor actuals. Along with the months their is another field for
year. Looking at the table you can see which month and which year the
hours belong to but since the month buckets are not actual dates it is
hard to write certain criteria's that represent dates. I have linked
to a suppliers database and that is why it is this way (not something
i created).

Problem:
What i need is to write a parameter function that would allow me to
see only the dates that are greater than a parameter value.

My not so good VB skills solution (need help finishing):
I thought that i could do a select case that would look something like
this. I would have a combo box for month (cboMonth) and combo box for
year (cboYear).

Case1: Jan+Feb+Mar+Apr...
Case2: Feb+Mar+Apr+May...
Case3: Mar+Apr+May+Jun...
Case4: Apr+May+Jun+Jul...

If CboMonth = "Jan" then
Case1 where [Year] >= cboYear
If CboMonth = "Feb" then
Case2 where year = cboYear + Case1 where [year] cboYear
If CboMonth = "Mar" then
Case3 where year =cboYear + Case1 where [year] cboYear
...

First question - how close am i on the code? Am i on the right
track?
Second Question - If i save this as a function and call it in a query
how does it work to get the parameters that are on my form? Really i
need someone to fill the gaps of how to do this. i really suck at the
VB side of Access.

Any help would be awesome.

KO

I'm not sure you really need code...if you are using a form to store
your form's month and year.

I created a form that has 2 combos; C1 and C2. C1 for year, C2 for
Month. In my table is a Date field called DateFld. I demonstrate how
you can use a specific value or use values from the form.

SELECT ID, DateFld, DateFld
FROM Table1
WHERE (((DateFld)>DateSerial(2007,1,1)) AND
((DateFld)>DateSerial(2007,[Forms]![Form1]![t1],[Forms]![Form1]![t2])));

Bomb
http://www.youtube.com/watch?v=nETVIeygqjY

[turtle]

On Dec 12, 3:18 pm, Technolust <[e-mail]:

On Dec 12, 10:32 am, turtle <[e-mail]:

Background:
I have a table of fields each field representing a month (Jan, Feb,
Mar...). These are not a date field but more of a bucket to hold
labor actuals. Along with the months their is another field for
year. Looking at the table you can see which month and which year the
hours belong to but since the month buckets are not actual dates it is
hard to write certain criteria's that represent dates. I have linked
to a suppliers database and that is why it is this way (not something
i created).

Problem:
What i need is to write a parameter function that would allow me to
see only the dates that are greater than a parameter value.

My not so good VB skills solution (need help finishing):
I thought that i could do a select case that would look something like
this. I would have a combo box for month (cboMonth) and combo box for
year (cboYear).

Case1: Jan+Feb+Mar+Apr...
Case2: Feb+Mar+Apr+May...
Case3: Mar+Apr+May+Jun...
Case4: Apr+May+Jun+Jul...

If CboMonth = "Jan" then
Case1 where [Year] >= cboYear
If CboMonth = "Feb" then
Case2 where year = cboYear + Case1 where [year] cboYear
If CboMonth = "Mar" then
Case3 where year =cboYear + Case1 where [year] cboYear
...

First question - how close am i on the code? Am i on the right
track?
Second Question - If i save this as a function and call it in a query
how does it work to get the parameters that are on my form? Really i
need someone to fill the gaps of how to do this. i really suck at the
VB side of Access.

Any help would be awesome.

KO

Maybe u need something like this?...

Public Function GetStartDate() As Date
Dim iMonth%, iYr%
Dim dtStartDate As Date

On Error GoTo Err_GetStartDate

iYr = CInt(cboYear.Value)
iMonth = GetMonthNumFromSelection()

' Add 1 to month to determine the
' last day of the selected month later on...
iMonth = iMonth + 1
dtStartDate = CDate((CStr(iMonth) & "/01/" & CStr(iYr)))

' Now, subtract 1 day to get the
' last day of the month...
dtStartDate = DateAdd("d", -1, dtStartDate)

' return results
GetStartDate = dtStartDate
Exit_GetStartDate:
Exit Function

Err_GetStartDate:
Dim iResp%

iResp = MsgBox("Error #" & Err.Number & vbCrLf & _
Err.Description, vbRetryCancel + vbExclamation)
If iResp = vbRetry Then Resume
Resume Exit_GetStartDate
End Function

Public Function GetMonthNumFromSelection() As Integer
Select Case cboMonth.Value
Case "Jan"
GetMonthNumFromSelection = 1
Case "Feb"
GetMonthNumFromSelection = 2
Case "Mar"
GetMonthNumFromSelection = 3
Case "Apr"
GetMonthNumFromSelection = 4
Case "May"
GetMonthNumFromSelection = 5
Case "Jun"
GetMonthNumFromSelection = 6
Case "Jul"
GetMonthNumFromSelection = 7
Case "Aug"
GetMonthNumFromSelection = 8
Case "Sep"
GetMonthNumFromSelection = 9
Case "Oct"
GetMonthNumFromSelection = 10
Case "Nov"
GetMonthNumFromSelection = 11
Case "Dec"
GetMonthNumFromSelection = 12
Case Else
GetMonthNumFromSelection = 0
End Select
End Function

Use GetStartDate() in your criteria - (Ex: dtRcvd_Date >
GetStartDate())- Hide quoted text -

- Show quoted text -

I don't have any date fields just a year field. In what field to i
put the criteria? Sorry for my lack of understanding.
thanks,
KO

[Arch]

On Wed, 12 Dec 2007 10:32:04 -0800 (PST), turtle
<ko****@vistacontrols.comwrote:

>Background:
I have a table of fields each field representing a month (Jan, Feb,
Mar...). These are not a date field but more of a bucket to hold
labor actuals. Along with the months their is another field for
year. Looking at the table you can see which month and which year the
hours belong to but since the month buckets are not actual dates it is
hard to write certain criteria's that represent dates. I have linked
to a suppliers database and that is why it is this way (not something
i created).

Problem:
What i need is to write a parameter function that would allow me to
see only the dates that are greater than a parameter value.

My not so good VB skills solution (need help finishing):
I thought that i could do a select case that would look something like
this. I would have a combo box for month (cboMonth) and combo box for
year (cboYear).

Case1: Jan+Feb+Mar+Apr...
Case2: Feb+Mar+Apr+May...
Case3: Mar+Apr+May+Jun...
Case4: Apr+May+Jun+Jul...

If CboMonth = "Jan" then
Case1 where [Year] >= cboYear
If CboMonth = "Feb" then
Case2 where year = cboYear + Case1 where [year] cboYear
If CboMonth = "Mar" then
Case3 where year =cboYear + Case1 where [year] cboYear
...

First question - how close am i on the code? Am i on the right
track?
Second Question - If i save this as a function and call it in a query
how does it work to get the parameters that are on my form? Really i
need someone to fill the gaps of how to do this. i really suck at the
VB side of Access.

Any help would be awesome.

KO

One possible approach might be to create a "normalizing" query. This
is a bit convoluted, but I tried it and it seems to work.

select Jan as M, cvdate("1/1/" & [fldYear]) as D from t
union all
select Feb, cvdate("2/1/" & [fldYear]) from t
union all
select Mar, cvdate("3/1/" & [fldYear]) from t
.... etc

All of your valiues end up in M with the date in D. You can then
easily query from that, using D in the WHERE clause.

Hope this might be of some help.

[turtle]

On Dec 13, 11:49 am, Arch <[e-mail]:

On Wed, 12 Dec 2007 10:32:04 -0800 (PST), turtle

<[e-mail]:

Background:
I have a table of fields each field representing a month (Jan, Feb,
Mar...). These are not a date field but more of a bucket to hold
labor actuals. Along with the months their is another field for
year. Looking at the table you can see which month and which year the
hours belong to but since the month buckets are not actual dates it is
hard to write certain criteria's that represent dates. I have linked
to a suppliers database and that is why it is this way (not something
i created).

Problem:
What i need is to write a parameter function that would allow me to
see only the dates that are greater than a parameter value.

My not so good VB skills solution (need help finishing):
I thought that i could do a select case that would look something like
this. I would have a combo box for month (cboMonth) and combo box for
year (cboYear).

Case1: Jan+Feb+Mar+Apr...
Case2: Feb+Mar+Apr+May...
Case3: Mar+Apr+May+Jun...
Case4: Apr+May+Jun+Jul...

If CboMonth = "Jan" then
Case1 where [Year] >= cboYear
If CboMonth = "Feb" then
Case2 where year = cboYear + Case1 where [year] cboYear
If CboMonth = "Mar" then
Case3 where year =cboYear + Case1 where [year] cboYear
...

First question - how close am i on the code? Am i on the right
track?
Second Question - If i save this as a function and call it in a query
how does it work to get the parameters that are on my form? Really i
need someone to fill the gaps of how to do this. i really suck at the
VB side of Access.

Any help would be awesome.

KO

One possible approach might be to create a "normalizing" query. This
is a bit convoluted, but I tried it and it seems to work.

select Jan as M, cvdate("1/1/" & [fldYear]) as D from t
union all
select Feb, cvdate("2/1/" & [fldYear]) from t
union all
select Mar, cvdate("3/1/" & [fldYear]) from t
... etc

All of your valiues end up in M with the date in D. You can then
easily query from that, using D in the WHERE clause.

Hope this might be of some help.- Hide quoted text -

- Show quoted text -

I think that might work but if i have other fields how do i include
them?
Do i just add a Select statement at the beginning or the end? When i
try it i either get a number of columns different from the two tables
or a from clause error.

thanks,
KO