Results 1 to 11 of 11
  1. #1
    snipe's Avatar
    snipe is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Sep 2013
    Posts
    287

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


    Hey guys,

    I have a simple function that finds the first date of the current pay period:
    Code:
    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:
    Code:
    debug.print DateFrom(Date)
    I'm still getting the error on that. Any idea what is going on??

  2. #2
    snipe's Avatar
    snipe is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Sep 2013
    Posts
    287
    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.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    snipe's Avatar
    snipe is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Sep 2013
    Posts
    287
    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)

  5. #5
    snipe's Avatar
    snipe is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Sep 2013
    Posts
    287
    Even with a hard coded date (which works in the immediate window) it fails on the sub.


    Click image for larger version. 

Name:	Screen Shot 2016-05-02 at 6.12.16 PM.png 
Views:	9 
Size:	8.3 KB 
ID:	24541
    Click image for larger version. 

Name:	Screen Shot 2016-05-02 at 6.12.34 PM.png 
Views:	9 
Size:	14.2 KB 
ID:	24542

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    snipe's Avatar
    snipe is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Sep 2013
    Posts
    287
    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.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Ah, you saw my message before I did the edit.
    Yes it works!

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

  9. #9
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    snipe's Avatar
    snipe is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Sep 2013
    Posts
    287
    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.

  11. #11
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Type 13 (Type Mismatch) error
    By sdel_nevo in forum Programming
    Replies: 5
    Last Post: 01-22-2016, 10:01 AM
  2. Edit/Update function data type mismatch
    By gaker10 in forum Forms
    Replies: 11
    Last Post: 07-09-2014, 02:41 PM
  3. Replies: 1
    Last Post: 08-28-2013, 06:11 AM
  4. Replies: 1
    Last Post: 07-19-2013, 12:00 PM
  5. Type Mismatch....WHY?!!?
    By jgelpi16 in forum Queries
    Replies: 9
    Last Post: 03-07-2011, 09:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums