DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Would you try this for me, please..(SQL)

  1. #1
    Join Date
    Aug 2004
    Posts
    43,023

    Would you try this for me, please..(SQL)

    [Originally posted by zane]

    Hi, my friend
    I have a problem with the control DTPicker. I don't understand what happened with it (or with me.) It doesn't work well when I use the SQL criteria BETWEEN.˙ I˙ like to think about me that I know enough VB to solve the majority˙ of˙ problems, but I thought that yesterday.So, I'm trying to do a interogate use both (DTPicker and BETWEEN). I asked many a time what I had to do but nobody gave me a˙ correct answer.This time I have a entreaty .Could you try to do that˙ for˙ me? (Of course if you have some time for it).
    First,on a form put two DTPICKER˙ and a DataGrid. You must have a database (MSAcces) which has a field where you can put some dates. Than, you must select all dates between a period which you chose with DTPicker1 and DTPicker2. I have this SQL:
    ˙ 
    strSQL = "select * from TABLE1 where ([DATE] between #" & DTPicker1.Value & "# _ and #" & DTPicker2.Value & "#)"

    Finaly, you must choose one date to DTPicker1 and other date to DTPicker2. OK! One thing you have to do, please put on DTPicker2 the last day of month and then choose a day on DTPicker1 until 13th day. Should be OK.but it doesn't ! Why?????? At˙ me it's all OK until I choose a date which is after twelfth day (e.g.13). If I choose a date after 12th˙ I have in a DataGrid all records even if those˙ are in a other month or day. I know that I have to set:
    - my date's system, format date in a DTPicker control and format date in my database to be the same. I forget something?PLEASE TRY THIS e.g. FOR ME˙ AND TELL ME WHAT'S WRONG.THERE IS A SETTING WHICH I DIDN'T KNOW?
    THANK YOU FOR YOUR TIME SPENDING FOR ME! I HOPE YOU UNDERSTAND WHAT I SAY. I wait your answer as soon as you can and REMEMBER that you can't help me if you don't try it on your system. I want to know if you have this problem on your computer.˙ ˙ ˙
    All my consideration for you!

  2. #2
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Would you try this for me, please..(SQL)

    [Originally posted by Jim]

    It definitely sounds like a date format issue. Try using the three char month format.˙ This should prevent any confusion for the app.

    eg.

    strSQL = "select * from TABLE1 where ([DATE] between CDate('" & format(DTPicker1.Value,"yyyy-mmm-dd") & "') and CDate('" & format(DTPicker2.Value,"yyyy-mmm-dd") & "'))"

  3. #3
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Would you try this for me, please..(SQL)

    [Originally posted by Mike Lyons]

    No matter what your regional settings are on your PC, you must supply the date to queries in "American" format, which is "mm/dd/yyyy".˙ So adapt Jim's response to use that format.

    A couple of other things to note.˙ First, your choice of DATE as a field name could cause you problems.˙ I strongly recommend that you change this if you have the authority to do so, or pass this recommendation along to whoever does have authority.˙ Date() is already a function in VBA and is also a data type.

    Also, if there is any time value stored in your date field, you could miss values if you don't include some way of taking that into account.˙ Here's one way (including a renamed field):

    strSQL = "select * from TABLE1 where ([YourDateFld] between #" & Format$(DTPicker1.Value, "mm/dd/yyyy") & "#" _ "
    ˙ " and #" & Format$(DTPicker2.Value, "mm/dd/yyyy") & " 23:59:59#)"

    (Above code has not been tested)


    Hope this gives you a place to start.

    Mike

  4. #4
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Would you try this for me, please..(SQL)

    [Originally posted by Andon K.]

    I would like to add a few words (because I got burned a couple of times):

    The Format function uses the character that was set as date delimiter in Control Panel. So, if on the target system instead of the slash character (/) you use the dot (.) when you call

    Format$(SomeDate, "mm/dd/yyyy")

    it will return:

    mm.dd.yyyy

    If the database expects slashes, you have a problem. But the following statement works (returns slashes), no matter what character is set as system date delimiter:

    Format$(SomeDate, "mm""/""dd""/""yyyy")

    Also, If I remember correctly, Access, SQL Server and most other popular databases accept also the "universal" yyyy/mm/dd date format, so a good practice would be to use it. In case you later change your backend database, you will have one headache less while migrating.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center