Results 1 to 10 of 10
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    data type mismatch in criteria expression


    Hi Guys, i am receiving data type mismatch in criteria expression, there is an invoice with paid ticked and an invoice with paid not ticked just to retrieve the answer

    The paid field is a yes/no field and total is set to currency, i can't see why !!!

    Code:
    Dim StartDate As Date, EndDate As DateDim ExpRecs As Integer, InvRecs As Integer
    Dim InvUnpaid As Currency, InvPaid As Currency, InvTotal As Currency
    Dim ExpUnpaid As Currency, ExpPaid As Currency, ExpTotal As Currency
    Dim InvPaidY As String, InvUnpaidN As String, ExpPaidY As String, ExpUnpaidN As String
    
    
    StartDate = DateSerial(Me.cboYear, Me.cboMonth, 1)
    EndDate = DateSerial(Me.cboYear, Me.cboMonth + 1, 0)
    
    
    Me.txtStartDate = StartDate
    Me.txtEndDate = EndDate
    
    
    InvPaidY = "Yes"
    InvUnpaidN = "No"
    ExpPaidY = "Yes"
    ExpUnpaidN = "No"
    
    
    ExpRecs = DCount("ReceiptNo", "tblExpenses", "[Date] Between #" & StartDate & "# And #" & EndDate & "#")
    InvRecs = DCount("InvoiceNumber", "tblInvoices", "[InvoiceDate] Between #" & StartDate & "# And #" & EndDate & "#")
    
    
    InvTotal = DSum("Total", "tblInvoices", "[InvoiceDate] Between #" & StartDate & "# And #" & EndDate & "#")
    ExpTotal = DSum("Amount", "tblExpenses", "[Date] Between #" & StartDate & "# And #" & EndDate & "#")
    
    
    InvPaid = DSum("Total", "tblInvoices", "[InvoiceDate] Between #" & StartDate & "# And #" & EndDate & "# And [Paid] = '" & InvPaidY & "'")
    InvUnpaid = DSum("Total", "tblInvoices", "[InvoiceDate] Between #" & StartDate & "# And #" & EndDate & "# And [Paid] = '" & InvUnpaidN & "'")
    
    
    ExpPaid = DSum("Amount", "tblExpenses", "[Date] Between #" & StartDate & "# And #" & EndDate & "# And [Paid] = '" & ExpPaidY & "'")
    ExpUnpaid = DSum("Amount", "tblExpenses", "[Date] Between #" & StartDate & "# And #" & EndDate & "# And [Paid] = '" & ExpUnpaidN & "'")
    
    
    Me.txtTotalInvoices = InvRecs
    Me.txtInvoicesPaid = InvPaidY
    Me.txtTotalInvoicesAmount = InvTotal
    Me.txtInvoicesUnpaid = InvUnpaidN
    
    
    Me.txtTotalExpenses = ExpRecs
    Me.txtExpensesPaid = ExpPaidY
    Me.txtExpensesUnpaid = ExpUnpaidN
    Me.txtTotalExpensesAmount = ExpTotal

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Yes/No field is a number type (values of -1 or 0). Yes/No and True/False are intrinsic constants for -1/0. Don't use apostrophe delimiters.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The delimiters would be required if the table field was text. The variables in the code are defined as text and assigned text data types with values as "Yes" or "No". Likely the reason for the failure is because the underlying field is not text. In that case, the sql would work if using the correct data type, but any code that has to deal with the numbers -1 or 0 where the variable is text might also fail.

    DMT Dave - I'm surprised that with your forum experience you didn't specify which line the error is raised on.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I tested and strings "Yes" and "No" were treated as booleans in the domain aggregate when apostrophe delimiters were eliminated.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by June7 View Post
    I tested and strings "Yes" and "No" were treated as booleans in the domain aggregate when apostrophe delimiters were eliminated.
    Don't know what you're doing but this works on a text field
    SELECT tblYNtext.ID, tblYNtext.txtYesNo
    FROM tblYNtext WHERE (((tblYNtext.txtYesNo)="no"));
    and booleans never come into the picture. You must have missed some point about my post.
    I will try it in some sort of aggregate function, but I wasn't going down that road with my comments.
    Last edited by Micron; 11-29-2019 at 04:21 PM. Reason: added comment
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Quote Originally Posted by DMT Dave View Post
    … there is an invoice with paid ticked and an invoice with paid not ticked...
    The paid field is a yes/no field ...
    So I tested domain aggregate using Yes/No field with text value "Yes" because posted code uses DSum. I understood your points about text field needing delimiters and about possible failure with Yes/No field comparing to string variable. My point is it did not fail without delimiters.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I did some tests also. Got sql to work but not vba expression. During the process my pc screen died so that's all for now I guess.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi Guys, sorry i did highlight the line in red but did edit it afterwards which maybe i forgot to highlight, all points taken, i will take string delimiters out and i think i have dome this in past where you just type a simple Yes or No at the end of the line ?

    Kindest

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Correct, the string variables are really not necessary.
    Code:
    InvPaid = DSum("Total", "tblInvoices", "[InvoiceDate] Between #" & StartDate & "# And #" & EndDate & "# And [Paid] = Yes")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Thank you so much all, i wll adapt tho that and thank you for your help

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Data type mismatch in criteria expression
    By hehrerh in forum Queries
    Replies: 3
    Last Post: 08-10-2017, 03:04 PM
  2. Data type mismatch in criteria expression.
    By KERRYDEE in forum Access
    Replies: 2
    Last Post: 05-06-2016, 11:56 AM
  3. Data type mismatch in criteria expression
    By khughes46 in forum Queries
    Replies: 12
    Last Post: 05-01-2014, 07:34 AM
  4. data type mismatch in criteria expression
    By broecher in forum Access
    Replies: 4
    Last Post: 09-10-2010, 08:29 AM
  5. Data type mismatch in criteria expression
    By shexe in forum Queries
    Replies: 2
    Last Post: 09-01-2010, 12:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums