We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

type mismatch error 13

cskehan
cskehan asked
on
Medium Priority
792 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
Comment
Watch Question

Alan WarrenApplications Developer

Commented:
Hi cskehan


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



Alan
cskehanCFO

Author

Commented:
alan,

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

Connie
Alan WarrenApplications Developer

Commented:
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
cskehanCFO

Author

Commented:
Alan

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

Connie
Alan WarrenApplications Developer

Commented:
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
cskehanCFO

Author

Commented:
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
Alan WarrenApplications Developer

Commented:
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
cskehanCFO

Author

Commented:
Alan

Still doesn't work I get invalid use of me

Connie

Alan WarrenApplications Developer

Commented:

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
CERTIFIED EXPERT
Top Expert 2006

Commented:
'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,
cskehanCFO

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2006

Commented:
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


Commented:
You could change the function as follows:

Public Function getdate(ByVal mydate As Date) As Date
   getdate=mydate
End Function
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
cskehanCFO

Author

Commented:
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?
CERTIFIED EXPERT
Top Expert 2006

Commented:
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

CERTIFIED EXPERT
Top Expert 2006
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Alan WarrenApplications Developer

Commented:
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 :)
Applications Developer
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
cskehanCFO

Author

Commented:
ty both of you for all you help much appreciated. You both contributed to getting me pointed in the right direction.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.