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
4 2277
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?
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. - Public Function MyTuesday() As String
-
-
Dim MyDate As Date
-
Dim MyDate1 As Date
-
Dim i As Integer
-
-
MyDate = Date - 8
-
-
'loop thru 7 days of week to find week ago Tuesday
-
For i = 0 To 6
-
'datepart for weekday, 1=sun, 7=sat
-
If DatePart("w", MyDate - i) = 3 Then
-
MyDate1 = MyDate - i
-
Exit For
-
End If
-
Next
-
-
MyTuesday = MyDate1
-
'Debug.Print MyTuesday
-
End Function
-
-
Public Function MyMonday() As String
-
Dim MyDate As Date
-
Dim MyDate1 As Date
-
Dim i As Integer
-
-
MyDate = Date
-
'loop through 7 days of week to find previous Monday
-
For i = 0 To 6
-
'datepart for weekday, 1=sun, 7=sat
-
If DatePart("w", MyDate - i) = 2 Then
-
MyDate1 = MyDate - i
-
Exit For
-
End If
-
Next
-
-
MyMonday = MyDate1
-
'Debug.Print MyMonday
-
End Function
Then the query will look like this: - SELECT tblEvent.EventID, tblEvent.EventDate
-
FROM tblEvent
-
WHERE (((tblEvent.EventDate) BETWEEN MyTuesday() AND MyMonday()));
-
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, 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. - Public Function MyTuesday() As String
-
-
Dim MyDate As Date
-
Dim MyDate1 As Date
-
Dim i As Integer
-
-
MyDate = Date - 8
-
-
'loop thru 7 days of week to find week ago Tuesday
-
For i = 0 To 6
-
'datepart for weekday, 1=sun, 7=sat
-
If DatePart("w", MyDate - i) = 3 Then
-
MyDate1 = MyDate - i
-
Exit For
-
End If
-
Next
-
-
MyTuesday = MyDate1
-
'Debug.Print MyTuesday
-
End Function
-
-
Public Function MyMonday() As String
-
Dim MyDate As Date
-
Dim MyDate1 As Date
-
Dim i As Integer
-
-
MyDate = Date
-
'loop through 7 days of week to find previous Monday
-
For i = 0 To 6
-
'datepart for weekday, 1=sun, 7=sat
-
If DatePart("w", MyDate - i) = 2 Then
-
MyDate1 = MyDate - i
-
Exit For
-
End If
-
Next
-
-
MyMonday = MyDate1
-
'Debug.Print MyMonday
-
End Function
Then the query will look like this: - SELECT tblEvent.EventID, tblEvent.EventDate
-
FROM tblEvent
-
WHERE (((tblEvent.EventDate) BETWEEN MyTuesday() AND MyMonday()));
-
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
Glad you got it figured out!
¡Qué bueno que llegó a una solución!
Regards,
Scott from Perú
Sign in to post your reply or Sign up for a free account.
Similar topics
by: David B |
last post by:
I have 2 querys with the following in the date criteria
Between DateAdd("m",-6,(!!)) And
(!!)
Between DateAdd("m",-24,(!!)) And
DateAdd("m",-6,(!!))
Problem is they both look at the same...
|
by: Matthew |
last post by:
I have a form with fields that act as user specified criteria for a
form. Two of the fields are start and finish date. The fields in my
query are "ID", "TimeStart", "TimeEnd", etc. Currently, I...
|
by: Matthew |
last post by:
Hey ,
I have built a query which takes values from unbounded fields on a
form, and it all works except for one thing. I have a few fields in
my query that are dates. I also have a start and...
|
by: dskillingstad |
last post by:
I'm trying to set up a parameter query based on an unbound form. This
search form has about 5 text boxes where the user can type in values
within each box and search a specific table based on the...
|
by: meganrobertson22 |
last post by:
Hi Everyone-
I have a question about how to add and then use the "All" selection in
a combo box. I am trying to figure out how to:
(1) add "All" as a selection to a combo box and then
(2)...
|
by: EJO |
last post by:
In the query builder, I can create a query that will take a date/time
as criteria, and the query finds the records:
SELECT SRSrvcsEquip.Stock, SRSrvcsEquip.Activity,
SRSrvcsEquip.EquipOwned,...
|
by: Mark Roughton |
last post by:
I have a form where the users need to view records for various
criteria, one of which is a date field on which they may wish to view
all related data for the selected date, for all dates upto and...
|
by: Dr Al |
last post by:
I have a table with four date fields, some of which may not be filled
in based on our data entry needs. I have a criteria set as <date()-180
which is supposed to pull dates older than 180 days ago....
|
by: sharsy |
last post by:
Hello guys,
I would like some help in generating query criteria that will identify
credit cards that have expired on an access database.
The specific Field is formatted with a Data Type of...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |