Hi,
This is what I'm trying to accomplish. I have a table that contains different locations. Each location has a unique ID named LocID
Each location may contain one or more Positions. These Positions have an Activation Date and Posssibly a Deactivation Date.
Once a month we get invoices against these Positions. My query is suppose to filter the table by:
1. Location ID
"Select * from TableA where TableA.LocID = " & intLocID &
Where intLocid is a variable selected by the User. This works, no problems
2. Get all the locations that were Active for the particular month.
" AND ((TableA.PosActivationDate)< " & "#" & strLastDay & "#" & ")" & _
Where strLastDay is the last day of the Month
3. Make sure that the positions are not Deactivated prior to the beginning of the month, or if they were deactivated during the month in question. If they were deactivated during the month in question, we need to include them in our results.
" AND (((Isnull(PosDeActivationDate) = true)
OR (PosDeActivationDate between " & "#" & dtdFirstDate & "#" & _
& "AND " & "#" & dtdLastDate & "#" & ")))",
If I asked for all the positions that were activated prior to the end of the month, were PosDeactivationDate is NULL, it works.
If I asked for all the positions that were activated prior to the end of the month, were PosDeactivationDate is between the first day and last day of the month, it works.
The problem is when I ask for all the positions were PosDeactivationDate = NULL OR
PosDeactivationdate between the first and last day of the month. It would only show the positions where Deactivation is Null, and it ignores everything after the OR.
strSql = "Select * from TableA WHERE (((TableA.LocID)= " & intLocID & ")" & _
" AND ((TableA.PosActivationDate)< " & "#" & strLastDay & "#" & ")" & _
" AND((Isnull(PosDeactivationDate) =True
OR
(PosDeactivationDate between " & "#" & strFirstDay & "#" & " AND " & "#" & strLastDay & "#" & "))))"
Any ideas?
Thanks
This is what I'm trying to accomplish. I have a table that contains different locations. Each location has a unique ID named LocID
Each location may contain one or more Positions. These Positions have an Activation Date and Posssibly a Deactivation Date.
Once a month we get invoices against these Positions. My query is suppose to filter the table by:
1. Location ID
"Select * from TableA where TableA.LocID = " & intLocID &
Where intLocid is a variable selected by the User. This works, no problems
2. Get all the locations that were Active for the particular month.
" AND ((TableA.PosActivationDate)< " & "#" & strLastDay & "#" & ")" & _
Where strLastDay is the last day of the Month
3. Make sure that the positions are not Deactivated prior to the beginning of the month, or if they were deactivated during the month in question. If they were deactivated during the month in question, we need to include them in our results.
" AND (((Isnull(PosDeActivationDate) = true)
OR (PosDeActivationDate between " & "#" & dtdFirstDate & "#" & _
& "AND " & "#" & dtdLastDate & "#" & ")))",
If I asked for all the positions that were activated prior to the end of the month, were PosDeactivationDate is NULL, it works.
If I asked for all the positions that were activated prior to the end of the month, were PosDeactivationDate is between the first day and last day of the month, it works.
The problem is when I ask for all the positions were PosDeactivationDate = NULL OR
PosDeactivationdate between the first and last day of the month. It would only show the positions where Deactivation is Null, and it ignores everything after the OR.
strSql = "Select * from TableA WHERE (((TableA.LocID)= " & intLocID & ")" & _
" AND ((TableA.PosActivationDate)< " & "#" & strLastDay & "#" & ")" & _
" AND((Isnull(PosDeactivationDate) =True
OR
(PosDeactivationDate between " & "#" & strFirstDay & "#" & " AND " & "#" & strLastDay & "#" & "))))"
Any ideas?
Thanks
