Display Record between two dates

[saleemraza]

I am using VB6 and Access database. In a sales form I have objects like DatePicker1, DatePicker2, DataGrid1, Adodc1, Command1. Adodc1 has Command string connected to Jet 4.0, and database (dbSales) and table tblSls. how can I get the record between two dates given on run-time thru DatePickers? Pls help by sending code.

[gibra]

You can use WHERE ... BETWEEN, like this:

    Dim sql As String
    sql = "SELECT fields_list "
    sql = sql & " FROM table 
    sql = sql & " WHERE field_date BETWEEN #" & DatePicker1.Date & "# AND #" & DatePicker2.Date & "#"

[SamOscarBrown]

gibra has done your homework for you...good job, gib.

BUT, this forum is not generally designed for folks to come and 'get code', but to 'get help' writing their own code (or in the case of the code bank, 'steal' some really good stuff!!!). What we'd like to see in this section is your efforts at trying to accomplish your own project and as you run into trouble, seek assistance. (Of course many (including I) will 'give you code', but normally we refrain from doing it all for you).

As a suggestion (if you are just learning VB6), steer clear of the ADODC control....use your own code instead...you may find it much more easier and flexible...just a suggestion. Also, "I" prefer the flexgrid options over the Data Bound Grids as well. For ME, it just seems more 'flex'ible.

Sammi

[dilettante]

The sample code in post #2 contains time bombs though.

Jet SQL date/time literals must be formatted in Invariant Locale format: mm/dd/yyyy. But the example relies on Date to String coercion, which will employ the user's current language and locale settings. In many cases this might be dd/mm/yyy or dd-mm-yyy and so on.

While you can get lucky some of the time, this can fail miserably other times. Consider January 12, 2016. If you end up feeding Jet #12/1/2016# because you are in the UK for example Jet will take this as January 12, 2016.

January 13, 2016 fed in as #13/1/2016# will work, but only because the date/time parser knows 13 is not a valid month number.


This is the real problem with the ADODC, not data binding. ADODCs are only meant for trivial cases. What you want instead is a DataEnvironment which supports parameter queries via Command objects. Sadly this seems to be a lost art, so people end up advocating ASP script style cobbled up SQL and manual ADO object fiddling without any data binding.

I can only assume this comes from poor examples endlessly copy/pasted over the last two decades. Copy/paste is what passes for "learning" in far too much of the VB6 community. The advanced features in VB6 don't lend themselves to such "copy/paste learning." You need classroom lecture and hands-on demonstration or else (egad!) you have to break down and do a lot of reading.


There is no single "right" way to go about these things. It is your problem to learn your options and make a choice.

However there are definately "wrong" ways, as shown above for example where locale issues are casually ignored or not understood.

[gibra]

I do not understand these observations, in my view totally unnecessary.

If you read carefully, my code will not claiming to be working code, as you claim.
I just wanted to provide a starting point, so saleemraza can start studying in the right direction.


@SamOscarBrown

BUT, this forum is not generally designed for folks to come and 'get code', but to 'get help' writing their own code

I find it ridiculous that just you say that, because yourself have supplied ready code several times, and zip projects, too.
Just read your post!

And like you, many other in this forum, especially the older members!

[SamOscarBrown]

errr..sorry..yup, I do give examples of code, for sure (I even mentioned that), but USUALLY my examples are not what the OP needs to COMPLETE his//her mission, but rather to give examples of how to do something very similar. Sorry if I hit a sour note.

[IkkeEnGij]

    Dim sql As String
    sql = "SELECT fields_list "
    sql = sql & " FROM table 
    sql = sql & " WHERE field_date BETWEEN #" & DatePicker1.Date & "# AND #" & DatePicker2.Date & "#"

DatePicker1.Date ?

[dilettante]

Probably air-code and not actual code.

[gibra]

DatePicker1.Date ?

but it is so difficult to understand that pseudo-code?

[IkkeEnGij]

no, its just that to me:
'DatePicker1.Date'
looks more like a property of an existing control than pseudo code
but i'm sure OP will know it's just pseudo code

[dilettante]

I'd worry more about the incorrect results it can produce.

[IkkeEnGij]

true,but
you have already given the solution in post#4 (Invariant Locale format)
although i prefer the international standard date notation
http://www.cl.cam.ac.uk/~mgk25/iso-time.html
but these solutions can still produce surprise results
specially if the data in the table includes a time portion
i often see a default value of Now()
and that can produce non desirable results such as:
Between 2016-02-01 And 2016-02-10
wich will almost certainly return none of the records from 2016-02-10
exept if there are records from 2016-02-10 that have been inserted at exactly midnight