[RESOLVED] VB6/Access SQL string problem

I'm writing a data base export module.
It's in VB6 using an Access 2000 DB
It has a table named 'tblRules' with a field named 'Updated'
The idea is to export only new or updated Rules from the table.
Here's the SQL string code:

Code:

---

    sSQL = "SELECT * FROM tblRules WHERE Updated BETWEEN #" & dtpStart.Value & "# AND #" & dtpEnd.Value & "#"

---

And here's a sample of the built string:

Code:

---

    SELECT * FROM tblRules WHERE Updated BETWEEN #9/20/2008# AND #10/11/2008#

---

The problem is, it's pulling all the records, including ones dated 9/18/2008

I just changed the code to this, and it works:

Code:

---

    sSQL = "SELECT * FROM tblRules WHERE Updated >= #" & dtpStart.Value & "# AND Updated <= #" & dtpEnd.Value & "#"

---

But I'd still like to know what was wrong with the original code.

I found the problem
The 'Updated' field was incorrectly set as a text field instead of a date field

Good stuff, thanks for letting us know.

By the way, your code is not putting the date values into sSQL appropriately, while it seems to work for you at the moment it isn't guaranteed to carry on doing so - if your code ever runs on a computer with a different date format in Regional Settings, your code will suddenly make mistakes and/or have errors.

What you should be doing is formatting the date values, as explained in the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of this forum)

Quote:

---

Originally Posted by si_the_geek

By the way, your code is not putting the date values into sSQL appropriately, while it seems to work for you at the moment it isn't guaranteed to carry on doing so - if your code ever runs on a computer with a different date format in Regional Settings, your code will suddenly make mistakes and/or have errors.

What you should be doing is formatting the date values, as explained in the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of this forum)

---

Thx Si,
I must be slow today, I've looked through the links in your sig.
But I don't understand what you're saying should be done.

My current methods of saving 'Updated' and testing it are.

Code:

---

        'saving it
    rsAtrib.Fields("Updated") = Date

    'Testing it, the dtp's are DTPicker controls
    sSQL = "SELECT * FROM tblRules WHERE Updated BETWEEN #" & dtpStart.Value & "# AND #" & dtpEnd.Value & "#"

---

The only thing I can think to do would be to change the 'Updated' field to a long then use:

Code:

---

        'saving it
    rsAtrib.Fields("Updated") = clng(Date)

    'Testing it, the dtp's are DTPicker controls
    sSQL = "SELECT * FROM tblRules WHERE Updated BETWEEN " & clng(dtpStart.Value) & " AND " & clng(dtpEnd.Value)

---

How would you do these?

Format the value in the datetimepicker to a specified format (MM/DD/YYYY or YYYY-MM-DD)

Quote:

---

Originally Posted by GaryMazzone

Format the value in the datetimepicker to a specified format (MM/DD/YYYY or YYYY-MM-DD)

---

So you're saying my test should be:

Code:

---

sSQL = "SELECT * FROM tblRules WHERE Updated BETWEEN #" & _
        Format(dtpStart.Value, "mm/dd/yyyy") & "# AND #" & _
        Format(dtpEnd.Value, "mm/dd/yyyy") & "#"

---

If so, I'd also need to make sure it's saved to the same format:

Code:

---

    rsAtrib.Fields("Updated") = Format(Date, "mm/dd/yyyy")

---

But Si say's not to use Format when saving to a date field :P

No need to format when saving. The SQL statement will always expect US date format when query the database for a result so 1/7/2008 is Jan 7th and not July 1st (as in the European format).

Quote:

---

Originally Posted by GaryMazzone

No need to format when saving. The SQL statement will always expect US date format when query the database for a result so 1/7/2008 is Jan 7th and not July 1st (as in the European format).

---

So:

Code:

---

    rsAtrib.Fields("Updated") = Date

---

will be saved as "mm/dd/yyyy", even if the user making the update has a computer set to "dd/mm/yyyy" ?

No the format has nothing to do with how the data is saved in the database just how it is displayed to a user.

Thx Gary,

But it does bring up another question.
Those DTPickers are set to CustomFormat = "M/d/yyy"

So would I still need the Format's in the test string?

And what's going to happen when a "dd/mm/yyyy" computer user tries using my DTPickers?

If the dd/mm/yy setting is set you need to use the format to one of the standards (US or ANSI). If used on a computer with US settings then it will not make a difference.

I think I've got it.
I'd need to use the Format()'s in the SQL test string IF my DTPickers where allowed to config themselves to the users date/time format.

Right now the DTPickers are set to CustomFormat = "M/d/yyy"
so the user see something like 'Sep 20, 08'

So if I want the app to be internationally friendly I should detect the computer date settings and change the CustomFormat string to "d/M/yyy" when needed and keep the Format()'s in the SQL string.

Pls tell me I've got it right now :)

P.S. the date pickers are set by the user so they can select a date range to export,

Not quite... you always need the Format.

The CustomFormat of the DTPicker only affects the user (how it is displayed, what they can enter, etc), and not your code - because the .Value property is always a Date.

As a Date does not have a format, your code that treats it as a Date is fine (which includes rsAtrib.Fields("Updated") = Date , as .Fields uses the same data-type as the field it refers to).

The problems come when you treat it as a String (like when you append it to an SQL statement), as it needs to be converted to a String first - which is when it gets a format. If you don't specify which format to use (with the Format function), it will be given whatever format is specified in Regional Settings, and that may not be valid for the database system (which is when you get bugs/errors).


My preference for DTPickers is to use the format that the user usually uses - by selecting dtpShortDate (which uses the Regional Settings as set on their computer).

Sorry, had to run some errands,

I want to make sure I understand so I don't have problems later.

The issue is, the DTPickers value is actually a string which the data base can misinterpret?

If so, couldn't this cause a problem:

Code:

---

    rsLog.Fields("TimeEntered") = dtpEntered.Value

---

I'd make it:
rsLog.Fields("TimeEntered") = Format(dtpEntered.Value, "mm/dd/yyyy")

But Si says not to use Format while saving a date.
So should I go? :

Code:

---

    Dim dtMyDate as Date

    dtMyDate  = Format(dtpEntered.Value, "mm/dd/yyyy")
    rsLog.Fields("TimeEntered") = dtMyDate

---

No, the .Value property of a DTPicker is a Date.

When assigning a Date value to a Date property/variable/field/... there is no problem (as a Date does not have a format). So, this is fine:

Code:

---

    rsLog.Fields("TimeEntered") = dtpEntered.Value    'both have a data type of Date

---

The problems come when you put the Date value into a String, such as sSQL, and then the format becomes an issue - as Strings hold formatted data, and the Date value needs to be formatted somehow in order to be put into the String.

Within SQL statements (which are Strings in VB) the valid/safe formats are limited, so to be safe you need to specify which one to use, like you did here:

Code:

---

sSQL = "SELECT * FROM tblRules WHERE Updated BETWEEN #" & _
        Format(dtpStart.Value, "mm/dd/yyyy") & "# AND #" & _
        Format(dtpEnd.Value, "mm/dd/yyyy") & "#"

---

There is more info about the issues with Dates/Strings in the article Why are my dates not working properly? from our Classic VB FAQs