Referencing Tables in Functions

[RMires]

I am trying to create a function which will lookup the volume in a table when
given the gauge of a tank. Each tank has a separate table.

Function Strap(MeasGauge As Double, Tank As String) As Double
Strap = DLookup("[Volume]", "tblStrap"+Tank, MeasGauge =
["tblStrap"&Tank].[Gauge])
End Function

MeasGauge is the gauge
Tank is the tank I want to look up.
The tables are named:
2611tblStrap
2612tblStrap
2613tblStap etc.

The tables each have Fields named Gauge and Volume. I do not know the proper
procedures for referencing tables in a function, especially with variable
table names.

 

[Jeff Boyce]

Why does your database have separate tables for each tank?

This sounds like a good design ... for a spreadsheet! Are you saying that
there are so many differences from one tank to the next that a common table
with "tank" information is not feasible?

Access is a relational database, and to get the most of the features and
functions, your data needs to be well-normalized. Before you pursue finding
a way to force Access to do this, consider normalizing your data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tom Wickerath]

Each tank has a separate table.

Big red flag. This is an improper design. All tank data should be in the
same table. Add a field to the table to designate which tank.

With your current design, you could try something like the following
(untested):

Dim strTank As String
strTank = "tblStrap '" & Tank &"'"

Strap = DLookup("[Volume]", "strTank", "MeasGauge = " & Gauge)


Does Gauge really need to be a Double? You might be better off passing it
in as a Single instead.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[RMires]

This is only a small part of a very large database so seperate tables are
necessary for other forms and querys.

I really don't understand all of the quotation marks in your
strTank = "tblStrap '" & Tank &"'"
Does the & Tank & have single AND double quotation marks around it?

The compiler calls the Gauge in
Strap = DLookup("[Volume]", "strTank", "MeasGauge = " & Gauge)
An undefined variable.

Each tank has a separate table.

Big red flag. This is an improper design. All tank data should be in the
same table. Add a field to the table to designate which tank.

With your current design, you could try something like the following
(untested):

Dim strTank As String
strTank = "tblStrap '" & Tank &"'"

Strap = DLookup("[Volume]", "strTank", "MeasGauge = " & Gauge)


Does Gauge really need to be a Double? You might be better off passing it
in as a Single instead.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I am trying to create a function which will lookup the volume in a table when
given the gauge of a tank. Each tank has a separate table.

Function Strap(MeasGauge As Double, Tank As String) As Double
Strap = DLookup("[Volume]", "tblStrap"+Tank, MeasGauge =
["tblStrap"&Tank].[Gauge])
End Function

MeasGauge is the gauge
Tank is the tank I want to look up.
The tables are named:
2611tblStrap
2612tblStrap
2613tblStap etc.

The tables each have Fields named Gauge and Volume. I do not know the proper
procedures for referencing tables in a function, especially with variable
table names.

 

[Tom Wickerath]

This is only a small part of a very large database so seperate tables are

necessary for other forms and querys.

I find it very difficult to believe that this is a good justification. How
much do you know about the topic of database normalization?

In my previous post, I did indicate untested. Here is some tested code that
works, as long as the field name is Gauge in the table. If you misspell the
name (as I initially did, by using Guage), then you will get a very cryptic
"Run-time error '2001' You canceled the previous operation. Say what,
Access?

Option Compare Database
Option Explicit

Function Strap(MeasGauge As Double, Tank As String) As Double
On Error GoTo ProcError

Dim strTank As String
strTank = "tblStrap" & Tank

Strap = Nz(DLookup("Volume", strTank, "Gauge = " & MeasGauge), 0)


ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Strap..."
Resume ExitProc
End Function



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

This is only a small part of a very large database so seperate tables are
necessary for other forms and querys.

I really don't understand all of the quotation marks in your
strTank = "tblStrap '" & Tank &"'"
Does the & Tank & have single AND double quotation marks around it?

The compiler calls the Gauge in
Strap = DLookup("[Volume]", "strTank", "MeasGauge = " & Gauge)
An undefined variable.

 

[RMires]

That works great...except it only works for the first record of my query. I
may be able to figure that one out though. To be honest, I know very little
about database normalization. I'm learning as I go. Do you have any
suggestions for references or manuals or such? I tried putting all of the
tank data on one table, but it led to three dimensional records
(tblDaily2611.Gauge, tblDaily2611.Temp, tblDaily2612.Gauge,
tblDaily2612.Temp, etc). Things are working better with seperate tables, but
it doesn't seem very efficient to me. I realize that the application I'm
trying to create is ambitious beyond my abilities, but if I can get it to
work it will save a LOT of headache for several people. Also, I am learning a
lot as I go, though I wish there were better references on Access available.

This is only a small part of a very large database so seperate tables are
necessary for other forms and querys.

I find it very difficult to believe that this is a good justification. How
much do you know about the topic of database normalization?

In my previous post, I did indicate untested. Here is some tested code that
works, as long as the field name is Gauge in the table. If you misspell the
name (as I initially did, by using Guage), then you will get a very cryptic
"Run-time error '2001' You canceled the previous operation. Say what,
Access?

Option Compare Database
Option Explicit

Function Strap(MeasGauge As Double, Tank As String) As Double
On Error GoTo ProcError

Dim strTank As String
strTank = "tblStrap" & Tank

Strap = Nz(DLookup("Volume", strTank, "Gauge = " & MeasGauge), 0)


ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Strap..."
Resume ExitProc
End Function



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

This is only a small part of a very large database so seperate tables are
necessary for other forms and querys.

I really don't understand all of the quotation marks in your
strTank = "tblStrap '" & Tank &"'"
Does the & Tank & have single AND double quotation marks around it?

The compiler calls the Gauge in
Strap = DLookup("[Volume]", "strTank", "MeasGauge = " & Gauge)
An undefined variable.

 

[Tom Wickerath]

That works great...except it only works for the first record of my query.

Can you show us the SQL statement for your query? To do so, open your query
in design view. Then click on View > SQL View. Copy the SQL statement that
you should see and paste it into a reply.

To be honest, I know very little about database normalization. I'm learning
as I go.....I realize that the application I'm trying to create is ambitious
beyond my abilities, but if I can get it to work it will save a LOT of headache
for several people.

That's exactly how most people learn! By pushing yourself out of your
comfort zone. Congratulations. You know you're making progress when you are
continuing to learn.

Do you have any suggestions for references or manuals or such?

Yes. Try this link at the Access Junkie's web site:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

I tried putting all of the tank data on one table, but it led to three dimensional
records (tblDaily2611.Gauge, tblDaily2611.Temp, tblDaily2612.Gauge,
tblDaily2612.Temp, etc).

I'm not sure exactly what you mean by "three dimensional records". However,
on first glance, you appear to have a one-to-many (1:M) relationship between
tank number and recorded temperatures for a given time (ie. one tank can have
many temperature readings). So, that would require two tables. You'd likely
want to record the date, or perhaps the date & time, of each temperature
reading for each tank.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________