Type Mismatch in Sub, but not when using the function alone

[snipe]

Hey guys,



I have a simple function that finds the first date of the current pay period:

Public Function DateFrom(Today As Date) As Date

'if Today is the first day of the pay period, then pull the entire last period
If DatePart("ww", Today, vbMonday, vbFirstFullWeek) Mod 2 = 0 And _
    DatePart("w", Today, vbMonday, vbFirstFullWeek) = 1 Then
    DateFrom = Today - 14


Else


' determine if current day is in 1st or 2nd week of pay period
' 1st weeks are odd, 2nd are even
Dim DayMod As Integer
If DatePart("ww", Today, vbMonday, vbFirstFullWeek) Mod 2 = 1 Then
    DayMod = 7
    Else
    DayMod = 0
End If


'determine how many days back you need to go to get to monday
'of the first week in the pay period
DayMod = DayMod + (DatePart("w", Today, vbMonday, vbFirstFullWeek) - 1)


DateFrom = DateAdd("d", -1 * DayMod, Today)


End If


End Function

This code works just fine when set as the default date for a textbook on a form as well as just running it on the immediate window. I have a sub that does quite a bit, but at the very beginning, i was getting a Type Mismatch error. For testing, I added a line right after the Dims:

debug.print DateFrom(Date)

I'm still getting the error on that. Any idea what is going on??

[snipe]

Ok I just tried to simplify things. In the immediate window: ? DateFrom(date) returns the correct value. I created a button that just does: debug.print DateFrom(date) and I get a type mismatch. Definitely stumped now.

[Micron]

Which line generates the error?
Maybe your use of a reserved word (Today) as a function parameter is not compatible with what's being passed if you're running this from a form but works in the vb editor. I would not use Today, but rather a variable in the function parameter that you can assign to a variable where you're making a call to the function.

[snipe]

Yeah I already fixed the Today part. It's not ThisDay. Didn't fix the problem though.

The function DateFrom doesn't throw an error. I'm getting the error on a sub, with the line being debug.print DateFrom(Date)

[snipe]

Even with a hard coded date (which works in the immediate window) it fails on the sub.

[orange]

Just tried this in the immediate window


?Date
02/05/2016
?DateFrom(Date)
18/04/2016

[snipe]

Why do you say that? The function seems to have worked perfectly for you as well. April 18 is the beginning of the previous pay period.

[orange]

Ah, you saw my message before I did the edit.
Yes it works!

Hard coded
?DateFrom(#14/5/2016#)
02/05/2016

[Micron]

I have copied your code and replicated your button click code and do not get the error, even with the "Today as Date" in it.

Private Sub Command0_Click()
Debug.Print DateFrom(#5/2/2016#)
End Sub

OUTPUT: 4/18/2016

If there's no chance you've posted an earlier version of the function (other than having just changed Today as Date), and you have not declared a user defined data type that could be involved) I would create a new db with a new module and copy/paste the code into it (not import the module). Then I'd create a new form with only a button to call the function and see what happens. You might be looking at corruption in the current db, but if you get the error in the new db, the only thing I can think of is a regional settings issue. That's grasping at straws, though.

[snipe]

Guess we are going with data corruption. Works great after doing it in a fresh db. Compact/repair and compile didn't fix it either. So many hours about to be wasted copying things over.

Appreciate the help, guys.

[Micron]

You might find that simply opening a new db and importing all objects (watch out for options re: menu bars & stuff) in one operation will take care of it. The corruption is probably in a system table, which you would not import. Should only take a minute or two to build and complete the import.