By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,273 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Criteria in query to obtaining Date of fixed form

P: 36
Hello People,

As I can obtain data with a dated fixed criteria. Without utilizing a parameter.

Example:

My field is [DateReceived].

I need a criteria that indicate me a range date on Tuesday of the previous week to Monday of the current week.

<Spanish Ver>
Hola amigos,

Como puedo obtener datos con un criterio de fecha fijo. Sin utilizar un parametro.

Ejemplo:

Mi campo es [DateReceived].

Necesito saber que criterio utilizar para extraer datos desde el martes de la semana anterior hasta el lunes de la semana actual.

Thanks, Gracias! for any answer
Sep 6 '07 #1
Share this Question
Share on Google+
4 Replies


JConsulting
Expert 100+
P: 603
Hello People,

As I can obtain data with a dated fixed criteria. Without utilizing a parameter.

Example:

My field is [DateReceived].

I need a criteria that indicate me a range date on Tuesday of the previous week to Monday of the current week.

<Spanish Ver>
Hola amigos,

Como puedo obtener datos con un criterio de fecha fijo. Sin utilizar un parametro.

Ejemplo:

Mi campo es [DateReceived].

Necesito saber que criterio utilizar para extraer datos desde el martes de la semana anterior hasta el lunes de la semana actual.

Thanks, Gracias! for any answer
ola,
How do you want that range returned? how are you using it? the coding isn't hard, but how then would your field fit into it? do you want all 7 dates put into that field?
Sep 7 '07 #2

Scott Price
Expert 100+
P: 1,384
Hola, El Tipo,

¿Como está? No hemos visto a ud por un tiempito :-)

This will be a WHERE criteria used in a query to return data BETWEEN Tuesday a week ago AND last Monday.

This code goes into a public module in the VBA editor window.

Expand|Select|Wrap|Line Numbers
  1. Public Function MyTuesday() As String
  2.  
  3. Dim MyDate As Date
  4. Dim MyDate1 As Date
  5. Dim i As Integer
  6.  
  7. MyDate = Date - 8
  8.  
  9. 'loop thru 7 days of week to find week ago Tuesday
  10. For i = 0 To 6
  11. 'datepart for weekday, 1=sun, 7=sat
  12. If DatePart("w", MyDate - i) = 3 Then
  13. MyDate1 = MyDate - i
  14. Exit For
  15. End If
  16. Next
  17.  
  18. MyTuesday = MyDate1
  19. 'Debug.Print MyTuesday
  20. End Function
  21.  
  22. Public Function MyMonday() As String
  23. Dim MyDate As Date
  24. Dim MyDate1 As Date
  25. Dim i As Integer
  26.  
  27. MyDate = Date
  28. 'loop through 7 days of week to find previous Monday
  29. For i = 0 To 6
  30. 'datepart for weekday, 1=sun, 7=sat
  31. If DatePart("w", MyDate - i) = 2 Then
  32. MyDate1 = MyDate - i
  33. Exit For
  34. End If
  35. Next
  36.  
  37. MyMonday = MyDate1
  38. 'Debug.Print MyMonday
  39. End Function
Then the query will look like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblEvent.EventID, tblEvent.EventDate
  2. FROM tblEvent
  3. WHERE (((tblEvent.EventDate) BETWEEN MyTuesday() AND MyMonday()));
  4.  
You'll change the names of the tables and fields to reflect your tables and fields.

If you need this explained in Spanish, I'll give it a try! :-)

Regards,
Scott
Sep 7 '07 #3

P: 36
Hola, El Tipo,

¿Como está? No hemos visto a ud por un tiempito :-)

This will be a WHERE criteria used in a query to return data BETWEEN Tuesday a week ago AND last Monday.

This code goes into a public module in the VBA editor window.

Expand|Select|Wrap|Line Numbers
  1. Public Function MyTuesday() As String
  2.  
  3. Dim MyDate As Date
  4. Dim MyDate1 As Date
  5. Dim i As Integer
  6.  
  7. MyDate = Date - 8
  8.  
  9. 'loop thru 7 days of week to find week ago Tuesday
  10. For i = 0 To 6
  11. 'datepart for weekday, 1=sun, 7=sat
  12. If DatePart("w", MyDate - i) = 3 Then
  13. MyDate1 = MyDate - i
  14. Exit For
  15. End If
  16. Next
  17.  
  18. MyTuesday = MyDate1
  19. 'Debug.Print MyTuesday
  20. End Function
  21.  
  22. Public Function MyMonday() As String
  23. Dim MyDate As Date
  24. Dim MyDate1 As Date
  25. Dim i As Integer
  26.  
  27. MyDate = Date
  28. 'loop through 7 days of week to find previous Monday
  29. For i = 0 To 6
  30. 'datepart for weekday, 1=sun, 7=sat
  31. If DatePart("w", MyDate - i) = 2 Then
  32. MyDate1 = MyDate - i
  33. Exit For
  34. End If
  35. Next
  36.  
  37. MyMonday = MyDate1
  38. 'Debug.Print MyMonday
  39. End Function
Then the query will look like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblEvent.EventID, tblEvent.EventDate
  2. FROM tblEvent
  3. WHERE (((tblEvent.EventDate) BETWEEN MyTuesday() AND MyMonday()));
  4.  
You'll change the names of the tables and fields to reflect your tables and fields.

If you need this explained in Spanish, I'll give it a try! :-)

Regards,
Scott
Hola my gente, Hi my people. Thanks for the answer and excuses for my late.

Ok, I put in my query this criteria:

Between Date() - WeekDay(Date(), 0) - 4 And Date() - WeekDay(Date(), 0) + 3

I think it's work!

Date() - WeekDay(Date(), 0) - 4 = Actual Weekday - 4 send to tuesday for previous weekday

Date() - WeekDay(Date(), 0) + 3 = Actual Weekday +3 send to actual tuesday not previous

Thanks A lot Scott.... from Puerto Rico Gracias
Sep 9 '07 #4

Scott Price
Expert 100+
P: 1,384
Glad you got it figured out!

¡Qué bueno que llegó a una solución!

Regards,
Scott from Perú
Sep 9 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.