VBA constant in sql is it only evaluated once?

[petebob796]

Hi at the moment I am using a bit of VBA code like below:

Public Const currentYear As String = "0708"

Static Function GetCurrentYear()
GetCurrentYear = currentYear
End Function

I then call this from my ms access sql statement with GetCurrentYear() am I correct in thinking this will only need to be evaluated once (I am just thinking in terms of performance) as it is a static function and a constant or is there a quicker way to do this. I couldn't see a way to easily get the value from a constant without a function. I may be missing something though thanks for any advice.

 

[Banana]

You could just hard-type it in the Criteria field.

That said, I'd imagine there'd be a minor performance gain since Jet will execute a query plan and the more it knows about what a query wants to collect, the faster it can perform the job.

 

[WayneRyan]

PB,

Your function will only be evaluated once.

Any global variables (or constants) are accessed through function calls in
Access queries.

You could store the values on a hidden form and use form references in
your queries.

I use some of each method and don't think the performance difference is
anything to speak of.

hth,
Wayne

 

[petebob796]

reply to banana

Well banana it is currently hard coded but thats not very good programing practice as it is used in about 200 queries to perform error checking and validation on data (someone else wrote it). The number/actually a string is the academic year so 0607 last year 0708 this year it would be nice to be able to each year just change the constant rather than go through all queries again.

I have tried it now and it seems to run at the same speed as before so im happy.

 

[boblarson]

Well banana it is currently hard coded but thats not very good programing practice as it is used in about 200 queries to perform error checking and validation on data (someone else wrote it). The number/actually a string is the academic year so 0607 last year 0708 this year it would be nice to be able to each year just change the constant rather than go through all queries again.

I have tried it now and it seems to run at the same speed as before so im happy.

If it's going to change year to year, then coding in a constant isn't a good way either. You should store the value in a table and retreive it upon opening the database. That way NO coding has to be changed EVER for that change of year and you can just open a table to change it.