Results 1 to 34 of 34

Thread: [RESOLVED] do i need to use here 2 where clause ?

  1. #1

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Resolved [RESOLVED] do i need to use here 2 where clause ?

    i am trying to figure out how do i check if a customer has a birthday today based on the selected text
    how ever i already have a where clause that i check the date
    do i need to use 2 where clause also for the customer name?
    i got a little confused
    this is the code i am using
    Code:
        Dim rs As New ADODB.Recordset
        rs.Open "SELECT * FROM Customers WHERE DAY(DateBirth) = DAY(#" & MyDate(Now)            & "#) " & _
        "AND MONTH(DateBirth) = MONTH(#" & MyDate(Now) & "#)", CN
         If Not rs.EOF Then
       MsgBox "HappyBirthday To " & " " & txtSubject.Text
        rs.Close
        End If
    any help will be appreciated
    regards salsa31

  2. #2
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: do i need to use here 2 where clause ?

    I seem to remember the answer has been given to you before try looking through your old posts.
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  3. #3

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: do i need to use here 2 where clause ?

    hey NW i dont think for this question

  4. #4

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: do i need to use here 2 where clause ?

    well i tried this but something still missing
    Code:
    Dim rs As New ADODB.Recordset
    rs.Open "SELECT * FROM Customers WHERE FullName = '" & txtSubject.Text & "' &  DAY(DateBirth) = DAY(#" & MyDate(Now) & "#) " & _
    "AND MONTH(DateBirth) = MONTH(#" & MyDate(Now) & "#)", CN
       If Not rs.EOF Then
    MsgBox "HappyBirthday To " & " " & txtSubject.Text
        rs.Close
        End If

  5. #5
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Re: do i need to use here 2 where clause ?

    why don't you try datediff function .and you need to check diffdate is less than and equal to 1 .
    Code:
    DiffDate = DateDiff("d", Date, EDate)

  6. #6

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: do i need to use here 2 where clause ?

    what do you meen firoz?

  7. #7
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: do i need to use here 2 where clause ?

    Using your hairdresser database as an example

    Code:
    Private Sub Form_Load()
    'Return the number of treatments by a particular customer
        Dim ConnectionString As String, person As String, treatment As String, calend As String, calStart As String
               Set cn = New ADODB.Connection
          cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;" & _
              "Data Source= " & App.Path & "\db1.mdb"
           cn.Open ConnectionString
            Set rs = New ADODB.Recordset
            rs.CursorLocation = adUseClient
            person = "salsa"
            calStart = "09:15:00"
            calend = "10:00:00"
            treatment = "haircut"
          rs.Open "CalendarHistory", cn, adOpenKeyset, adLockPessimistic, adCmdTable
          'Count (CalTreatment)
          ssql = "Select * FROM CalendarHistory where CalCust = '" & person & "'" & " AND CalStart='" & calStart & "'" & " AND CalEnd='" & calend & "'"
           Set rs = cn.Execute(ssql)
            MsgBox (rs.GetString)
        rs.Close
        cn.Close
    End Sub
    See this line

    Code:
          ssql = "Select * FROM CalendarHistory where CalCust = '" & person & "'" & " AND CalStart='" & calStart & "'" & " AND CalEnd='" & calend & "'"
    That is retrieving the customer whose name is "salsa" AND their appointment starts at "09:15:00" AND ends at "10:00:00".
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  8. #8
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: do i need to use here 2 where clause ?

    Quote Originally Posted by Nightwalker83 View Post
    I seem to remember the answer has been given to you before try looking through your old posts.
    Salsa....yes, please check your old posts...I gave you an example on how to use datediff to do just what you wanted to do. As I told you before, as well, I have a program (a couple, actually), that runs early mornings and sends me emails as birthday reminders 10 days out, 9 days out, etc.
    Sammi

  9. #9
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: do i need to use here 2 where clause ?

    I also did a couple programs using an msflexgrid to temporarily hold all the 'data' on a person, and used GMAIL to send me the notification....here's the section that populates the grid using datePart() instead of datediff():
    Code:
    rs.Open "select * from bday", cnn, adOpenKeyset, adLockPessimistic
    Do While Not rs.EOF
        If DatePart("y", rs!bday) - DatePart("y", Date) < 11 And DatePart("y", rs!bday) - DatePart("y", Date) > -1 Then   'starts 10 days out, down to last day
            iAge = Year(Date) - Year(rs!bday)
            grid1.Rows = grid1.Rows + 1    'populate the grid
            grid1.TextMatrix(grid1.Rows - 1, 0) = "BIRTHDAY"
            grid1.TextMatrix(grid1.Rows - 1, 1) = rs!firstname
            grid1.TextMatrix(grid1.Rows - 1, 3) = rs!lastname
            grid1.TextMatrix(grid1.Rows - 1, 4) = rs!bday
            grid1.TextMatrix(grid1.Rows - 1, 5) = iAge
            grid1.TextMatrix(grid1.Rows - 1, 6) = rs!email
            grid1.TextMatrix(grid1.Rows - 1, 8) = DatePart("y", rs!bday) - DatePart("y", Date)
        End If
    rs.movenext
    loop
    rs.close
    Then I called the sub to send the email for all entries in the grid:
    Code:
    sendStuffToEmailModule
    And this is what that sub looks like:
    [codePrivate Sub sendStuffToEmailModule()
    Dim x As Integer
    Dim typeDay As String
    Dim haveHas As String
    Dim dayDays As String
    For x = 0 To grid1.Rows - 1
    typeDay = grid1.TextMatrix(x, 0)
    Select Case grid1.TextMatrix(x, 8)
    Case "1"
    dayDays = " day on "
    Case "0"
    dayDays = " days(TODAY) on "
    Case Else
    dayDays = " days on "
    End Select
    GmailSend "~MY EMAIL LOGIN USER ID GOES HERE ~", "~MY EMAIL ACCT PWD GOES HERE", "~MY EMAIL GOES HERE~ ", "UPCOMING " & typeDay, grid1.TextMatrix(x, 1) & " " & grid1.TextMatrix(x, 3) _
    & " has a " & grid1.TextMatrix(x, 0) & " in " & grid1.TextMatrix(x, 8) & dayDays & WeekdayName(Weekday(Date + grid1.TextMatrix(x, 8))) & _
    ", " & Day(grid1.TextMatrix(x, 4)) & " " & MonthName(Month(grid1.TextMatrix(x, 4))) & _
    " AGE-" & grid1.TextMatrix(x, 5) & " EMAIL: " & grid1.TextMatrix(x, 6)
    Next x
    End Sub[/code]
    The gmailSend function can be found on the forum, but as you are not using email, instead of calling that function (sendStuffToEmailModule), you can use a messagebox, popup with textbox or listbox, or any way you want to be notified.

  10. #10

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: do i need to use here 2 where clause ?

    hey sami why i need to use datediff?
    this code that i posted workes fine when i load the birthday form
    but what i need is to check if a single customer has a birthday or not.
    E.X let say i am making a appointment for haircut for salsa
    now when i choose salsa from the list of customers if salsa has a birthday today then a msg will pop out
    saying "salsa has a birthday today"
    my question was can or i need to use 2 where clause to achieve this goal

  11. #11

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: do i need to use here 2 where clause ?

    this is not working i tried it
    Code:
    Dim rs As New ADODB.Recordset
    rs.Open "SELECT * FROM Customers WHERE FullName = '" & txtSubject.Text & "' &  DAY(DateBirth) = DAY(#" & MyDate(Now) & "#) " & _
    "AND MONTH(DateBirth) = MONTH(#" & MyDate(Now) & "#)", CN
       If Not rs.EOF Then
    MsgBox "HappyBirthday To " & " " & txtSubject.Text
        rs.Close
        End If

  12. #12
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: do i need to use here 2 where clause ?

    try this:
    Code:
    Dim rs As New ADODB.Recordset
    rs.Open "SELECT * FROM Customers WHERE FullName = '" & txtSubject.Text & "' &  DAY(DateBirth) = '" & DAY(Date) & "'  AND MONTH(DateBirth) = '" & MONTH(Date) & "'", CN
       If Not rs.EOF Then
    MsgBox "HappyBirthday To " & " " & txtSubject.Text
        rs.Close
        End If

  13. #13

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: do i need to use here 2 where clause ?

    nope
    it is still skipping the birhday of the customer

  14. #14
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: do i need to use here 2 where clause ?

    Not in MY database...works fine for me. Are you checking for ucase/lcase, that is, does the upper and lower case of FullName (in your database) match exactly what is in txtSubject.text?

  15. #15
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: do i need to use here 2 where clause ?

    That Red & should be AND

    To answer the thread question no, you do not use two where clauses. If and when you need to filter by more than one thing you use AND or you use OR depending on you needs. You may even need to use both in some cases and you may need to group them in ().

    The basic logic of this has been explained many times in several of your previous threads.

  16. #16
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: do i need to use here 2 where clause ?

    Quote Originally Posted by DataMiser View Post
    That Red & should be AND
    Oh yeah, I didn't 'fix' that first part of the line....I DO have 'AND' in MY query.

    Good catch.

  17. #17
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: do i need to use here 2 where clause ?

    Another thing that is important, especially when you are trying to find an issue in a query is rather than do this
    Code:
    rs.Open "SELECT * FROM Customers WHERE FullName = '" & txtSubject.Text & "' &  DAY(DateBirth) = '" & DAY(Date) & "'  AND MONTH(DateBirth) = '" & MONTH(Date) & "'", CN
    Do this
    Code:
    strSQL="SELECT * FROM Customers WHERE FullName = '" & txtSubject.Text & "' &  DAY(DateBirth) = '" & DAY(Date) & "'  AND MONTH(DateBirth) = '" & MONTH(Date) & "'", CN
    
    rs.Open  strSQL
    which allows you to insert a Debug message between the two lines and examine the actual query that is being passed to the database. You can even copy the query from the debug window and try it directly in Access or SQL server and sometimes get a better indication of what the problem is an how to fix it.

  18. #18

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: do i need to use here 2 where clause ?

    sry DM sir this code dosnt work also
    Code:
    StrSql = "SELECT * FROM Customers WHERE FullName = '" & txtSubject.Text & "' &  DAY(DateBirth) = '" & Day(Date) & "'  AND MONTH(DateBirth) = '" & Month(Date) & "',cn"
    rs.Open StrSql
    Error:expected: end of statement

  19. #19
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: do i need to use here 2 where clause ?

    Salsa...you didn't listen to DM....this works:

    Code:
    StrSql = "SELECT * FROM Customers where fullname = '" & txtSubject.Text & "' and DAY(DateBirth) = '" & DAY(Date) & "'  AND MONTH(DateBirth) = '" & MONTH(Date) & "'", CN
    
    rs.Open StrSql

  20. #20
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: do i need to use here 2 where clause ?

    Apparently you just copied that code from post #17. That was just an example of how to write code in order to be able to use debug.print to show your query. The actual query was from a previous post which did not work.

    Don't copy and paste. Read post #13 and change your query to correct the issue

    Or I guess now you can copy and paste the code from #19 but you really are not going to be able to do this on your own if you do not read the posts and try to understand how they apply rather than just pasted the code and then posting again that it did not work.

  21. #21
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: do i need to use here 2 where clause ?

    It READS:

    SELECT everything from the customers table WHERE the fullname field is equal to the text value of txtSubject AND the day of datebirth is equal to the day of today AND the month of datebirth is equal to the day of today.

  22. #22

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: do i need to use here 2 where clause ?

    Or I guess now you can copy and paste the code from #19 but you really are not going to be able to do this on your own if you do not read the posts and try to understand how they apply rather than just pasted the code and then posting again that it did not work
    i know DM i have Dyslexia so i am trying to do my best
    if you break the code for me i will surely understand
    Salsa...you didn't listen to DM....this works:
    i tried it gave me a error here
    Code:
    , CN

  23. #23
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: do i need to use here 2 where clause ?

    Post what you tried?

    There is an error in Sams code as well, that ,CN should not be on the variable assignment, it should be on the end of the open statement.

    If you typed it rather than pasting it you would have probably saw this yourself

    I am partly at fault there too as I just pasted that code into the post above without editing the end of the line, can't see it when reading the post without scrolling over

    Var=" whatever"

    rs.open var,cn
    Last edited by DataMiser; Aug 27th, 2014 at 03:16 PM.

  24. #24

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: do i need to use here 2 where clause ?

    instead of rs.open i use cn.execute
    but how will it check if i use only 1 where clause ?

  25. #25
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: do i need to use here 2 where clause ?

    Maybe the problem is that you do not understand English very well?

    Look at Sams post #21

    When you use AND you are telling it that you want to check for more than one thing

    Imagine a set of If statements
    Code:
    If Name="somename" Then
          If Sex="Male" Then 
                If Age>30 Then
                       Show Me this record
                Else
                       Do not show this record
                End If
          Else
                Do not show this record
          End If
    Else
         Do not show this record
    End If
    This could be written
    Code:
    If Name="somename" AND Sex="Male" AND Age>30 Then      
         Show Me this record           
    Else
         Do not show this record
    End If
    A where clause to do the same thing would be
    Code:
    Where Name='somename' AND Sex='Male' AND Age>30
    You will only get records where everything there matches because you said you want it to match this AND this AND this

    You really need to do some reading and then do some trial and error coding yourself to try and understand this very basic and very simple logic

  26. #26
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: do i need to use here 2 where clause ?

    Quote Originally Posted by salsa31 View Post
    instead of rs.open i use cn.execute
    but how will it check if i use only 1 where clause ?
    Why would you change it from RS.Open to CN.Execute?
    Your first post is using RS.Open so I would expect that you would continue to do that rather than change it to CN.Execute

    Is it that you do not understand how to use these two methods? CN.Execute is fine though more limited than RS.Open. Either would work in this case if coded properly.
    Of course now I have absolutely no idea what your code might look like. You said you tried what was posted but nothing has been posted using CN.Execute so who knows what you are actually doing.

  27. #27
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: do i need to use here 2 where clause ?

    oops, sorry...darn cut/paste...was trying to merge salsa's code with mine...as DM says:
    Code:
    StrSql = "SELECT * FROM Customers where fullname = '" & txtSubject.Text & "' and DAY(DateBirth) = '" & DAY(Date) & "'  AND MONTH(DateBirth) = '" & MONTH(Date) & "'"
    
    rs.Open StrSql, CN

  28. #28
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: do i need to use here 2 where clause ?

    salsa31,

    Is the DateBirth field in the database actually of datatype Date/Time or is it a Text field?
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  29. #29

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: do i need to use here 2 where clause ?

    hey sam where is the if condition? Post#27
    Code:
    Is it that you do not understand how to use these two methods? CN.Execute is fine though more limited than RS.Open. Either would work in this case if coded properly.
    Of course now I have absolutely no idea what your code might look like. You said you tried what was posted but nothing has been posted using CN.Execute so who knows what you are actually doing.
    i know how to use a little
    all i am trying to do is to check if a customer has a birthday today and then pop up a msgbox
    this what happens when i click on the listiew
    Code:
    Private Sub LsVw_ItemClick(ByVal Item As XtremeSuiteControls.ListViewItem)
    txtSubject.Text = LsVw.SelectedItem.SubItems(1)
    CheckBirthDay
    Code:
    StrSql = "SELECT * FROM Customers where fullname = '" & txtSubject.Text & "' and DAY(DateBirth) = '" & Day(Date) & "'  AND MONTH(DateBirth) = '" & Month(Date) & "'"
    If Not rs.EOF Then
    
    MsgBox "yes"
    CN.Execute StrSql

  30. #30
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: do i need to use here 2 where clause ?

    You REALLY have extremely little knowledge of how to code in VB. Like I suggested a long time ago, go to class, get a book, STUDY!

    Code:
    StrSql = "SELECT * FROM Customers where fullname = '" & txtSubject.Text & "' and DAY(DateBirth) = '" & DAY(Date) & "'  AND MONTH(DateBirth) = '" & MONTH(Date) & "'"
    'Like DM said, put a print statement, or msgbox, or something so you can see precisely what is being sent to your database.
    'You can correct 90% of your errors right here.  For example:
    'MsgBox StrSql
    rs.Open StrSql, CN     
    If Not rs.EOF Then
        MsgBox "HappyBirthday To " & " " & txtSubject.Text
    End If
    rs.Close
    

  31. #31
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: do i need to use here 2 where clause ?

    It would behoove you (that means, it would be to your benefit) if you would download the "Black Book" for Visual Basic 6, available HERE. Go through it, page by page, you can learn a lot from this FREE DOWNLOAD.

  32. #32
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: do i need to use here 2 where clause ?

    Why on earth did you do this. Is RS even a valid object at the point where you reference it?
    Code:
    StrSql = "SELECT * FROM Customers where fullname = '" & txtSubject.Text & "' and DAY(DateBirth) = '" & Day(Date) & "'  AND MONTH(DateBirth) = '" & Month(Date) & "'"
    If Not rs.EOF Then
    
    MsgBox "yes"
    CN.Execute StrSql
    Also surely by now you know that to get data from a CN.Execute you have to set an rs= to it. Of course what you should have done was used RS open like has been shown in almost every post of this thread.

    Related to the message that shows the strsql I would recommend using debug.print rather than msgbox. This allows the query to be copied from the debug window at the bottom and pasted into access or the forums if need be and allows you to see it clearly without the need to have a msgbox popping up on the screen.

  33. #33
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: do i need to use here 2 where clause ?

    Quote Originally Posted by DataMiser View Post
    Related to the message that shows the strsql I would recommend using debug.print rather than msgbox. This allows the query to be copied from the debug window at the bottom and pasted into access or the forums if need be and allows you to see it clearly without the need to have a msgbox popping up on the screen.
    Good point...."I" prefer the msgbox as can "I" can usually immediately see the issue within the message; however, beginners (or experienced developers using complex SQLs) may elect your alternative. -Sammi

  34. #34

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: do i need to use here 2 where clause ?

    Also surely by now you know that to get data from a CN.Execute you have to set an rs= to it. Of course what you should have done was used RS open like has been shown in almost every post of this thread.
    yes i know sir
    like i said before i have Dyslexia so it takes me a while to understand
    tnk you very much for your help
    sami i download that pdf hope to get it right next time
    tnk you for your help the post 30# works like a charm

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