13Type Mismatch Access 2007

Question

I'm attempting to fix some botched up VBA from someone whom I inherited this Access database from. Aside from the hardly-useful notes left in VBA, there is no documentation, so I am trying to figure out what everything does, and if it is correct. I continue getting a 13Type Mismatch error when I am Clicking a button to add either units or a value to a table of Contributions. I thought it was an easy fix such as a messed up variable declaration, however I've changed them to Double and it didn't seem to correct my error. Does anyone see anything off the bat that they might recognize as throwing this error? Thanks ahead of time for your efforts.

    Private Sub AddContributionBtn_Click()
    On Error GoTo Err_AddContributionBtn

  Dim Cancel As Integer
  Dim CurrentNAVDate As Date
  Dim CurrentNAV As Double
  Dim ConfirmAddCont As Double
  Dim CalcContUnits As Double
  Dim CalcContValue As Double
  Dim StringSQL As String

    'get current NAV
    CurrentNAVDate = Format(DateAdd("s", -1, DateAdd("q", DateDiff("q", "1/1/1900", Date),             "1/1/1900")), "Short Date")
    CurrentNAV = Format(DLookup("NetAssetValue", "NAV_Tbl", "Format(NAV_Date, ""mmddyyyy"") = " & Format(CurrentNAVDate, "mmddyyyy")), "Currency")

    'validation to require either contribution units or value is entered, not both
    If IsNull(Me.ContValueTxt) = True And IsNull(Me.ContUnitsTxt) = True Then
        MsgBox "Please enter contribution units or value."
        Me.ContUnitsTxt.SetFocus
        Cancel = True
        Exit Sub
    ElseIf IsNull(Me.ContValueTxt) = False And IsNull(Me.ContUnitsTxt) = False Then
        MsgBox "Both contribution units and value may not be entered."
        Me.ContUnitsTxt.SetFocus
        Cancel = True
        Exit Sub
    Else:
        If IsNull(Me.ContValueTxt) = True And IsNull(Me.ContUnitsTxt) = False Then
            'calculate contribution value from units
            CalcContUnits = Me.ContUnitsTxt
            CalcContValue = CalcContUnits * CurrentNAV

            GoTo ConfirmAppend

        ElseIf IsNull(Me.ContValueTxt) = False And IsNull(Me.ContUnitsTxt) = True Then
            'calculate contribution units from value
            CalcContValue = Me.ContValueTxt
            CalcContUnits = CalcContValue / CurrentNAV

            GoTo ConfirmAppend
        End If
    End If

 ConfirmAppend:
    'confirm contribution value and units, run append query
    ConfirmAddCont = MsgBox("Add " & Format(CalcContUnits, "fixed") & " units for a      contribution value of " & Format(CalcContValue, "currency") & "?", _
    vbOKCancel, "Add Contribution")
    If ConfirmAddCont = vbOK Then
        DoCmd.Hourglass True
        DoCmd.SetWarnings False
            StringSQL = "INSERT INTO ContributionTbl(ContDate, ContUnits, ContNAV,   ContType) VALUES (#" & Date & "#, " & CalcContUnits & ", #" & CurrentNAVDate & "#, " & 1 & ");"
            DoCmd.RunSQL (StringSQL)
        DoCmd.SetWarnings True
        DoCmd.Hourglass False

        Me.ContUnitsTxt = Null
        Me.ContValueTxt = Null
        Forms!PlanFrm![PlanContributedUnitsFrm].Requery
    Else
        Cancel = True
        Exit Sub
    End If

 Exit_AddContributionBtn:
    Exit Sub
 Err_AddContributionBtn:
    MsgBox Err.Number & Err.Description
    Resume Exit_AddContributionBtn
 End Sub

I truly apologize, but I am not sure. This is just the VBA code that I found behind the click of a button, and I am getting the 13Type Mismatch error. Some of the number variables were Integer and Double, so I changed them all to Double assuming that would help, but it didn't... — Mark C., Nov 08 '13 at 21:05
Set a breakpoint (with F9) on the `CurrentNAVDate = Format(` line. Then use F8 to step through the code one line at a time. That should allow you to identify which line triggers the error. Good luck, Truly. — HansUp, Nov 08 '13 at 21:10
HansUp, thanks for your reply. It pinpointed the CurrentNAVDate and CurrentNAV lines. Is this where I should be looking at some kind of problems with the variable declaration? — Mark C., Nov 08 '13 at 21:16
Still threw the error, Jacouh. Thanks for the suggestion, though. — Mark C., Nov 08 '13 at 21:26
Error may be here: CurrentNAV = Format(DLookup("NetAssetValue", "NAV_Tbl", "Format(NAV_Date, ""mmddyyyy"") = " & Format(CurrentNAVDate, "mmddyyyy")), "Currency"), as DLookup() gets NULL, Format(NULL, "Currency") gets 13 Type Mismatch, as I reproduced this in Access 2007? — jacouh, Nov 08 '13 at 21:51

jacouh · Accepted Answer · 2013-11-11T19:23:12.057

0

As shown in the discussion, I make our guess clearer in this temporary reponse:

Error may be here:

CurrentNAV = Format(DLookup("NetAssetValue", "NAV_Tbl", "Format(NAV_Date, ""mmddyyyy"") = " & Format(CurrentNAVDate, "mmddyyyy")), "Currency")

as DLookup("NetAssetValue",...) gets NULL,

Format(NULL, "Currency") gets 13 Type Mismatch, as I've reproduced this in Access 2007.

This can be explained: Since there is no recent date in the table field NAV_Tbl.NetAssetValue, as in the case we get the date CurrentNAVDate = 09/30/2013 (last date of the last quarter).

So you may try code like this, by introducing varCurrency variable to handle this NULL value case:

Private Sub AddContributionBtn_Click()

  On Error GoTo Err_AddContributionBtn

  Dim Cancel As Integer
  Dim CurrentNAVDate As Date
  Dim CurrentNAV As Double
  Dim ConfirmAddCont As Double
  Dim CalcContUnits As Double
  Dim CalcContValue As Double
  Dim StringSQL As String

  Dim varCurrency

  'get current NAV
  CurrentNAVDate = Format(DateAdd("s", -1, DateAdd("q", DateDiff("q", "1/1/1900", Date),             "1/1/1900")), "Short Date")
  varCurrency = DLookup("NetAssetValue", "NAV_Tbl", "Format(NAV_Date, ""mmddyyyy"") = " & Format(CurrentNAVDate, "mmddyyyy"))
  If(IsNull(varCurrency) then
    CurrentNAV = 0
  Else
    CurrentNAV = Format(varCurrency, "Currency")
  End If

  'validation to require either contribution units or value is entered, not both
  If IsNull(Me.ContValueTxt) = True And IsNull(Me.ContUnitsTxt) = True Then
    MsgBox "Please enter contribution units or value."
    Me.ContUnitsTxt.SetFocus
    Cancel = True
    Exit Sub
  ElseIf IsNull(Me.ContValueTxt) = False And IsNull(Me.ContUnitsTxt) = False Then
    MsgBox "Both contribution units and value may not be entered."
    Me.ContUnitsTxt.SetFocus
    Cancel = True
    Exit Sub
  Else:
    If IsNull(Me.ContValueTxt) = True And IsNull(Me.ContUnitsTxt) = False Then
      'calculate contribution value from units
      CalcContUnits = Me.ContUnitsTxt
      CalcContValue = CalcContUnits * CurrentNAV

      GoTo ConfirmAppend

    ElseIf IsNull(Me.ContValueTxt) = False And IsNull(Me.ContUnitsTxt) = True Then
      'calculate contribution units from value
      CalcContValue = Me.ContValueTxt
      CalcContUnits = CalcContValue / CurrentNAV

      GoTo ConfirmAppend
    End If
  End If

ConfirmAppend:
  'confirm contribution value and units, run append query
  ConfirmAddCont = MsgBox("Add " & Format(CalcContUnits, "fixed") & " units for a      contribution value of " & Format(CalcContValue, "currency") & "?", _
  vbOKCancel, "Add Contribution")
  If ConfirmAddCont = vbOK Then
    DoCmd.Hourglass True
    DoCmd.SetWarnings False
    StringSQL = "INSERT INTO ContributionTbl(ContDate, ContUnits, ContNAV,   ContType) VALUES (#" & Date & "#, " & CalcContUnits & ", #" & CurrentNAVDate & "#, " & 1 & ");"
    DoCmd.RunSQL (StringSQL)
    DoCmd.SetWarnings True
    DoCmd.Hourglass False

    Me.ContUnitsTxt = Null
    Me.ContValueTxt = Null
    Forms!PlanFrm![PlanContributedUnitsFrm].Requery
  Else
    Cancel = True
    Exit Sub
  End If

Exit_AddContributionBtn:
  Exit Sub
Err_AddContributionBtn:
  MsgBox Err.Number & Err.Description
  Resume Exit_AddContributionBtn
End Sub

For DLookup():

varCurrency = DLookup("NetAssetValue", "NAV_Tbl", "NAV_Date >= #" & Format(CurrentNAVDate, "yyyy-mm-dd") & "#")

edited Nov 11 '13 at 19:23

answered Nov 08 '13 at 22:07

jacouh

8,473
5
32
43

Is there a possible reason CurrentNAV / CurrentNAVDate aren't working? I know in Query design, to get the most recent NAV Date, I would simply sort descending. I'm not quite sure what the two NAV lines of code are doing, hence why I didn't know what was throwing the error. How can I make sure it retrives the correct info? – Mark C. Nov 11 '13 at 18:31
You shall feed data into the table NAV_Tbl to recent date. CurrentNAV is money amount, I guess. – jacouh Nov 11 '13 at 18:35
No, no. You're exactly correct. The current date is a quarterly updated Date field. The CurrentNAV is a dollar amount that is also updated quarterly. I am not sure that the CurrentNAVDate is succeeding in finding the row with the most recent date and then finding the CurrentNAV associated with that date... Is that possible to do in VBA? – Mark C. Nov 11 '13 at 18:40
Yeah, the DLookup for the varCurrency is not working. There is no calculation being done. I'm not sure how to go about solving that... – Mark C. Nov 11 '13 at 18:50
It's very probable. You shall use filter like "NAV_Date = #2013-09-30#", But it depends on the type of NAV_Tbl.NAV_Date, DateTime, or Simple String, ... – jacouh Nov 11 '13 at 18:55
What do you think of creating a TempNAV_Tbl where I only store the most recent NAV? Then when a new NAV comes along, I can simply append it to NAV tbl and delete it from the Temp tbl and add the new NAV to the Temp table. – Mark C. Nov 11 '13 at 18:59
It's simpler, you look at the field type of NAV_Tbl.NAV_Date. We then format corret date string for it. – jacouh Nov 11 '13 at 19:01
Yes. but how? I am not sure how to code to look for the most recent NAV_Tbl.NAV_Date ... Then use that date to get NAV in the same row. – Mark C. Nov 11 '13 at 19:08
You look on Access Navigation Pane, locate the table and open it in design view... Datatype will be shown. Before this, I'll add a line to you to test in my Answer. – jacouh Nov 11 '13 at 19:16
CurrentNAVDate is Date/Time and NAV is Currency – Mark C. Nov 11 '13 at 19:17
varCurrency = DLookup("NetAssetValue", "NAV_Tbl", "NAV_Date >= #" & Format(CurrentNAVDate, "yyyy-mm-dd") & "#") – jacouh Nov 11 '13 at 19:23
I thought that would have done it, but it did not. Is this line correct? CurrentNAVDate = Format(DateAdd("s", -1, DateAdd("q", DateDiff("q", "1/1/1900", Date), "1/1/1900")), "Short Date") – Mark C. Nov 11 '13 at 19:30
Yes I got 30/09/2013. Please check the last NAV_Tbl.NAV_Date value. And put it here. – jacouh Nov 11 '13 at 19:48
NAV_Date = 6/30/2013 and NAV = $2,000.00 (that's the most recent record) – Mark C. Nov 11 '13 at 19:50
I just added a new NAV Date to reflect your findings. 9/30/2013 and $2,000.00 – Mark C. Nov 11 '13 at 19:52
I added this and removed the other DLookups.. and it worked: currNAV = DLast("NetAssetValue", "RecentNAVQry") – Mark C. Nov 11 '13 at 19:58
Your shall insert record for accounting on the last date of a quarter, 03/31/2013, 6/30/2013, 09/30/2013, 12/31/2013,... – jacouh Nov 11 '13 at 20:01
I have a form where I enter a new NAV for each period, which is divided quarterly automatically. I'm not sure what you're trying to tell me. I was still unsuccessful at getting the CurrentNAVDate to work, and then getting the CurrentNAVDate to get me the varCurrency value. – Mark C. Nov 11 '13 at 20:02
This was just a guess. With your new added record 09/30/2013 and $2,000.00, varCurrency = DLookup("NetAssetValue", "NAV_Tbl", "NAV_Date >= #" & Format(CurrentNAVDate, "yyyy-mm-dd") & "#") should work. – jacouh Nov 11 '13 at 20:11
Can you tell me what the CurrentNAVDate and the DLookup do, respectively? I don't understand the date code.. and THANK YOU FOR YOUR EFFORT AND TIME!! – Mark C. Nov 11 '13 at 20:14
CurrentNAVDate is the last date of the last quarter, for today it's 09/30/2013. Please consult VBA Format() function by searching on Google. DLoopup searh the Money, ie, NetAssetValue in the NAV_Tbl, with NAV_Date >= #2013-09-30# . See http://office.microsoft.com/en-us/access-help/dlookup-function-HA001228825.aspx – jacouh Nov 11 '13 at 20:24
Thank you very much. The CurrentNAVDate really threw me off. I still don't quite understand it, even as I read up on it... But thank you again. I don't think I would have ever been able to get that. – Mark C. Nov 11 '13 at 20:26

13Type Mismatch Access 2007

1 Answers1