Re: How do you store a single constant in Access?

Albert
I have now tried option 1 solution, and still hit a snag. I have to say
that I prefer method 2 (using a form) and my other post re. that problem
takes precedence, for me. Even so, here is my problem with method 1.

I have a standard module that reads ...
Option Compare Database
Public MyRollDate As Variant 'possibly should be Long
Private Sub Autoexec()
Set MyRollDate = CurrentDb.OpenRecordset("T_RollDate")
End Sub
Public Function FnRollDate(RollDate As Long) As Variant 'possibly should be
Long
FnRollDate = MyRollDate(RollDate)
End Function

I have a query
UPDATE T_Clients
SET T_Clients.RollDateStage1 =
DateSerial(Year(FnRollDate("RollDate")),[YEM],[YED]);

If I run the query I get "Data type mismatch in criteria expression".

Help!!?? Thanks

"Albert D. Kallal" <PleaseNOOOsPAMmkallal@xxxxxxx> wrote in message
news:uuSEAwU4GHA.3740@xxxxxxxxxxxxxxxxxxxxxxx
If the value really is a constant, then you can hard code it in your code.

However, you mentioned that users *might* need to change this So, then, so
save values from a user, it makes sense to build a table to hold those
values. So, you can make a one record table to store those values.

Not only does hits solve the problem of a USER being able to change the
value, but, when you close the database, the values are saved! (kind of
what a database is for!!!).

To use the values through out the application, you have two general
choices

1) in your start-up code, open up a global reocrdset
or
2) open a up a form, and make it invisible.

With idea #1 above, you will have to write a function to return the value
in question (you need to do this, since you can use variables, or values
from a table directly in your quires - so, you build a function to do
this).


eg:

startup code:
set gblrstDefaults = currentdb.OpenRecordSet("tblDefaults")

The above thus opens a reocrdset. the variable gblrstDefaults would be a
global variable (one defined as public in a standard code module).

Now, in that same code module, we can place a public function like:

Public Function GetMyDefault(strField As String) As Variant

GetMyDefault = gblrstDefaults(strField)

End Function


Now, in forms, or sql quires, you can return that value like

GetMyDefault("Defaultcity")

You could also dispense with having to "pass" the name of the field you
want if you only have ONE value to store

eg:

Public Function GetDefaultCity as string

GetDefaultCity = gblrstDefaults!City

end function

It is your choice..if you only have a few fields/values that you save,
then you can write a new function for each new field in this one record
table, but my first example forces you to pass the field name, but the
tradeoffs is that for each default value, you don't have to write a new
function.

Default city would be the default for a city. We had to write that
little function since sql query does not allow us to reference variables
directly (but, a query or form can reference a public function directly).

Option #2 is to open a form that is attached to that one record table.
Then, you can use anywhere

forms!MyDefaultForm!DefaultCity

So, you can use that forms expression anywhere you want.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx


"Jack ***" <mind-the-gap@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:uhxS4EU4GHA.2144@xxxxxxxxxxxxxxxxxxxxxxx
Currently I have a select query that prompts for a value when I run the
query. It is a single value that does not vary with any records. How
would I go about storing that constant separately within the database,
for the query to interrogate when run so that the query does not prompt
for it? Periodically the user would want to overwrite that value, which
would then be stored in the same place. I would also want to be able
separately to interrogate the value of that constant via a query.

Thanks

--
Return email address is not as DEEP as it appears





.

Quantcast