Number Cruncher wrote:
> 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.
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" wrote:
>
>> 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
>>>
Similar Threads
