Dim a table's field

  • Thread starter Thread starter Joker via AccessMonster.com
  • Start date Start date
J

Joker via AccessMonster.com

Hello,

I have a quick question. Can you dim a variable and then set that variable
equal to a table's field? What I tried is below:
Dim Frequency2 As String

Frequency2 = [tbl_RemitDays]![Frequency]

I am getting an object required error. Can anyone please provide me with
some input on this. Thanks.
 
If there's only a single row in the table (or if you don't care which row's
value is returned), you use DLookup:

Frequency2 = DLookup("[Frequency]", "[tbl_RemitDays]")

If you know how to uniquely identify the row of interest, you can add that
as a 3rd parameter:

Frequency2 = DLookup("[Frequency]", "[tbl_RemitDays]", "[ID] = 3")

Alternatively, you open a recordset, and loop through it.
 
Joker via AccessMonster.com said:
Hello,

I have a quick question. Can you dim a variable and then set that
variable equal to a table's field? What I tried is below:
Dim Frequency2 As String

Frequency2 = [tbl_RemitDays]![Frequency]

I am getting an object required error. Can anyone please provide me
with some input on this. Thanks.
Click to expand...

Unless this code is running on a form that is bound to tbl_RemitDays, or
to a query that includes the desired field from that table, you need to
use one of the domain aggregate functions, probably DLookup(), to look
up the field value for the record you want. The question is, how will
you identify which record in that table contains the value you want? If
you just write,

Frequency2 = DLookup("Frequency", "tbl_RemitDays")

you'll get the value from whatever record comes up first when the
function queries the table. If, on the other hand, the table has a
primary key field named, for example, "ID", you could look up the value
for a specific record, like this:

Frequency2 = DLookup("Frequency", "tbl_RemitDays", "ID=123")

That would get you the Frequency value from the record with ID = 123.
 
Maybe I need to explain what I'm trying to do more detailed. I have a large
table (tbl_RemitDays) set up similar to this

Number | Frequency | Days
---------------------------------------------------
001 | CD | 18
32C | BD | 5
F34 | FCD | 5


I want to run this function in a query connecting to a daily report by Number.
I have the code setup with Case statements.. eg..
Select Case Frequency2
Case "BD"
GoTo BDFunk 'Call Function BDFunk
Case "CD"
GoTo CDFunk 'Call Function CDFunk
Case "Daily" 'Daily only returns the current date. This is because the
remittance would drop last night with 1BD making it today.
GoTo Daily
Case "FCD"
GoTo FCDFunk 'Call Function for FCD

I need the code to look at that specific record in the table to know which it
is so the Case statement can send it to the correct date function. Thanks a
lot Douglas.
 
Joker via AccessMonster.com said:
Maybe I need to explain what I'm trying to do more detailed. I have
a large table (tbl_RemitDays) set up similar to this

Number | Frequency | Days
---------------------------------------------------
001 | CD | 18
32C | BD | 5
F34 | FCD | 5


I want to run this function in a query connecting to a daily report
by Number. I have the code setup with Case statements.. eg..
Select Case Frequency2
Case "BD"
GoTo BDFunk 'Call Function BDFunk
Case "CD"
GoTo CDFunk 'Call Function CDFunk
Case "Daily" 'Daily only returns the current date. This is
because the remittance would drop last night with 1BD making it today.
GoTo Daily
Case "FCD"
GoTo FCDFunk 'Call Function for FCD

I need the code to look at that specific record in the table to know
which it is so the Case statement can send it to the correct date
function. Thanks a lot Douglas.
Click to expand...

Sounds like you should probably just join your tbl_RemitDays to the
appropriate table in the query your report is based on, and include the
fields you need from tbl_RemitDays in the query's list of fields
selected. Then the information would be available without need to call
the DLookup function at all.
 
Maybe I need to explain what I'm trying to do more detailed. I have a large
table (tbl_RemitDays) set up similar to this

Number | Frequency | Days
---------------------------------------------------
001 | CD | 18
32C | BD | 5
F34 | FCD | 5

I want to run this function in a query connecting to a daily report by Number.

I have the code setup with Case statements.. eg..
Select Case Frequency2
Case "BD"
GoTo BDFunk 'Call Function BDFunk
Case "CD"
GoTo CDFunk 'Call Function CDFunk
Case "Daily" 'Daily only returns the current date. This is because the
remittance would drop last night with 1BD making it today.
GoTo Daily
Case "FCD"
GoTo FCDFunk 'Call Function for FCD

I need the code to look at that specific record in the table to know which it
is so the Case statement can send it to the correct date function. Thanks a
lot guys.
 
Dirk said:
Maybe I need to explain what I'm trying to do more detailed. I have
a large table (tbl_RemitDays) set up similar to this
Click to expand...
[quoted text clipped - 21 lines]
which it is so the Case statement can send it to the correct date
function. Thanks a lot Douglas.
Click to expand...

Sounds like you should probably just join your tbl_RemitDays to the
appropriate table in the query your report is based on, and include the
fields you need from tbl_RemitDays in the query's list of fields
selected. Then the information would be available without need to call
the DLookup function at all.
Click to expand...


How could I reference a query value in the VBA code to send it to the correct
Case statement? Do I just use the name as if I was doing it in an expression
for the query?
 
steinmetzw42 via AccessMonster.com said:
Dirk said:
Maybe I need to explain what I'm trying to do more detailed. I have
a large table (tbl_RemitDays) set up similar to this
Click to expand...
[quoted text clipped - 21 lines]
which it is so the Case statement can send it to the correct date
function. Thanks a lot Douglas.
Click to expand...

Sounds like you should probably just join your tbl_RemitDays to the
appropriate table in the query your report is based on, and include
the fields you need from tbl_RemitDays in the query's list of fields
selected. Then the information would be available without need to
call the DLookup function at all.
Click to expand...

How could I reference a query value in the VBA code to send it to the
correct Case statement? Do I just use the name as if I was doing it
in an expression for the query?
Click to expand...

I don't think you've given quite enough information. First: is this
code in fact running behind a report? Or is it on a form, or in a
standard module, or what? If it's on a report or form, please post the
SQL of the report's current recordsource query.

Basically, if it's on a report or form, and the recordsource query
includes the field in question (and, on a report, you have included a
control bound to the field somewhere on the report), then you can refer
to it in the code by name, just as in a query expression. However, you
may avoid some possible problems if you use the "Me!" qualifier; e.g.,

Select Case Me!Frequency
 
Dirk said:
[quoted text clipped - 11 lines]
correct Case statement? Do I just use the name as if I was doing it
in an expression for the query?
Click to expand...

I don't think you've given quite enough information. First: is this
code in fact running behind a report? Or is it on a form, or in a
standard module, or what? If it's on a report or form, please post the
SQL of the report's current recordsource query.

Basically, if it's on a report or form, and the recordsource query
includes the field in question (and, on a report, you have included a
control bound to the field somewhere on the report), then you can refer
to it in the code by name, just as in a query expression. However, you
may avoid some possible problems if you use the "Me!" qualifier; e.g.,

Select Case Me!Frequency
Click to expand...


I'm trying to created a user defined function in the VBA editor. I need this
function to be called in a query to find due dates depending on the frequency
that that specific number is equal to. eg.. Days = 5 and Frequency =BD then
I need it to calculate 5 workdays from yesterdays date (because this when the
money would have dropped, and it would show on the report today) but if it
Days = 18 and Frequency = CD then I need it to bring back the next up comming
18th day of the month (such as 3/18/07). Let me know if you need more
details.. Thanks.
 
steinmetzw42 via AccessMonster.com said:
I'm trying to created a user defined function in the VBA editor. I
need this function to be called in a query to find due dates
depending on the frequency that that specific number is equal to.
eg.. Days = 5 and Frequency =BD then I need it to calculate 5
workdays from yesterdays date (because this when the money would have
dropped, and it would show on the report today) but if it Days = 18
and Frequency = CD then I need it to bring back the next up comming
18th day of the month (such as 3/18/07). Let me know if you need
more details.. Thanks.
Click to expand...

Ah. I'd thought the code was going to be in the module of a report or
form. If you're going to be putting it in a standard module, in a
function intended to be called from a query, it's best to avoid adding
the overhead of a call to DLookup if you can. If I were you, I'd use
the query itself to join tbl_RemitDays, and then pass the fields
necessary to calculate the due date from the query to the function. So
your query would be something loosely along the lines of this:

SELECT
<existing list of fields>,
fncDueDate(tbl_RemitDays.Frequency, tbl_RemitDays.Days)
As DueDate
FROM
<some table>
LEFT JOIN
tbl_RemitDays
ON <some table>.Number = tbl_RemitDays.Number

Then your function, which I have prospectively named "fncDueDate", would
be declared something like this:

'----- start of example code -----
Function fncDueDate( pFrequency As Variant, pDays As Variant) _
As Variant

If IsNull(pFrequency) Or IsNull(pDays) Then
fncDueDate = Null
Else

' Depending on the frequency specified, compute
' and return the appropriate due date.

Select Case pFrequency
Case "BD"
fncDueDate = BDFunk(pFrequency, pDays)
Case "CD"
fncDueDate = CDFunk(pFrequency, pDays)
Case "Daily"
fncDueDate = Daily()
Case "FCD"
fncDueDate = FCDFunk(pFrequency, pDays)
Case Else
fncDueDate = Null ' or else return an error
End Select

End If

End Function
'----- end of example code -----
 
Thanks a lot Dirk. With a little tweaking it worked perfectly! Your help is
much appreciated.
 
Back
Top