Date variable (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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 & “#”

Reply | Quote