Function undefined

[miketheitguy]

Any ideas as to why access is giving me the error that this function is undefined? It's entered as you see below and is compiled without errors.
---------------------BEGIN CODE
Option Explicit
Function Getwkdate()
Dim currentdate As String
currentdate = Getwkdate
Select Case currentdate
Case "Sunday" ' Sunday
Getwkdate = "" & Date - Weekday(Date) + 1
Case "Monday" ' Monday
Getwkdate = "" & Date - Weekday(Date) + 2
Case "Tuesday"
Getwkdate = "" & Date - Weekday(Date) + 3
Case "Wednesday"
Getwkdate = "" & Date - Weekday(Date) + 4
Case "Thurday"
Getwkdate = "" & Date - Weekday(Date) + 5
Case "Friday"
Getwkdate = "" & Date - Weekday(Date) + 6
Case "Saturday"
Getwkdate = "" & Date - Weekday(Date) + 7
End Select
End Function
---------------------------END CODE

A little background. I am attemtping to extrapolate the date based on a day name. I have a form designed to print a set of reports for any day of the week. You select the day from the combo box and it calls a report based on a query that selects data with the day selected in the combo box as it's criteria.

Example:

myday has it's criteria set to form_name!combo_box

wkdaydate: getwkdate([myday])

It looks simple enough, the function compiles with no errors but Access tells me it's undefined.

 

[kjv1611]

Typo: You have:

Function Getwkdate()

and

wkdaydate: getwkdate([myday])
[code]
The [b]g[/b]etwkdate is not capitalized

That is problem # 1

Next, you also are using it in 2 ways, the function as shown for your module does not take a value, but you are trying to give it a value in your example..

Stephen         [infinity]
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[miketheitguy]

The actual query has it capitalized, I generally use the builder and point it to the function through that, less chance of typos. As for the passing a string, I have tried the code
Function Getwkdate(currentdate)

As well as

Function Getwkdate()
Dim currentdate As String

And both methods return the function as being undefined.

 

[SkipVought]

Your problem is in this statement

currentdate = Getwkdate

It should be...

currentdate = Format(Date, "dddd")

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[SkipVought]

I think that what you want can be more easily done using a formula like this

Function Getwkdate() As Date
   Getwkdate = Int(Date / 7) * 7
End Function

You can "adjust" the "start on" day by +/- inc...

Function Getwkdate() As Date
  inc = -1
  Getwkdate = Int((Date + inc) / 7) * 7
End Function

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[miketheitguy]

My new code looks as follows

------------BEGIN CODE
Option Compare Database
Option Explicit
Public Function Getwkdate(ByVal currentdate As String) As String
Dim returndate As String
currentdate = Format(Date, "dddd")
Getwkdate = currentdate
Select Case currentdate
Case "Sunday"
returndate = "" & Date - Weekday(Date) + 1
Case "Monday"
returndate = "" & Date - Weekday(Date) + 2
Case "Tuesday"
returndate = "" & Date - Weekday(Date) + 3
Case "Wednesday"
returndate = "" & Date - Weekday(Date) + 4
Case "Thurday"
returndate = "" & Date - Weekday(Date) + 5
Case "Friday"
returndate = "" & Date - Weekday(Date) + 6
Case "Saturday"
returndate = "" & Date - Weekday(Date) + 7
End Select
Getwkdate = returndate
End Function
------------------END CODE

And is still giving me undefined function error.

 

[SkipVought]

How are you USING the function when you get the error?

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[miketheitguy]

Thanks for the effort Skip, I tried your code as well and still get undefined function error.

 

[SkipVought]

AGAIN...

How are you USING the function when you get the error?

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[miketheitguy]

Here's the SQL from my Query. The tables routesbyday and routesbydriver each contain one half of our route table. Each day the routes change so I merge the two halves based on the day. As you can see I'm using it to represent a value in a query. This example is from the version I tried with your code, I have been using Getwkdate([drivers_days_tbl.day]) AS Myday

SELECT Drivers_Days_TBL.cdrivno, Drivers_Days_TBL.cname, Drivers_Days_TBL.Day, routesbyday!route & routebydriver!route AS route, Getwkdate() AS Myday
FROM (Drivers_Days_TBL LEFT JOIN RoutesByDay ON Drivers_Days_TBL.Day = RoutesByDay.Day) LEFT JOIN RouteByDriver ON Drivers_Days_TBL.cdrivno = RouteByDriver.Driver
WITH OWNERACCESS OPTION;

 

[SkipVought]

I disagree with your last code posting. I used this function in a query with success. However, I question the VALUE that it is returning.

Public Function Getwkdate() As String
         Dim returndate As String, currentdate As String
         currentdate = Format(Date, "dddd")
         Select Case currentdate
         Case "Sunday"
            returndate = "" & Date - Weekday(Date) + 1
         Case "Monday"
            returndate = "" & Date - Weekday(Date) + 2
         Case "Tuesday"
            returndate = "" & Date - Weekday(Date) + 3
         Case "Wednesday"
           returndate = "" & Date - Weekday(Date) + 4
        Case "Thurday"
           returndate = "" & Date - Weekday(Date) + 5
        Case "Friday"
           returndate = "" & Date - Weekday(Date) + 6
        Case "Saturday"
           returndate = "" & Date - Weekday(Date) + 7
       End Select
       Getwkdate = returndate
   End Function

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[miketheitguy]

I'm not positive it will return the correct value, I haven't been able to get that far.

 

[SkipVought]

Based on the CURRENT DATE, what VALUE are you trying to return?

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[miketheitguy]

Currentdate is a bit of a misnomer I suppose. I culled it from some code I found elsewhere and never bothered to change it. What I am looking to get is, if the user selects Monday from the combo_box the query returns Mondays date, same for tues, weds, etc.

 

[miketheitguy]

Got it working, with a tweak or two and DUH, had to add some references in my tools.

Code follows

Option Compare Database
Option Explicit
Public Function Getwkdate(currentdate As String) As Date

Select Case currentdate
Case "Sunday" ' Sunday
Getwkdate = Date - Weekday(Date) + 1
Case "Monday" ' Monday
Getwkdate = Date - Weekday(Date) + 2
Case "Tuesday"
Getwkdate = Date - Weekday(Date) + 3
Case "Wednesday"
Getwkdate = Date - Weekday(Date) + 4
Case "Thursday"
Getwkdate = Date - Weekday(Date) + 5
Case "Friday"
Getwkdate = Date - Weekday(Date) + 6
Case "Saturday"
Getwkdate = Date - Weekday(Date) + 7
End Select
End Function

 

[SkipVought]

Pass your combobox string in the function

Function Getwkdate(dow As String) As String
   Select Case dow
      Case "Sunday"
         Getwkdate = "" & Date - Weekday(Date) + 1
      Case "Monday"
         Getwkdate = "" & Date - Weekday(Date) + 2
      Case "Tuesday"
         Getwkdate = "" & Date - Weekday(Date) + 3
      Case "Wednesday"
         Getwkdate = "" & Date - Weekday(Date) + 4
      Case "Thurday"
         Getwkdate = "" & Date - Weekday(Date) + 5
      Case "Friday"
         Getwkdate = "" & Date - Weekday(Date) + 6
      Case "Saturday"
         Getwkdate = "" & Date - Weekday(Date) + 7
   End Select
End Function

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[SkipVought]

also...

Make sure that your FUNCTION is in a MODULE and not in the Form Code window.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}