Record Set bringing wrong set of data (1 Viewer)

satwah

Registered User.
Local time
Today, 20:00
Joined
Aug 22, 2017
Messages
17
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
 

Minty

AWF VIP
Local time
Today, 20:00
Joined
Jul 26, 2013
Messages
10,355
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?
 

satwah

Registered User.
Local time
Today, 20:00
Joined
Aug 22, 2017
Messages
17
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")
 

Minty

AWF VIP
Local time
Today, 20:00
Joined
Jul 26, 2013
Messages
10,355
Build you sql string before using it - it's much easier to debug. Your formatting code is a bit off - see below.
Code:
Dim sSql        As String
Dim dStartDate  As Date
Dim dEndDate    As Date

dStartDate = Me.txt_DtTi - 1
dEndDate = Me.txt_DtTi + Me.txtDays

sSql = "SELECT * FROM T_Load_Profile Where Cal_date Between " & Format(dStartDate, "\#dd\/mm\/yyyy\#") & " AND " & Format(dEndDate, "\#dd\/mm\/yyyy\#") & " Order by LoadProfile,TimeSlot,Cal_date"

Debug.Print sSql

Set rsLp = CurrentDb.OpenRecordset(sSql)

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

satwah

Registered User.
Local time
Today, 20:00
Joined
Aug 22, 2017
Messages
17
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
 

Minty

AWF VIP
Local time
Today, 20:00
Joined
Jul 26, 2013
Messages
10,355
LOL - Sorry to laugh(at myself) here but I didn't notice that the formatting in the clauses is still UK style.

Code:
sSql = "SELECT * FROM T_Load_Profile Where Cal_date Between " & Format(dStartDate, "\#mm\/dd\/yyyy\#") & " AND " & Format(dEndDate, "\#mm\/dd\/yyyy\#") & " Order by LoadProfile,TimeSlot,Cal_date"

Try that. /embarrassed.com/
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:00
Joined
Feb 19, 2002
Messages
42,973
The problem exists only with STRING dates. If you have controls on forms that are bound to date data type fields or have their format set to Short Date, then referencing them from a query should work fine. However, if you build the SQL String in code, then the dates will be strings and that will REQUIRE that they be specifically formatted to mm/dd/yyyy or yyyy/mm/dd. I suggest the latter since it is unambiguous and even if you are working with a string, dates in that format will sort correctly and will compare correctly. String dates in the format of mm/dd/yyyy or dd/mm/yyyy will NOT compare correctly and they will not sort correctly because strings are sorted character by character, left to right.

Dates are stored internally as double precision numbers. The integer part is the number of days since Dec 30, 1899 (for Access - the origin date is different for SQL Server and Excel). The decimal is the elapsed time since midnight.

How dates are formatted for human consumption is dependent on your Windows settings. Access can override these but that is the path to problems. Best to use the generic format of "Short Date" rather than specific dd/mm/yy format for controls on forms or reports.
 

satwah

Registered User.
Local time
Today, 20:00
Joined
Aug 22, 2017
Messages
17
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.
 

Minty

AWF VIP
Local time
Today, 20:00
Joined
Jul 26, 2013
Messages
10,355
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.

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

Code:
Public Function SQLDate(varDate As Variant) As String
'Purpose:    Return a delimited string in the date format used natively by JET SQL.
'Argument:   A date/time value.
'Note:       Returns just the date format if the argument has no time component,
'                or a date/time format if it does.
'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function
Save this in a Module call it something like BasicFunctions. Then in your code this;
Code:
sSql = "SELECT * FROM T_Load_Profile Where Cal_date Between " & Format(dStartDate, "\#mm\/dd\/yyyy\#") & " AND " & Format(dEndDate, "\#mm\/dd\/yyyy\#") & " Order by LoadProfile,TimeSlot,Cal_date"

would become this
Code:
sSql = "SELECT * FROM T_Load_Profile Where Cal_date Between " & SQLDate(dStartDate) & " AND " & SQLDate(dEndDate) & " Order by LoadProfile,TimeSlot,Cal_date"
 

Users who are viewing this thread

Top Bottom