Convert string to code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve - 06 Sep 2006 15:16 GMT

I have table that must be manipulated differently for each record. In each
record, I have a field that I have stored the manipulation necessary. For
example, one record might say "Price+Total-Shipping"(fields in the record),
while the next record might say "Price+Shipping+Taxes". What would be the
best way to loop through the records and have that manipulation done on
another field in the record?

In other words, loop through the records, look at the calculation, do the
calculation and put the result in an existing field in the record, move on to
the next.

Query? Form? What code?

Thanks so much..

Reply to this Message

Allen Browne - 06 Sep 2006 15:36 GMT

This table needs to be redesigned, so your data is normalized.

There are 2 fundamental flaws in the table as it stands:
a) It contains dependent values (the Total that depends on other fields),
and
b) It contains repeating fields (similar entities in different colums.)

Remove all the price fields from this table.
Instead, create another table with fields:
AmountType drop-down list for "Price", "Shipping", or "Tax"
Amount Currency. The amount for this row.
ForeignID relates to the primary key of your main table.

You can then sum the values in this one Amount field, to get the total for
the invoice/order or whatever it is.

For a basic example of this kind of structure, open the Northwind sample
database that installs with Access. Choose Relationships on the Tools menu.
You will see how one order has many rows, so there is an Order Details table
related the Orders table. That's the basic idea.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have table that must be manipulated differently for each record. In each
> record, I have a field that I have stored the manipulation necessary. For
[quoted text clipped - 12 lines]
>
> Thanks so much..

Reply to this Message

Steve - 06 Sep 2006 16:01 GMT

Thank you Allen. I appreciate what you are saying, but I have no control of
table structure. This is a flat .csv file that is being downloaded daily,
then manipulted to get a final price and then the .csv file is deleted. The
pricing is good for only about 2 hours at a time and then run again. So,
normalizing is not necessarily an option.
I guess what I am trying to find out is this:

Is there a way, through code, to loop through the record and look at a
formula that is pre-determined in a field, and have that calculation take
place. While looping through the record, the data in the field ends up a
string and I want it to perform a calculation on the record.

Thanks,

> This table needs to be redesigned, so your data is normalized.
>
[quoted text clipped - 33 lines]
> >
> > Thanks so much..

Reply to this Message

Allen Browne - 06 Sep 2006 16:24 GMT

Okay, a nasty workaround is that the first argument for DLookup() can be
such an expresion.

So, if:
- the manipulation description is in a field named Formula,
- the table is Table1,
- the primary key is named ID
you could type a calculated field into the query like this:
Result: DLookUp([Formula],"Table1","ID = " & [ID])

JET will probably have trouble with the data type of that expression, so you
probably want:
Result: CCur(Nz(DLookUp([Formula],"Table1","ID = " & [ID]), 0))

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Thank you Allen. I appreciate what you are saying, but I have no control
> of
[quoted text clipped - 57 lines]
>> >
>> > Thanks so much..

Reply to this Message

chris.nebinger@gmail.com - 06 Sep 2006 15:57 GMT

I had to do something similar. Essentially:

Open the Recordset
Run a Replace function to replace Price with the value from the field.
Repeat for each field.
Then eval() the result.

It isn't pretty. Any changes to the data require the field to be
recalculated. It's a major PITA.

Chris Nebinger

> I have table that must be manipulated differently for each record. In each
> record, I have a field that I have stored the manipulation necessary. For
[quoted text clipped - 10 lines]
>
> Thanks so much..

Reply to this Message

Steve - 06 Sep 2006 16:16 GMT

Sure, it does not have to be pretty. If I get 100% accuracy, that is what I
am looking for.

> I had to do something similar. Essentially:
>
[quoted text clipped - 22 lines]
> >
> > Thanks so much..

Reply to this Message

David Cox - 07 Sep 2006 00:43 GMT

horrendous though it is it could be done along these lines (abbreviations
used, {calc] is the formula field):-

ans: IIf(InStr("+" & [calc],"+pr"),[pr],0)-IIf(InStr("+" &
[calc],"-pr"),[pr],0)+IIf(InStr("+" & [calc],"+sh"),[sh],0)-IIf(InStr("+" &
[calc],"-sh"),[sh],0) .....

Reply to this Message