VBA question on SQL statement multi Joins and Where clauses

[Archide]

Ok, well first off, I am very new to Excel/Access. Have used other scripts/DB's in the past ... granted a while ago. So please know that first when replying as I need responses in kindergarten form.

I think this would go here, but since it's really an SQL question it might need to go in Access. However, I'm getting an Excel error that I'm missing an operator. I've rewritten this about 6 times to make it more and more concise, but having problems with multi joins and multi where clauses.

I think my problems are in the WHERE clause.

I am trying to grab all rows from all tables where the EID matches, the person is active, and their schedule is a current one.

 strSql = "SELECT * FROM ((([tblEmployee] " & _
   " INNER JOIN [tblEmployment]" & _
         " ON [tblEmployee].[EID] = [tblEmployment].[EID]) " & _
    " INNER JOIN [tblHierarchy]" & _
         " ON [tblEmployee].[EID] = [tblHierarchy].[EID]) " & _
    " INNER JOIN [tblSchedules]" & _
         " ON [tblEmployee].[EID] = [tblSchedules].[EID]) " & _
   "WHERE (([tblEmployee].[EID]='" & EmpEID & "')" & _
         " AND ([tblEmployment].[Active]='true')" & _
         " AND ([tblSchedules].[ShiftStartDate]] < '" & Now() & "' < [tblSchedules].[ShiftEndDate]))"

[\code]

Thoughts?

Thanks,

James

 

[Fazza]

hello, James

I think the problem is the last line,
AND ShiftStartDate < now < ShiftEndDate

Try two separate checks, or use BETWEEN

HTH. Regards, Fazza

 

[Fazza]

Hi, James

Another thought on this. Currently the Now() returns text.

Such as : AND ShiftStartDate < '10/05/2014 10:12:14 AM'

So some date is less than some text. This isn't good. You'll need some numeric comparison. Such as by explicitly converting the date to a numeric value.

What I sometimes do is explicitly convert both fields to integers. (You may prefer to stay with dates, depending on your source data table.)

Something like

AND CLng(ShiftStartDate) <= CLng(Date) AND CLng(ShiftEndDate) >= CLng(Date)

regards

 

[Archide]

Thanks Fazza, looks like I made a few mistakes:

Had an extra "]" at the end of ShiftStartDate, and I then added the following:

  Dim TodaysDate As Date
  TodaysDate = Date

Then changed the last line to:

" AND ([tblSchedules].[ShiftStartDate] < " & TodaysDate & ")"

So now it compares, I wrote both to a cell in Excel and it auto-formatted to Short Date on both (I had to change Access' format to short date)

However now it's failing to match TodaysDate as > the ShiftStartDate, which is an older date. This looks from what I can see that it's now comparing two dates instead of a date/text but it's erroring out as the recordset is not being filled by anything.

Any more thoughts?

 

[Archide]

Hrm - so .. if I use a > sign it works. Which means to me they still are not comparing short date to short date, the formatting is then just some "display format" and I am not comparing data in apples to apples because today's date will always be > some date in the past.

Updated SQL string:

    strSql = "SELECT * FROM ((([tblEmployee] " & _
        " INNER JOIN [tblEmployment]" & _
            " ON [tblEmployee].[EID] = [tblEmployment].[EID]) " & _
        " INNER JOIN [tblHierarchy]" & _
            " ON [tblEmployee].[EID] = [tblHierarchy].[EID]) " & _
        " INNER JOIN [tblSchedules]" & _
            " ON [tblEmployee].[EID] = [tblSchedules].[EID]) " & _
        "WHERE ([tblEmployee].[EID]='" & EmpEID & "')" & _
            " AND ([tblEmployment].[Active]=True)" & _
            " AND ([tblSchedules].[ShiftStartDate] > " & TodaysDate & ")"

Debug.print yields:

SELECT * FROM ((([tblEmployee]  INNER JOIN [tblEmployment] ON [tblEmployee].[EID] = [tblEmployment].[EID])  INNER JOIN [tblHierarchy] ON [tblEmployee].[EID] = [tblHierarchy].[EID])  INNER JOIN [tblSchedules] ON [tblEmployee].[EID] = [tblSchedules].[EID]) WHERE ([tblEmployee].[EID]='******') AND ([tblEmployment].[Active]=True) AND ([tblSchedules].[ShiftStartDate] > 6/16/2014)

When I take the two cells in Excel where I set to ShiftStartDate and TodaysDate I can convert to general format and TodaysDate is showing as a larger number.

Gahh!!

 

[TerryHogarth21]

Are you querying a SQL Server or Access?

Try replacing your last line with this below

 " AND ([tblSchedules].[ShiftStartDate] > " & "'" Date & "')"

 

[Archide]

Access, and that gave me a type mismatch, perhaps because of the single quotes - so I took those out and switched the compare operator to < instead of > and still getting recordset not filled error.

 

[Archide]

Offering up a solution which worked:

I ended up writing up the date comparison in Access and used what worked there. Thank you for all the help, I have a much better idea now of some of my failures. Here's the code which worked (added in if not Null)

 strSql = "SELECT * FROM ((([tblEmployee] " & _
        " INNER JOIN [tblEmployment]" & _
            " ON [tblEmployee].[EID] = [tblEmployment].[EID]) " & _
        " INNER JOIN [tblHierarchy]" & _
            " ON [tblEmployee].[EID] = [tblHierarchy].[EID]) " & _
        " INNER JOIN [tblSchedules]" & _
            " ON [tblEmployee].[EID] = [tblSchedules].[EID]) " & _
        "WHERE ([tblEmployee].[EID]='" & EmpEID & "')" & _
            " AND ([tblSchedules].[ShiftStartDate] < Date()) " & _
            " AND (([tblSchedules].[ShiftEndDate] > Date()) OR (IsNull([tblSchedules].[ShiftEndDate])=True))" & _
            " AND (IsNull([tblSchedules].[ShiftStartDate])=False);"

 

[Fazza]

Good work, James

Just in case, be sure at the date limits the comparisons are what you want. It is Start < Date and End > Date. You may want Start <= Date and End >= Date

(Right at the end) I've not seen the IsNull function used before like that. Normal is : OR Is Null [tblSchedules].[ShiftEndDate] AND Not Is Null [tblSchedules].[ShiftStartDate]

I think calling a function will slow things down.

It makes no difference to performance but another thing is to have table aliases if you want. So when you first reference a table in a FROM clause after a space provide a shorter name (alias) that you use elsewhere. So to give the idea, you could change the current SQL to

    strSql = "SELECT * FROM ((([tblEmployee] EM" & _
            " INNER JOIN [tblEmployment] ET " & _
            " ON EM.[EID] = ET.[EID]) " & _
            " INNER JOIN [tblHierarchy] H" & _
            " ON EM.[EID] = H.[EID]) " & _
            " INNER JOIN [tblSchedules] S" & _
            " ON EM.[EID] = S.[EID]) " & _
            "WHERE (EM.[EID]='" & EmpEID & "')" & _
            " AND (S.[ShiftStartDate] < Date()) " & _
            " AND ((S.[ShiftEndDate] > Date()) OR (IsNull(S.[ShiftEndDate])=True))" & _
            " AND (IsNull(S.[ShiftStartDate])=False);"

 

[Fazza]

hey, James

I think I screwed up about the nulls. Got mislead by the syntax of the IsNull functions! Normal is : OR tblSchedule.ShiftEndDate Is Null AND tblSchedule.ShiftStartDate Is Not Null

Makes no difference, I think, to instead have : OR tblSchedule.ShiftEndDate Is Null AND Not tblSchedule.ShiftStartDate Is Null

regards