Union query date issue (1 Viewer)

mattkorguk

mattkorguk

Registered User.
Local time
Today, 08:58
Joined
Jun 26, 2007
Messages
301
Hi All,
I'm building the below query based on a selection made on a form, as I know Access does some interesting things with dates, I already swap the month and day around in the vb if the month field is >12.
This 'appears' to work fine and produces the query below. The issue I'm having is that it's bringing back the correct details. When I chop a section of the query out and paste it into a standard Access query window the dates have swapped round, only for the start date though (01/11/2014 instead of 11/01/2014) This then brings back all records for some reason as the start date is before the end date.
Any suggestion please guys?!
cheers
Matt
Code: 
SELECT 'Inadequate justification of why recommended cancelled' AS [Issue Raised], Sum(IIf([TPC0201]='-1',1,0)) AS Sought, Sum(IIf([TPC0201A]='-1',1,0)) AS Material, Sum(IIf([TPC0201B]='-1',1,0)) AS [S/Check], Sum(IIf([TPC0201C]='-1',1,0)) AS [Rec'd], '1' AS SortNo 
FROM ProtCIC
INNER JOIN _SR ON ProtCIC.URN = [_SR].URN 
WHERE ((([_SR].SR03) Like '*') AND (([_SR].SR04) Like '*') AND (([_SR].SR06) Between #11/01/2014# And #31/01/2014#) AND (([_SR].SR18) Like 'S25' OR ([_SR].SR18)= 'S30')) 
UNION SELECT 'Inadequate justification of why recommended retain' AS [Issue Raised], Sum(IIf([TPC0202]='-1',1,0)) AS Sought, Sum(IIf([TPC0202A]='-1',1,0)) AS Material, Sum(IIf([TPC0202B]='-1',1,0)) AS [S/Check], Sum(IIf([TPC0202C]='-1',1,0)) AS [Rec'd], '2' AS SortNo 
FROM ProtCIC
INNER JOIN _SR ON ProtCIC.URN = [_SR].URN 
WHERE ((([_SR].SR03) Like '*') AND (([_SR].SR04) Like '*') AND (([_SR].SR06) Between #11/01/2014# And #31/01/2014#) AND (([_SR].SR18) Like 'S25' OR ([_SR].SR18)= 'S30')) 
UNION SELECT 'New policy recommended & no recommendation on existing' AS [Issue Raised], Sum(IIf([TPC0203]='-1',1,0)) AS Sought, Sum(IIf([TPC0203A]='-1',1,0)) AS Material, Sum(IIf([TPC0203B]='-1',1,0)) AS [S/Check], Sum(IIf([TPC0203C]='-1',1,0)) AS [Rec'd], '3' AS SortNo
FROM ProtCIC 
INNER JOIN _SR ON ProtCIC.URN = [_SR].URN 
WHERE ((([_SR].SR03) Like '*') AND (([_SR].SR04) Like '*') AND (([_SR].SR06) Between #11/01/2014# And #31/01/2014#) AND (([_SR].SR18) Like 'S25' OR ([_SR].SR18)= 'S30')) 
UNION SELECT 'CIC Cancellation process not followed' AS [Issue Raised], Sum(IIf([TPC0204]='-1',1,0)) AS Sought, Sum(IIf([TPC0204A]='-1',1,0)) AS Material, Sum(IIf([TPC0204B]='-1',1,0)) AS [S/Check], Sum(IIf([TPC0204C]='-1',1,0)) AS [Rec'd], '4' AS SortNo 
FROM ProtCIC 
INNER JOIN _SR ON ProtCIC.URN = [_SR].URN WHERE ((([_SR].SR03) Like '*') AND (([_SR].SR04) Like '*') AND (([_SR].SR06) Between #11/01/2014# And #31/01/2014#) AND (([_SR].SR18) Like 'S25' OR ([_SR].SR18)= 'S30')) 
UNION SELECT 'Other' AS [Issue Raised], Sum(IIf([TPC0205]='-1',1,0)) AS Sought, Sum(IIf([TPC0205A]='-1',1,0)) AS Material, Sum(IIf([TPC0205B]='-1',1,0)) AS [S/Check], Sum(IIf([TPC0205C]='-1',1,0)) AS [Rec'd], '5' AS SortNo 
FROM ProtCIC 
INNER JOIN _SR ON ProtCIC.URN = [_SR].URN WHERE ((([_SR].SR03) Like '*') AND (([_SR].SR04) Like '*') AND (([_SR].SR06) Between #11/01/2014# And #31/01/2014#) AND (([_SR].SR18) Like 'S25' OR ([_SR].SR18)= 'S30'))
ORDER BY 6;
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 08:58
Joined
Nov 30, 2011
Messages
8,494
I would Divide and Conquer, also for dates, you do not need any fancy coding, just a small twist using the Format function..
Code: 
strSQL = "SELECT someField FROM someTable " & _
         "WHERE dateField = " & [COLOR=Red][B]Format([/B][/COLOR]Date(), [COLOR=Red][B]"[COLOR=Blue]\#mm\/dd\/yyyy\#[/COLOR]")[/B][/COLOR]
 
mattkorguk

mattkorguk

Registered User.
Local time
Today, 08:58
Joined
Jun 26, 2007
Messages
301
Thanks for reply Paul.
I've added this into the vb and it seems to have done the job. :D
Cheers
Matt
Code: 
strDay = Val(Left([Forms]![Switchboard-Reports]![selStart], 2))
strMonth = Val(Mid([Forms]![Switchboard-Reports]![selStart], 4, 2))
strYear = Val(Right([Forms]![Switchboard-Reports]![selStart], 4))
strEDay = Val(Left([Forms]![Switchboard-Reports]![selEnd], 2))
strEMonth = Val(Mid([Forms]![Switchboard-Reports]![selEnd], 4, 2))
strEYear = Val(Right([Forms]![Switchboard-Reports]![selEnd], 4))
If strDay < 12 And strMonth < 12 Then
    strStart = strMonth & "/" & strDay & "/" & strYear
    strStart = Format(strStart, "dd/mm/yyyy")
Else
    strStart = strDay & "/" & strMonth & "/" & strYear
    strStart = Format(strStart, "dd/mm/yyyy")
End If
If strEDay > 12 And strEMonth < 12 Then
    strEnd = strEMonth & "/" & strEDay & "/" & strEYear
    strEnd = Format(strEnd, "dd/mm/yyyy")
Else
    strEnd = strEDay & "/" & strEMonth & "/" & strEYear
    strEnd = Format(strEnd, "dd/mm/yyyy")
End If
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 08:58
Joined
Nov 30, 2011
Messages
8,494
Exactly my point, when you can do the same in two lines, you writes lines after lines, and still your data will not be exactly correct, as you still are using the incorrect format and the return is a String. Think about it carefully. Good Luck !
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 09:58
Joined
Aug 11, 2003
Messages
11,695
mattkorguk said:
Thanks for reply Paul.
I've added this into the vb and it seems to have done the job. :D
Cheers
Matt
Code: 
strDay = Val(Left([Forms]![Switchboard-Reports]![selStart], 2))
strMonth = Val(Mid([Forms]![Switchboard-Reports]![selStart], 4, 2))
strYear = Val(Right([Forms]![Switchboard-Reports]![selStart], 4))
strEDay = Val(Left([Forms]![Switchboard-Reports]![selEnd], 2))
strEMonth = Val(Mid([Forms]![Switchboard-Reports]![selEnd], 4, 2))
strEYear = Val(Right([Forms]![Switchboard-Reports]![selEnd], 4))
If strDay < 12 And strMonth < 12 Then
    strStart = strMonth & "/" & strDay & "/" & strYear
    strStart = Format(strStart, "dd/mm/yyyy")
Else
    strStart = strDay & "/" & strMonth & "/" & strYear
    strStart = Format(strStart, "dd/mm/yyyy")
End If
If strEDay > 12 And strEMonth < 12 Then
    strEnd = strEMonth & "/" & strEDay & "/" & strEYear
    strEnd = Format(strEnd, "dd/mm/yyyy")
Else
    strEnd = strEDay & "/" & strEMonth & "/" & strEYear
    strEnd = Format(strEnd, "dd/mm/yyyy")
End If
Click to expand...

Huh wait what? Is your strEnd a string? If so doing the format is quite useless.
Also when working with dates, it isnt dependand on month beeing greater or less than 12.... it is always MM/DD/YYYY
i.e. from the OP
(01/11/2014 instead of 11/01/2014)
Click to expand...
Jan / 11 / 2015 instead of Nov / 01 / 2014

Totaly unrelated to your concerns about months, it is just always wrong... "except" for a happy coincidence that 16/11/2014 just happily becomes Nov / 16 / 2014, because the 16th month dont exist.

Back to your code, it should always be MM/DD/YYYY never DD/MM/YYYY.
One of the savest ways to "create" a proper date is the Dateserial(year, Month, Day) function.... because simply it cant break.
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 09:58
Joined
Aug 11, 2003
Messages
11,695
CDbl(Date) assumes you already have a "proper date"
 
mattkorguk

mattkorguk

Registered User.
Local time
Today, 08:58
Joined
Jun 26, 2007
Messages
301
OK, thanks guys.
I tried to use the DateSerial function, using the results of strDay, StrMonth and strYear and still had the incorrect data being returned.
The resulting union query looks CORRECT, but when run, is INCORRECT.
The Start and End dates are on a form as Short Date, I've had no issues if the query is a standard SELECT, as soon as I build a UNION the days and months get switched if they're both below 12.
Here's how one of the queries is built;
Code: 
strSQLtabPCIC = "SELECT 'Inadequate justification of why recommended cancelled' AS [Issue Raised], Sum(IIf([TPC0201]='-1',1,0)) AS Sought, Sum(IIf([TPC0201A]='-1',1,0)) AS Material, Sum(IIf([TPC0201B]='-1',1,0)) AS [S/Check], Sum(IIf([TPC0201C]='-1',1,0)) AS [Rec'd], '1' AS SortNo FROM ProtCIC INNER JOIN _SR ON ProtCIC.URN = [_SR].URN " _
            & "WHERE ((([_SR].SR03) Like '" & [Forms]![Switchboard-Reports]![SelFirm] & "') " _
            & "AND (([_SR].SR04) Like '" & [Forms]![Switchboard-Reports]![SelAdv] & "') " _
            & "AND (([_SR].SR06) Between #" & strStart _
            & "# And #" & strEnd & "#) " _
            & "AND (([_SR].SR18) Like " & Trim(strref) & ")) " _
            & "UNION SELECT 'Inadequate justification of why recommended retain' AS [Issue Raised], Sum(IIf([TPC0202]='-1',1,0)) AS Sought, Sum(IIf([TPC0202A]='-1',1,0)) AS Material, Sum(IIf([TPC0202B]='-1',1,0)) AS [S/Check], Sum(IIf([TPC0202C]='-1',1,0)) AS [Rec'd], '2' AS SortNo FROM ProtCIC INNER JOIN _SR ON ProtCIC.URN = [_SR].URN " _
            & "WHERE ((([_SR].SR03) Like '" & [Forms]![Switchboard-Reports]![SelFirm] & "') " _
            & "AND (([_SR].SR04) Like '" & [Forms]![Switchboard-Reports]![SelAdv] & "') " _
            & "AND (([_SR].SR06) Between #" & strStart _
            & "# And #" & strEnd & "#) " _
            & "AND (([_SR].SR18) Like " & Trim(strref) & ")) " _
            & "UNION SELECT 'New policy recommended & no recommendation on existing' AS [Issue Raised], Sum(IIf([TPC0203]='-1',1,0)) AS Sought, Sum(IIf([TPC0203A]='-1',1,0)) AS Material, Sum(IIf([TPC0203B]='-1',1,0)) AS [S/Check], Sum(IIf([TPC0203C]='-1',1,0)) AS [Rec'd], '3' AS SortNo FROM ProtCIC INNER JOIN _SR ON ProtCIC.URN = [_SR].URN " _
            & "WHERE ((([_SR].SR03) Like '" & [Forms]![Switchboard-Reports]![SelFirm] & "') " _
            & "AND (([_SR].SR04) Like '" & [Forms]![Switchboard-Reports]![SelAdv] & "') " _
            & "AND (([_SR].SR06) Between #" & strStart _
            & "# And #" & strEnd & "#) " _
            & "AND (([_SR].SR18) Like " & Trim(strref) & ")) " _
            & "UNION SELECT 'CIC Cancellation process not followed' AS [Issue Raised], Sum(IIf([TPC0204]='-1',1,0)) AS Sought, Sum(IIf([TPC0204A]='-1',1,0)) AS Material, Sum(IIf([TPC0204B]='-1',1,0)) AS [S/Check], Sum(IIf([TPC0204C]='-1',1,0)) AS [Rec'd], '4' AS SortNo FROM ProtCIC INNER JOIN _SR ON ProtCIC.URN = [_SR].URN " _
            & "WHERE ((([_SR].SR03) Like '" & [Forms]![Switchboard-Reports]![SelFirm] & "') " _
            & "AND (([_SR].SR04) Like '" & [Forms]![Switchboard-Reports]![SelAdv] & "') " _
            & "AND (([_SR].SR06) Between #" & strStart _
            & "# And #" & strEnd & "#) " _
            & "AND (([_SR].SR18) Like " & Trim(strref) & ")) "
    strSQLtabPCIC2 = "UNION SELECT 'Other' AS [Issue Raised], Sum(IIf([TPC0205]='-1',1,0)) AS Sought, Sum(IIf([TPC0205A]='-1',1,0)) AS Material, Sum(IIf([TPC0205B]='-1',1,0)) AS [S/Check], Sum(IIf([TPC0205C]='-1',1,0)) AS [Rec'd], '5' AS SortNo FROM ProtCIC INNER JOIN _SR ON ProtCIC.URN = [_SR].URN " _
            & "WHERE ((([_SR].SR03) Like '" & [Forms]![Switchboard-Reports]![SelFirm] & "') " _
            & "AND (([_SR].SR04) Like '" & [Forms]![Switchboard-Reports]![SelAdv] & "') " _
            & "AND (([_SR].SR06) Between #" & strStart _
            & "# And #" & strEnd & "#) " _
            & "AND (([_SR].SR18) Like " & Trim(strref) & ")) " _
            & "ORDER BY 6;"
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 09:58
Joined
Aug 11, 2003
Messages
11,695
the intresting part of the code is missing, how do you fill your strStart and strEnd
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 08:58
Joined
Nov 30, 2011
Messages
8,494
I thought me and namliam, helped you open your eyes to the reality, you do not need that piece of code, but all you need is either
Code: 
Dim stDate As Date, endDate As Date
stDate = [Forms]![Switchboard-Reports]![selStart]
endDate = [Forms]![Switchboard-Reports]![selEnd]

[B]............. [/B]someDateField BETWEEN " & Format(stDate, "\#mm\/dd\/yyyy\#")  & " AND " & Format(endDate, "\#mm\/dd\/yyyy\#")
Or
Code: 
Dim stDate As Date, endDate As Date
stDate = [Forms]![Switchboard-Reports]![selStart]
endDate = [Forms]![Switchboard-Reports]![selEnd]

[B]............. [/B]someDateField BETWEEN #" & DateSerial(Year(stDate), Month(stDate), Day(stDate))  & "# AND #" & DateSerial(Year(endDate), Month(endDate), Day(endDate)) & "#"
 
mattkorguk

mattkorguk

Registered User.
Local time
Today, 08:58
Joined
Jun 26, 2007
Messages
301
I went with the latter option, replacing all 197 startdates and all 197 enddates...but as previously mentioned, the SQL UNION query looks correct but returns the WRONG data.
The dates in the union come across as #11/01/2014# And #31/01/2014# but if you segment one of the select queries it looks like this;
Between #01/11/2014# And #31/01/2014#

:banghead:
Code: 
strSQLtabPCIC = "SELECT 'Inadequate justification of why recommended cancelled' AS [Issue Raised], Sum(IIf([TPC0201]='-1',1,0)) AS Sought, Sum(IIf([TPC0201A]='-1',1,0)) AS Material, Sum(IIf([TPC0201B]='-1',1,0)) AS [S/Check], Sum(IIf([TPC0201C]='-1',1,0)) AS [Rec'd], '1' AS SortNo FROM ProtCIC INNER JOIN _SR ON ProtCIC.URN = [_SR].URN " _
            & "WHERE ((([_SR].SR03) Like '" & [Forms]![Switchboard-Reports]![SelFirm] & "') " _
            & "AND (([_SR].SR04) Like '" & [Forms]![Switchboard-Reports]![SelAdv] & "') " _
            & "AND (([_SR].SR06) Between #" & DateSerial(Year(stDate), Month(stDate), Day(stDate)) _
            & "# And #" & DateSerial(Year(endDate), Month(endDate), Day(endDate)) & "#) " _
            & "AND (([_SR].SR18) Like " & Trim(strref) & ")) " _
            & "UNION SELECT 'Inadequate justification of why recommended retain' AS [Issue Raised], Sum(IIf([TPC0202]='-1',1,0)) AS Sought, Sum(IIf([TPC0202A]='-1',1,0)) AS Material, Sum(IIf([TPC0202B]='-1',1,0)) AS [S/Check], Sum(IIf([TPC0202C]='-1',1,0)) AS [Rec'd], '2' AS SortNo FROM ProtCIC INNER JOIN _SR ON ProtCIC.URN = [_SR].URN " _
            & "WHERE ((([_SR].SR03) Like '" & [Forms]![Switchboard-Reports]![SelFirm] & "') " _
            & "AND (([_SR].SR04) Like '" & [Forms]![Switchboard-Reports]![SelAdv] & "') " _
            & "AND (([_SR].SR06) Between #" & DateSerial(Year(stDate), Month(stDate), Day(stDate)) _
            & "# And #" & DateSerial(Year(endDate), Month(endDate), Day(endDate)) & "#) " _
            & "AND (([_SR].SR18) Like " & Trim(strref) & ")) " _
            & "UNION SELECT 'New policy recommended & no recommendation on existing' AS [Issue Raised], Sum(IIf([TPC0203]='-1',1,0)) AS Sought, Sum(IIf([TPC0203A]='-1',1,0)) AS Material, Sum(IIf([TPC0203B]='-1',1,0)) AS [S/Check], Sum(IIf([TPC0203C]='-1',1,0)) AS [Rec'd], '3' AS SortNo FROM ProtCIC INNER JOIN _SR ON ProtCIC.URN = [_SR].URN " _
            & "WHERE ((([_SR].SR03) Like '" & [Forms]![Switchboard-Reports]![SelFirm] & "') " _
            & "AND (([_SR].SR04) Like '" & [Forms]![Switchboard-Reports]![SelAdv] & "') " _
            & "AND (([_SR].SR06) Between #" & DateSerial(Year(stDate), Month(stDate), Day(stDate)) _
            & "# And #" & DateSerial(Year(endDate), Month(endDate), Day(endDate)) & "#) " _
            & "AND (([_SR].SR18) Like " & Trim(strref) & ")) " _
            & "UNION SELECT 'CIC Cancellation process not followed' AS [Issue Raised], Sum(IIf([TPC0204]='-1',1,0)) AS Sought, Sum(IIf([TPC0204A]='-1',1,0)) AS Material, Sum(IIf([TPC0204B]='-1',1,0)) AS [S/Check], Sum(IIf([TPC0204C]='-1',1,0)) AS [Rec'd], '4' AS SortNo FROM ProtCIC INNER JOIN _SR ON ProtCIC.URN = [_SR].URN " _
            & "WHERE ((([_SR].SR03) Like '" & [Forms]![Switchboard-Reports]![SelFirm] & "') " _
            & "AND (([_SR].SR04) Like '" & [Forms]![Switchboard-Reports]![SelAdv] & "') " _
            & "AND (([_SR].SR06) Between #" & DateSerial(Year(stDate), Month(stDate), Day(stDate)) _
            & "# And #" & DateSerial(Year(endDate), Month(endDate), Day(endDate)) & "#) " _
            & "AND (([_SR].SR18) Like " & Trim(strref) & ")) "
    strSQLtabPCIC2 = "UNION SELECT 'Other' AS [Issue Raised], Sum(IIf([TPC0205]='-1',1,0)) AS Sought, Sum(IIf([TPC0205A]='-1',1,0)) AS Material, Sum(IIf([TPC0205B]='-1',1,0)) AS [S/Check], Sum(IIf([TPC0205C]='-1',1,0)) AS [Rec'd], '5' AS SortNo FROM ProtCIC INNER JOIN _SR ON ProtCIC.URN = [_SR].URN " _
            & "WHERE ((([_SR].SR03) Like '" & [Forms]![Switchboard-Reports]![SelFirm] & "') " _
            & "AND (([_SR].SR04) Like '" & [Forms]![Switchboard-Reports]![SelAdv] & "') " _
            & "AND (([_SR].SR06) Between #" & DateSerial(Year(stDate), Month(stDate), Day(stDate)) _
            & "# And #" & DateSerial(Year(endDate), Month(endDate), Day(endDate)) & "#) " _
            & "AND (([_SR].SR18) Like " & Trim(strref) & ")) " _
            & "ORDER BY 6;"
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 08:58
Joined
Nov 30, 2011
Messages
8,494
So what happens when you use this?
Code: 
strSQLtabPCIC = "SELECT 'Inadequate justification of why recommended cancelled' AS [Issue Raised], Sum(IIf([TPC0201]='-1',1,0)) AS Sought, Sum(IIf([TPC0201A]='-1',1,0)) AS Material, Sum(IIf([TPC0201B]='-1',1,0)) AS [S/Check], Sum(IIf([TPC0201C]='-1',1,0)) AS [Rec'd], '1' AS SortNo FROM ProtCIC INNER JOIN _SR ON ProtCIC.URN = [_SR].URN " _
            & "WHERE ((([_SR].SR03) Like '" & [Forms]![Switchboard-Reports]![SelFirm] & "') " _
            & "AND (([_SR].SR04) Like '" & [Forms]![Switchboard-Reports]![SelAdv] & "') " _
            & "AND (([_SR].SR06) Between " & Format(stDate, "\#mm\/dd\/yyyy\#")  & " AND " & Format(endDate, "\#mm\/dd\/yyyy\#") & ") " & _
            & "AND (([_SR].SR18) Like " & Trim(strref) & ")) " _
            & "UNION SELECT 'Inadequate justification of why recommended retain' AS [Issue Raised], Sum(IIf([TPC0202]='-1',1,0)) AS Sought, Sum(IIf([TPC0202A]='-1',1,0)) AS Material, Sum(IIf([TPC0202B]='-1',1,0)) AS [S/Check], Sum(IIf([TPC0202C]='-1',1,0)) AS [Rec'd], '2' AS SortNo FROM ProtCIC INNER JOIN _SR ON ProtCIC.URN = [_SR].URN " _
            & "WHERE ((([_SR].SR03) Like '" & [Forms]![Switchboard-Reports]![SelFirm] & "') " _
            & "AND (([_SR].SR04) Like '" & [Forms]![Switchboard-Reports]![SelAdv] & "') " _
            & "AND (([_SR].SR06) Between " & Format(stDate, "\#mm\/dd\/yyyy\#")  & " AND " & Format(endDate, "\#mm\/dd\/yyyy\#") & ") " & _
            & "AND (([_SR].SR18) Like " & Trim(strref) & ")) " _
            & "UNION SELECT 'New policy recommended & no recommendation on existing' AS [Issue Raised], Sum(IIf([TPC0203]='-1',1,0)) AS Sought, Sum(IIf([TPC0203A]='-1',1,0)) AS Material, Sum(IIf([TPC0203B]='-1',1,0)) AS [S/Check], Sum(IIf([TPC0203C]='-1',1,0)) AS [Rec'd], '3' AS SortNo FROM ProtCIC INNER JOIN _SR ON ProtCIC.URN = [_SR].URN " _
            & "WHERE ((([_SR].SR03) Like '" & [Forms]![Switchboard-Reports]![SelFirm] & "') " _
            & "AND (([_SR].SR04) Like '" & [Forms]![Switchboard-Reports]![SelAdv] & "') " _
            & "AND (([_SR].SR06) Between " & Format(stDate, "\#mm\/dd\/yyyy\#")  & " AND " & Format(endDate, "\#mm\/dd\/yyyy\#") & ") " & _
            & "AND (([_SR].SR18) Like " & Trim(strref) & ")) " _
            & "UNION SELECT 'CIC Cancellation process not followed' AS [Issue Raised], Sum(IIf([TPC0204]='-1',1,0)) AS Sought, Sum(IIf([TPC0204A]='-1',1,0)) AS Material, Sum(IIf([TPC0204B]='-1',1,0)) AS [S/Check], Sum(IIf([TPC0204C]='-1',1,0)) AS [Rec'd], '4' AS SortNo FROM ProtCIC INNER JOIN _SR ON ProtCIC.URN = [_SR].URN " _
            & "WHERE ((([_SR].SR03) Like '" & [Forms]![Switchboard-Reports]![SelFirm] & "') " _
            & "AND (([_SR].SR04) Like '" & [Forms]![Switchboard-Reports]![SelAdv] & "') " _
            & "AND (([_SR].SR06) Between " & Format(stDate, "\#mm\/dd\/yyyy\#")  & " AND " & Format(endDate, "\#mm\/dd\/yyyy\#") & ") " & _
            & "AND (([_SR].SR18) Like " & Trim(strref) & ")) "
    strSQLtabPCIC2 = "UNION SELECT 'Other' AS [Issue Raised], Sum(IIf([TPC0205]='-1',1,0)) AS Sought, Sum(IIf([TPC0205A]='-1',1,0)) AS Material, Sum(IIf([TPC0205B]='-1',1,0)) AS [S/Check], Sum(IIf([TPC0205C]='-1',1,0)) AS [Rec'd], '5' AS SortNo FROM ProtCIC INNER JOIN _SR ON ProtCIC.URN = [_SR].URN " _
            & "WHERE ((([_SR].SR03) Like '" & [Forms]![Switchboard-Reports]![SelFirm] & "') " _
            & "AND (([_SR].SR04) Like '" & [Forms]![Switchboard-Reports]![SelAdv] & "') " _
            & "AND (([_SR].SR06) Between " & Format(stDate, "\#mm\/dd\/yyyy\#")  & " AND " & Format(endDate, "\#mm\/dd\/yyyy\#") & ") " & _
            & "AND (([_SR].SR18) Like " & Trim(strref) & ")) " _
            & "ORDER BY 6;"
 
mattkorguk

mattkorguk

Registered User.
Local time
Today, 08:58
Joined
Jun 26, 2007
Messages
301
Happy, Happy, Happy!! :D

That did the trick, thank you both very much for your help. I shall ensure I use that approach in the future.
A leason learnt.

*The Union now looks like this;
Between #01/11/2014# And #01/31/2014#)

And one of the selects looks like this;
Between #11/01/2014# And #31/01/2014#
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 08:58
Joined
Nov 30, 2011
Messages
8,494
Praise be to God ! Finally you have seen what I have been trying to say since Post#2 :rolleyes:
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 09:58
Joined
Aug 11, 2003
Messages
11,695
I am sorry but tell me... how does this make sence??
Code: 
" & DateSerial(Year(endDate), Month(endDate), Day(endDate)) & "#)

it just doesnt....
Simple solution would be
Code: 
#" & Format(endDate, "MM/DD/YYYY") & "#)
Which is basicaly what Paul also said, but slightly different :)

The dateserial you use it to build your date to either
your variable:
EndDate = Dateserial()
or perhaps in your sql
Code: 
...Dateserial ( " & strYear & "," & strMonth & ", " & StrDay & ")...
 
mattkorguk

mattkorguk

Registered User.
Local time
Today, 08:58
Joined
Jun 26, 2007
Messages
301
Apologies for my misunderstanding, every day is a school day.
Thanks again.
 
You must log in or register to reply here.

Similar threads

MajP
Solved Aggregate Query Returns Different Values When a Field is Included not in Group By
2
Replies
27
Views
1,526
MajP
MajP
BeardedSith
Nesting queries into nested queries, into nested queries - help!
Replies
9
Views
1,140
BeardedSith
BeardedSith
A
Invalid Procedure Call error in select sum query
Replies
1
Views
605
CJ_London
C
C
Multiple Criteria Search Filter on Query
Replies
6
Views
1,088
theDBguy
theDBguy
W
DSum for running totals of groups.
2
Replies
22
Views
3,237
wrlgrain
W

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom