If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date Expression
I use this expression in the criteria in a Query. How can I modify it to
obtain records for a 24 hour time period? At present it produces records for the previous day. I need it to produce records on the previous 24 hour time period. (Example) Tuesday 06:00 to Wednesday 06:00 Wednesday 06:00 to Thursday 06:00 DateAdd("d",-1,Date()) And DateAdd("d",1,Date()) Thanks in advance -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
Date Expression
Loggical via AccessMonster.com wrote:
I use this expression in the criteria in a Query. How can I modify it to obtain records for a 24 hour time period? At present it produces records for the previous day. I need it to produce records on the previous 24 hour time period. (Example) Tuesday 06:00 to Wednesday 06:00 Wednesday 06:00 to Thursday 06:00 DateAdd("d",-1,Date()) And DateAdd("d",1,Date()) If you want to take the time of day into account, use Now() instead of Date() field Between DateAdd("d",-1,Now()) And Now() -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Date Expression
Use DateAdd() with "h" instead of "d".
Calculate the number of hours you want to subtract (18 rather than 24.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Loggical via AccessMonster.com" u28343@uwe wrote in message news:9f5f213986bf9@uwe... I use this expression in the criteria in a Query. How can I modify it to obtain records for a 24 hour time period? At present it produces records for the previous day. I need it to produce records on the previous 24 hour time period. (Example) Tuesday 06:00 to Wednesday 06:00 Wednesday 06:00 to Thursday 06:00 DateAdd("d",-1,Date()) And DateAdd("d",1,Date()) Thanks in advance |
#4
|
|||
|
|||
Date Expression
Thank you Allen,
This what I have and it doesn't show any results. I have tried it in the Date field and the Time field. My Time field is formatted as the following 00:00 (24 hr) DateAdd("h",-18,Date()) And DateAdd("h",18,Date()) Allen Browne wrote: Use DateAdd() with "h" instead of "d". Calculate the number of hours you want to subtract (18 rather than 24.) I use this expression in the criteria in a Query. How can I modify it to obtain records for a 24 hour time period? At present it produces records [quoted text clipped - 9 lines] Thanks in advance -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200911/1 |
#5
|
|||
|
|||
Date Expression
Did you include the greater than sign, e.g.:
= DateAdd("h",-18,Date()) And DateAdd("h",18,Date()) 18 hours before today's date would be 6am yesterday. 18 hours after today's date would be 6pm today. Is that the right range? -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Loggical via AccessMonster.com" u28343@uwe wrote in message news:9f61a70a11e42@uwe... Thank you Allen, This what I have and it doesn't show any results. I have tried it in the Date field and the Time field. My Time field is formatted as the following 00:00 (24 hr) DateAdd("h",-18,Date()) And DateAdd("h",18,Date()) Allen Browne wrote: Use DateAdd() with "h" instead of "d". Calculate the number of hours you want to subtract (18 rather than 24.) I use this expression in the criteria in a Query. How can I modify it to obtain records for a 24 hour time period? At present it produces records [quoted text clipped - 9 lines] Thanks in advance |
#6
|
|||
|
|||
Date Expression
Thank you Allen for your help.
I don't think this will give me the results I require. Trying to do this on the Date field it requires the entry to be made in order of occurrence. This is not necessarily the way the entries are made into the DB. I could have an entry that should go into the DB at 05:00 AM not entered until 14:00PM. Please correct me if I am wrong. I have a time field which is the exact time of the occurrence. This field has the times I'm looking for. Is there an expression I can use in this field? Thanks again for your help. Much appreciated. Allen Browne wrote: Did you include the greater than sign, e.g.: = DateAdd("h",-18,Date()) And DateAdd("h",18,Date()) 18 hours before today's date would be 6am yesterday. 18 hours after today's date would be 6pm today. Is that the right range? Thank you Allen, [quoted text clipped - 14 lines] Thanks in advance -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200911/1 |
#7
|
|||
|
|||
Date Expression
What's in the time field? Is it time only (i.e. it lacks the date)?
The best solution would be to put the date and time into the field. Otherwise, it would be possible to use DateValue() on the date field (to get just the date), and then a logical expression on the time field to figure out if it's before 6am (and so treated as the previous date.) For example, this should return -1 for times before 6am, and 0 for times after that: DateDiff("h", #0:00:00#, [YourTimeField]) 6 -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Loggical via AccessMonster.com" u28343@uwe wrote in message news:9f8566c616c93@uwe... Thank you Allen for your help. I don't think this will give me the results I require. Trying to do this on the Date field it requires the entry to be made in order of occurrence. This is not necessarily the way the entries are made into the DB. I could have an entry that should go into the DB at 05:00 AM not entered until 14:00PM. Please correct me if I am wrong. I have a time field which is the exact time of the occurrence. This field has the times I'm looking for. Is there an expression I can use in this field? Thanks again for your help. Much appreciated. Allen Browne wrote: Did you include the greater than sign, e.g.: = DateAdd("h",-18,Date()) And DateAdd("h",18,Date()) 18 hours before today's date would be 6am yesterday. 18 hours after today's date would be 6pm today. Is that the right range? Thank you Allen, [quoted text clipped - 14 lines] Thanks in advance -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200911/1 |
#8
|
|||
|
|||
Date Expression
Allen this is what I have done and it looks like it has given me the results
I require. If you see any problems with what I have done could you please let me know? In the Query I made another field and combined both the Date and Time Fields. DateTime: ([Date]+[Time]) Then used the previous expression you gave in the DateTime critiera. =DateAdd("h",-18,Date()) And DateAdd("h",6,Date()) Much appreciated for your time and help. Allen Browne wrote: What's in the time field? Is it time only (i.e. it lacks the date)? The best solution would be to put the date and time into the field. Otherwise, it would be possible to use DateValue() on the date field (to get just the date), and then a logical expression on the time field to figure out if it's before 6am (and so treated as the previous date.) For example, this should return -1 for times before 6am, and 0 for times after that: DateDiff("h", #0:00:00#, [YourTimeField]) 6 Thank you Allen for your help. [quoted text clipped - 23 lines] Thanks in advance -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200911/1 |
#9
|
|||
|
|||
Date Expression
That should be fine.
(Hopefully your fields are not actually named Date, Time and DateTime, as these are reserved words.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Loggical via AccessMonster.com" u28343@uwe wrote in message news:9f92a5c306c08@uwe... Allen this is what I have done and it looks like it has given me the results I require. If you see any problems with what I have done could you please let me know? In the Query I made another field and combined both the Date and Time Fields. DateTime: ([Date]+[Time]) Then used the previous expression you gave in the DateTime critiera. =DateAdd("h",-18,Date()) And DateAdd("h",6,Date()) Much appreciated for your time and help. Allen Browne wrote: What's in the time field? Is it time only (i.e. it lacks the date)? The best solution would be to put the date and time into the field. Otherwise, it would be possible to use DateValue() on the date field (to get just the date), and then a logical expression on the time field to figure out if it's before 6am (and so treated as the previous date.) For example, this should return -1 for times before 6am, and 0 for times after that: DateDiff("h", #0:00:00#, [YourTimeField]) 6 Thank you Allen for your help. [quoted text clipped - 23 lines] Thanks in advance -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200911/1 |
#10
|
|||
|
|||
Date Expression
No there actually named OccDate and OccTime
Allen Browne wrote: That should be fine. (Hopefully your fields are not actually named Date, Time and DateTime, as these are reserved words.) Allen this is what I have done and it looks like it has given me the results [quoted text clipped - 30 lines] Thanks in advance -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200911/1 |
Thread Tools | |
Display Modes | |
|
|