Results 1 to 19 of 19

Thread: [RESOLVED] all functions are correct but doesn't seem to execute it

  1. #1

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

    Resolved [RESOLVED] all functions are correct but doesn't seem to execute it

    i have a table that holds 2 values
    SmsDate & SmsTime
    i am trying to check if the SmsDate Is smaller than the date today and the time is smaller then the time now
    but it doesn't seem to be executing alto the parameters are correct
    what is the problem?
    Code:
    Dim TimeToday As Date
    Dim DateToday As Date
    TimeToday = Format(Now, "HH:MM")
    DateToday = CDate(Date & " 00:01")
    Dim Rs As ADODB.Recordset
    Set Rs = New ADODB.Recordset
    Rs.Open "SELECT * FROM TizmonSms WHERE SmsStatus ='Pending' AND SmsDate < #" & MyDate(Now) & "# And SmsTime < #" & TimeToday & "#", CN
     If Not Rs.EOF Then
     CN.Execute "Update Event Set SmsSentTizmon ='0' Where EventID=" & Rs!EventID
     CN.Execute "Update TizmonSms set SmsStatus ='Not Delivered' where EventID=" & Rs!EventID
    any help will be appreciated
    regards salsa31

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

    Re: all functions are correct but doesn't seem to execute it

    So are you saying that nothing is updated or that the select is returning no records?
    Also remember what I told you about putting your query in a variable and using debug.print to see the actual query that is being executed.

    btw there is no reason to select * when you are only going to be using one field. You should select only the field(s) you will be using.

    Also be aware that you are executing a select that could return more than one record but your code is not written to deal with more than one record so it would fail to update any additional eventids that may be returned even if everything is working properly as coded. You had the exact same issue where you have a query that could return more than one record but code that followed only processed the first one a few days ago in another thread

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

    Re: all functions are correct but doesn't seem to execute it

    You have three fields that are required NOT two! The fields are "smsStatus", "smsDate" and "smsTime", also MyDate (Now) is incorrect it should be DATE(Now).
    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

  4. #4

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

    Re: all functions are correct but doesn't seem to execute it

    hey sir
    i am saying that the 2 values are lower then the today values
    i mean this
    SmsDate in the table is 20/09/2014 and SmsTime in the table is 14:00
    now today is 29/09/2014 and the time is 07:41
    so the SmsDate is lower then the date today and also the SmsTime
    but it skips the update although the values are both right

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

    Re: all functions are correct but doesn't seem to execute it

    Quote Originally Posted by salsa31 View Post
    hey sir
    i am saying that the 2 values are lower then the today values
    i mean this
    SmsDate in the table is 20/09/2014 and SmsTime in the table is 14:00
    now today is 29/09/2014 and the time is 07:41
    so the SmsDate is lower then the date today and also the SmsTime
    but it skips the update although the values are both right
    Yes, I know! However, since you have NOT provided the database for us to check whether or not you created it properly I said to make sure you needed three fields in the database not just two.
    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

  6. #6

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

    Re: all functions are correct but doesn't seem to execute it

    Code:
    Table Name = TizmonSms
    fields
    EventID =Number
    SmsDate = Date/Time
    SmsTime = Date/Time
    SmsStatus = Text

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

    Re: all functions are correct but doesn't seem to execute it

    You could try something such as

    Code:
       
     Dim CN As Connection, RS As Recordset
        Dim TimeToday As Date
        Dim DateToday As Date
        Set CN = New ADODB.Connection
        TimeToday = Format(Now, "HH:MM")
        DateToday = CDate(Date & " 00:01")
        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
        RS.Open "TizmonSms", CN, adOpenKeyset, adLockPessimistic, adCmdTable
        RS.AddNew
        RS.Fields("SmsStatus") = "Pending"
        RS.Fields("SmsDate") = Date
        RS.Fields("SmsTime") = TimeToday
        If RS.Fields("SmsDate") = Date Then RS.Fields("SmsStatus") = "Not Delivered"
        RS.Update
        CN.Close
    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

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

    Re: all functions are correct but doesn't seem to execute it

    hey NW tnx for the reply
    i am not trying to add a new record
    i am trying to check if the SmsDate and Sms Time is smaller than today's date and time

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

    Re: all functions are correct but doesn't seem to execute it

    Quote Originally Posted by salsa31 View Post
    but it skips the update although the values are both right
    So how do you know that it is skipping the update? Did you step through the code and see that it does not execute the code inside the If statement?

    Do you understand what I said about the debug.print to see your actual query that is being executed?
    Do you understand what I said about the query could return more than one record but your code not being written to deal with more than one record?

  10. #10

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

    Re: all functions are correct but doesn't seem to execute it

    So how do you know that it is skipping the update? Did you step through the code and see that it does not execute the code inside the If statement?

    Yes I did
    Do you understand what I said about the debug.print to see your actual query that is being executed?
    Do you understand what I said about the query could return more than one record but your code not being written to deal with more than one record
    why?beacuse i select *? i defined the values that i want to make the query [SmsDate,SmsTime,SmsStatus]
    i didnt know that it will cause an issue

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

    Re: all functions are correct but doesn't seem to execute it

    The problem is that 14:00 is not smaller than 07:41.

    You're checking that the date is smaller - and it is. You're also checking that the time is smaller - and it isn't. I believe you want to check that the combined date and time is smaller but that's not what you're doing - you're checking them separately.

    There are a few things you can do to sort this out:-
    1. Change that field to a DateTime so it holds the combined dat and time. Then compare that to Now. This would be the best thing to do IMO.
    2. Change the comparison. Here's what you want in pseudo code:-
    recordDate < ParmDate Or (recordDate = ParmDate and recordTime < ParmTime)
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

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

  12. #12

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

    Re: all functions are correct but doesn't seem to execute it

    1. Change that field to a DateTime so it holds the combined dat and time. Then compare that to Now. This would be the best thing to do IMO.
    2. Change the comparison. Here's what you want in pseudo code:-
    recordDate < ParmDate Or (recordDate = ParmDate and recordTime < ParmTime)
    which field?
    they are both Date/Time

    You're checking that the date is smaller - and it is. You're also checking that the time is smaller - and it isn't. I believe you want to check that the combined date and time is smaller but that's not what you're doing - you're checking them separately
    it not possible to check 2 queries?
    what query would you do to solve this, based on the fields i provided?
    Code:
    Table Name = TizmonSms
    fields
    SmsDate = Date/Time if date is bigger then today
    SmsTime = Date/Time if the time is bigger then the time now

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

    Re: all functions are correct but doesn't seem to execute it

    Quote Originally Posted by salsa31 View Post
    Yes I did

    why?beacuse i select *? i defined the values that i want to make the query [SmsDate,SmsTime,SmsStatus]
    i didnt know that it will cause an issue
    No the * has nothing to do with it though you should not be doing that either. That tells it you want all the fields but since you are only using one that is just a waste of resources and bandwidth if the DB is networked.

    The problem is that you are doing a where clause that could result in more than one record being returned and the code that follows checks the current record. So if one record is returned it would work but if more than one record is returned it would only process the first record and ignore any others that were returned.

    As for the issues with date and time, you really should be using a datetime field and have those values together in the same field rather than split into two different fields, makes things much easier as well as reducing the size of the db and resources used.

    The way you have it you could alter the where clause a bit and should be able to get the results you want

    Where SmsStatus ='Pending' AND ((SmsDate < #" & MyDate(Now) & "#) Or ( SmsDate = #" & MyDate(Now) & "# And SmsTime < #" & Time & "#))"

    So assuming I typed that correctly it should return all records with a status of pending that have a date older than today using the first part of the clause and the second part will return all records with a status of pending and todays date with an earlier time.

    Of course you still have a case where more than one record can be returned so you would need to add a loop in order to process more than the first one

  14. #14

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

    Re: all functions are correct but doesn't seem to execute it

    well sir..
    i tried the code you provided
    i had 2 pending events stored in the table
    Code:
    1) Date =29/09/2014 and the time is 20:00 'needs to be pending according to the time
    2) Date =29/09/2014 and the time is 10:00 'needs to be expired
    the 2 events was updated.the first event suppose to be on 20:00 in the evening
    i think beacuse you added the Or in the query

    i found my mistake in my query that if the date is lower then the date today but the time his higer then it dosnt execute
    what i need is:
    if SmsDate is lower than the date today AND the SmsTime is lower then the time now than this updated should execute.

    Of course you still have a case where more than one record can be returned so you would need to add a loop in order to process more than the first one
    i added the loop tnx for the reminder

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

    Re: all functions are correct but doesn't seem to execute it

    The logic you want is this:-
    recordDate < ParmDate Or (recordDate = ParmDate and recordTime < ParmTime)
    as per my post 11

    What I don't understand is why you've got two datetime fields, one for the date and one for the time. One DateTime field could hold both.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

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

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

    Re: all functions are correct but doesn't seem to execute it

    Well, based up reading these posts so far, I believe your answers can be found within them.....

    Code:
    Rs.Open "SELECT eventID FROM TizmonSms WHERE SmsStatus ='Pending' AND SmsDate < #" & Now & "#",CN
     Do while Not Rs.EOF 
         CN.Execute "Update Event Set SmsSentTizmon ='0' Where EventID=" & Rs!EventID
         CN.Execute "Update TizmonSms set SmsStatus ='Not Delivered' where EventID=" & Rs!EventID
         rs.movenext
    loop
    Last edited by SamOscarBrown; Sep 29th, 2014 at 01:08 PM.

  17. #17

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

    Re: all functions are correct but doesn't seem to execute it

    well DM sir my fault
    the Or was in the right place
    i just added this
    Code:
    Dim TimeToday As Date
    TimeToday = Format(Now, "HH:MM:SS")
    i used the TimeToday in the time query
    tnk for for you help sir
    Funky Dexter you 2 appreciate your time

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

    Re: [RESOLVED] all functions are correct but doesn't seem to execute it

    Dim TimeToday As Date
    TimeToday = Format(Now, "HH:MM:SS")
    That code is not needed at all. There is no reason to use Format() when assigning a value to a date variable. That is only useful when working with strings.
    There is also no need to set a separate var for the time. The Time statement will give you the current time as I showed in my example where clause above.

    I am also unclear on why you have a function called MyDate() and why you are using it. The Date statement will return the current date.
    Code:
    Debug.Print Date
    Debug.Print Time
    Debug.Print Now
    yields
    9/29/2014
    12:39:59 PM
    9/29/2014 12:39:59 PM
    Last edited by DataMiser; Sep 29th, 2014 at 11:40 AM.

  19. #19

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

    Re: [RESOLVED] all functions are correct but doesn't seem to execute it

    Code:
    Function MyDate(D As Date) As String
        MyDate = Format(D, "dd/mm/yyyy")
        MyDate = Mid$(MyDate, 4, 2) & "/" & Left$(MyDate, 2) & "/" & Right$(MyDate, 4)
    End Function

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