Function Help

K

kontra

I would also appreciate any suggestions to make the script better (or more
correct). The Function does return the correct period (tested by changing
system date), but it's giving a data mismatch if I am trying to use it in the
query as Between #SomeDate# and #SomeDate# Thanks in advance:

******************************
Public Function CBDPeriod() ' returns current Business Days Period

Dim rsDays
Dim DayDate
Dim CDay
Dim StartDate
Dim EndDate
Dim PMonth

Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])=Month(Date())")

PMonth = Month(Date) - 1
DayDate = rsDays!Date
CDay = Date

If CDay = DayDate Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date())-1 ORDER BY Date ASC")
End If

StartDate = rsDays!Date
Debug.Print StartDate

If Month(StartDate) = PMonth Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date())-1 ORDER BY Date DESC")
Else:
rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date()) ORDER BY Date DESC")
End If

EndDate = rsDays!Date
CBDPeriod = "Between " & StartDate & " and " & EndDate

Debug.Print PMonth
Debug.Print DayDate
Debug.Print CDay
Debug.Print StartDate
Debug.Print EndDate
Debug.Print CBDPeriod

End Function
***********************
 
S

sparker

Try one of these:

1) CBDPeriod = "Between #" & StartDate & "# and #" & EndDate & "#"

2) CBDPeriod = ">= #" & StartDate & "# and <= #" & EndDate & "#"

Take Care & God Bless ~ SPARKER ~
 
K

kontra

either one doesn't work, returns the same data time mismatch, I checked the
table to make sure , it is a date/time field. Not sure if cause the return
data type is variant?
--
If at first you don''''t succeed, destroy all evidence that you tried.


sparker said:
Try one of these:

1) CBDPeriod = "Between #" & StartDate & "# and #" & EndDate & "#"

2) CBDPeriod = ">= #" & StartDate & "# and <= #" & EndDate & "#"

Take Care & God Bless ~ SPARKER ~


kontra said:
I would also appreciate any suggestions to make the script better (or more
correct). The Function does return the correct period (tested by changing
system date), but it's giving a data mismatch if I am trying to use it in the
query as Between #SomeDate# and #SomeDate# Thanks in advance:

******************************
Public Function CBDPeriod() ' returns current Business Days Period

Dim rsDays
Dim DayDate
Dim CDay
Dim StartDate
Dim EndDate
Dim PMonth

Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])=Month(Date())")

PMonth = Month(Date) - 1
DayDate = rsDays!Date
CDay = Date

If CDay = DayDate Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date())-1 ORDER BY Date ASC")
End If

StartDate = rsDays!Date
Debug.Print StartDate

If Month(StartDate) = PMonth Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date())-1 ORDER BY Date DESC")
Else:
rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date()) ORDER BY Date DESC")
End If

EndDate = rsDays!Date
CBDPeriod = "Between " & StartDate & " and " & EndDate

Debug.Print PMonth
Debug.Print DayDate
Debug.Print CDay
Debug.Print StartDate
Debug.Print EndDate
Debug.Print CBDPeriod

End Function
***********************
Click to expand...
Click to expand...
 
S

sparker

?????
What do you mean the return data type is variant?

Take Care & God Bless ~ SPARKER ~


kontra said:
either one doesn't work, returns the same data time mismatch, I checked the
table to make sure , it is a date/time field. Not sure if cause the return
data type is variant?
--
If at first you don''''t succeed, destroy all evidence that you tried.


sparker said:
Try one of these:

1) CBDPeriod = "Between #" & StartDate & "# and #" & EndDate & "#"

2) CBDPeriod = ">= #" & StartDate & "# and <= #" & EndDate & "#"

Take Care & God Bless ~ SPARKER ~


kontra said:
I would also appreciate any suggestions to make the script better (or more
correct). The Function does return the correct period (tested by changing
system date), but it's giving a data mismatch if I am trying to use it in the
query as Between #SomeDate# and #SomeDate# Thanks in advance:

******************************
Public Function CBDPeriod() ' returns current Business Days Period

Dim rsDays
Dim DayDate
Dim CDay
Dim StartDate
Dim EndDate
Dim PMonth

Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])=Month(Date())")

PMonth = Month(Date) - 1
DayDate = rsDays!Date
CDay = Date

If CDay = DayDate Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date())-1 ORDER BY Date ASC")
End If

StartDate = rsDays!Date
Debug.Print StartDate

If Month(StartDate) = PMonth Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date())-1 ORDER BY Date DESC")
Else:
rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date()) ORDER BY Date DESC")
End If

EndDate = rsDays!Date
CBDPeriod = "Between " & StartDate & " and " & EndDate

Debug.Print PMonth
Debug.Print DayDate
Debug.Print CDay
Debug.Print StartDate
Debug.Print EndDate
Debug.Print CBDPeriod

End Function
***********************
Click to expand...
Click to expand...
Click to expand...
 
K

kontra

I thought that function returns a value, value has to be some sort of a data
type, and if no data type is assigned by the function then the return would
be variant
Example:
dim test1
Watch : : test1 : <Out of context> : Variant/Empty : Module1.test

Am I wrong?

Thanks
--
If at first you don''''t succeed, destroy all evidence that you tried.


sparker said:
?????
What do you mean the return data type is variant?

Take Care & God Bless ~ SPARKER ~


kontra said:
either one doesn't work, returns the same data time mismatch, I checked the
table to make sure , it is a date/time field. Not sure if cause the return
data type is variant?
--
If at first you don''''t succeed, destroy all evidence that you tried.


sparker said:
Try one of these:

1) CBDPeriod = "Between #" & StartDate & "# and #" & EndDate & "#"

2) CBDPeriod = ">= #" & StartDate & "# and <= #" & EndDate & "#"

Take Care & God Bless ~ SPARKER ~


:

I would also appreciate any suggestions to make the script better (or more
correct). The Function does return the correct period (tested by changing
system date), but it's giving a data mismatch if I am trying to use it in the
query as Between #SomeDate# and #SomeDate# Thanks in advance:

******************************
Public Function CBDPeriod() ' returns current Business Days Period

Dim rsDays
Dim DayDate
Dim CDay
Dim StartDate
Dim EndDate
Dim PMonth

Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])=Month(Date())")

PMonth = Month(Date) - 1
DayDate = rsDays!Date
CDay = Date

If CDay = DayDate Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date())-1 ORDER BY Date ASC")
End If

StartDate = rsDays!Date
Debug.Print StartDate

If Month(StartDate) = PMonth Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date())-1 ORDER BY Date DESC")
Else:
rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date()) ORDER BY Date DESC")
End If

EndDate = rsDays!Date
CBDPeriod = "Between " & StartDate & " and " & EndDate

Debug.Print PMonth
Debug.Print DayDate
Debug.Print CDay
Debug.Print StartDate
Debug.Print EndDate
Debug.Print CBDPeriod

End Function
***********************
Click to expand...
Click to expand...
Click to expand...
 
S

SteveS

Kontra,

Here is my 2 cents worth.. :)

If the query has an error when you use the function, please post the SQL of
the query so we can see what you are working with.


~ "Date" is a reserved word in Access and shouldn't be used as a field (or
any other object) name. search help for "reserved words". Also, what does
"Date" represent? Hire date? Birth date? If you *must* use Date as an field
name, enclose it in brackets ( [ ] ).

~ Set the function return type:

Public Function CBDPeriod() as String


~ Set a datatype when you Dim variables.

These are my guesses as to the data types:

Dim rsDays as DAO.Recordset
Dim DayDate as Date
Dim CDay as Date
Dim StartDate As Date
Dim EndDate As Date
Dim PMonth as Integer


~ In a *standard module*, paste the following sub:

'*****beg code **********
Sub try()
Dim str As String

' this should be one line
str = "SELECT TOP 1 [Date] FROM tbl_BD_2006 WHERE
Month([Date])=Month(Date())")"
debug.Print str

End Sub
"*****end code *********


Run the sub by pressing the F5 key. Notice there is no date in the string.


Now change the line to:

str = "SELECT TOP 1 [Date] FROM tbl_BD_2006 WHERE Month([Date])=
Month(" & Date & )"

and run it again. You should see today's date. To get a variable (or
parameter) into a string, you must concatenate (using "&") it with the text
parts.


~ The colon after the "Else" is not needed.

~ Clean up before the function (or sub) ends. Since you opened a recordset,
you should close it and destroy it:

rsDays.close
Set rsDays = nothing

Here is your Function after I edited it:
(untested!!!)
'------------------------------------------

Public Function CBDPeriod() As String
'***************************
' returns current Business Days Period
'***************************

' declare variables
Dim rsDays As DAO.Recordset
Dim DayDate As Date
Dim CDay As Date
Dim StartDate As Date
Dim EndDate As Date
Dim PMonth As Integer

'set default function return value
CBDPeriod = ""


Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 [Date] FROM
tbl_BD_2006 WHERE Month([Date])=Month(" & Date & ")")

'should check to see if there are records
' If rsDays.bof and rsDays.eof then
' Msgbox "No Records"
' Exit Function
' End If

PMonth = Month([Date]) - 1
DayDate = rsDays!Date
CDay = Date

If CDay = DayDate Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 [Date] FROM
tbl_BD_2006 WHERE Month([Date])= Month(" & Date & ")-1 ORDER BY Date ASC")
End If

'should check to see if there are records
' If rsDays.bof and rsDays.eof then
' Msgbox "No Records"
' Exit Function
' End If

StartDate = rsDays!Date
Debug.Print StartDate

If Month(StartDate) = PMonth Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 [Date] FROM
tbl_BD_2006 WHERE Month([Date])= Month(" & Date & ")-1 ORDER BY Date DESC")
Else
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 [Date] FROM
tbl_BD_2006 WHERE Month([Date])= Month(" & Date & ") ORDER BY Date DESC")
End If

'should check to see if there are records
' If rsDays.bof and rsDays.eof then
' Msgbox "No Records"

' maybe do this:
' CBDPeriod = ">= " & StartDate

' Exit Function
' End If

EndDate = rsDays!Date
CBDPeriod = "Between #" & StartDate & "# and #" & EndDate & "#"


' ****debugging****
Debug.Print PMonth
Debug.Print DayDate
Debug.Print CDay
Debug.Print StartDate
Debug.Print EndDate
Debug.Print CBDPeriod
'****debugging ****

rsDays.Close
Set rsDays = Nothing

End Function
'***********************

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


kontra said:
I would also appreciate any suggestions to make the script better (or more
correct). The Function does return the correct period (tested by changing
system date), but it's giving a data mismatch if I am trying to use it in the
query as Between #SomeDate# and #SomeDate# Thanks in advance:

******************************
Public Function CBDPeriod() ' returns current Business Days Period

Dim rsDays
Dim DayDate
Dim CDay
Dim StartDate
Dim EndDate
Dim PMonth

Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])=Month(Date())")

PMonth = Month(Date) - 1
DayDate = rsDays!Date
CDay = Date

If CDay = DayDate Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date())-1 ORDER BY Date ASC")
End If

StartDate = rsDays!Date
Debug.Print StartDate

If Month(StartDate) = PMonth Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date())-1 ORDER BY Date DESC")
Else:
rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date()) ORDER BY Date DESC")
End If

EndDate = rsDays!Date
CBDPeriod = "Between " & StartDate & " and " & EndDate

Debug.Print PMonth
Debug.Print DayDate
Debug.Print CDay
Debug.Print StartDate
Debug.Print EndDate
Debug.Print CBDPeriod

End Function
***********************
Click to expand...
 
S

sparker

Yup I am going to have to agree with Steve here we need to see something more
on what you are working with. From what you have posted I would think you are
simply working with a date and the first two examples I provided should have
done the trick. Please post something more so we can get a better idea as to
what it is you are trying to do. Take Care & God Bless ~ SPARKER ~


SteveS said:
Kontra,

Here is my 2 cents worth.. :)

If the query has an error when you use the function, please post the SQL of
the query so we can see what you are working with.


~ "Date" is a reserved word in Access and shouldn't be used as a field (or
any other object) name. search help for "reserved words". Also, what does
"Date" represent? Hire date? Birth date? If you *must* use Date as an field
name, enclose it in brackets ( [ ] ).

~ Set the function return type:

Public Function CBDPeriod() as String


~ Set a datatype when you Dim variables.

These are my guesses as to the data types:

Dim rsDays as DAO.Recordset
Dim DayDate as Date
Dim CDay as Date
Dim StartDate As Date
Dim EndDate As Date
Dim PMonth as Integer


~ In a *standard module*, paste the following sub:

'*****beg code **********
Sub try()
Dim str As String

' this should be one line
str = "SELECT TOP 1 [Date] FROM tbl_BD_2006 WHERE
Month([Date])=Month(Date())")"
debug.Print str

End Sub
"*****end code *********


Run the sub by pressing the F5 key. Notice there is no date in the string.


Now change the line to:

str = "SELECT TOP 1 [Date] FROM tbl_BD_2006 WHERE Month([Date])=
Month(" & Date & )"

and run it again. You should see today's date. To get a variable (or
parameter) into a string, you must concatenate (using "&") it with the text
parts.


~ The colon after the "Else" is not needed.

~ Clean up before the function (or sub) ends. Since you opened a recordset,
you should close it and destroy it:

rsDays.close
Set rsDays = nothing

Here is your Function after I edited it:
(untested!!!)
'------------------------------------------

Public Function CBDPeriod() As String
'***************************
' returns current Business Days Period
'***************************

' declare variables
Dim rsDays As DAO.Recordset
Dim DayDate As Date
Dim CDay As Date
Dim StartDate As Date
Dim EndDate As Date
Dim PMonth As Integer

'set default function return value
CBDPeriod = ""


Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 [Date] FROM
tbl_BD_2006 WHERE Month([Date])=Month(" & Date & ")")

'should check to see if there are records
' If rsDays.bof and rsDays.eof then
' Msgbox "No Records"
' Exit Function
' End If

PMonth = Month([Date]) - 1
DayDate = rsDays!Date
CDay = Date

If CDay = DayDate Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 [Date] FROM
tbl_BD_2006 WHERE Month([Date])= Month(" & Date & ")-1 ORDER BY Date ASC")
End If

'should check to see if there are records
' If rsDays.bof and rsDays.eof then
' Msgbox "No Records"
' Exit Function
' End If

StartDate = rsDays!Date
Debug.Print StartDate

If Month(StartDate) = PMonth Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 [Date] FROM
tbl_BD_2006 WHERE Month([Date])= Month(" & Date & ")-1 ORDER BY Date DESC")
Else
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 [Date] FROM
tbl_BD_2006 WHERE Month([Date])= Month(" & Date & ") ORDER BY Date DESC")
End If

'should check to see if there are records
' If rsDays.bof and rsDays.eof then
' Msgbox "No Records"

' maybe do this:
' CBDPeriod = ">= " & StartDate

' Exit Function
' End If

EndDate = rsDays!Date
CBDPeriod = "Between #" & StartDate & "# and #" & EndDate & "#"


' ****debugging****
Debug.Print PMonth
Debug.Print DayDate
Debug.Print CDay
Debug.Print StartDate
Debug.Print EndDate
Debug.Print CBDPeriod
'****debugging ****

rsDays.Close
Set rsDays = Nothing

End Function
'***********************

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


kontra said:
I would also appreciate any suggestions to make the script better (or more
correct). The Function does return the correct period (tested by changing
system date), but it's giving a data mismatch if I am trying to use it in the
query as Between #SomeDate# and #SomeDate# Thanks in advance:

******************************
Public Function CBDPeriod() ' returns current Business Days Period

Dim rsDays
Dim DayDate
Dim CDay
Dim StartDate
Dim EndDate
Dim PMonth

Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])=Month(Date())")

PMonth = Month(Date) - 1
DayDate = rsDays!Date
CDay = Date

If CDay = DayDate Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date())-1 ORDER BY Date ASC")
End If

StartDate = rsDays!Date
Debug.Print StartDate

If Month(StartDate) = PMonth Then
Set rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date())-1 ORDER BY Date DESC")
Else:
rsDays = CurrentDb.OpenRecordset("SELECT TOP 1 Date FROM tbl_BD_2006
WHERE Month([Date])= Month(date()) ORDER BY Date DESC")
End If

EndDate = rsDays!Date
CBDPeriod = "Between " & StartDate & " and " & EndDate

Debug.Print PMonth
Debug.Print DayDate
Debug.Print CDay
Debug.Print StartDate
Debug.Print EndDate
Debug.Print CBDPeriod

End Function
***********************
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

Problems with MS Access reading dates as US, not British formats. 2
Holiday day tracking 2
Access 2003 using Excel NetWorkdays function 2
DateDiff Working Days only not including holidays code problem 7
DateDiff Working Days only not including holidays code problem 1
Custom Date Partition Function 1
Query takes to long 1
Holiday - Error 4

Top