Results 1 to 9 of 9

Thread: [RESOLVED] Help with SQL statement !!

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    68

    Resolved [RESOLVED] Help with SQL statement !!

    Hi every 1,

    i have access DB with my VB App. contain users activation dates gust like below,

    SUBUSER ------------------ FDATE

    user1 ------------------ 02/02/2105
    user1 ------------------ 30/03/2015
    user2 ------------------ 25/12/2105
    user3 ------------------ 23/01/2105
    please i need to execute an SQL statement to get users names which their last activation date is less than the first day of current month, which mean only user2,user3 will be included
    i tried the SQL statement as below but i get error : Syntax error in JOIN operation.
    Code:
     "select subuser from(subuser,max(fdate) as xxx from activation group by subuser)where xxx < #" & DateSerial(Year(Today), Month(Today), 1) & "#"
    Any help will be much appreciated

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,501

    Re: Help with SQL statement !!

    Thread moved to the 'Database Development' forum - which is where you should always post SQL questions (while SQL can be used in VB.Net, it is certainly not specific to VB.Net)

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    68

    Re: Help with SQL statement !!

    I agree with u but the SQL statement include a vb code !!

  4. #4
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,623

    Re: Help with SQL statement !!

    Well i dont know if its your only problem but you sub select has NO Select keyword so this;

    Code:
    "select subuser from(subuser,max(fdate) as xxx from activation group by subuser)where xxx < #" & DateSerial(Year(Today), Month(Today), 1) & "#"
    should read this -

    Code:
    "select subuser from(select subuser,max(fdate) as xxx from activation group by subuser)where xxx < #" & DateSerial(Year(Today), Month(Today), 1) & "#"
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  5. #5
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,623

    Re: Help with SQL statement !!

    Hmm no looking at it further there are more problems with your SQL, i will post again ....
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  6. #6
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,623

    Re: Help with SQL statement !!

    (NB - You shouldn't be using table names with spaces in them, they can cause you all sorts of problems)

    As far as i can see there should be no need for a sub select, all you need is a list of distinct users based up on a condition.

    SELECT Distinct subuser FROM [activation group] WHERE fdate < #" & DateSerial(Year(Today), Month(Today), 1) & "#"

    ps - i have left your date condition in as i don't normally use Access and dont know the syntax of the top of my head.
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,953

    Re: Help with SQL statement !!

    Quote Originally Posted by zeus85 View Post
    I agree with u but the SQL statement include a vb code !!
    Doesn't matter... especially since in this case the problem is the SQL itself, not the VB code.

    Quote Originally Posted by NeedSomeAnswers View Post
    (NB - You shouldn't be using table names with spaces in them, they can cause you all sorts of problems)

    As far as i can see there should be no need for a sub select, all you need is a list of distinct users based up on a condition.

    SELECT Distinct subuser FROM [activation group] WHERE fdate < #" & DateSerial(Year(Today), Month(Today), 1) & "#"

    ps - i have left your date condition in as i don't normally use Access and dont know the syntax of the top of my head.
    Yuuuup, that's the right syntax... although I don't like the string concatenation, I would have gone with a parameterized format. But that's because I try to avoid the Little Bobby Drop Tables syndrome.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    68

    Re: Help with SQL statement !!

    thank u very much for your replies and hints, iv figured out what is wrong with my statement its was in front of me all the time the modification is just like below hope to help some one

    Code:
     "select subuser,xxx from(select subuser,max(fdate) as xxx from activation group by subuser) where xxx < #" & DateSerial(Year(Today), Month(Today), 1) & "#"
    thanx again

  9. #9
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,623

    Re: [RESOLVED] Help with SQL statement !!

    Yuuuup, that's the right syntax... although I don't like the string concatenation, I would have gone with a parameterized format. But that's because I try to avoid the Little Bobby Drop Tables syndrome.
    Agreed, i was just concentrating on the syntax so i missed saying anything about the parameters.

    zeus85 - You should look into using Parameters for your queries, !!!
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



Posting Permissions

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



Click Here to Expand Forum to Full Width