Query formula

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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
If you have a formula that complex, instead of trying to do it with a formula in a query, you may want to create your own User Defined Function in VBA, and then you can reference that UDF in your query (or other VBA).
 
Upvote 0
If you have a formula that complex, instead of trying to do it with a formula in a query, you may want to create your own User Defined Function in VBA, and then you can reference that UDF in your query (or other VBA).

Sadly I'm brand new to Access so don't know the first thing about VBA
 
Upvote 0
Sadly I'm brand new to Access so don't know the first thing about VBA
Well, there's no time to learn like the present, especially if you are going to attempt to do some higher level stuff in Access. :biggrin:

Here is a link that talks about creating UDFs in Access VBA.
http://www.fontstuff.com/vba/vbatut01.htm

Your other option is to try to break your function down in to smaller components and do a bit at a time (kind of like having "helper" columns in Excel where you do part of a calculation, and then use that calc in the next calc).
 
Upvote 0
Nice one Joe, I'll have a good rummage through the stuff on your link. It looks like VBA is my destiny so I might as well embrace it :)

I did what you said though on this and broke it down. Took about 7 columns but it worked perfectly. Stuck it in a separate query so I don't end up with 5000 columns in this query tho! Getting there :)

Many Thanks,
Mart
 
Upvote 0
You'll find that Access isn't quite as intuitive as Excel, so the learning curve is a bit steeper. If you are designing a large database in Access, I would recommend taking some courses (or ready some introductory books) on Access and Relational Databases. Otherwise, it is VERY easy to program yourself into a corner.

The first Access database I tried to design without understanding Access or the concepts of Relational Databases (especially the Rules of Normalization), and was pulling my hair out. I took some classes, and read some books and ended up scrapping a few months worth of work and started over. It was painful, but worked out for the best in the long run! I learned the hard way!
 
Upvote 0
I think you need to consider redesigning things.

It sounds like you have a many-to-many relationship going on.

You should probably have a table for items, which would have a unique primary key eg ItemID.

Another table would be a list of all the further descriptions which would also have a unique primary key eg FurtherDescID.

You would link these 2 table with a 'junction' table which would have the primary keys from both table.

If you could post some more sample data we could perhaps explain better.:)
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top