Basic question

  • Thread starter Thread starter John in Va
  • Start date Start date
J

John in Va

Just a general question...I read some of these threads and learned how to a
Fuction so that I could use a variable in the criteria field for a query.

Problem is, I want my variable to be whatever is in a certain field of a
table. The table has only 1 record and I need whatever is in that
field...sounds easy but it is whupping me good!

Any ideas?
 
If there is just one record in the table, DLookup without criteria will
return a field from that record:
DLookup('SomeField","YourTable")

There shouldn't be much of a performance issue with a one-record table.

Does the value in the one record ever change? Can you include the table in
the query? What function are you using? What have you tried so far?
 
John in Va said:
Just a general question...I read some of these threads and learned how to
a
Fuction so that I could use a variable in the criteria field for a query.

Problem is, I want my variable to be whatever is in a certain field of a
table. The table has only 1 record and I need whatever is in that
field...sounds easy but it is whupping me good!

Any ideas?
Click to expand...


The simple method is to use the DLookup function to get the field value from
the table. Under some circumstances, this may be slow (if the function has
to be called over and over again in a loop). In such cases, you can write a
VBA function that calls DLookup to get the value only once, stores it in a
static variable, and returns the stored variable thereafter.

If you're using a simple DLookup with static arguments as a criterion in a
query, the function will only be called once, so you don't need to worry
about repeated calls making it slow. On the other hand, if your call to
DLookup involved row values from the query, it would be called for every
row, slowing things down enormously.
 
YOu could use DLookup to get the value in the table.

DLookup("[Some Field]","[Some Table]")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
SPOT ON! Not only did you provide the right fuction but also let me know I
was using the wrong syntax...thanks to all who posted...unbeleivable how fast
you all resonded...thanks again!
 
Back
Top