Results 1 to 13 of 13
  1. #1
    SPW_12 is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    14

    VBA strSQL mismatch

    I am having trouble debugging an error, any help would be appreciated. Thank you.

    Scenario
    I am writing a On_Click VBA that executes an SQL string. One of the criteria (WHERE) uses the BETWEEN / AND condition. I have a combo box that passes the date to a txtbox, and the strSQL reads the txtbox from the WHERE strSQL

    ERROR
    date type mismatch.

    Steps I took
    I looked at both the txtStartDate and txtEndDate and made sure I set the formate to short date.
    I also looked at cboStartDate and cboEndDate, and made those short date format - both to no avail.


    Finally, I had the strSQL point directly to the cboStart and EndDate, hoping the strSQL would recognize the cbo as date...I am beginning to think
    my solution lies in formatting the line:
    BETWEEN '&#&Me.cboStartDate&#&' AND '&#&me.cboEndDate&#&' " or converting cboStartDate and cboEndDate to DATE???? and that I
    am also having trouble with....

    Code

    Private Sub lstAccount_Click()
    On Error GoTo Error_Handler

    strSQL = "SELECT DISTINCTROW tbl_FinRpt.Entity, tbl_FinRpt.Months, tbl_FinRpt.Account, tbl_FinRpt.Amount " & _
    "FROM tbl_CoA INNER JOIN tbl_FinRpt " & _
    "ON tbl_CoA.Account = tbl_FinRpt.Account " & _
    "WHERE tbl_FinRpt.Entity = '" & Me.txtPhysician & "' " & _
    "AND tbl_FinRpt.Account = '" & Me.txtAccount & "' " & _
    "AND tbl_FinRpt.Months BETWEEN '&#&Me.cboStartDate&#&' AND '&#&me.cboEndDate&#&' "

    Error_Handler:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_Here

    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Presuming it's a date/time field:

    "AND tbl_FinRpt.Months BETWEEN #" & Me.cboStartDate & "# AND #" & me.cboEndDate & "#"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    SPW_12 is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    14
    Thank you pbaldy! looks like I parsed the SQL with the # ' one too many times.
    Solved

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help! You weren't really starting and stopping the string correctly, and the apostrophe is only appropriate for text values.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    SPW_12 is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    14

    IN Operator not working: strSQL in VBA

    I see that now.
    I wish I could finish this by myself, but I am having trouble getting the IN statement to work properly. I can click on the account number and it populates my subform; however, I have the listbox set to SIMPLE MULTI SELECT, and when I click another account, the subform query is cleared - an unexpected result. I know it is a simple syntax error, but I can't see it. Can you see where I made my error?

    strSQL = "SELECT DISTINCTROW tbl_FinRpt.Entity, tbl_FinRpt.Months, tbl_FinRpt.Account, tbl_FinRpt.Amount " & _
    "FROM tbl_CoA INNER JOIN tbl_FinRpt ON tbl_CoA.Account = tbl_FinRpt.Account " & _
    "WHERE (((tbl_FinRpt.Account) " & _
    "IN ('" & Me.txtAccount & "')) " & _
    "AND tbl_FinRpt.Entity = '" & Me.txtPhysician & "') " & _
    "AND tbl_FinRpt.Months BETWEEN #" & Me.cboStartDate & "# AND #" & Me.cboEndDate & "#"

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Use this technique to debug the SQL:

    http://www.baldyweb.com/ImmediateWindow.htm

    If you don't spot the error, post the SQL here.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    SPW_12 is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    14
    I am not getting the Immediate window to work...sooo I think I just need to keep inspecting. I can run the SQL, the results are just not what I expected. I can select multiple accounts from a listbox, just not populate the subform with the multiple accounts selected. The SQL works when I select one account, but returns nothing when I select more than one account.

    I think it is a problem with parentheses, here is the code - I will keep inspecting, but if you spot something, please let me know.

    Thanks.

    Code

    strSQL = "SELECT DISTINCTROW tbl_FinRpt.Entity, tbl_FinRpt.Months, tbl_FinRpt.Account, tbl_FinRpt.Amount " & _
    "FROM tbl_CoA INNER JOIN tbl_FinRpt ON tbl_CoA.Account = tbl_FinRpt.Account " & _
    "WHERE ((tbl_FinRpt.Account) " & _
    "IN ('" & Me.txtAccount & "')) " & _
    "AND tbl_FinRpt.Entity = '" & Me.txtPhysician & "' " & _
    "AND tbl_FinRpt.Months BETWEEN #" & Me.cboStartDate & "# AND #" & Me.cboEndDate & "#"

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, I'd like to get that working, because it's one of the best tools to help you debug SQL in code. I suspect the problem is coming from that textbox, and this is an easy way to see what that is. Did you add the Debug.Print line after the string is built?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    SPW_12 is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    14
    I'm working on it but cant get it to work. Yes, I placed Debug.Print strSQL directly under the code (same code I pasted above). I also tried ? strSQL...and i'll need to look more into that. The issue is not that the strSQL returns and error, it is that it runs and produces unexpected results. I expected the code to run all the selected items from lstAccount, but does not, the result is a blank subform; only one account can be selected if I want the subform to populate with results.

    I appreciate your help on the "#" error. this one, I think I will need to play with it more...

    I got the debug to work, here is the SQL from the design view... and I see the problem. Just need to figure out how to get quotes around each selection from the listbox (which is passed to the txt box).

    SELECT DISTINCTROW tbl_FinRpt.Entity, tbl_FinRpt.Months, tbl_FinRpt.Account, tbl_FinRpt.Amount FROM tbl_CoA INNER JOIN tbl_FinRpt ON tbl_CoA.Account = tbl_FinRpt.Account WHERE ((tbl_FinRpt.Account) IN ('acct1100, acct1200') AND tbl_FinRpt.Entity = 'ENTITY : 11.0867 DR. WHITE' AND tbl_FinRpt.Months BETWEEN #1/1/2012# AND #12/1/2012#

    so, I need to get IN('acct1100, acct1200') to IN('acct1100', 'acct1200')
    Last edited by SPW_12; 07-31-2012 at 06:47 PM. Reason: found offender

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, the technique isn't just to find errors. In your case, the SQL produces an unexpected result, but that's also hard to fix without knowing what the SQL is. What's the full code now?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    SPW_12 is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    14
    I posted the full code, minus the declaration of variables, and then posted the SQL produced in the immediate window. This was a useful technique, like I mentioned above, it see that the IN(code) does not place ' ' around each selection -- which is needed since this is text (as you pointed out to me in one of your previous posts). I think to get around this I will have the listbox reference a #, so I wont need the quotes...
    suggestions welcome

    much appreciated for your feedback!

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You haven't posted the code building that string, but you can see one method here (the commented out line for text):

    http://www.baldyweb.com/multiselect.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    SPW_12 is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    14
    That's cool! you are using the IN operator on EmpID - EmpID IN(" & strWhere & ") type number or integer. If it were string, such as my example, the code would need to place " around the selection.

    This is how I was going about it:

    Private Sub lstAccount_Click()
    On Error GoTo Error_Handler
    Dim varItem As Variant
    Dim strList As String
    Dim strSQL As String
    With Me!lstAccount
    If .MultiSelect = 0 Then
    Me!txtAccount = .Value
    Else
    For Each varItem In .ItemsSelected
    strList = strList & .Column(0, varItem) & ","
    Next varItem
    If strList <> "" Then
    strList = Left$(strList, Len(strList) - 1)
    End If
    Me.txtAccount = strList
    End If
    End With

    strSQL = "SELECT DISTINCTROW tbl_FinRpt.Entity, tbl_FinRpt.Months, tbl_FinRpt.Account, tbl_FinRpt.Amount " & _
    "FROM tbl_CoA INNER JOIN tbl_FinRpt ON tbl_CoA.Account = tbl_FinRpt.Account " & _
    "WHERE ((tbl_FinRpt.Account) " & _
    "IN ('" & Me.txtAccount & "')) " & _
    "AND tbl_FinRpt.Entity = '" & Me.txtPhysician & "' " & _
    "AND tbl_FinRpt.Months BETWEEN #" & Me.cboStartDate & "# AND #" & Me.cboEndDate & "#"
    Debug.Print strSQL
    If Me.txtAccount = "" Then
    Me.sfm_UserDefined_Empty.Form.RecordSource = "qry_UserDefined_Empty"
    Exit Sub
    Else
    Me.sfm_UserDefined_Empty.Form.RecordSource = strSQL
    End If
    Exit_Here:
    Exit Sub

    Error_Handler:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_Here

    End Sub

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

Similar Threads

  1. Need to add 2nd criteria to strSQL
    By robsworld78 in forum Forms
    Replies: 8
    Last Post: 08-23-2011, 02:57 PM
  2. DLookup in strSQL for OpenRecordset
    By szucker1 in forum Programming
    Replies: 2
    Last Post: 07-30-2011, 06:00 PM
  3. Type Mismatch....WHY?!!?
    By jgelpi16 in forum Queries
    Replies: 9
    Last Post: 03-07-2011, 09:18 AM
  4. Type Mismatch
    By Alex Motilal in forum Programming
    Replies: 10
    Last Post: 02-13-2011, 05:42 AM
  5. Type mismatch
    By jgelpi16 in forum Programming
    Replies: 1
    Last Post: 08-07-2010, 06:54 AM

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