-
Sep 29th, 2014, 12:09 AM
#1
Thread Starter
Enjoy the moment
[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
-
Sep 29th, 2014, 12:39 AM
#2
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
-
Sep 29th, 2014, 12:41 AM
#3
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
-
Sep 29th, 2014, 12:43 AM
#4
Thread Starter
Enjoy the moment
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
-
Sep 29th, 2014, 12:48 AM
#5
Re: all functions are correct but doesn't seem to execute it
Originally Posted by salsa31
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
-
Sep 29th, 2014, 02:06 AM
#6
Thread Starter
Enjoy the moment
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
-
Sep 29th, 2014, 04:58 AM
#7
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
-
Sep 29th, 2014, 05:49 AM
#8
Thread Starter
Enjoy the moment
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
-
Sep 29th, 2014, 07:27 AM
#9
Re: all functions are correct but doesn't seem to execute it
Originally Posted by salsa31
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?
-
Sep 29th, 2014, 07:39 AM
#10
Thread Starter
Enjoy the moment
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
-
Sep 29th, 2014, 08:18 AM
#11
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
-
Sep 29th, 2014, 09:07 AM
#12
Thread Starter
Enjoy the moment
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
-
Sep 29th, 2014, 09:22 AM
#13
Re: all functions are correct but doesn't seem to execute it
Originally Posted by salsa31
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
-
Sep 29th, 2014, 09:40 AM
#14
Thread Starter
Enjoy the moment
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
-
Sep 29th, 2014, 09:51 AM
#15
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
-
Sep 29th, 2014, 10:04 AM
#16
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.
-
Sep 29th, 2014, 10:05 AM
#17
Thread Starter
Enjoy the moment
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
-
Sep 29th, 2014, 11:36 AM
#18
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.
-
Sep 29th, 2014, 03:24 PM
#19
Thread Starter
Enjoy the moment
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|