?
Solved

type mismatch error 13

Posted on 2004-04-09
20
Medium Priority
?
767 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 4
  • +2
20 Comments
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10795566
Hi cskehan


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



Alan
0
 

Author Comment

by:cskehan
ID: 10795585
alan,

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

Connie
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10795606
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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 

Author Comment

by:cskehan
ID: 10795626
Alan

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

Connie
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10795640
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
ID: 10795677
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
ID: 10795686
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
ID: 10795705
Alan

Still doesn't work I get invalid use of me

Connie

0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10795722

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
ID: 10795830
'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
 

Author Comment

by:cskehan
ID: 10795928
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
ID: 10795989
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
ID: 10796472
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
ID: 10796480
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
ID: 10797649
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
ID: 10797707
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 1000 total points
ID: 10797995
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
ID: 10799223
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 1000 total points
ID: 10799229
Correction:

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

Alan
0
 

Author Comment

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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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 …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

777 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