Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2012
    Posts
    79

    Unanswered: Type Mismatch in Dcount function

    I have the following code behind a form:

    Code:
    Me.txtCompleted = Nz(DCount("[fltSuffix]", "[FlightLog]", "[fltSuffix]='Completed'" And Month("[txtDate]") = Month(Me.txtDateTime) And Year("[txtDate]") = Month(txtDateTime)), 0)
    I'm trying to get a count of all of the records in the table FlightLog where the fltSuffix = Completed and the Month and Year = the current month and year. Me.txtDateTime is a textbox on the form with the =Now() function which was already there for user reference.

    I get a type mismatch in the date portions of the code. The field txtDate is a Date/Time field. I've tried multiple configurations (quotes, single quotes, parenthesis, etc) and can't seem to find the right way of doing it despite a few hours on google!
    Version: Access 2010

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok so on the face of it the vba code looks OK, but we'd need to see what that code actually resolves to at runtime when it reports an error.

    type ismatch usulalyu measn that you are supplying a value of the wrong datatype to a something. and that usually means am alphanumeric to a numeric, or a non date to a datetime variable / control / column

    so when the code fails the line thats throwing the error should be highlighted
    move your move the mouse cursor over each value you are supplying (eg txtdate, txtdatetime and so on). make certain those values are correct.

    then prove which particualr bit off the code is throwing the wobbler.
    ...in the immediate window examine each element individually and prefix it with the ?... this forces the runtime compiler to evaluate the follwoign expression


    eg
    Code:
    ?Me.txtDateTime
    will display the value of the control or variable Me.txtDateTime
    Code:
    ?Month(Me.txtDateTime)
    ...but it can only display the values of 'stuff' it knows about so you can't see what txtdate is as that is a column in your tbale/query/view which the immediate windows doens't knw about.

    but straightaway Imnpretty certain you have a loigic error
    Code:
    And Year("[txtDate]") = Month(txtDateTime)), 0)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2012
    Posts
    79
    but straightaway Imnpretty certain you have a loigic error
    Code:
    And Year("[txtDate]") = Month(txtDateTime)), 0)
    I've done as you'd advised and as far as I can tell they are spitting out the correct values on debug. I'm fairly certain as well that I've just got some quotes and stuff messed up.

    I've done a very similar expression before using DMax instead of DCount and instead of Month(txtDateTime) I had previously set up a couple of variables: frmMonth and frmYear which I created from frmDate which has the Now function.

    Code:
    dMaxLstReq = Nz(DMax("reqNumb", "FlightLog", "Month([txtDate])=" & frmMonth & " And Year([txtDate])=" & frmYear & ""), 0)
    The only difference besides using DMax instead of DCount are the variables. I don't really want to do that this time around because I don't need them for anything else except the DCount expression!
    Version: Access 2010

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so can we see those values
    can we see the table (+column) design

    Or use the debugger to identify what term is causing the problem
    ...
    when the code halts
    copy and paste the line of code, put it into the immediate window prefixed by a ?
    then remove each term till you find wheat is causing the fault
    eg:-
    code as is
    Code:
    ?Nz(DCount("[fltSuffix]", "[FlightLog]", "[fltSuffix]='Completed'" And Month("[txtDate]") = Month(Me.txtDateTime) And Year("[txtDate]") = Month(txtDateTime)), 0)
    'code should fault on error
    'remove outer NZ
    Code:
    ?DCount("[fltSuffix]", "[FlightLog]", "[fltSuffix]='Completed'" And Month("[txtDate]") = Month(Me.txtDateTime) And Year("[txtDate]") = Month(txtDateTime))
    'code will almost certainly fault
    Code:
    ?DCount("[fltSuffix]", "[FlightLog]", "[fltSuffix]='Completed'" And Month("[txtDate]") = Month(Me.txtDateTime))
    Code:
    ?DCount("[fltSuffix]", "[FlightLog]", "[fltSuffix]='Completed'" )
    if it faults on all 3 modifications then my expectation is that fltSuffix does is actually a numeric value NOT a string/text value
    if it doesnt' fault ont he 3rd iteration "[fltSuffix]='Completed'" ... then its your month term, most likely reason is the datatype of the column is string / text NOT numeric
    ...if so then its almost the same fault with the month term.
    as I say without knowing your tabel design and or seeing what the actual values are in your code is impossible to tell.

    bit above all else spend some time learning how to use the debugger, how to analyse your code and compare what it is doing with what you think it should be doing, and always refer back tot eh source (table design) not just take a wild presumption that it must .... prove all assumptions and carefully work through each step till you find the culprit.

    actually hold it a minute what you are saying cannot possibly be true
    you are not dropping oin and out of VBA correctly.
    Me.txtCompleted = Nz(DCount("[fltSuffix]", "[FlightLog]", "[fltSuffix]='Completed'" And Month("[txtDate]") = Month(Me.txtDateTime) And Year("[txtDate]") = Month(txtDateTime)), 0)
    is invalid SQL
    ..what you are actually saying is garbage I think it should be
    Code:
    Me.txtCompleted = Nz(DCount("[fltSuffix]", "[FlightLog]", "[fltSuffix]='Completed' And " & Month([txtDate]) & " =  " & Month(Me.txtDateTime) & " And & " Year([txtDate]) & "  = " & Month(txtDateTime)), 0)
    you need to drop in and out of VBA to make when you add variables to a string
    I havent got a clue whether txtdate is the name of the column or a control/variable, if its a column then frankly its a daft idea using such prefixes in table / column design. its agisnt all standards and practises in SQL. reverse polish notation may have a place in VBA code but not in SQL table & column definitions.

    again you can quickly prove that by copying the 3rd term for the dcount and pasting that in the immediate window and make certain its saying what you think it ought
    Code:
    ?"[fltSuffix]='Completed'" And Month("[txtDate]") = Month(Me.txtDateTime) And Year("[txtDate]") = Month(txtDateTime)
    as opposed to what I think you want which is
    Code:
    ?"[fltSuffix]='Completed'" And Month([txtDate]) =  " & Month(Me.txtDateTime) & " And Year([txtDate]) = " & Month(txtDateTime)
    that assumes that txtDate is the name of the column in the db, please dont say its so.... and is of datetime
    Last edited by healdem; 08-10-15 at 07:36.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •