Manage IN Clause With VBA

[joeKra]

Hi
i am trying to dynamically change the IN sql from within VBA using parameters. for some reason i have no luck, no errors shows up, but it's actually not picking up the criteria. can someone guide me what i am doing wrong here ?

thanks in advance !

THE SQL IN STATEMENT

In (select RemID from [ReminderAssignees] Where RemDate between [Date1] and [Date2] And [sDismiss] )));

THE VBA CODE

    qdf.Parameters("date1") = Date1
    qdf.Parameters("date2") = Date2
    
    Select Case iDismissed
        Case 0, 1
            qdf.Parameters("sDismiss") = "1=1"
        Case 2
            qdf.Parameters("sDismiss") = "(not isdate(Dismiss) or Dismiss > #" & Now & "#)"
        Case 3
            qdf.Parameters("sDismiss") = " isdate(Dismiss) and  Dismiss < #" & Now & "#"
    End Select
    
    qdf.Execute

 

[llkhoutx]

your SQL string is not bejng constructed properly. Look at the actual "constructed" SQL string to see what you're building. Your error should then be obvious to you.

 

[joeKra]

Thanks for reply:

here's the SQL as an whole. please point me where i am wrong

PARAMETERS [sDismiss] Value, [date1] DateTime, [date2] DateTime;
INSERT INTO tmpReminderList ( RemID, Tbl, Fld, PrimKey, Description, Reminding, mValue, Remarks, Created, [User], Assignees )
SELECT Reminders.RemID, Reminders.Tbl, Reminders.Fld, Reminders.PrimKey, Reminders.Description, Reminders.Reminding, Reminders.mValue, Reminders.Remarks, Reminders.Created, Reminders.User, Nz(DCount("*","ReminderAssignees","RemID = " & [remid]),0) AS Assignees
FROM Reminders
WHERE (((Reminders.RemID) In (select RemID from [ReminderAssignees] Where RemDate between [Date1] and [Date2] and [sDismiss])));

 

[gemma-the-husky]

eg

between [Date1] and [Date2]

needs to look like

between #1/1/2014# and #31/12/2014#

note that ambiguous dates WILL be treated as US dates. so 8/1/2014 is August 1st, not 8th of January. If you want UK dates, you need to construct the string slightly differently to force the date to be treated as a UK date

----
where you have

where remdate between date1 and date2 and BooleanTest, you may want to add explicit brackets to ensure the evaluation is what you expect.

where (remdate between date1 and date2) and BooleanTest

 

[joeKra]

Thanks for trying to help !!
Sorry, but It's not even close to my direction.
1. I'm in USA so I don't even understand what you want to point out with date format
2. The 3rd parameter (sDismiss) is not a Boolean I want to pass a chunk of string (optional criteria) using the parameter (review the initial post where the vba is provided)

 

[smig]

I never used qdf.parameters
What I do and work like a charm is making these parapeters as public vars and use public functions for the query to read them.

 

[namliam]

The date thing

In Europe and most of the rest of the world our dates are writen DD/MM/YYYY (or some other character instead of /). This is a HUGE reason for non-us-citizens to have trouble with this kind of situation, since they send DD/MM/YYYY to the sql.
Where SQL requires US Format (MM/DD/YYYY), for us 10-1-2015 is 10 jan 2015, not 1 oct 2015. However SQL defaults to US dates, causing obvious issues.
While 15-1-2015 is obviously 15 jan 2015, since no 15th month exists and SQL will "fix" this to 1/15/2015 since it too knows no 15th month is valid.
This in turn causes (even greater) confusion since sometimes it works, sometimes it dont.

Since this is a european forum ... assumptions are quickly made dispite your location

-- That for the fun and entertainment of today --

You cannot build SQL this way the sDismiss will be treated as a string, not as a logical part of your query

I.e.
where you are looking for
Where RemDate between #12/01/2014# and #12/15/2014#
and isdate(Dismiss) and Dismiss < #12/11/2014 07:16:25#

You are actually getting
Where RemDate between #12/01/2014# and #12/15/2014#
and "isdate(Dismiss) and Dismiss < #12/11/2014 07:16:25#"

Which makes a HUGE difference

The best way to go about this is to "fix" the sql the proper way and completely and stick it into the query object as you need it at that moment in time.

Now since you are in the US and you are probably using a date picker and/or are entering the dates already in US format, you probably dont need the Format in the following:

    Dim mySQL as string
    mySQL = " INSERT INTO tmpReminderList ( RemID, Tbl, Fld, PrimKey, Description, Reminding, mValue, Remarks, Created, [User], Assignees ) " & _
            " SELECT Reminders.RemID, Reminders.Tbl, Reminders.Fld, Reminders.PrimKey, Reminders.Description, Reminders.Reminding, Reminders.mValue, Reminders.Remarks, Reminders.Created, Reminders.User, Nz(DCount(""*"",""ReminderAssignees"",""RemID = "" & [remid]),0) AS Assignees " & _
            " FROM Reminders " & _ 
            " WHERE Reminders.RemID In (select RemID " & _
                                      " from [ReminderAssignees] " & _ 
                                      " Where RemDate between #" & Format(Date1, "MM/DD/YYYY") & "# and #" & Format(Date2, "MM/DD/YYYY") & "# "  

    Select Case iDismissed
        Case 0, 1
            ' Do nothing, nothing to add to the sql
        Case 2
            mysql = mysql & " AND (     not isdate(Dismiss) " & _ 
                                   " or Dismiss > #" & Format(Now(), "MM/DD/YYYY HH:MM:SS") & "#)"
        Case 3
            mysql = mysql & " AND isdate(Dismiss) " & _ 
                            " and  Dismiss < #" & Now & "#"
    End Select

    Currentdb.execute (mySQL)

I am foregoing the discusion about
1) why you would need an insert into statement like this
2) the logic of the where statement at case 2 in particular but also case 3
3) the (ab)use of an in statement that should be a join IMNSHO
4) the use of restricted words (user) hence the need to use []
5) the non-use of a naming convention