Built-Ins For Date Arithmetic Intervals?

  • Thread starter Thread starter (PeteCresswell)
  • Start date Start date
P

(PeteCresswell)

Right now, I'm doing this:

' ---------------------------------------------------------------
Global Const gDateInterval_Day As String = "d"
Global Const gDateInterval_Week As String = "ww"
Global Const gDateInterval_Month As String = "m"
Global Const gDateInterval_Quarter As String = "q"
Global Const gDateInterval_Year As String = "yyyy"
' ---------------------------------------------------------------


But it looks kind of lame in light of my suspicion that
VBA must have built-in constants for those values.

If it does, does anybody know the naming convention?
 
In
(PeteCresswell) said:
Right now, I'm doing this:

' ---------------------------------------------------------------
Global Const gDateInterval_Day As String = "d"
Global Const gDateInterval_Week As String = "ww"
Global Const gDateInterval_Month As String = "m"
Global Const gDateInterval_Quarter As String = "q"
Global Const gDateInterval_Year As String = "yyyy"
' ---------------------------------------------------------------


But it looks kind of lame in light of my suspicion that
VBA must have built-in constants for those values.

If it does, does anybody know the naming convention?
Click to expand...

Looking at the VBA object library in the Object Browser, I don't see any
defined constants for those values.
 
if you were using SQL Server and ADP then you could do this:

DateDiff(Day, Date1, Date2)



I mean come on guys-- talk about re-inventing the wheel
 
Per Jeff Boyce:
How are you intending to use these?
Click to expand...


In a routine that spawns records by incrementing dates.

One of the passed arguments is the interval to increment by.
 
In
(PeteCresswell) said:
Per Jeff Boyce:


In a routine that spawns records by incrementing dates.

One of the passed arguments is the interval to increment by.
Click to expand...

Pete -

If you're spawning records (great term!), are you familiar with the
query-cross-product method of generating records? You start with a
table containing, say, 10 (or 100) records, with a single number field,
numbered from 1 to 10 (or 100). There are no other fields in the table.
Following the lead of the person who showed my this trick, I usually
call this table "Iotas", and name the field "Iota". How many records
you seed the table with depends on how many records you are likely to
need to generate.

Now, with this table containing 10 records, if I want to generate 100
records, I use a query that includes the table twice, with no joins
between the two tables, like this:

qryMake100Records:
SELECT [A].[Iota]+(10*.[Iota]) AS Iota
FROM Iotas AS A, Iotas AS B;

If I need 1000 records, I include the table three times:

qryMake1000Records:
SELECT [A].[Iota]+(10*.[Iota])]+(100*[C].[Iota]) AS Iota
FROM Iotas AS A, Iotas AS B, Iotas AS C;

By including the appropriate "Iotas" query in other queries, I can
generate as many records as I want. The final query may or may not use
the actual Iota field for anything. I don't remember who showed me
this, but I've found it quite handy.
 
Back
Top