Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Thanked 0 Times in 0 Posts

    Date variable (Access 2000)

    I have a special module called constants where i keep my different Date Constants as :
    Public Const strInvoiceDate As String = "And ((orders.invoicedate) > #1/1/2001#)"
    Public Const strInvoiceDate2 As String = "AND ((Orders.invoicedate)>#1/1/2001#))"
    Public Const strInvoiceDate3 As String = " WHERE (((Orders.invoicedate) > #1/1/2000#))"
    Public Const strYear As String = " WHERE (((Year([InvoiceDate]))=2001)" ' to be found in the report Products Annual
    Public Const strOrderDate = " AND ((orders.orderdate)>#1/1/2001#))"
    So each year i change the date for example > #1/1/2002#
    Is there another way of doing it,to change the year only once for all the constants for my database?
    I have tried with Dim Mydate = > #1/1/2001# but i coulnd make the constant or the variable

    I will be grateful for any help

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: Date variable (Access 2000)

    Variables and constants are two different things.

    You declare a variable as a data type but you can't assign it a value in the dim statement. The variable has its value assigned only in another statement within a sub or function and it only holds that value until it goes out of scope.

    You declare a constant as a data type and assign it a one-time value, but you can't change it elsewhere.

    I would change your constants to something like this:

    Public Const strInvoiceDate As String = "((orders.invoicedate) > #1/1/"

    I would not have separate constants to add a single paren as you have in strInvoiceDate and Date2 and I wouldn't include the AND or WHERE in those constants, since those, along with the closing parens and the closing # can easily be added to your sql string in code or be stored in still another constant if you feel it's absolutely necessary. Then, I would simply use Year(Now()) in code to assign the current year to a variable or to add it directly to the sql I was generating.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Crystal Beach, FL, Florida, USA
    Thanked 41 Times in 40 Posts

    Re: Date variable (Access 2000)

    Quite frankly, I don't think this was the right approach to take at all. You traded the slight inconvenience of having to type a string each time with the major inconvenience of having to figure out what to do each year.

    I personally would eliminate the constants altogether. It will make your code easier to read since you will see the actual string "AND orders.invoicedate....." instead of strInvoiceDate. I would then use either a global variable (which I tend not to like to use), or better still use a hidden form that is always open and you can refer to. This form would contain a control containing date you want to use. You SQL code would then look something like this:

    "And orders.invoicedate>#" & Forms!yourformname!yourdatecontrol & "#"
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts