Solved Function to calculate your start and end dates for the current day (1 Viewer)

[victorlindh]

How do I call this function to calculate your start and end dates for the current day. Kindly look at the attached database

 

[victorlindh]

How do I apply this code?

StartDate: GetCurrentDayDates()(0)
EndDate: GetCurrentDayDates()(1)

 

[bob fitz]

How do I apply this code?

StartDate: GetCurrentDayDates()(0)
EndDate: GetCurrentDayDates()(1)

Where do you want to use it?
In a query, you could use each line as an expression to create a calculated field for each value.
On a form, you could use:

= GetCurrentDayDates()(0)

and

= GetCurrentDayDates()(1)

as the Control Source properties of two unbound textboxes.

 

[victorlindh]

Where do you want to use it?
In a query, you could use each line as an expression to create a calculated field for each value.
On a form, you could use:

= GetCurrentDayDates()(0)

and

= GetCurrentDayDates()(1)

as the Control Source properties of two unbound textboxes.

I want to use in a query. Apparently it is not working. Thanks in advance.

 

[GPGeorge]

I want to use in a query. Apparently it is not working. Thanks in advance.

What does "it is not working" mean? Errors? Wrong results? Nothing?

Show us, please, the VBA in the function called GetCurrentDayDates() as well.

 

[bob fitz]

I want to use in a query. Apparently it is not working. Thanks in advance.

In that case, I would use the following expressions as calculated fields in the query:

startOfDay: DateValue(Now())
endOfDay: DateAdd("s",-1,DateAdd("d",1,[startOfDay]))

 

[mike60smart]

I want to use in a query. Apparently it is not working. Thanks in advance.

Why do you want to display the same date twice in the same record?

 

[victorlindh]

In that case, I would use the following expressions as calculated fields in the query:

startOfDay: DateValue(Now())
endOfDay: DateAdd("s",-1,DateAdd("d",1,[startOfDay]))

Thanks. Works perfectly

 

[bob fitz]

Thanks. Works perfectly

Good. Always glad to help if I can

 

[victorlindh]

Good. Always glad to help if I can

Thanks. I have encountered a slight problem. I am trying to filter using your expressions

WHERE >=DateValue(Now()) AND <=DateAdd("s",-1,DateAdd("d",1,[StartOfDay]))

 

[ebs17]

for the current day

WHERE YourDateTimeField >= Date()
  AND YourDateTimeField < Date() + 1

 

[bob fitz]

Thanks. I have encountered a slight problem. I am trying to filter using your expressions

WHERE >=DateValue(Now()) AND <=DateAdd("s",-1,DateAdd("d",1,[StartOfDay]))

In plain words, can you explain what criteria you want to apply?

 

[Gasman]

Why not just Date instead of DateValue(Now())

 

[victorlindh]

I have found the solution. Thanks everyone. God bless you so much.

 

[bob fitz]

I have found the solution. Thanks everyone. God bless you so much.

Can't you share it with us?

 

[victorlindh]

Can't you share it with us?

SELECT
*,
Now() >= StartDate And Now() < EndDate AS Active
FROM YourTable

Criteria
Active = True

 

[victorlindh]

SELECT
   *,
   Now() >= StartDate And Now() < EndDate AS Active
FROM YourTable

 

[Gasman]

I hope there is no time element in your fields?

 

[victorlindh]

I hope there is no time element in your fields?

I will appreciate if you added input on time element.

 

[Gasman]

I will appreciate if you added input on time element.

Depends on the values of those date fields.

Now() is 27/04/2024 12:12:20
If Endate is 27/04/2024 10:12:20 that is not going to work.
Best to use Datvalue() on those fields to be sure.

Be better to use Date() as that would be 27/04/2024 00:00:00 and so would less that EndDate, but then not > StartDate if that has time element.

Date() >= DateValue(StartDate) And Date() <= DateValue(EndDate) AS Active