changing query in VBA not working with calculated dates (1 Viewer)

[Ruzz2k]

Hi I am trying to run a change an existing query in real time to allow date filtering for 4 different categories. I can get two of them to work. Calibration Date and Icepoint Date. But for The two calculated fields Calibration due date and Icepoint date I cannot get it to filter properly e.g. for 2013 dates it also includes 2014 dates and just does not work properly. I am thinking its due to it being a calculated field but don't have a clue how to fix it. see pasted code for calibration due date filter where Todate and Fromdate are the 2 dates used. I also have the on current code and the exit code to reset the query to its original status.

any help is much appreciated.
Thanks

Public Sub SetDate1()

'Apply date filter and rebuild query in real time

On Error GoTo Err_SetDate1

If IsNull(Me!ToDate) And IsNull(Me!FromDate) Then
MsgBox ("Please Enter Date First"), vbExclamation
GoTo Exit_SetDate1
End If

If IsNull(Me!ToDate) Then Me!ToDate = Me!FromDate
If IsNull(Me!FromDate) Then Me!FromDate = Me!ToDate

Dim A As Date

Me.FromDate = Format([Forms]![frmMain]![FromDate], "dd/mm/yyyy")
Me.ToDate = Format([Forms]![frmMain]![ToDate], "dd/mm/yyyy")
A = [Forms]![frmMain]![FromDate]

If [Forms]![frmMain]![FromDate] = [Forms]![frmMain]![ToDate] Then

wSQL = " SELECT qryR2.[Thermometer ID], qryR2.Range, tblThermometers.Type, qryL2.[Location Description], qryL2.[Location ID], qryLC2.Range, qryL2.Department, qryLC2.[Calibration Date], qryIP2.[Icepoint Date], qryL2.[Responsible Person], IIf(IsNull([qryR2].[Deactivation Date]),-1,0) AS Active, qryR2.[Activation Date], qryR2.[Deactivation Date], IIf([qryLC2].[Pass/Fail]='Fail','Decomission or Repeat',Format(DateAdd('m',[qryR2].[Calibration Term],[qryLC2].[Calibration Date]),'dd/mm/yyyy')) AS [Calibration Due], IIf([tblThermometers].[Icepoint Required]=False,'NA',IIf([qryIP2].[Pass/Fail]='Fail','Repeat Full Calibration',Format(DateAdd('m',[tblThermometers].[Icepoint Term],[qryIP2].[Icepoint Date]),'dd/mm/yyyy'))) AS [Icepoint Due]" & _
" FROM ((tblThermometers RIGHT JOIN (qryL2 RIGHT JOIN qryR2 ON qryL2.[Thermometer ID] = qryR2.[Thermometer ID]) ON tblThermometers.[Thermometer ID] = qryR2.[Thermometer ID]) LEFT JOIN qryIP2 ON qryR2.[Thermometer ID] = qryIP2.[Working Ref]) LEFT JOIN qryLC2 ON (qryR2.[Thermometer ID] = qryLC2.[Working Ref]) AND (qryR2.Range = qryLC2.Range)"

wSQL = wSQL & " WHERE (((IIf(IsNull([qryR2].[Deactivation Date]),-1,0))=[Forms]![frmMain]![Active]) AND ((IIf([qryLC2].[Pass/Fail]='Fail','Decomission or Repeat',Format(DateAdd('m',[qryR2].[Calibration Term],[qryLC2].[Calibration Date]),'dd/mm/yyyy')))" & "=" & "#" & A & "#" & ") AND ((IIf(IsNull([Forms]![frmMain]![RangeFilter]),True,[qryR2].[Range]=[Forms]![frmMain]![RangeFilter]))<>False) AND ((IIf(IsNull([Forms]![frmMain]![TypeFilter]),True,[tblThermometers].[Type]=[Forms]![frmMain]![TypeFilter]))<>False) AND ((IIf(IsNull([Forms]![frmMain]![LocationDescriptionFilter]),True,[qryL2].[Location Description]=[Forms]![frmMain]![LocationDescriptionFilter]))<>False) AND ((IIf(IsNull([Forms]![frmMain]![LocationIDFilter]),True,[qryL2].[Location ID]=[Forms]![frmMain]![LocationIDFilter]))<>False)" & _

" AND ((IIf(IsNull([Forms]![frmMain]![DepartmentFilter]),True,[qryL2].[Department]=[Forms]![frmMain]![DepartmentFilter]))<>False) AND ((IIf(IsNull([Forms]![frmMain]![ResponsiblePersonFilter]),True,[qryL2].[Responsible Person]=[Forms]![frmMain]![ResponsiblePersonFilter]))<>False) AND ((IIf(IsNull([Forms]![frmMain]![ThermometerIDFilter]),True,[qryR2].[Thermometer ID]=[Forms]![frmMain]![ThermometerIDFilter]))<>False));"

rsSQL.SQL = wSQL

ElseIf [Forms]![frmMain]![FromDate] > [Forms]![frmMain]![ToDate] Then

MsgBox ("Your Date Range is Incorrect"), vbCritical

Else

wSQL = " SELECT qryR2.[Thermometer ID], qryR2.Range, tblThermometers.Type, qryL2.[Location Description], qryL2.[Location ID], qryLC2.Range, qryL2.Department, qryLC2.[Calibration Date], qryIP2.[Icepoint Date], qryL2.[Responsible Person], IIf(IsNull([qryR2].[Deactivation Date]),-1,0) AS Active, qryR2.[Activation Date], qryR2.[Deactivation Date], IIf([qryLC2].[Pass/Fail]='Fail','Decomission or Repeat',Format(DateAdd('m',[qryR2].[Calibration Term],[qryLC2].[Calibration Date]),'dd/mm/yyyy')) AS [Calibration Due], IIf([tblThermometers].[Icepoint Required]=False,'NA',IIf([qryIP2].[Pass/Fail]='Fail','Repeat Full Calibration',Format(DateAdd('m',[tblThermometers].[Icepoint Term],[qryIP2].[Icepoint Date]),'dd/mm/yyyy'))) AS [Icepoint Due]" & _
" FROM ((tblThermometers RIGHT JOIN (qryL2 RIGHT JOIN qryR2 ON qryL2.[Thermometer ID] = qryR2.[Thermometer ID]) ON tblThermometers.[Thermometer ID] = qryR2.[Thermometer ID]) LEFT JOIN qryIP2 ON qryR2.[Thermometer ID] = qryIP2.[Working Ref]) LEFT JOIN qryLC2 ON (qryR2.[Thermometer ID] = qryLC2.[Working Ref]) AND (qryR2.Range = qryLC2.Range)"

wSQL = wSQL & " WHERE (((IIf(IsNull([qryR2].[Deactivation Date]),-1,0))=[Forms]![frmMain]![Active]) AND ((IIf([qryLC2].[Pass/Fail]='Fail','Decomission or Repeat',Format(DateAdd('m',[qryR2].[Calibration Term],[qryLC2].[Calibration Date]),'dd/mm/yyyy'))) Between [Forms]![frmMain]![FromDate] And [Forms]![frmMain]![ToDate]) AND ((IIf(IsNull([Forms]![frmMain]![RangeFilter]),True,[qryR2].[Range]=[Forms]![frmMain]![RangeFilter]))<>False) AND ((IIf(IsNull([Forms]![frmMain]![TypeFilter]),True,[tblThermometers].[Type]=[Forms]![frmMain]![TypeFilter]))<>False) AND ((IIf(IsNull([Forms]![frmMain]![LocationDescriptionFilter]),True,[qryL2].[Location Description]=[Forms]![frmMain]![LocationDescriptionFilter]))<>False) AND ((IIf(IsNull([Forms]![frmMain]![LocationIDFilter]),True,[qryL2].[Location ID]=[Forms]![frmMain]![LocationIDFilter]))<>False)" & _

" AND ((IIf(IsNull([Forms]![frmMain]![DepartmentFilter]),True,[qryL2].[Department]=[Forms]![frmMain]![DepartmentFilter]))<>False) AND ((IIf(IsNull([Forms]![frmMain]![ResponsiblePersonFilter]),True,[qryL2].[Responsible Person]=[Forms]![frmMain]![ResponsiblePersonFilter]))<>False) AND ((IIf(IsNull([Forms]![frmMain]![ThermometerIDFilter]),True,[qryR2].[Thermometer ID]=[Forms]![frmMain]![ThermometerIDFilter]))<>False));"

rsSQL.SQL = wSQL

End If

Me.frmLatestCalibrations.Form.RecordSource = "qryLog"

Exit_SetDate1:
Exit Sub
Err_SetDate1:
MsgBox Err.Description
Resume Exit_SetDate1
End Sub

 

[namliam]

Hey, when posting code, please use the code wraps [ code ] and [ /code ], whitout the space

Further more your query is frigging unreadable as it is a mass hash from the query builder without any "cleaning up" to it...
Atleast some formatting like

         wSQL = " SELECT qryR2.[Thermometer ID]" & _
                    " , qryR2.Range, tblThermometers.Type" & _
                    " , qryL2.[Location Description]" & _
                    " , qryL2.....

or another way:

        wSQL = "   SELECT qryR2.[Thermometer ID]"
        wSQL = wSQL & " , qryR2.Range, tblThermometers.Type"
        wSQL = wSQL & " , qryL2.[Location Description]"
        wSQL = wSQL & " , qryL2.....

would go a LONG way to making it more readable and understandable...

Trying to read thru the mess... you are relying on access logic to do conversions... which is probably causing your problem, here
... Format(DateAdd('m',[qryR2].[Calibration Term],[qryLC2].[Calibration Date]),'dd/mm/yyyy')))" & "=" & "#" & A & "#" & ") ....
You are formatting a date to text and comparing that to a "proper" date (as indicated by the #...#
Same thing happens with your between query....

Your problem will probably be fixed if you remove your format thing all together so that you are comparing dates and dates instead of strings and dates

 

[Ruzz2k]

Sorry cant tidy the code off my phone will try later. I tried putting dates directly into the QBE and the same happens so I figured its becoz it's a calculated field it is doing that. I am working with date time rather than just date. I will have a go later and post something more clearer

Thanks

 

[Ruzz2k]

fixed it thanks for pointing me in the right direction I took the formatting out and used cv date and it works.