martiniextradry
Board Regular
- Joined
- Mar 4, 2008
- Messages
- 73
I'm trying to convert my current Excel custom made system over to Access and I'm struggling a bit with formulas. In Excel I regularly had very longwinded formulas (might not be doing them the best way) but Access doesn't seem to wanna play ball with me on them. Can anyone help see where I'm going wrong?
What the formula is trying to do is text based, and it is basically trying to get brackets and commas to make sense. It outputs items of foam for a cutting sheet, each of which can have between 0-6 extra pieces of information about them. I got it working fine to put nothing after it when none of the 6 were applicable but then I started stumbling and creating a very long formula, which is no doubt unnecessary. Here's what it is so far:
Description: [ItemName] & IIf([TMainTable]![FurtherDescription] Is Null And [TMainTable]![Fab'd?]=False And [TMainTable]![ToTemp?]=False And [TMainTable]![Corovin?]=False And [TMainTable]![Dacron?]=0 And [TMainTable]![Net?]=False,""," (" & IIf([TMainTable]![FurtherDescription] Is Not Null And [TMainTable]![Fab'd?]=False And [TMainTable]![ToTemp?]=False And [TMainTable]![Corovin?]=False And [TMainTable]![Dacron?]=0 And [TMainTable]![Net?]=False,[TMainTable]![FurtherDescription] & ")",IIf([TMainTable]![Fab'd?]=True And [TMainTable]![ToTemp?]=False And [TMainTable]![Corovin?]=False And [TMainTable]![Dacron?]=0 And [TMainTable]![Net?]=False,"Fab'd)",IIf([TMainTable]![ToTemp?]=True And [TMainTable]![Corovin?]=False And [TMainTable]![Dacron?]=0 And [TMainTable]![Net?]=False,"TT)","x"))))
So it puts the item name (eg Chatsworth Seats) then it checks if there is a Further Description (eg 8x4 cut out),
if it's Fab'd (yes or no),
if it's To Template (yes or no),
if it has Corovin on (yes or no),
if it has Dacron (eg 235g Dac, so would need to put the number from that field and then concatenate "g Dac" to it)
and finally if it has Net (yes or no).
NB: the "x" was just what I was using at each step to see if it was working.
I think I'm at the third step above, and it's basically using process of elimination to say whether it needs to mention each thing and put a comma after or ignore it and move on to the next.
I was shown a trick of putting it in brackets with a plus sign or something (I forget exactly how it went) but that would only seem to work for one condition.
A few examples for if you're confused;
I'm trying to make
Arms (, 0, 0, 0, 0, 0)
show as
Arms
Arms (, 0, -1, 0, 0, 0)
show as
Arms (TT)
Chaise Seats (25½x7 cut out, 0, 0, 0, 135, -1)
show as
Chaise Seats (25½x7 cut out, 135g Dac, Net'd)
Does anybody have any ideas?
Many thanks,
Mart
What the formula is trying to do is text based, and it is basically trying to get brackets and commas to make sense. It outputs items of foam for a cutting sheet, each of which can have between 0-6 extra pieces of information about them. I got it working fine to put nothing after it when none of the 6 were applicable but then I started stumbling and creating a very long formula, which is no doubt unnecessary. Here's what it is so far:
Description: [ItemName] & IIf([TMainTable]![FurtherDescription] Is Null And [TMainTable]![Fab'd?]=False And [TMainTable]![ToTemp?]=False And [TMainTable]![Corovin?]=False And [TMainTable]![Dacron?]=0 And [TMainTable]![Net?]=False,""," (" & IIf([TMainTable]![FurtherDescription] Is Not Null And [TMainTable]![Fab'd?]=False And [TMainTable]![ToTemp?]=False And [TMainTable]![Corovin?]=False And [TMainTable]![Dacron?]=0 And [TMainTable]![Net?]=False,[TMainTable]![FurtherDescription] & ")",IIf([TMainTable]![Fab'd?]=True And [TMainTable]![ToTemp?]=False And [TMainTable]![Corovin?]=False And [TMainTable]![Dacron?]=0 And [TMainTable]![Net?]=False,"Fab'd)",IIf([TMainTable]![ToTemp?]=True And [TMainTable]![Corovin?]=False And [TMainTable]![Dacron?]=0 And [TMainTable]![Net?]=False,"TT)","x"))))
So it puts the item name (eg Chatsworth Seats) then it checks if there is a Further Description (eg 8x4 cut out),
if it's Fab'd (yes or no),
if it's To Template (yes or no),
if it has Corovin on (yes or no),
if it has Dacron (eg 235g Dac, so would need to put the number from that field and then concatenate "g Dac" to it)
and finally if it has Net (yes or no).
NB: the "x" was just what I was using at each step to see if it was working.
I think I'm at the third step above, and it's basically using process of elimination to say whether it needs to mention each thing and put a comma after or ignore it and move on to the next.
I was shown a trick of putting it in brackets with a plus sign or something (I forget exactly how it went) but that would only seem to work for one condition.
A few examples for if you're confused;
I'm trying to make
Arms (, 0, 0, 0, 0, 0)
show as
Arms
Arms (, 0, -1, 0, 0, 0)
show as
Arms (TT)
Chaise Seats (25½x7 cut out, 0, 0, 0, 135, -1)
show as
Chaise Seats (25½x7 cut out, 135g Dac, Net'd)
Does anybody have any ideas?
Many thanks,
Mart