How is it possible to rewrite this code and make it shorter

[Natalie007]

Hello,
I have an application, which i need to modify, 1 line is really long, so when I add additional condition to it, the program gives an error. I was looking for options to re write it and make it shorter:

  Me.RecordSource = "SELECT JobOrder.*, IIf([ShipType]=""1"",""ground"",IIf([ShipType]=""2"",""air"")) AS ShipText, IIf([PackType]=""1"",""skid(s)"",IIf([packtype]=""2"",""carton"")) AS PackText, TotalTax, Store.City, Store.PostalCode, Store.StoreName, Int(ReturnMax([weight],[dimweight])+1) AS BillWeight, IIf([ProvinceState].[Region]=""E"",1,IIf([ProvinceState].[Region]=""O"",2,IIf([ProvinceState].[Region]=""U"",3,4))) AS RegionSort, IIf([ProvinceState].[Country]=""CAN"",1,IIf([ProvinceState].[Country]=""USA"",2,3)) AS CountrySort FROM (JobOrder LEFT JOIN Store ON JobOrder.StoreNo = Store.StoreNo) LEFT JOIN ProvinceState ON Store.Province = ProvinceState.StateID WHERE (((JobOrder.JobNo)=[forms]![fdreportjoborder]![jobflt])" & ordflt & ");"

i thought about 'select case', or putting some values into variable, like this :

TotalTax = ntz([GSTAmount]) + ntz([PSTAmount]) + ntz([HSTAmount]) + ntz([NSHAmount])

But no luck

I'll appreciate your help

thanks

Tali

[jkmyoung]

Create temporary tables:
shiptype(id, type)
1 - ground
2 - air

packtype(id, type)
1 - skid
2 - carton
etc...

and use a join. You will have to be specific about the fields you select though, which is probably better anyways.

[Natalie007]

thanks for your reply, jkmyoung

i tried it, but guess have some issues with joining the tables. Is it the right way to do so?

 Me.RecordSource = "SELECT JobOrder.*, temp_shiptype.type AS ShipText, temp_packtype.type AS PackText, ntz([GSTAmount]) + ntz([PSTAmount]) + ntz([HSTAmount]) + ntz([NSHAmount])as TotalTax, Store.City, Store.PostalCode, Store.StoreName, Int(ReturnMax([weight],[dimweight])+1) AS BillWeight, IIf([ProvinceState].[Region]=""E"",1,IIf([ProvinceState].[Region]=""O"",2,IIf([ProvinceState].[Region]=""U"",3,4))) as RegionSort, IIf([ProvinceState].[Country]=""CAN"",1,IIf([ProvinceState].[Country]=""USA"",2,3)) AS CountrySort FROM ((JobOrder LEFT JOIN Store ON JobOrder.StoreNo = Store.StoreNo) left join temp_shiptype on joborder.shiptype=temp_shiptype.id) LEFT JOIN ProvinceState ON Store.Province = ProvinceState.StateID WHERE (((JobOrder.JobNo)=[forms]![fdreportjoborder]![jobflt])" & ordflt & ");"

Thanks

[MikeTheBike]

Hi

If you are going to use TotalTax in a query string then I think you need to specifiy the ValueIfNull argument, ie.
TotalTax = ntz([GSTAmount],0) + ntz([PSTAmount],0) + ntz([HSTAmount],0) + ntz([NSHAmount],0)

Even added together this is not a very long string for a query (perhaps the above will fix it?).

With regards to the multiple nested IIf() statements: if the sort order is always the same for Region and Country, I would be tempted add two field the the ProvinceState table (RegionSort and CountrySort and add the sorting values to this and then just Order By these to files in the query.

If fact, on second thoughts, I woud have two separate tables for Region and Country (both with sort order field) and relate then to the StateProvince table (or watever relation conforms to third normal form) and join then to the query to provide both the Region/Counry names and the sort order in the query !!??

Hope that is not too confusing

MTB

[Natalie007]

Yes, it was confusing.
TotalTax value comes from database as well - to the RecordSource, so I'm not sure how to do that :
TotalTax = ntz([GSTAmount],0) + ntz([PSTAmount],0) + ntz([HSTAmount],0) + ntz([NSHAmount],0)
Do I need another recordsource?

Thanks

N.