Problem with query
in [Help]
|
Prev: Running Sum (opposite)
Next: 2010: Essential Diffs?
From: Poul Erik on 8 Apr 2010 15:05 I have made a query called qryK�bSalg in my access 2007 database with the following columns. itemId ref to the table tblItemTransaction descrition ref to the table tblItem transDate ref to table tblItemTransaction transType ref to table tblItemTransaction kurs ref to table tblItemTransaction transQty ref to table tblItemTransaction TotalAktier do not ref to any table but I have this expressions: TotalAktier: (IIf([transType]='S';[transQty]*[Kurs];IIf([transType]='P';-[transQty]*[ Kurs]))) Omk ref to table tblItemTransaction When I enter keys in the query all data are automatically pasted into the table tblItemTransaction in the following columns itemId > itemId transType > transType transDate > transTade kurs > kurs transQty > transQty Omk > Omk My problem is the column TotalAktier which not are pasted into the tblItemTransaction table. Did I miss something in the expressions? Poul --
From: Salad on 8 Apr 2010 16:31 Poul Erik wrote: > I have made a query called qryK�bSalg in my access 2007 database with > the following columns. > > itemId ref to the table tblItemTransaction > > descrition ref to the table tblItem > > transDate ref to table tblItemTransaction > > transType ref to table tblItemTransaction > > kurs ref to table tblItemTransaction > > transQty ref to table tblItemTransaction > > TotalAktier do not ref to any table but I have this expressions: > TotalAktier: > (IIf([transType]='S';[transQty]*[Kurs];IIf([transType]='P';-[transQty]*[ > Kurs]))) > > Omk ref to table tblItemTransaction > > > When I enter keys in the query all data are automatically pasted into > the table tblItemTransaction in the following columns > > itemId > itemId > transType > transType > transDate > transTade > kurs > kurs > transQty > transQty > Omk > Omk > > My problem is the column TotalAktier which not are pasted into the > tblItemTransaction table. > > Did I miss something in the expressions? > > Poul > You use ";" instead of "," for separating the fields. I doubt it would run in Access. Ex: IIf([transType]='S'; Is this an Update query? If so, select from the menu and select Query/Select. IOW, change it temporarily from and Update to Select. Then run the query. Is there a value in TotalAktier? If there is then there's a problem. Maybe you selected the wrong field to uodate. If there isn't, then you do have a problem with your expression. Remober to change the query back to Update query.
From: Marco Pagliero on 8 Apr 2010 18:51 On 8 Apr., 22:31, Salad wrote: > You use ";" instead of "," for separating the fields. I doubt it would > run in Access. Ex: IIf([transType]='S'; In localised Acces if the standard for decimal point is comma, one has to use semicolons instead of comma in the query editor (but not in the VBA editor). Very nerving, but one gets accustomed to. After a few years. Greetings Marco P
From: Marco Pagliero on 8 Apr 2010 19:01 On 8 Apr., 21:05, "Poul Erik" wrote: > My problem is the column TotalAktier which not are pasted into the > tblItemTransaction table. Has tblItemTransaction a field TotalAktier at all? Or, which field did you select for TotalAktier to paste into? If you already store transType, kurs and transQty into tblItemTransaction, what do you want also to store TotalAktier there for? > IIf([transType]='S';[transQty]*[Kurs];IIf([transType]='P'.... If transType is not 'S' or 'P' then TotalAktier is Null. Yes, do what Salad says to check whether TotalAktier is empty. Greetings Marco P
From: Poul Erik on 9 Apr 2010 12:07
Salad wrote: > You use ";" instead of "," for separating the fields. I doubt it > would run in Access. Ex: IIf([transType]='S'; > > Is this an Update query? If so, select from the menu and select > Query/Select. IOW, change it temporarily from and Update to Select. > Then run the query. Is there a value in TotalAktier? If there is > then there's a problem. Maybe you selected the wrong field to > uodate. If there isn't, then you do have a problem with your > expression. Remober to change the query back to Update query. I'm using semicolon because comma don�t work in access 2007 at least not in a query. But I have tried both. Yes there is a field called TotalAktier in both the tblItemTransaction table and in the query qryK�bSalg It is not a update query I use. (I don't know how to use an update query, I'm still a novice in access) Below there is a list of the column in the table "tblItemTransaction" itemId | transType | transDate| Kurs | transQty | transCost | TotalAktier And a list of my query (qryK�bSalg) itemId | description | transType | transDate | Kurs | transQty | TotalAktier| transCost In the query i fill in the date in itemId, description, transType,transDate, Kurs, transQty and transCost. TotalAktier calculate automatically based on Kurs*transQty. No problem with this in the query. The problem is, that all data should be sent to the tblItemTransaction table from that query, and the data from all fields except from the TotalAktier did that. I write in the keys directly in the query into the table view. I'm wonder if I in this syntax below should tell where the result should be placed which shoud be in the table tblItemTransaction and in the field TotalAktier, but I don't know how and where. TotalAktier: (IIf([transType]='S';[transQty]*[Kurs];IIf([transType]='P';-[transQty]*[ Kurs]))) -- |