Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Feb 2019
    Posts
    11

    Error 3464 data type mismatch in Visual Basic

    I have my database built, and now I am working on creating a form with report options for my end users to use. There are 4 types of reports, with up to 3 criteria fields for the end users to determine what information they want to print. I am getting the same errors in all three of the criteria settings that do not function, and the same section of the VB is highlighted when I click debug, however the criteria is not the same in the three that do not work.



    Report 1 - 3 types of criteria, 2 work, and 1 does not.
    Report 2 - 1 type of criteria, works perfect.
    Report 3 - 3 types of criteria, only 1 works
    Report 4 - 1 type of criteria, works perfect.


    Report 1: 3 types of criteria, 2 work, and 1 does not

    Click image for larger version. 

Name:	Master location crit location - building name - error.jpg 
Views:	21 
Size:	116.2 KB 
ID:	37336Click image for larger version. 

Name:	Master location crit location - building name - debug.jpg 
Views:	23 
Size:	143.6 KB 
ID:	37334


    This issue is the Master list of all building names, the criteria they are searching for is on particular building name. In this example (See screenshots) they want to see only those offices in Monroe MOB. I get pop up - Run-time error 3075: syntax error in date in query expression '(Location=#Monroe MOB#' The report does not open at all.

    When I click Debug, I get the next screenshot with the information highlighted. That highlighted section is how I allow end users to select to print or print preview their report.

    Any suggestions?

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    you appear to be wrapping text values inside of date delimiters (#)
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Join Date
    Feb 2019
    Posts
    11
    Thanks, Micron. To be honest, I am not sure how exactly to fix that issue. the VB text I used was from a class I took on Access, not something I wrote myself. Any further direction would be helpful if you have the time to do so.

  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,822
    Text fields require apostrophe (or doubled quotes as in your code, I prefer apostrophe) delimiters for parameters.

    "='" & Me.cboCrit2 & "'"

    Date fields required # delimiters. Number fields do not use delimiters.

    Now what is the actual value held by the combobox? Is the combobox RowSource an SQL statement that includes a number ID as well as the descriptive text? Is it bound to the ID field which is hidden?

    You should post code as text between CODE tags, not as image.
    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
    Join Date
    Feb 2019
    Posts
    11
    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub cboReport_AfterUpdate()
    If IsNull(DLookup("ControlID", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit1""")) = False Then
    Me.cboCrit1.Visible = True
    Me.cboCrit1.RowSource = DLookup("ControlSql", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit1""")
    Me.lblCrit1.Caption = DLookup("ControlCaption", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit1""")
    Me.cboCrit1.Tag = DLookup("FieldName", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit1""")
    Else
    Me.cboCrit1.Visible = False
    End If
    
    
    If IsNull(DLookup("ControlID", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit2""")) = False Then
    Me.cboCrit2.Visible = True
    Me.cboCrit2.RowSource = DLookup("ControlSql", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit2""")
    Me.lblCrit2.Caption = DLookup("ControlCaption", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit2""")
    Me.cboCrit2.Tag = DLookup("FieldName", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit2""")
    Else
    Me.cboCrit2.Visible = False
    End If
    
    
    
    
    If IsNull(DLookup("ControlID", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")) = False Then
    Me.cboCrit3.Visible = True
    Me.cboCrit3.RowSource = DLookup("ControlSql", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")
    Me.lblCrit3.Caption = DLookup("ControlCaption", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")
    Me.cboCrit3.Tag = DLookup("FieldName", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")
    Else
    Me.cboCrit3.Visible = False
    End If
    
    
    
    
    Me.lblDetails.Caption = Me.cboReport.Column(2)
    Me.cboPrintMethod = 1
    
    
    End Sub
    
    
    Private Sub cmdClearCrit_Click()
    
    
    Me.cboCrit1.Value = ""
    Me.cboCrit2.Value = ""
    Me.cboCrit3.Value = ""
    
    
    End Sub
    
    
    Private Sub cmdPrint_Click()
    Dim strCrit As String
    If Me.cboCrit1 <> "" Then
              strCrit = Me.cboCrit1.Tag & "=""" & Me.cboCrit1.Value & """"
    End If
    If Me.cboCrit2 <> "" Then
              If strCrit = "" Then
                         strCrit = Me.cboCrit2.Tag & "=#" & Me.cboCrit2 & "#"
    
    
    Else
    
    
    strCrit = strCrit & " AND " & Me.cboCrit2.Tag & "=#" & Me.cboCrit2.Value & "#"
    
    
    End If
    
    
    End If
    
    
    If Me.cboCrit3 <> "" Then
    
    
    If strCrit = "" Then
    
    
    strCrit = Me.cboCrit3.Tag & "=""" & Me.cboCrit3 & """"
    
    
    Else: strCrit = strCrit & " AND " & Me.cboCrit3.Tag & "=""" & Me.cboCrit3.Value & """ "
    
    
    End If
    
    
    End If
       If Me.cboPrintMethod = 1 Then
           DoCmd.OpenReport Me.cboReport.Column(3), acViewPreview, , strCrit
       Else
           DoCmd.OpenReport Me.cboReport.Column(3), acViewNormal, , strCrit
       End If
       DoCmd.Close acForm, "frmDlgRpts"
    
    
    End Sub

  6. #6
    Join Date
    Feb 2019
    Posts
    11
    Okay, pasted the whole code for that form, is that better? My apologies, this is my first time trying to work with VB.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I meant for the future. And it is not within CODE tags. See the # icon on editor menu? Click it and post code between tags. This will retain indentation and readability. Try editing your existing posts instead of creating another.
    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.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    First, put OPTION EXPLICIT under Option Compare Database, then compile this code (in your db) to check for problems. If none found, then yes, please go back to your posted code and select it, then choose # to wrap it in code tags. "Go Advanced" might provide more editing room as well as a preview. If the compiling presents a problem you don't know how to resolve, indicate what that is and what line it occurs on. No sense reviewing too much if it won't compile.
    You should go to Tools Options in vb editor and find the option to Always Require Variable Declaration and set it, otherwise you're setting yourself up for more grief. This will not affect existing code modules.
    Last edited by Micron; 02-07-2019 at 03:01 PM. Reason: clarification

  9. #9
    Join Date
    Feb 2019
    Posts
    11
    Okay, that all made sense to me. I have added Option explicit and debugged, no errors were found. I figured out that code tag and repasted in the code there. I also added Always Require Variable Declaration.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Okay, did you try changing delimiters as described?

    Looks like your code formatting could be improved by better usage of indenting.
    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.

  11. #11
    Join Date
    Feb 2019
    Posts
    11
    Quote Originally Posted by June7 View Post
    Okay, did you try changing delimiters as described?
    I am sorry, I am not sure how to do that. Can you link me to maybe some step-by-step directions on what exactly I would need to do, please?

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I was a bit vague about it in post 2, but you have the answer in post 4. Look closely - there are single quotes within the double quotes.

  13. #13
    Join Date
    Feb 2019
    Posts
    11
    Per the suggestions here, I changed the # to ' in the code, see below. This did not change of the outcomes in my reports. Continuing to have the same errors, when I debug the same text is highlighted in yellow.


    Interestingly, when I switch the print method to print, rather than the defaulted print preview, it says it is now printing, but does not print and instead freezes on the "Now Printing" window. I actually have to force access to close and reopen it.

    I attached the images of the report form, maybe that will give you a better idea as to what I did?


    Other things that may be relevant - in order to have drop down options in my forms for data entry, some things that are truly text are Number data type. Some of these fields, when used as criteria function, but some do not - could this be the source of my error?

    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub cboReport_AfterUpdate()
    If IsNull(DLookup("ControlID", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit1""")) = False Then
    Me.cboCrit1.Visible = True
    Me.cboCrit1.RowSource = DLookup("ControlSql", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit1""")
    Me.lblCrit1.Caption = DLookup("ControlCaption", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit1""")
    Me.cboCrit1.Tag = DLookup("FieldName", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit1""")
    Else
    Me.cboCrit1.Visible = False
    End If
    
    
    If IsNull(DLookup("ControlID", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit2""")) = False Then
    Me.cboCrit2.Visible = True
    Me.cboCrit2.RowSource = DLookup("ControlSql", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit2""")
    Me.lblCrit2.Caption = DLookup("ControlCaption", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit2""")
    Me.cboCrit2.Tag = DLookup("FieldName", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit2""")
    Else
    Me.cboCrit2.Visible = False
    End If
    
    
    
    
    If IsNull(DLookup("ControlID", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")) = False Then
    Me.cboCrit3.Visible = True
    Me.cboCrit3.RowSource = DLookup("ControlSql", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")
    Me.lblCrit3.Caption = DLookup("ControlCaption", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")
    Me.cboCrit3.Tag = DLookup("FieldName", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")
    Else
    Me.cboCrit3.Visible = False
    End If
    
    
    
    
    Me.lblDetails.Caption = Me.cboReport.Column(2)
    Me.cboPrintMethod = 1
    
    
    End Sub
    
    
    Private Sub cmdClearCrit_Click()
    
    
    Me.cboCrit1.Value = ""
    Me.cboCrit2.Value = ""
    Me.cboCrit3.Value = ""
    
    
    End Sub
    
    
    Private Sub cmdPrint_Click()
    Dim strCrit As String
    If Me.cboCrit1 <> "" Then
              strCrit = Me.cboCrit1.Tag & "=""" & Me.cboCrit1.Value & """"
    End If
    If Me.cboCrit2 <> "" Then
              If strCrit = "" Then
                         strCrit = Me.cboCrit2.Tag & "='" & Me.cboCrit2 & "'"
    
    
    Else
    
    
    strCrit = strCrit & " AND " & Me.cboCrit2.Tag & "='" & Me.cboCrit2.Value & "'"
    
    
    End If
    
    
    End If
    
    
    If Me.cboCrit3 <> "" Then
    
    
    If strCrit = "" Then
    
    
    strCrit = Me.cboCrit3.Tag & "=""" & Me.cboCrit3 & """"
    
    
    Else: strCrit = strCrit & " AND " & Me.cboCrit3.Tag & "=""" & Me.cboCrit3.Value & """ "
    
    
    End If
    
    
    End If
       If Me.cboPrintMethod = 1 Then
           DoCmd.OpenReport Me.cboReport.Column(3), acViewPreview, , strCrit
       Else
           DoCmd.OpenReport Me.cboReport.Column(3), acViewNormal, , strCrit
       End If
       DoCmd.Close acForm, "frmDlgRpts"
    
    
    End Sub
    Click image for larger version. 

Name:	frmdlgrpts design view.jpg 
Views:	13 
Size:	123.2 KB 
ID:	37347Click image for larger version. 

Name:	frmdlgrpts.jpg 
Views:	13 
Size:	33.3 KB 
ID:	37348



    Edited to add-

    I got some direct messages asking me about the cboCrit1, cboCrit2, cboCrit3... there are three fields that may or may not be available to input the criteria based on the report that is selected. If the report only has 1 type of criteria, only one field appears. There are steps to ensure that the fields appear or disappear as needed. I have that input in another table that manages the controls. Here is what that looks like -

    Click image for larger version. 

Name:	control.jpg 
Views:	12 
Size:	130.0 KB 
ID:	37350

    Here are the steps I used from my Access class to create that -

    Click image for larger version. 

Name:	controls.jpg 
Views:	13 
Size:	95.7 KB 
ID:	37349
    Last edited by russianthistle; 02-08-2019 at 03:14 PM. Reason: edited to add more information

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If you want to provide db for analysis, follow instructions at bottom of my post.

    Yes, if the code constructs a criteria with varying data type fields, then the static parameter delimiters might not fit.
    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.

  15. #15
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Couldn't see any ' that you say you added so I did a ctrl+F (to find) on your code and found none. In that case, I'd say you haven't progressed.
    This is some kind of homework or school project?

    To each their own I guess, but I consider at a strange (to say the least) way to do things. If you need code to handle/assign sql statements, then why not just write sql statements on the vb side (or use built queries) and avoid the inherent problems that occur when trying to mesh the Access side of things with the ACE (formerly JET) side, not to mention a plethora of DLookup function calls to alter control row sources??

    Either address what appears to be the missing single quotes as per post 4? or I think you'll have to post a zipped copy of your db.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 03-14-2017, 02:36 PM
  2. Replies: 2
    Last Post: 08-24-2015, 09:14 PM
  3. Error 3464 - Data type mismatch
    By JustLearning in forum Forms
    Replies: 1
    Last Post: 01-18-2013, 08:31 PM
  4. Replies: 1
    Last Post: 05-11-2012, 10:59 AM
  5. Data type mismatch error
    By AccessUser123 in forum Queries
    Replies: 1
    Last Post: 05-22-2011, 07:48 PM

Tags for this Thread

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