Solved

type mismatch error 13

Posted on 2004-04-09
20
754 Views
Last Modified: 2008-02-01
I am tring to call a function within my module that gets the date from invdate field in a table. I keep getting type mismatch error 13 I have checked my references and nothing is missing or tagged as missing. In addition, when I put a breakpoint on the function it show the expected results.

I have included the function and the part of the module that calls the function

  Loop While (strAcct = ![AccessNumber])
        aSheet.Cells(i, 1) = "SPL"
        aSheet.Cells(i, 3) = "INVOICE"
        aSheet.Cells(i, 4) = getdate(yourdate)
        yourdate = rst!date
        newdate = getdate(yourdate)
        aSheet.Cells(i, 5) = "Sales Tax Payable"
        aSheet.Cells(i, 6) = "State Comptroller"
        i = i + 1
        aSheet.Cells(i, 1) = "ENDTRANS"

Public Function getdate(ByVal mydate As Date) As Date

mydate = ([date])

End Function
0
Comment
Question by:cskehan
  • 7
  • 7
  • 4
  • +2
20 Comments
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi cskehan


Try this, without the funtion call.
newdate = Format(Date(),"dd-mmm-yyyy")



Alan
0
 

Author Comment

by:cskehan
Comment Utility
alan,

I am tring to get the date from the invdate field.

Connie
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi Connie,

Do you have a field on the form called Date?
And your Function is a Form level function?

If so thry this:

Public Function getdate(ByVal mydate As Date) As Date

  mydate = Me.Controls("Date")

End Function

Alan
0
 

Author Comment

by:cskehan
Comment Utility
Alan

Field is called "invdate" in a table called "tblsubinvoice"

Connie
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi Connie,


If you have an Primary key field in tblsubinvoice you can do a dlookup with a where condition

  Loop While (strAcct = ![AccessNumber])
        aSheet.Cells(i, 1) = "SPL"
        aSheet.Cells(i, 3) = "INVOICE"
        aSheet.Cells(i, 4) = getdate(yourdate)
        yourdate = rst!date
        newdate = getdate(yourdate, Me.YourIDField)
        aSheet.Cells(i, 5) = "Sales Tax Payable"
        aSheet.Cells(i, 6) = "State Comptroller"
        i = i + 1
        aSheet.Cells(i, 1) = "ENDTRANS"



Public Function getdate(ByVal mydate As Date, lID as long) As Date
  dim sWhere as string
  sWhere  = "YourIDField = " & lID
  mydate = Dlookup("invdate", "tblsubinvoice", sWhere)

End Function


hth

Alan
0
 

Author Comment

by:cskehan
Comment Utility
Alan

table does not have a primary key. I gave it one and tried your code and got an error at line "aSheet.Cells(i, 4) = getdate(yourdate)" compile error argument not optional.

Connie
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi Connie,

So long as the ne PK field is in the forms recordset, this should work
I actually did modify that line in the last post I posted to include the new argument

newdate = getdate(yourdate, Me.YourIDField)
-----------------------------------------^-- name of your new PK field
Alan
0
 

Author Comment

by:cskehan
Comment Utility
Alan

Still doesn't work I get invalid use of me

Connie

0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility

Hi Connie,

Must be that the main code is not in a Form module, but rather a general module, try explicitly referencing the form that contains the ID field, of move your main code to the Form

newdate = getdate(yourdate, Forms!FormName.YourIDField)

Alan
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
'You are passing yourdate but it will not have anything set because the function is defined as ByVal and not ByRef - you are passing by value not by reference. If by reference then it will hold the value you set it to ie. Date
        newdate = getdate(yourdate)

Public Function getdate(ByVal mydate As Date) As Date

mydate = ([date])

End Function



'Just out of interest, would this line below be okay considering Date is a reserved word, dont know
        yourdate = rst!date


Can I ask something, what is the variable i initialised to
is it 0 or 1 or something else

a common mistake is initialised to zero,
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:cskehan
Comment Utility
Alan

I saw the yourdate=rst!date and change it to yourdate=!invdate

i = i + 1 since the information is going to an excel spread sheet.

 and dim I as interger
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
regarding my last commet about byRef

I said that because you are setting the value of the field you are passing in


but in VB, for a function to return a value, you have to set the function name

Public Function getdate(ByVal mydate As Date) As Date
    mydate = ([date])
End Function

whatever value you want to return you have to do
    getdate = ...


in your code where you do
  ... = getdate(yourdate)

you are setting it to nothing because getdate returns nothing


0
 

Expert Comment

by:wasimhc
Comment Utility
You could change the function as follows:

Public Function getdate(ByVal mydate As Date) As Date
   getdate=mydate
End Function
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
this Function:

Public Function getdate(ByVal mydate As Date) As Date

mydate = ([date])

End Function


is coded incorrectly.

What are you trying to do here?  Explain what this function is supposed to do, in YOUR OWN WORDS, not by simply stating what the code says.

If MyDate (the argument) is a Date TYpe, what value are you trying to return?  

AW
0
 

Author Comment

by:cskehan
Comment Utility
I am using access 2000 to create a excel worksheet. The aSheet.Cells(i, 4) = getdate(yourdate) line of the sub Create_worksheet ( ) calls on the function getdate( ) (While in loop) which is to get the value, which is a date, in the field invdate, of the table tblsubinvoice

 I have dim yourvalue     -  which when I put a breakpoint at it shows 04/01/04  
          yourdate= rst!invoicedate   - which when I put a breakpoint at it shows 04/01/04
          aSheet.Cells(i, 4) = getdate(yourdate) -   - which when I put a breakpoint at it shows 04/01/04
          yourdate = rst!invdate -   - which when I put a breakpoint at it shows 04/01/04
         newdate = getdate(yourdate) -   - which when I put a breakpoint at it shows 04/01/04

This is the function


Public Function getdate(ByVal yourdate As String) As String
   yourdate = ([invdate])  which when I put a breakpoint at it shows 04/01/04
End Function

I get the type mismatch error 13 at the yourdate=([invdate]) line of the function

the function argument is yourdate=([whatever is in the invdate field]) which is a date all records have a invdate and a date is in all the records.

Does this clarify what I am needing?
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
You set yourdate to the invoice date then you pass it into getdate
         yourdate= rst!invoicedate   - which when I put a breakpoint at it shows 04/01/04
          aSheet.Cells(i, 4) = getdate(yourdate) -   - which when I put a breakpoint at it shows 04/01/04

The reason by this function shows your date is because it is the value you passed in
Public Function getdate(ByVal yourdate As String) As String
   yourdate = ([invdate])  which when I put a breakpoint at it shows 04/01/04
End Function

but what is invdate supposed to be

you see, when you use variables, it uses the locally defined one first before using global ones
invdate is referring to a variable, which you do not have defined
therefore you are applying nothing to yourdate

plus the function returns a date right?

          aSheet.Cells(i, 4) = getdate(yourdate)

aSheet.Cells(i,4) is being set to the return value of getdate
but you are returning nothing

explain what invdate is then we can go from there

0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 250 total points
Comment Utility
on the first line of your code put in the following

option explicit

then do a debug/compile,
if you have any variables that u use but not declared, it will complain about them
you should declare them



if I give you an example of functions, hopefully that will give you an idea what is wrong

public function MyFunc() as integer

    MyFunc = 5

end function

If I do xx = MyFunc, the value of xx will be 5 because I am returning 5
Remember what I said earlier, to set a value,simply assign the function name


if I have
public function MyFunc() as integer

    yxz = 5

end function

and do xx = MyFunc, xx wll not have 5 because I am not returning anything



If I do


public sub MyFunc(byval x as string)
  x = 1
end sub

this will not return anything as this is a procedure nor will it give you x = 1

public sub MyFunc(byref x as string)
  x = 1
end sub

this will still not return anything but will set the value of x to 1 because Im passing by reference and Im setting that value


Hopefully this will help u understand your getdate function



0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi Connie,

You are assigning the return value of the function which is a String data type to your local variable newdate which is a date/time data type, hence the error.

Try this:
Call getdate(yourdate)
newdate = yourdate

Or change the return type of the function from String to Date

Alan :)
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 250 total points
Comment Utility
Correction:

         yourdate = rst!invdate
         getdate(yourdate)
         newdate = yourdate

Alan
0
 

Author Comment

by:cskehan
Comment Utility
ty both of you for all you help much appreciated. You both contributed to getting me pointed in the right direction.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now