Calculated Expression
Aug 16, 2005
I have two email addresses in a table one personal and one work and a question asking which to use, I have a query running from the table and have an IIF expression creating a field which looks to see which one to use (that all works fine !) anyway my client has now asked for an extra field in the query which combines the two email addresses, as in aaa@homedomain.com;bbb@workdomain.com so that I can run an email to both addresses at the same time. fraid I just can't figure it out ! I'm sure it's obvious, I can't find a similar question posted either, I've tried the expression builder using AND for the two email addressses but just end up getting -1 in field when I run the query, not the two email addresses separated by a ;
Anyone any ideas
many tks
:)
View Replies
ADVERTISEMENT
Mar 30, 2007
I am very inexperinced with Access, please be kind.
My problem is that from the form view the simple division of 2 #'s are placed in the correct text box I selected, but I am unable to get the value to enter into the data table. Please help.
Thank you
View 4 Replies
View Related
May 8, 2013
I having a problem with the expression builder in a table.I got a lot of fields with dates and I what a calculated field so I can see if any of the dates are newer than 7 days.I have been trying this formula:
IIf([Field1]or[Field2]or[Field3]<"Date() -7";true;false)
But it don't seen to work.
View 1 Replies
View Related
Nov 4, 2013
I've now since moved everything into one table but i need formatting of my calculated field (I'm new to all this).
Below are the 3 expressions working independently of one another, I just need them combined in to one if possible. As I am getting an "invalid syntax" error or "too many arguments" when I try to get it working
PHP Code:
0.5*IIf([Type]="Offlist" And [APP_LINE_OFFA]>3,1,0)+0.2*IIf([Type]="Offlist" And [APP_LINE_OFFB]>2,1,0)+
IIf([Type]="Offlist",Abs([EP_DETAILS_OFF]*0.15+[REF_DETAILS_OFF]*0.15),0)
PHP Code:
IIf([Type]="Onlist",Abs([EP_DETAILS_ON]*0.15+[REF_DETAILS_ON]*0.2+[GEN_NOTE_ON]*0.2+[APP_LINE_ON]*0.25+[SPEC_INS_ON]*0.2),0)
PHP Code:
IIf([Type]="Aged Report", Abs([EP_DETAILS_AGED]*0.2+[REF_DETAILS_AGED]*0.2+[GEN_NOTES_AGED]*0.3+[SPEC_INS_AGED]*0.3),0)
View 4 Replies
View Related
Jul 31, 2014
Currently I have a calculated field in my table; however, under certain circumstances I need to change the value to another value which is not related to the calculation. I understand that the calculated fields are read only (why is that?), so I was wondering if there was any way to change values within a calculated column without actually changing the expression itself.
View 1 Replies
View Related
Mar 28, 2015
I've can do this on excel but don't know how in Access. I'm calculating bonuses. My table has salaries, and my query simply multiples each salary amount by a % to get the bonus amount. But I need to calculate adjustments to the bonuses using the sum total of the bonuses my query calculated:
Salary (from table) Bonus (calc'd by query) Adjustment (to be calced)
100,000 1,000 Sum of total bonuses/salary*4%
90,000 900
How do I capture the total of my calculated bonus column to use to calculate the adjustments in my right-most column?
View 13 Replies
View Related
Jan 9, 2014
I tend to work with large amounts of data (extracts from company systems) and I create a lot of Access/VBA based tools to automate processes.
I have an annoying error which has always appeared but I don't understand the root cause of it.
When viewing a query, if I filter, I get an error message pop up (though after clicking through the error I can still use the filter function):
"syntax error missing operator in expression 'name of field'".
This seems to happen when I add several calculated fields. Here are some examples of the conditions and calculated field formulas I'm using in this current one:
Conditions:
<>"CINEMATIC" And <>"SFX"
Not Like "*_ZZ*" And Not Like "*test*" And Not Like "EP_*"
[Forms]![FRMscriptPrintReview]![selectLangCombo]
Calculated Field:
Audio Ref Guide: IIf(Left$(Right$([TBLdata]![Script Resource],2),1)<>"_","?",Right$([TBLdata]![Script Resource],2))
I get the impression that its more of a bug with Access as the formulas aren't complicated really but need confirmation on this and if there is a way I can avoid it.
View 2 Replies
View Related
Jun 8, 2005
Good day all
Heres what im trying to do:
in one textfield i have =Count(*)
this returns all the records in my db.
now i want to split it up. i want to count all the records for each month.
my field name is datein_now but its just a normal textfield and not a date field.
example in field: 05/02/02 10:24:31 AM.
also got a datein textfield:05/02/02
i had
Count(*) where datein_now between 05/05/01 and 05/05/31
please help
lee
View 1 Replies
View Related
Apr 12, 2006
My query contains two calculated fields [TaxSavings1] and [TaxSavings2], which are based on some currency and number-type fields in one of my underlying tables.
I just created another field in my query which looks like: [TaxSavings1]+[TaxSavings2]. Instead of adding the two fields, it actually lumps the two numbers together. For example, if [TaxSavings1] =135 and [TaxSavings2]=30.25, it will give me: 13530.25. I need it just to simply add, i.e. answer of 165.25.
Does anyone know how to correct this? Thanks in advance.
:confused:
View 1 Replies
View Related
Apr 6, 2006
I have a table that contains 2 columns:
1 = Deduction Reference Code
2 = The Value associated with each Code.
for example
Deduction Reference Value
999997 10000
666 6000
Code 999997 duplicates the value within code 666 so I wish to set up a caluclated value to say:
If deduction reference = 999997 then the value of 999997 minus the value of deduction reference 666.
I have tried several statements but none of which seem to work.
Can anyone help?
Thanks
View 1 Replies
View Related
Mar 15, 2006
Hi:
I'm using this expression...
=DateDiff("ww",#8/26/2005#,[cDate])
I want to add to it the ability to exclude five one week periods which are school vacations.
How can I do that?
Thanks,
xeb
View 1 Replies
View Related
Oct 22, 2006
PART NUMBER: IIf([MANUFACTOR SENT]=2,[GE PART #])OR IIf([MANUFACTOR SENT]=3,[PHILLIPS PART #])OR IIf([MANUFACTOR SENT]=1,[SYLVANIA PART #])
CAN ANYONE HELP ME FIX THIS EXPRESSION
View 1 Replies
View Related
Jun 11, 2007
I have got the following code as an expression for a report:
=Int(([Amount])/50000)*50000 & " - " & Int((([Amount])/50000)+1)*50000
How do I get it to pick figures greater than 50,000 and then figures greater than 100,000 plus?
View 3 Replies
View Related
Jun 25, 2007
I need help setting up an expression. I need to set the default value = date + 2. But this also needs to take into account working days. I am just having problems with the syntax for this, as i would normally do this in VBA and do not have much experience with Expression Builder. And yes it does have to be done in Expression Builder.
I know this is horribly wrong... thats why I am asking for help.
IIf ((WeekDay(DateAdd ("d", 2, Date)) = 1, (DateAdd("d", 3, Date)),(DateAdd("d", 2, Date))) OR IIf (WeekDay(DateAdd ("d", 2, Date)) = 7, (DateAdd("d", 4, Date)),(DateAdd("d", 2, Date))) OR IIf (WeekDay(DateAdd ("d", 2, Date)) < 7 AND IIf (WeekDay(DateAdd ("d", 2, Date)) > 1, (DateAdd ("d", 2, Date),(DateAdd("d", 2, Date)))
I need 3 If statements:
1 for if its Saterday
1 for if its Sunday
1 for if its neither.
In VBA I would do something like this:
If WeekDay(DateAdd ("d", 2. Date)) = 1 Then Box = Date() + 3
If WeekDay(DateAdd ("d", 2. Date)) = 2 Then Box = Date() + 4
If WeekDay(DateAdd ("d", 2. Date)) > 1 And WeekDay(DateAdd ("d", 2. Date)) < 7 Then Box = Date() + 2
Please any assistance would be appreciated
View 6 Replies
View Related
Feb 11, 2008
Hi All,
could i get some advise on an expression i need in a query?
i have 5 fields
SumMats,SumLabs,MatsDed,LabsDed,Ttreat
i need another field that will give a value on a condition. the condition is that if LabsDed isNull, MatsDed will be divided by Ttreat otherwise LabsDed will be divided instead. can this be done? if so, how could i do it?
many thanks,
Nigel
View 1 Replies
View Related
Jun 14, 2006
I need an expression for a table. I'm making a game database and for the ID input I need an expression.
For example if have a XBOX game I want the ID to be X1 or X2 etc, same goes for pc games, GameCube games: PC1 or PC2, NGC1 or NGC92 etc.
I thougt it would be "X???" Or "PC???", but that doesn't work.
Does someone knows?
Please help.
View 4 Replies
View Related
Jan 9, 2007
Okay
Maybe i am totally confused but here is initially what i have and what i want to do.
I currently have a db for carrer development-- i have three fields
Last Review Date
Next Discussion Due
Status
My next discussion due is on the fly-- i did a dateadd (by one year) from the last review date to get the current result for the next disussion due--
here is what i want to do with the status and am confused with how to do it--
i want the status to have three options-- completed,overdue and upcoming--
i want these to be autogenerated but am unable to find what expression i need-- as the IIf expression only gives the option of two possible results--
so how would i go about doing the expression with having three possible outcomes?
View 6 Replies
View Related
Nov 10, 2005
I am trying to execute a query that calculates a retail pricing off the whsl pricing. This is the excel calculation i use: =IF(F2<10,(SUM(F2*4)),(SUM(F2*2))). I changed the IF to IIF, and replaced "F2" with "[WHSL PRICE].
However, I am getting an error that the "category" expression is not included in the function (when I have all the fields in the query) or the function if I have just the field I am trying to calculate in the query. Can somone help me understand what I am doing wrong.
I would also like this calculation to save in the table, if possible. As I use this information to update my fields in my web database.
Best Regards,
Irish
View 11 Replies
View Related
Mar 1, 2006
Ok, I have a query that has a range of number for 1 - 126, I need and an expression that will flag sub-ranges in the list of numbers.
Example I want to group or flag results 0-25 as one group, 26-50 as another, 50 - 75 and 76 -100.
Any ideas? I statred with an IF than expression, but I can't figure how to indicate each range.
thanks
-pete
View 1 Replies
View Related
Mar 3, 2006
;) I want to create a field named ErrorCases that sums the rows meeting the following conditions: The row has a ValidityReason = 2 and for the same row ExpValidity is either null or 3. I want a count of the rows meeting these condtions.
Here is the formula I have constructed that does not work.
ErrorCases: Sum(IIf(([ValidityReason]="2" And [ExpValidity] Is Null) Or ([ValidityReason]="2" And [ExpValidity]="3"),1,0))
Any suggestions?
View 2 Replies
View Related
Jun 1, 2006
I having some trouble trying to use an expression in my query,
I have 3 indivisual exoressions in 3 fields I which work perfectly, however I want only 1 field produced so I tried placing 1 after the other as below:
Sector: IIf([SpacePos]=3,Left([PostalCode],[SpacePos]) & Mid([PostalCode],[SpacePos]+1,1)),IIf([SpacePos]=4,Left([PostalCode],[SpacePos])+"" & Mid([PostalCode],[SpacePos]+1,1)),IIf([SpacePos]=5,Left([PostalCode],[SpacePos]-1) & Mid([PostalCode],[SpacePos]+1,1))
I have also tried Or inbetween them but no luck either, I get the "contains invalid syntax" errors etc.
Any advise appricated
View 2 Replies
View Related
Jun 23, 2006
How can i write an expression in a query field that will return the following text; "A","B","C","P" and "F" Base on another field when the values of that field are <=44.44, between 44.45 and 54.44, Between 54.45 and 64.44, Between 64.45 and 74.44 and above 74.45 respectively?
View 2 Replies
View Related
Jul 20, 2006
How do I fix this to specify each table
Description: [level] & " " & [room] & " " & IIf(IsNull([direction]),[location],[direction] & " " & [location] & " " & [fixture] & " " & [note])
Description: [level] [level table] & " " & [room] [room table] DOESNT WORK
HELP
View 1 Replies
View Related
Oct 22, 2006
LETS SAY I HAVE THREE PART NUMBERS FOR DIFFERENT MANUFACTORS. IF THE USER SELECTS GE MANUFACTOR THEN THE GE PART NUMBER SHOULD BE LISTED IN PART NUMBER. I GET ERRORS WITH THIS EXPRESSION IN MY QUERRY
Expr1: IIf([MANUFACTOR SENT]="GENERAL ELECTRIC",[PART NUMBER]=[GE PART #])
HELP
View 4 Replies
View Related
Oct 22, 2006
IIf([MANUFACTOR SENT]=2,[GE PART #])
this expression works fine in my querry....but i need to add to it...if the user doesnt choose 2...if he chooses 1 then i need the sylvania part number or if he chooses 3 i need the phiilips part number....so i used this expression
IIf([MANUFACTOR SENT]=2,[GE PART #])IIf([MANUFACTOR SENT]=1,[sylvania PART #])IIf([MANUFACTOR SENT]=3,[phillips PART #])
but i get errors....how do you you string the expression...or what should i do to fix
View 1 Replies
View Related
Feb 3, 2007
I was hoping that someone could at least tell me if this is possible.
I have a table that (among other fields) has an integer field (call it X) and a date field call it Y).
I was hoping that I could create a query that captured these two fields (no problem), but also included a third, build expression field.
This latter field is supposed to be a sum of the previous X-values up until the current record date Y-value.
Ex:
If I have the following info
X Y
1 jan-3-07
3 jan-5-07
23 jan-5-07
12 jan-19-07
Then the query would be built so that the results would look like one of the following
X Y Z OR Y Z
1 jan-3-07 1 jan-3-07 1
3 jan-5-07 4 jan-5-07 27
23 jan-5-07 27
12 jan-19-07 39 jan-19-07 39
I can't seem to be able to build the right expression to achieve this. I can easily code this through a form using VB, but I assume it is equally possible through just a query.
I would be grateful for any and all help people can provide,
-arm
View 4 Replies
View Related
