Need quick help on vba date controls..

[RickDB]

Hello,

I'm trying to find a list of date / time controls that can be used in access aba. These will be for onclick event procedures on command buttons.

= Now()

is the only one that I can get to work at all in an Event Procedure!

How can you calculate these things:

- Current Date
- Current Week startingon Sunday
- Current Month (as in, 1st of the current month thru Now())
- Current year (as bove, year instead of month
- Last 7 days
- Last 30 Days
- last 365 Days

I have been searching google, clusters, this site, and have had zero luck finding an overview of these simple things!!!

Thanks!!

 

[alastair69]

Have you tried

http://www.access-programmers.co.uk/forums/showthread.php?t=103747

 

[RickDB]

Is there seriously not just a simple list of these and similiar functions to be found? It seems like it would be a handy resource for those trying to learn. I'm amazed.

 

[KernelK]

Lookup Format Function in the VBA help. If what you mean by calculate is how do you display these items, then the Format function just became your new best friend. It can display all of these for you:
- Current Date
- Current Week startingon Sunday
- Current Month (as in, 1st of the current month thru Now())
- Current year (as bove, year instead of month

But I'm not too clear on what you mean by these:
- Last 7 days
- Last 30 Days
- last 365 Days

Are you trying to popluate a list box with these values?

 

[RickDB]

I would like to be able to modify the criteria for a query using an option group of radio buttons.

Basically run a query based on:

Current date
Current week to date
Current Month to date
the past 7 days (todays date - 7 days would be the start date)
the past 31 days (todays date - 31 days would be the start date)
the past 365 days (todays date - 365 days would be the start date)

This seems so simple, but everything I try using the Now() function does not work. If I had just a couple of samples of calculating the above, I should be able to make this work.

Basically, all I need is a quick simple sample.

Thank you!

 

[RickDB]

I am using the following code right now to initiate the search and set the default StartDate / EndDate, which my query uses as criiteria for the search:

Private Sub cmdSearch_Click()
If IsNull(Me.txtStartDate) Then
Me.txtStartDate = "01/01/2000"
End If
If IsNull(Me.txtEndDate) Then
Me.txtEndDate = Int(Now())
End If
Me.SubformBasedOnQuery.Requery
End Sub

I would like to modify that code to calculate the above listed ranges for searching.

Thank you!

 

[gemma-the-husky]

the past 7 days would be

between date() and date()-7

(in a query criteria)

etc.


If you want to use these dates from a form, although others do it other ways, I would save the dates in a global variable. You then have to retrieve them with a function, for use in a query. (Others read them directly from the form)

so in a commandbutton say you

gbldate1 = txtdate1
gbldate2 = txtdate2

define functions (these have to be in module)
function readdate1 as date
readdate1=gbldate1
end function

etc

and then in your query you can use

between readdate1() and readdate2()

 

[KernelK]

I agree with gemma, this is the best practice for using variables in your query criteria. Then to futher gemma's post, you would just have a Select Case or a few If Thens to check your radio buttons and assign the correct start/end date for the query (also, I prefer to use the Date function as opposed to now, now returns a time with it and will confuse your queryies when specifying ranges. You will end up formatting the Now values to remove the time or else you will run into problems):

Current date:
StartDate = Date
EndDate = Date

the past 7 days:
StartDate = Date - 7
EndDate = Date

the past 31 days:
StartDate = Date - 31
EndDate = Date

Current Month to date:
StartDate = DateAdd("d", -(Format(Date, "d") - 1), Date)
EndDate = Date

Current Week to Date:
I can't think of any easy way to do this one. But an approach you could use is to use Format(Date, "dddd") which returns the name of the current day as a string ie "Thursday" then based on this you can do a Select Case or use IF Thens to figure out how far from Sun you are and then use that Number in the DateAdd function.

 

[RickDB]

Thanks 'KernelK', I am looking in the help file now.

Husky, thank you, I knew it would be simple like that, I was using extra () to wrap the calculation and kept getting errors. You're always point on, 100% of the time!

Your suggestion also sparked peabrained idea ) , that's the best I can do)...


could I use a global variable like that to create a very remedial audit system?

Have a main screen that requires a user enters their user name and hits a command button, which would set a global variable "gblUser=[txtEmployeeID]" to the entry and then makes the remainder of the menu visable. From then on, upon data entry, could I not use "gblUser" as the entry that goes into an EmployeeID field in a table?

I'm thinking on screen here, but I'm sure their would be more to it than that, that is too easy... (By the way, the concerns many have over secure logins and audit trails would not be a big concern because there will only be a couple isolated users.)

Thanks Husky!!!

 

[RickDB]

Right on KernalK, that is a really big help.

I know it's ridiculous I haven't wrapped my head around this at this point, but I think I have it down now.

Thanks to both of ya again!!!

 

[KernelK]

I used to be able to just use a global variable to store the username of the current user, it works very well, but then I had to start using User-Level security and can just use the built in CurrentUser function instead. One tip if you decide to use a global variable to store a username, when you are testing and debugging, if and when you run into an error and you reset the project or end the project, then your global variable will be reset as well and you will need to set it back by either using the immiediate window or running the code where it is initially set at. For example, my DB used to set this variable before the main switchboard even came up, at a login form. So, when testing my application, if I had to reset the project or end the debugger, I would have to reload the Login form and "login" again or set the variable manually from the immiediate window. If you don't then any code that relies on this variable will inevitably fail. Just something to keep in mind, as it took me a little bit to realize this is what was causing my problems when it happened to me.

 

[RickDB]

If I could just askk one more question on this subject:

Could you dissect the syntax of this statement:

DateAdd("d", -(Format(Date, "d") - 1), Date)

I assume that the first "d" declares that the enclosed () statement will alter the day, and that (Format(Date, "d") - 1) is the Function that decalares the "d" in the Date call is changed to 1.

It seems redundant, why is the "d" needed 2 times? Are the other operators in the Date function "m" , "y" for month and year.

What are the operators in the Now statement? m, d, y, h, m, s

If that works for altering the current date, could you use the Now function to alter the date and time? Say I wanted to assign a value of a week ago yesterday at 8:00 A.M. How would that be done? (Sorry I am being difficult, I would only ever use this to go back to the beginning of a workday, but seeing how the above example is done will hopefully show me how to find any day / time relative to Now)

Thanks again Dave & KernalK!

 

[gemma-the-husky]

--------------------------------------------------
now statement
month day year hour minute second.

now returns a date/time type, which you can choose to display in any standard format, or design any particular one for your own display purposes
--------------------------------------------------
setting a date.

Access stores dates as a number representing a count of milliseconds from a given start. We can display this info any way we want.

One way of setting a date is to use the function datevalue("datestring") which takes ANY sensibly formmated date and time string and turns it into the correct date value, but it needs to look like a date/time.

as well as now() [includes the time] there is also date() which excludes the time, and time() which excludes the date.

As an example this will help show you what it does.

Sub showit()
dim mydate as date
mydate = (DateValue(Now - 7) + TimeValue("08:00pm"))
msgbox(mydate)
End Sub

-------------------------------------
DateAdd("d", -(Format(Date, "d") - 1), Date

dateadd("d",n,mydate)

is an instruction to add a time type - in this case d=day
but it could be "h" = hours

it adds a certain number of days represented by the central term n

to the date represented by the final term mydate

I am not sure if the central term {Format(Date, "d") - 1} is correct in this context.

 

[KernelK]

Both d parameters are necessary in this case because I am using two functions that both require me to specify what part of the date I am modifying. I'll break down my function and show you how it works.

The nested function: Format(Date, "d")
The first parameter of the Format statement is string/date we wish to format, the second paramter is the actual formatting, it is kind of like an input mask formatting, using special characters to format. d simply returns just the numerical value of the day in a date. Another example to show you how it works is Format(Date, "yyyymmdd") would return 20061103 for today.Today is Nov, 3rd, so our original function will return the number 3.

If you look at the parent function with this in mind you will see that:
DateAdd("d", -(3 - 1), Date) for today

The DateAdd function will add/subtract numerical values for any given portion of a date (days, weeks, hours, etc).
What I did here is to realize that to get to the first day of the current month, you need only subtract the numerical value for today - 1 from the numerical value of today. So today is 3, to get to the 1st then we subtract 2. So in my DateAdd function the first parameter specifies that we are modifying the days portion, I made my value for the second parameter negative to subtract, and the third parameter is the date we are modifying, in this case today. There are probly other ways of expressing the first day of the current month but this what popped into my head first.

 

[RickDB]

You two should open a consulting firm together

Thanks a ton!!!

 

[RickDB]

So I have another problem now on these date controls...

I have a query that a subform uses to generate search results. It works great, except for one thing... It never includes results from today.

I am using the Now() function now, and KernalK I know that you said to use the Date function instead, and I have set up that way as well with the same results, always omitting today's entries.

Thanks for any help.

(Dave, I have been working with your recommendation of using variables and have not got it implemented yet)

The Query uses the following criteria:

For dates:
Between [Forms]![frmMainSearch]![txtStartDate] And [Forms]![frmMainSearch]![txtEndDate]

For all other:
Like "*" & [Forms]![frmMainSearch]![txt-FIELD-NAME] & "*"

Here is the VBA behind the form:

Option Compare Database
Option Explicit

Private Sub cmdSearch_Click()
If IsNull(Me.txtStartDate) Then
Me.txtStartDate = "01/01/2006"
End If
If IsNull(Me.txtEndDate) Then
Me.txtEndDate = Now()
End If
Me.SubformBasedOnQuery.Requery
End Sub

Private Sub cmdReset_Click()
Me.txtEmployee = Null
Me.txtCustomerName = Null
Me.txtPhone = Null
Me.txtCity = Null
Me.txtState = Null
Me.txtZip = Null
Me.txtStartDate = Null
Me.txtEndDate = Null

Me.SubformBasedOnQuery.Requery
End Sub

Private Sub cmdSearch_Enter()
If IsNull(Me.txtStartDate) Then
Me.txtStartDate = "01/01/2006"
End If
If IsNull(Me.txtEndDate) Then
Me.txtEndDate = Date
End If
Me.SubformBasedOnQuery.Requery
End Sub

Private Sub cmdSearchCurrent_Click()
Me.txtStartDate = DateAdd("d", -(Format(Date, "d") - 1), Date)
Me.txtEndDate = Now()
Me.SubformBasedOnQuery.Requery
End Sub

Private Sub cmdSearchMonth_Click()
Me.txtStartDate = Now() - 30
Me.txtEndDate = Now()
Me.SubformBasedOnQuery.Requery
End Sub

Private Sub cmdSearchToday_Click()
Me.txtStartDate = Now() - 1
Me.txtEndDate = Now()
Me.SubformBasedOnQuery.Requery
End Sub

Private Sub cmdSearchWeek_Click()
Me.txtStartDate = Now() - 7
Me.txtEndDate = Now()
Me.SubformBasedOnQuery.Requery
End Sub

Private Sub cmdViewQuery_Click()
DoCmd.OpenQuery "qrySearch"
End Sub

 

[pbaldy]

If the data in the table includes time, then your criteria will have to account for it. IOW, if the data looks like:

11/7/06 2:23 PM

then searching through 11/7/06 will miss that record, since if you don't specify time it will assume it to be 00:00.