Dange Range as a criteria (1 Viewer)

[fritz.panganiban]

Hi guys,

I'm humbly asking your help on my project

My code goes this way under criteria in query, but it produces incomplete data. Based on my initial finding, it excludes the first week of every month.

Between [Forms]![frmDateRange]![txtFrom] And [Forms]![frmDateRange]![txtTo]

The reason behind using date range is to allow users to have flexibility to view certain date range not just for the current month but also the past months.

 

[Ranman256]

I use this method, and it works.

 

[fritz.panganiban]

i am puzzled why the error happens though the format of dates(no time stamp) matches the format of date using a date picker on my form. However, there are dates that are not existing on query data like weekends and if there are no data at all to include pertaining to other dates.

 

[sneuberg]

I think there must be something else going on. I can't see any way the code you posted would exclude the first week of every month. Are you using american date formats (mm/dd/yyyy). If not that might be the problem. Check out this site. In the link you will find the following function that will convert dates.

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. [email]allen@allenbrowne.com[/email], 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

If this is not the problem could you upload you database so that we could take a further look.

 

[fritz.panganiban]

Yeah it follows that format. Sorry I could upload it here since it contains confidential data. However, there are dates that are not existing on query data like weekends and if there are no data at all to include pertaining to other dates. Would that matter?

 

[arnelgp]

then you have to recheck your data, if it is indeed correct.

 

[fritz.panganiban]

Hi Arnel,

I have checked the result produces by query vs. the data in my database, but again the first week is missing, to be exact is 9 days. It happens on every month

 

[arnelgp]

is that the only criteria or just a fragment of it?

 

[fritz.panganiban]

The only criteria im using is the beginning of the month and the end of the month

 

[sneuberg]

I suggest making a copy of the query and then started testing it piece by piece. For example take out the Between criteria and replace it with


>=[Forms]![frmDateRange]![txtFrom]

run the query with the form open and some date in txtFrom and see what the results are. Just keep poking at it until you figure it out.

 

[Minty]

If you remove the date criteria from your query and sort the query results by date and scroll through do you see the data you are expecting in the order you would expect?

 

[fritz.panganiban]

Bu that produces records beginning that certain day and onward but i just have to view records for certain date range

 

[fritz.panganiban]

Hi Minty,

Yeah i removed the criteria to test and nothing is missing.

 

[sneuberg]

The only criteria im using is the beginning of the month and the end of the month

For tesing I suggest adding an expression to your query so that you can see what the criteria is. For example add


From Date: CStr([Forms]![frmDateRange]![txtFrom] )


Also please tell us how you are determining the first and end dates of the month.

 

[sneuberg]

Bu that produces records beginning that certain day and onward but i just have to view records for certain date range

Yes but this is just for testing. The point is to see if that test is also excluding the first weeks of the months.

 

[fritz.panganiban]

Hi sneuberg

I'm just choosing the first day of the month and the end day of the month using a date picker

 

[fritz.panganiban]

Yes but this is just for testing. The point is to see if that test is also excluding the first weeks of the months.

Yeah, it shows all and nothing is missing. I could not figure out the issue if I set an end date to limit the data which query produces.

 

[arnelgp]

how about this criteria:

Where Format([dateField],"yyyymmdd")>=Format([Forms]![frmDateRange]![txtFrom],"yyyymmdd") And Format([dateField],"yyyymmdd")<=Format([Forms]![frmDateRange]![txtTo],"yyyymmdd")

 

[fritz.panganiban]

how about this criteria:

Where Format([dateField],"yyyymmdd")>=Format([Forms]![frmDateRange]![txtFrom],"yyyymmdd") And Format([dateField],"yyyymmdd")<=Format([Forms]![frmDateRange]![txtTo],"yyyymmdd")

Using this code produces no record since the format in my date picker is mm/dd/yyyy

 

[arnelgp]

it doesn't matter what format you have in the field.