Hi there
I am creating a record set based on users input of Dates.
it is picking up more than the required data. It appears the problem of date format i.e. mm/dd/yyyy where I need to pick up in dd/mm/yyyy . I have applied Format() funtion but still it is not working. can someone help please?
For 01/08/2017 input date, it should start picking records from D-1 i.e. 31/07/2017 upto number of days user have entered. if 0 then it should pick up 31/07/2017 and 01/08/2017.
it appears to me that it is picking American date i.e. mm/dd/yyyy 08th Jan 2017 and ignores row between 01st Aug to 08th Aug 2017. also ignore the end condition i.e. if txtDays is 0 then go up to 01/08/2017. it is picking all the dates for rest of the year.
Please help !
code is
If IsNull(Me.txt_LP) And IsNull(Me.lst_ts) Then
Set rsLp = CurrentDb.OpenRecordset("SELECT * FROM T_Load_Profile Where DateValue(Cal_date) Between FORMAT(#" & DateValue(Me.txt_DtTi) - 1 & "#, 'DD/MM/YYYY') AND FORMAT(#" & DateValue(Me.txt_DtTi) + Me.txtDays & "#, 'DD/MM/YYYY') Order by LoadProfile,TimeSlot,Cal_date")
ElseIf IsNull(Me.txt_LP) Then
Set rsLp = CurrentDb.OpenRecordset("SELECT * FROM T_Load_Profile WHERE DateValue(Cal_date) Between FORMAT(#" & DateValue(Me.txt_DtTi) - 1 & "#,'DD/MM/YYYY') AND FORMAT(#" & DateValue(Me.txt_DtTi) + Me.txtDays & "#,'DD/MM/YYYY') AND timeslot = '" & timeslot & "' Order by LoadProfile, TimeSlot,Cal_date")
ElseIf IsNull(Me.lst_ts) Then
Set rsLp = CurrentDb.OpenRecordset("SELECT * FROM T_Load_Profile WHERE DateValue(Cal_date) Between FORMAT(#" & DateValue(Me.txt_DtTi) - 1 & "#,'DD/MM/YYYY') AND FORMAT(#" & DateValue(Me.txt_DtTi) + Me.txtDays & "#,'DD/MM/YYYY') AND LoadProfile= " & loadprof & " Order by LoadProfile,TimeSlot,Cal_date")
Else
Set rsLp = CurrentDb.OpenRecordset("SELECT * FROM T_Load_Profile WHERE DateValue(Cal_date) Between FORMAT(#" & DateValue(Me.txt_DtTi) - 1 & "#,'DD/MM/YYYY') AND FORMAT(#" & DateValue(Me.txt_DtTi) + Me.txtDays & "#,'DD/MM/YYYY') AND LoadProfile= " & loadprof & " AND timeslot= '" & timeslot & "' Order by LoadProfile, TimeSlot, Cal_date")
End If
I am creating a record set based on users input of Dates.
it is picking up more than the required data. It appears the problem of date format i.e. mm/dd/yyyy where I need to pick up in dd/mm/yyyy . I have applied Format() funtion but still it is not working. can someone help please?
For 01/08/2017 input date, it should start picking records from D-1 i.e. 31/07/2017 upto number of days user have entered. if 0 then it should pick up 31/07/2017 and 01/08/2017.
it appears to me that it is picking American date i.e. mm/dd/yyyy 08th Jan 2017 and ignores row between 01st Aug to 08th Aug 2017. also ignore the end condition i.e. if txtDays is 0 then go up to 01/08/2017. it is picking all the dates for rest of the year.
Please help !
code is
If IsNull(Me.txt_LP) And IsNull(Me.lst_ts) Then
Set rsLp = CurrentDb.OpenRecordset("SELECT * FROM T_Load_Profile Where DateValue(Cal_date) Between FORMAT(#" & DateValue(Me.txt_DtTi) - 1 & "#, 'DD/MM/YYYY') AND FORMAT(#" & DateValue(Me.txt_DtTi) + Me.txtDays & "#, 'DD/MM/YYYY') Order by LoadProfile,TimeSlot,Cal_date")
ElseIf IsNull(Me.txt_LP) Then
Set rsLp = CurrentDb.OpenRecordset("SELECT * FROM T_Load_Profile WHERE DateValue(Cal_date) Between FORMAT(#" & DateValue(Me.txt_DtTi) - 1 & "#,'DD/MM/YYYY') AND FORMAT(#" & DateValue(Me.txt_DtTi) + Me.txtDays & "#,'DD/MM/YYYY') AND timeslot = '" & timeslot & "' Order by LoadProfile, TimeSlot,Cal_date")
ElseIf IsNull(Me.lst_ts) Then
Set rsLp = CurrentDb.OpenRecordset("SELECT * FROM T_Load_Profile WHERE DateValue(Cal_date) Between FORMAT(#" & DateValue(Me.txt_DtTi) - 1 & "#,'DD/MM/YYYY') AND FORMAT(#" & DateValue(Me.txt_DtTi) + Me.txtDays & "#,'DD/MM/YYYY') AND LoadProfile= " & loadprof & " Order by LoadProfile,TimeSlot,Cal_date")
Else
Set rsLp = CurrentDb.OpenRecordset("SELECT * FROM T_Load_Profile WHERE DateValue(Cal_date) Between FORMAT(#" & DateValue(Me.txt_DtTi) - 1 & "#,'DD/MM/YYYY') AND FORMAT(#" & DateValue(Me.txt_DtTi) + Me.txtDays & "#,'DD/MM/YYYY') AND LoadProfile= " & loadprof & " AND timeslot= '" & timeslot & "' Order by LoadProfile, TimeSlot, Cal_date")
End If