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
4 1129
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.
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
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
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Sune |
last post by:
Hi!
Pre-requisites:
-------------------
1) Consider I'm about to write a quite large program. Say 500 K
lines.
2) Part of this code will consist of 50 structs with, say, no more
than at most...
|
by: Jignesh Desai |
last post by:
There are essentially two ways that you can write code for an ASP.NET
application.
Inline code Style
---------------------------------------------------------------
<html>
<script...
|
by: Peter Olcott |
last post by:
Cab you write code directly in the Common Intermediate language? I need to
optimize a critical real-time function.
|
by: vinthan |
last post by:
hi,
I am new to python. I have to write test cases in python. An
application is open in the desk top ( application writen in .Net) I
have to write code to get focuse the application and click on...
|
by: mohammaditraders |
last post by:
#include <iostream.h>
#include <stdlib.h>
#include <conio.h>
#include <string.h>
class Matrix
{
private :
int numRows, numCols ;
int elements ;
|
by: vijay1012 |
last post by:
How to write code to modify a record. One way is using modify, for example $sqlSession->EditEntity( $objtoedit, "modify" );
But for some reasons i should not use this modify.
Is there any way to...
|
by: veron |
last post by:
hi. i want to send a sms to activate a device to open or close. can i use vb6 to write code to read sms from GSM modem? and how to use vb6 to write code to read sms from GSM modem?
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |