Calculated field

[RCurtin]

Hi,
I have a calculated field in a query that concatinates an order number prefix and the part number to give the order number:
OrderNum: [OrderPrefix] & [PartNum]

There are 2 tables:
tblPartsList
PartNum
PartDesc

tblPartsListCategories
OrderPrefix
OrderNumCategory

This works fine except for one of the categories. For the third category there are no entries in the tblPartsList - because they are miscellaneous.

How do I change the calculation so that it works as above where there are corresponding Part numbers in tblPartsList and otherwise gives me the order number and any 2 digits e.g. something like [OrderPrefix]*

Having been trying to do this for ages but having trouble with syntax.

 

[Len Boorman]

You can use an immediate If statement in the query. Goes something like this I think

OrderNum:IIf(not null[PartNum],[OrderPrefix] & [PartNum],[OrderPrefix] & "XYZ")

Not exactly sure of the syntax but maybe it will get you started and if the syntax is wrong maybe there is a kind soul looking in who will offer help as well

Len B

 

[RCurtin]

Nearly there..

Hi Len,
Thanks a million for that. Its exactly what I was looking for. This is what I have now:

OrderNum: IIf(IsNull([PartNum]),[OrderPrefix] & "*",[OrderPrefix] & [PartNum])

It does evaluate the condition properly but the wildcard search doesn't work propperly. The following gives the result 67081*
[OrderPrefix] & "*"

I know that its treating the * as a string because its in quotation marks but I can't get the syntax right to have it act as a wildcard. I've tried using Like but it seems that the syntax for this is different when used an expression like this?

 

[Len Boorman]

In this particular circumstance the wildcard effect is not going to happen at all. The problem is sort of "What field does it search ?"

The calculated field is looking over its shoulder at previous fields and "on the fly" calculating according to your specification.

So the * in this siytuation can as a multiplication sign ot text.

You would have to include another field that give the alternative text you require. You would then have

OrderNum: IIf(IsNull([PartNum]),[OrderPrefix] & [Another field],[OrderPrefix] & [PartNum])


Len

 

[RCurtin]

I just realised that I need to tell it the field to search:

OrderNum: IIf(IsNull([PartNum]),[OrderPrefix] Like "67081*",[OrderPrefix] & [PartNum])

I see what you mean about the wildcard effect not working though.. The data I require is in the the PartNum field for the other records but as this is calculated 'on the fly' it will only look at the current record for PartNum which is empty...

The only order number prefix that doesn't have corresponding part numbers in the Parts table is 67081. The part number is a 2 digit number going from 01 to 99. So I want the order number to return:
6708101
6708102
..
6708199


There has to be a way I can do this without creating 99 new records in tblPartsList just containing the orderNumPrefix (i.e.67081) and partNum for each one??

 

[Len Boorman]

Think that 99 records will be your only way out.
To do what you want to do you would need a For Loop
What you also want is 01, 02 etc which is text so simple loop increment of X = X+1 would not work
So you would end up wrapping a query in a For Loop where the query is an Append query and each time you would literally create a new table and write all the records to it which is wasted effort as most of the records are already available and an additional overhead on the application.

99 records sounds quick, cheap and effective

Len

 

[RCurtin]

OK thats fine. I'll do that. Thanks very much for your help. Wasn't wasted time anyways cos I learnt alot.

 

[Len Boorman]

My thought

If I can learn something new every day no matter how small then at the end of a month I have leanrt a lot and over a year an enormous amount ad infinitum

L

 

[Len Boorman]

My thought

If I can learn something new every day no matter how small then at the end of a month I have leanrt a lot and over a year an enormous amount ad infinitum

L

 

[Len Boorman]

My thought

If I can learn something new every day no matter how small then at the end of a month I have leanrt a lot and over a year an enormous amount ad infinitum

L

 

[Len Boorman]

My thought

If I can learn something new every day no matter how small then at the end of a month I have leanrt a lot and over a year an enormous amount ad infinitum

L