Wildcards in Calculated Expressions

[mjm01]

I'm trying to create a calculated filed in a query that sets the the turnaround time agreed with a client for design information. This is based on the contents of another field which happens to be [Disipline Code].

All [Disipline Code] data is in the same format eg A51, C69 etc.

We need to state in the calculated field that where the [Disipline Code] is a 50 series ie *5*, the turnaround time = 20 otherwise 10.

I've trued the IIF function and it works great if the criterie for [Disipline Code] is absolute eg:

IIf([tbl_document_details]![Discipline Code]="A57",20,10) ... no problem.

But if I try and wildcard the criteria eg:

IIf([tbl_document_details]![Discipline Code]="*5*",20,10) it sets all the data to the FALSE part.

Is there any way to structure the wildcard to get this to work??

 

[Mile-O]

Don't use the equals sign with a wildcard: Use Like!


IIf([tbl_document_details]![Discipline Code] Like "*5*",20,10)

 

[namliam]

when using wildcards use Like instead of =

Also i would advice you to NOT use spaces in your column names, it will come around to byte you on the *$$ soon....

Regards

The Mailman

 

[mjm01]

Thanks for the assistance ... worked a charm

... led to a couple of other problems but we fixed them too

Thanks again

Mark M

 

[billz260]

Yes, but what if you need to have multiple cases?

Trying to return the order qty depending on the type of line item.
This works...
SSS: IIf([MRPCn] Like "B3*",[Open_Delivery_Qty],0)


But this doesn't, I need to have multiple arguments in my statment. I can't seem to get this to work w/ out getting into writing some script, which I haven't done before.

DH: IIf([MRPCn] Not Like "B3*" and not like "B90",[Open_Delivery_Qty],0)

Any input? Or is it just time for me to take a class in SQL?

 

[RV]

Or is it just time for me to take a class in SQL?

Nope, it's 'bout time to take a class in proper database design
Using the IFF function like you intend to means that you actually dealing with an not properly normalized datastructure.
Create a new table which holds the values of MRPCn against a quantity.
Include this table in your query using a join on MRPCn

RV

 

[namliam]

DH: IIf([MRPCn] Not Like "B3*" and [MRPCn] not like "B90",[Open_Delivery_Qty],0)

But what are you using it for? Could it be much easier if you used it in a where clause?

Regards

 

[billz260]

Well, MRPCn is a identifier, the differnt codes mean different part families. I am trying to build a bar chart based upon open qty's for different ranges of the MRPCn fields, They are some logical order but since they include a letter and numbers, I guess ranges won't work. All this info in in one table currently and basically I am pulling Order number, Line item, MRP, and Open Qty.

I guess I could chop off the first digit in each MRPCn code, and then use a numbered range in my IIF statment, but here is what I have that is working for 3 of the 4.

DH= B40 - B75
DH: IIf([MRPCn] Not Like "B3*",[Open_Delivery_Qty],0)

SSS= B31 and B32
SSS: IIf([MRPCn] Like "B3*",[Open_Delivery_Qty],0)

CL= B90
CL: IIf([MRPCn]="B90",[Open_Delivery_Qty],0)

EDR= B96
EDR: IIf([MRPCn]="B96",[Open_Delivery_Qty],0)

These statments work well for the three that are very specific but currently the one for DH is including other MRPCn's that I don't want to include.

I have the statments written and working for all but DH. DH is currently only excluding b*. I am sure there is a better way to write this query. I can build a table from a query but that table would mirror the original table. I guess I don't understand how that would help.

 

[billz260]

DH: IIf([MRPCn] Not Like "B3*" and [MRPCn] not like "B90",[Open_Delivery_Qty],0)

Worked...

 

[namliam]

You "should" (read the best way to solve this) create a second table, using the key MRPCn and a second field (text) to store the things like SSS and BH.

Then make a crosstab query to build the report on, much easier to make, much easier to maintain, more flexible, etc...

Regards

 

[billz260]

DOH!

Now I feel like a friggin retard! I am still new and probably need some formal training. Although, we are moving my creation to an SQL server so I guess I will be learning how to do stuff right as we re-create it in developer.

THanks again fella's!!

 

[namliam]

Now I feel like a friggin retard!

It happenes to all of us every now and again

HTH as allways.