dcsimg
Results 1 to 10 of 10

Thread: [RESOLVED] Query and dates

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,286

    Resolved [RESOLVED] Query and dates

    Hi to all,

    I would to know in the query that i had made why they sort dates that are previous to the actual date...something wrong with the query?

    Code:
     sql = _
                     "select * " & _
                     " from " & tabela_abrir_provas & "" & _
                     " where ano_evento = '" & data_calendario & "'" & _
                     " and primeira_data >= #" & data_de_hoje & "#" & _
                     " or segunda_data >= #" & data_de_hoje & "#" & _
                     " or terceira_data >= #" & data_de_hoje & "#" & _
                     " or quarta_data >= #" & data_de_hoje & "#" & _
                     " or quinta_data >= #" & data_de_hoje & "#" & _
                     " or sexta_data >= #" & data_de_hoje & "#" & _
                     " order by primeira_data, segunda_data, terceira_data, quarta_data, quinta_data, sexta_data, setima_data, oitava_data "
    data_de_hoje is the same that today

    This is the report:

    Name:  Report.jpg
Views: 230
Size:  36.9 KB


    and here is the database and fields:

    Name:  DB.jpg
Views: 225
Size:  33.0 KB

    Thanks
    Manuel

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,634

    Re: Query and dates

    Don't use string concatenation to insert values into SQL code. ALWAYS use parameters. You should fix that regardless of whether it's a source of this issue.

    Apart from that, are you sure that the AND and OR operators are being evaluated in the right order there? AND takes precedence over OR so if you want any of the ORs to be evaluated before the AND then you need to use parentheses.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,286

    Re: Query and dates

    Hi,

    Thanks for your reply...Ok I start to change the string for parameters.
    Yes the AND and OR work well and in the right order.

    Thanks

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,031

    Re: Query and dates

    Sorry but I'm struggling to understand your question, probably due to the language barrier. (Though well done for posting in a second language - I know I couldn't do that).

    Are you saying that your WHERE clause isn't filtering out the records you'd expect it to? If so, could you show us some records which you don't want to appear and tell us why they should be filtered out.

    Or are you saying that the records aren't appearing in the correct order? If so could you give us some example records that are out of order and tell us what order you want them to appear in.

    Please try not to use screen shots, they're really difficult for us to read. I cannot make out any of the records on your screenshots so it's really hard for me to answer your question. Instead, just post the actual data into the forum. That's much easier for us.
    Last edited by FunkyDexter; Nov 23rd, 2018 at 03:00 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  5. #5

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,286

    Re: Query and dates

    Hi FunkyDexter,

    The first Screenshot is the report that sort Throught the query i hade made. The records marked as black is the records that are wrong's. Are Wrong because they should not be there (I think!!!!)

  6. #6
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,593

    Re: Query and dates

    Quote Originally Posted by sacramento View Post
    Thanks for your reply...Ok I start to change the string for parameters.
    Yes the AND and OR work well and in the right order.
    Database - Why should I use Parameters instead of putting values into my SQL string?-VBForums
    http://www.vbforums.com/showthread.p...=1#post3389994

  7. #7

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,286

    Re: Query and dates

    Thanks for the link gibra

  8. #8
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,031

    Re: Query and dates

    The first Screenshot is the report that sort Throught the query i hade made. The records marked as black is the records that are wrong's
    That's fine but it's almost impossible for me to read them from a screenshot so it doesn't really help me answer your question. I can just about read the first screenshot but I've got no chance at all of reading the second one. Don't post screenshots. Post data. Otherwise it's hard for us to give you anything but general advice.

    Are Wrong because they should not be there (I think!!!!)
    What does that mean? Why shouldn't they be there? Your WHERE clause will not exclude them because it uses ORs. So it will display any record that has at least one date that is after today. If you want to display only records where ALL the dates are greater than today then those ORs need to be ANDs.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  9. #9

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,286

    Re: Query and dates

    Hi,

    You have right, i had replace OR by AND and work perfecttly

    Thanks for the help

  10. #10

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,286

    Re: Query and dates

    Hi,

    You have right, i had replace OR by AND and work perfecttly

    Thanks for the help

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