Change the Date() (1 Viewer)

[gemma-the-husky]

Just wondering ...

I have a new dbs that makes heavy use of date() as a basis for various reports.

What I think I may want to do is use a different date instead, so that I can prepare some reports as of the financial year end, after the real date has moved to a new financial year.

Does the date() function use the windows system clock, or is there a way of changing the effective date without interfering with the real date.

In retrospect, I can go through and change everything, but it might be a big job, so if I can avoid it, it would be helpful.

 

[vbaInet]

You're looking for a way of overriding the Date() function right? Overriding isn't (to my knowledge) possible in VBA, in .NET or Java yes.

But with that said, overriding a function such as Date() requires much deeper analysis. So, say you use other date/time functions like Now(), you'll also need to consider overriding it in order for it to work in tandem with Date(), otherwise you'll end up with inconsistent results.

The best way is to alter your system clock or roll out your own date function. Other dbms allow you to alter the server date which some of the date/time functions rely on.

 

[MarkK]

Actually, you can override Now()

Function Now() As Date
    Now = Date - 2
End Function

Then try these two in the immediate pane . . .

? now
? vba.now

 

[vbaInet]

Strange that you can override Now() but not Date(). There must be a reason behind that.

 

[MarkK]

Because "Date" is a VBA type. You can override a VBA function, but you can't use a VBA type as an identifier or, consequently, a function name. In VB.Net this is solved using square brackets in the code, like . . .

Function [Date] As Date

. . . but in VBA it won't compile.

 

[vbaInet]

Yes good point!

 

[JLCantara]

Hi!

Instead of going into those fancy businesses, you could create a Public ReportDate as date in a utilities module then replace all Date() with ReportDate. In the Open event of the main menu, set ReportDate to Date(). Last, create a form that sets the ReportDate to whatever you want...

 

[gemma-the-husky]

Hi!

Instead of going into those fancy businesses, you could create a Public ReportDate as date in a utilities module then replace all Date() with ReportDate. In the Open event of the main menu, set ReportDate to Date(). Last, create a form that sets the ReportDate to whatever you want...

Yes, I know -just a lot of changes, so I wondered if there was an easier way. I can't just change every date either, as some do need to remain as today. I also need to think about now() as well.

 

[JLCantara]

Of course, you will not have to change the page footer... But let's think about it: any date in modules can be change using one replace. Am I mistaken in thinking that finally, it's mostly queries that need to be adjusted?

 

[vbaInet]

But let's think about it: any date in modules can be change using one replace.

JLC, the problem here is that it may have been used anywhere, i.e. query, module, control source, row source, record source, default value... the list goes on. gemma-the-husky is well aware of doing a deep-search if he wants to be able to replace all instances of Date() and that's where V-Tools comes in.

 

[JLCantara]

vbaInet:
1 - Modules: replace does the job in a single pass.
2 - Queries: I have a functions that transfers query text to a module and another one that move them back to the QueryDefs - simple to locate Date() and replace it...
3 - RowSource and ControlSource: there is no reasons to use explicit queries. Anyway, Access offers the tools to check that and correct the flaws.
4 - DefaultValue: again using Access tools to locate and modify them.

Gemma: from vba post, it is clear that there is no simple solutions...

Be patient!!!

 

[gemma-the-husky]

it's not really easy. I don't want to change every occurrence of the date()

I just want to change the usage of date() and replace it by effectivedate() for a particular set of reports and queries, so that I can run them as at a selected date, rather than as of today. I don't think the now() should be affected by this, but I need to consider that.

I definitely don't want to change the system clock either. That would not be sensible at all.

 

[JLCantara]

It is obvious that only certain occurrences of Date() need to be changed.
Simple question: how many queries, forms and reports are likely to be inspected?

 

[vbaInet]

gemma-the-husky is more than capable to tackle this from this point on... now it's just a matter of finding the occurrences in the relevant reports, forms etc and doing the needful.

If you had thought about this at a much earlier stage before development, it would have been an easy fix but unfortunately this wasn't the case. What I do in some cases is create a global Type that indicates whether I'm in Run mode, Debug mode or Testing mode(with Run mode being the default). I set this flag before running any code and when it's time to call a function/sub the necessary action/step is taken based on the indicator. Of course not every procedure I create requires this flag, just the ones I debug/test quite regularly.