Using a VBA Variable function from Access table data in Access que

  • Thread starter Thread starter Number Cruncher
  • Start date Start date
N

Number Cruncher

I want to use a VBA function that is a variable that references values in a
table is Access. I want to use the function in the normal query design of
Access. So, in the normal query designer, I would use as one of the columns
something like “NewCol:[NbrFromTblData]*2â€.

I have tried the following code and different variations of it. When I use
the following code and use the function in the regular Access query, the
value of the first record in the table shows up for every record in the query
results. I want the unique value from each record to show up in the results.

As much as I have tried different things, I am starting to wonder if it is
even possible to use a VBA function that references unique values in the
various rows of a table – to use it in a regular Access query. I know that
in my above example, I could skip VBA and put it all in the regular Access
query, but, I have complex situations where I think it would be much more
efficient using VBA code. Instead of doing nested if’s in the query, I would
rather use “Case†in VBA code – especially since in some situations, I have
twelve different “Casesâ€.

Here is the code I am trying:

Public Function TryVar() As Variant

Dim db As DAO.Database
Dim tblMyTbl As DAO.TableDef
Dim rst As DAO.Recordset
Dim fldMyField As DAO.Field

Set db = CurrentDb
Set tblMyTbl = db.TableDefs![acctcodes]

Set rst = db.OpenRecordset("acctcodes")

TryVar2 = rst![NbrFromTblData] ' In Access query,
' each row shows only the number in the first row
' I want the unique number in each row.

End Function
 
First, Iif() isn't the only option you have in Jet SQL. you can also use
Choose() or Switch() for multiple conditions and they're usually easier
than nested Iif(). If possible, I would sooner write more SQL than use
VBA because it'd always be faster to do it in SQL than in VBA.

But assuming it's indeed essential to do it in VBA, there are some
questions. Exactly how do you determine the number to use? From which
rows? Your function has no parameters. Are you trying to re-do the query
and figure out which number to give based on that. Wouldn't it be
quicker to use a parameter:

Public Function MyFunction(SomeStuff As Variant) As Variant

MyFunction = DLookup("SomeValue", "SomeTable", "SomeColumn=" & SomeStuff)

End Function,

which you can use in the query like this:

NewCol: MyFunction([NameOfOtherColumnYouWantToCompareInQuery])

HTH.
 
Thanks for the thoughts. I am a little familiar with VBA, but not very
familiar with SQL. Also, what I am really trying to do would not work that
great in SQL. To give more of the full scope, here is an example of what I
am ultimately trying to do. In a table, I have the following columns:
AccountDescription, CurrentMonth, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug,
Sep, Oct, Nov, Dec. With the function, ultimately I was planning to set up
12 cases to calculate the YearToDate total. For example, the Case statement
for the YearToDate for July would be, “Case Currentmonth = Jul, then
YearToDate=Jan+Feb+Mar+Apr+May+Jun+Julâ€. I would like to have it in a VBA
function where it could be easily retrieved in multiple Access queries. I
don’t want to have to rewrite it for every query – whether I rewrite it in
SQL for the query or in something like Iif statements. I just want to be
able to use “YearToDate()†in whatever query I want to use it in.

Also, in the query, I want to use the unique values in each row. I want the
YearToDate figures to show for each Account.

And as I originally stated, what I am really trying to do may not be
possible in VBA?


Banana said:
First, Iif() isn't the only option you have in Jet SQL. you can also use
Choose() or Switch() for multiple conditions and they're usually easier
than nested Iif(). If possible, I would sooner write more SQL than use
VBA because it'd always be faster to do it in SQL than in VBA.

But assuming it's indeed essential to do it in VBA, there are some
questions. Exactly how do you determine the number to use? From which
rows? Your function has no parameters. Are you trying to re-do the query
and figure out which number to give based on that. Wouldn't it be
quicker to use a parameter:

Public Function MyFunction(SomeStuff As Variant) As Variant

MyFunction = DLookup("SomeValue", "SomeTable", "SomeColumn=" & SomeStuff)

End Function,

which you can use in the query like this:

NewCol: MyFunction([NameOfOtherColumnYouWantToCompareInQuery])

HTH.

Number said:
I want to use a VBA function that is a variable that references values in a
table is Access. I want to use the function in the normal query design of
Access. So, in the normal query designer, I would use as one of the columns
something like “NewCol:[NbrFromTblData]*2â€.

I have tried the following code and different variations of it. When I use
the following code and use the function in the regular Access query, the
value of the first record in the table shows up for every record in the query
results. I want the unique value from each record to show up in the results.

As much as I have tried different things, I am starting to wonder if it is
even possible to use a VBA function that references unique values in the
various rows of a table – to use it in a regular Access query. I know that
in my above example, I could skip VBA and put it all in the regular Access
query, but, I have complex situations where I think it would be much more
efficient using VBA code. Instead of doing nested if’s in the query, I would
rather use “Case†in VBA code – especially since in some situations, I have
twelve different “Casesâ€.

Here is the code I am trying:

Public Function TryVar() As Variant

Dim db As DAO.Database
Dim tblMyTbl As DAO.TableDef
Dim rst As DAO.Recordset
Dim fldMyField As DAO.Field

Set db = CurrentDb
Set tblMyTbl = db.TableDefs![acctcodes]

Set rst = db.OpenRecordset("acctcodes")

TryVar2 = rst![NbrFromTblData] ' In Access query,
' each row shows only the number in the first row
' I want the unique number in each row.

End Function
Click to expand...
Click to expand...
 
Number said:
Thanks for the thoughts. I am a little familiar with VBA, but not very
familiar with SQL. Also, what I am really trying to do would not work that
great in SQL. To give more of the full scope, here is an example of what I
am ultimately trying to do.
Click to expand...
In a table, I have the following columns:
AccountDescription, CurrentMonth, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug,
Sep, Oct, Nov, Dec. With the function, ultimately I was planning to set up
12 cases to calculate the YearToDate total. For example, the Case statement
for the YearToDate for July would be, “Case Currentmonth = Jul, then
YearToDate=Jan+Feb+Mar+Apr+May+Jun+Julâ€. I would like to have it in a VBA
function where it could be easily retrieved in multiple Access queries. I
don’t want to have to rewrite it for every query – whether I rewrite it in
SQL for the query or in something like Iif statements. I just want to be
able to use “YearToDate()†in whatever query I want to use it in.
Click to expand...

Right there is the problem. It's not the query or the function but in
fact how the table is structured. If you read up on normalization and
database design, you would find out that it's usually considered a No-No
to store repeating groups such as {Jan, Feb, Mar...Nov, Dec}.

In fact, we would just have a table with just two columns;
AccountDescription and CurrentMonth and input several records, one for
each month in the table. You can then use a crosstab query to break it
back into the {Jan...Dec} presentation for your reporting purposes and
that would be far much easier than trying to write custom functions and
fighting against the data structure.

I'd strongly encourage you to go and read up on normalization, head over
to the other forum for table design and discuss how it can be optimized.
Also, in the query, I want to use the unique values in each row. I want the
YearToDate figures to show for each Account.

And as I originally stated, what I am really trying to do may not be
possible in VBA?


Banana said:
First, Iif() isn't the only option you have in Jet SQL. you can also use
Choose() or Switch() for multiple conditions and they're usually easier
than nested Iif(). If possible, I would sooner write more SQL than use
VBA because it'd always be faster to do it in SQL than in VBA.

But assuming it's indeed essential to do it in VBA, there are some
questions. Exactly how do you determine the number to use? From which
rows? Your function has no parameters. Are you trying to re-do the query
and figure out which number to give based on that. Wouldn't it be
quicker to use a parameter:

Public Function MyFunction(SomeStuff As Variant) As Variant

MyFunction = DLookup("SomeValue", "SomeTable", "SomeColumn=" & SomeStuff)

End Function,

which you can use in the query like this:

NewCol: MyFunction([NameOfOtherColumnYouWantToCompareInQuery])

HTH.

Number said:
I want to use a VBA function that is a variable that references values in a
table is Access. I want to use the function in the normal query design of
Access. So, in the normal query designer, I would use as one of the columns
something like “NewCol:[NbrFromTblData]*2â€.

I have tried the following code and different variations of it. When I use
the following code and use the function in the regular Access query, the
value of the first record in the table shows up for every record in the query
results. I want the unique value from each record to show up in the results.

As much as I have tried different things, I am starting to wonder if it is
even possible to use a VBA function that references unique values in the
various rows of a table – to use it in a regular Access query. I know that
in my above example, I could skip VBA and put it all in the regular Access
query, but, I have complex situations where I think it would be much more
efficient using VBA code. Instead of doing nested if’s in the query, I would
rather use “Case†in VBA code – especially since in some situations, I have
twelve different “Casesâ€.

Here is the code I am trying:

Public Function TryVar() As Variant

Dim db As DAO.Database
Dim tblMyTbl As DAO.TableDef
Dim rst As DAO.Recordset
Dim fldMyField As DAO.Field

Set db = CurrentDb
Set tblMyTbl = db.TableDefs![acctcodes]

Set rst = db.OpenRecordset("acctcodes")

TryVar2 = rst![NbrFromTblData] ' In Access query,
' each row shows only the number in the first row
' I want the unique number in each row.

End Function
Click to expand...
Click to expand...
Click to expand...
 
Thanks again for the thoughts. I would still have the issue of easily
calculating the YearToDate value as the months change.

Banana said:
Number said:
Thanks for the thoughts. I am a little familiar with VBA, but not very
familiar with SQL. Also, what I am really trying to do would not work that
great in SQL. To give more of the full scope, here is an example of what I
am ultimately trying to do.
Click to expand...
In a table, I have the following columns:
AccountDescription, CurrentMonth, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug,
Sep, Oct, Nov, Dec. With the function, ultimately I was planning to set up
12 cases to calculate the YearToDate total. For example, the Case statement
for the YearToDate for July would be, “Case Currentmonth = Jul, then
YearToDate=Jan+Feb+Mar+Apr+May+Jun+Julâ€. I would like to have it in a VBA
function where it could be easily retrieved in multiple Access queries. I
don’t want to have to rewrite it for every query – whether I rewrite it in
SQL for the query or in something like Iif statements. I just want to be
able to use “YearToDate()†in whatever query I want to use it in.
Click to expand...

Right there is the problem. It's not the query or the function but in
fact how the table is structured. If you read up on normalization and
database design, you would find out that it's usually considered a No-No
to store repeating groups such as {Jan, Feb, Mar...Nov, Dec}.

In fact, we would just have a table with just two columns;
AccountDescription and CurrentMonth and input several records, one for
each month in the table. You can then use a crosstab query to break it
back into the {Jan...Dec} presentation for your reporting purposes and
that would be far much easier than trying to write custom functions and
fighting against the data structure.

I'd strongly encourage you to go and read up on normalization, head over
to the other forum for table design and discuss how it can be optimized.
Also, in the query, I want to use the unique values in each row. I want the
YearToDate figures to show for each Account.

And as I originally stated, what I am really trying to do may not be
possible in VBA?


Banana said:
First, Iif() isn't the only option you have in Jet SQL. you can also use
Choose() or Switch() for multiple conditions and they're usually easier
than nested Iif(). If possible, I would sooner write more SQL than use
VBA because it'd always be faster to do it in SQL than in VBA.

But assuming it's indeed essential to do it in VBA, there are some
questions. Exactly how do you determine the number to use? From which
rows? Your function has no parameters. Are you trying to re-do the query
and figure out which number to give based on that. Wouldn't it be
quicker to use a parameter:

Public Function MyFunction(SomeStuff As Variant) As Variant

MyFunction = DLookup("SomeValue", "SomeTable", "SomeColumn=" & SomeStuff)

End Function,

which you can use in the query like this:

NewCol: MyFunction([NameOfOtherColumnYouWantToCompareInQuery])

HTH.

Number Cruncher wrote:
I want to use a VBA function that is a variable that references values in a
table is Access. I want to use the function in the normal query design of
Access. So, in the normal query designer, I would use as one of the columns
something like “NewCol:[NbrFromTblData]*2â€.

I have tried the following code and different variations of it. When I use
the following code and use the function in the regular Access query, the
value of the first record in the table shows up for every record in the query
results. I want the unique value from each record to show up in the results.

As much as I have tried different things, I am starting to wonder if it is
even possible to use a VBA function that references unique values in the
various rows of a table – to use it in a regular Access query. I know that
in my above example, I could skip VBA and put it all in the regular Access
query, but, I have complex situations where I think it would be much more
efficient using VBA code. Instead of doing nested if’s in the query, I would
rather use “Case†in VBA code – especially since in some situations, I have
twelve different “Casesâ€.

Here is the code I am trying:

Public Function TryVar() As Variant

Dim db As DAO.Database
Dim tblMyTbl As DAO.TableDef
Dim rst As DAO.Recordset
Dim fldMyField As DAO.Field

Set db = CurrentDb
Set tblMyTbl = db.TableDefs![acctcodes]

Set rst = db.OpenRecordset("acctcodes")

TryVar2 = rst![NbrFromTblData] ' In Access query,
' each row shows only the number in the first row
' I want the unique number in each row.

End Function
Click to expand...
Click to expand...
Click to expand...
 
Number said:
Thanks again for the thoughts. I would still have the issue of easily
calculating the YearToDate value as the months change.
Click to expand...

That would be easy on a normalized table, in fact.

Assuming the same table with one record for each month, and you want to
know much you have accumulated since Jan 2009 (e.g. the start of fiscal
year:

SELECT SUM(SomeMoney)
FROM ATable
WHERE RecordMonth > #1/1/2009#

Or maybe if a fiscal year is from October to September, and you want to
see total for that fiscal year:

SELECT SUM(SomeMoney)
FROM ATable
WHERE RecordMonth BEWTWEEN #10/1/2008# AND #9/1/2009#


Or maybe you want to have a per-month breakdown for the same fiscal year:

SELECT RecordMonth, SUM(SomeMoney)
FROM ATable
WHERE RecordMonth BETWEEN #10/1/2009# AND #9/1/2009#
GROUP BY RecordMonth



Hopefully this will illustrate the power of normalization can do to
simplify your querying requirement.
 
Back
Top