dcsimg
Results 1 to 11 of 11

Thread: Word database sql

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2011
    Posts
    18

    Word database sql

    Hello,
    I have created a simple macro in word to pull info from a database using sql. It seems to work fine UNTIL I try to search by 5/01/2011 or 5/05/2011.
    I debugged it and see that for some reason my sql statement only returns 5/10/2011 when I search with 5/01/2011. If I search 5/05/2011 it comes up empty. Other dates work fine. Is there something wrong with the sql statement?

    HTML Code:
    sql = "SELECT * FROM PerformancePoints WHERE DATEOF >= DateValue('" & StartDatee & "') AND TYCNUMBER = " & "'" & TYCN & "'" & " ORDER BY DATEOF"
    THanks,

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,871

    Re: Word database sql

    dates field values should be enclosed in # # rather than ' '
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Apr 2011
    Posts
    18

    Re: Word database sql

    Thanks but same thing. There is something else wrong with SQL statement, I just dont see it?
    Any ideas?

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Apr 2011
    Posts
    18

    Re: Word database sql

    The sql statement should be enough but what I am wanting is pull all the dates starting with the start date of one specific person.
    At I added the ORDER BY to get the dates in order because the ways I was doing it before it was pulling the dates but as they were all out of order.

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Apr 2011
    Posts
    18

    Re: Word database sql

    No avail. I tried a different record and the same exact thing happened, 5/01/2011 always pulls 5/10/2011. I am completley lost here. Any ideas?

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Apr 2011
    Posts
    18

    Re: Word database sql

    I tried other dates like 5/2 5/3 ect... but nothing is pulled out of the database at all? It is there, so why would it not get pulled by the statement.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Apr 2011
    Posts
    18

    Re: Word database sql

    OK I deleted all the fields and started over. Entered the following dates.
    5/01/2011 to 5/10/2011.
    Using the SQL statement It pulls nothing from the database when I search for any date in that range.
    However I entered 4/01/2011 to 4/10/2011 it worked fine when I searched in that range.
    Aggh I am pulling my hair out now!

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Apr 2011
    Posts
    18

    Re: Word database sql

    I did not want to use the recordset filter but it seemed to do the trick so far. My issues have stopped but I still do not understand why SQL could not do a simple search. Recordset did it with no problem. The SQL statement was right, anybody have any clues to what was wrong?

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Apr 2011
    Posts
    18

    Re: Word database sql

    This is what I did, seems to work so far....

    HTML Code:
    sql = "SELECT * FROM PerformancePoints WHERE TYCNUMBER = " & "'" & TYCN & "'" & "ORDER BY DATEOF"
    rs.Filter = "DATEOF > " & StartDatee - 1

  10. #10
    Frenzied Member
    Join Date
    Jan 2010
    Location
    Connecticut
    Posts
    1,687

    Re: Word database sql

    Why are you using the DateValue function?
    Did you try it without that function?
    VB6 Library

    If I helped you then please help me and rate my post!
    If you solved your problem, then please mark the post resolved

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Apr 2011
    Posts
    18

    Re: Word database sql

    The database data was a string according to documanetation and some peoples suggestions I stated using it to comvert the string to date.
    As you see in my second sode I eliminated the datevalue.
    I just do not see why I had to do it that way to make it work, SQL should of been able to handle it all by itself. Without me having to use the recordset filter command.

    All I am trying to do is simply select a ID number and get all records that is higher/bigger/greater than and = to the date the user selects.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width