Record Set bringing wrong set of data

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

You have to use either the American format mm/dd/yyyy or better proper SQL international date format yyyy/mm/dd , in constructed queries.

It's how access expects the dates to be. Have a read here: http://allenbrowne.com/ser-36.html

Purely from a make it easier to read perspective - I think you could simplify your code by having the main select SQL and only add the specific where clause using a select case construct?

Thanks Mandy for the advise on readability of code, I will fix that. I am very new to Access to learning couple of things here.

On Date format suggestion & link you have sent - I have change the select query to this but it is bringing no record now.
I think I am still using Format function wrong but can't figure out what is wrong in it.

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")

Build you sql string before using it - it's much easier to debug. Your formatting code is a bit off - see below.

This will also allow you to easily add in a specific where clause without re-writing all of it.

Thanks Minty

I have incorporated the code you have suggested but it is still bringing record set date as mm/dd/yyyy. For Example if I pass the parameters

Me.txt_DtTi as 01/08/2017
Me.txtDays as 0

So dStartDate gets value as 31/07/2017
and dEndDate gets values as 01/08/2017
and
sSql = SELECT * FROM T_Load_Profile Where Cal_date Between #31/07/2017# AND #01/08/2017# Order by LoadProfile,TimeSlot,Cal_date

So we should expect to extract dates row from 31/07/2017 and 01/08/2017 but it is still picking up from 08 Jan 2017 till 31/07/2017. As my table t_load_profile has row for each date. see attached my Table in Excel format as well.


Regards Satwah

LOL - Sorry to laugh(at myself) here but I didn't notice that the formatting in the clauses is still UK style.


Try that. /embarrassed.com/

Thanks Minty - I am sorted now.

Thanks Pat for explanation & good advice. I will take care of it in next build. for the time being Minty response has resolved my issue to move forward.

satwah said:

Thanks Pat for explanation & good advice. I will take care of it in next build. for the time being Minty response has resolved my issue to move forward.

Click to expand...

I would build a simple function as suggested by Allen Browne to handle the conversion , as it also eases your code readability;

Save this in a Module call it something like BasicFunctions. Then in your code this;

would become this