Forms :: Convert Excel Formula Into Expression Builder - Invalid Syntax
Mar 22, 2013
I am trying to convert an excel formula into the expression builder on my form.
the excel formula is this:
IF(AND(E4>=2.65,F4<1.85),"Poor",IF(AND(E4<2.65,F4< 1.85),"Unacceptable",IF(AND(E4>=2.65,F4>=1.85,F4<2 .65),"Generally",IF(AND(E4<2.65,E4>=1.85,F4<2.65,F 4>=1.85),"Poor",IF(AND(E4<1.85,F4<2.65,F4>=1.85)," Unacceptable",IF(AND(E4>=3.45,F4<3.45,F4>=2.65),"H igh",IF(AND(E4>=2.65,E4<3.45,F4<3.45,F4>=2.65),
[Code] ....
In the expression builder I put this:
= IF(AND( [Objective Total] >=2.65, [KSF Totals] <1.85),"Poor",IF(AND( [Objective Total] <2.65, [KSF Totals] <1.85),"Unacceptable",IF(AND( [Objective Total] >=2.65, [KSF Totals] >=1.85, [KSF Totals] <2.65),"Generally",IF(AND( [Objective Total] <2.65, [Objective Total] >=1.85, [KSF Totals] <2.65, [KSF Totals] >=1.85),"Poor",
[Code] ....
It gives me an error 'expression you entered contains invalid syntax, you may have entered a comma without a preceding value.'
View Replies
ADVERTISEMENT
Apr 24, 2013
I would like to convert a text string to integer.
Lots of posts say to use val, but it is not listed in access 2010
So I am trying,
Creating a field that is numeric then just referring to the string field.
This works good except where it finds actual text. It puts the value "#Type!" in there.
Would there be some kind of function to check for an error or check if the value is text.
View 1 Replies
View Related
Feb 14, 2008
I have an excel worksheet with the following formula
=IF(b40<50000,b40*0%,IF(b40<100000,b40*2.5%,b40*5%))
and I would like to convert it for use in access.
I came up with the following:
=iif([TOTAL]<50000,[TOTAL]*0%,IIF([TOTAL]<100000,[TOTAL]*2.5%,[TOTAL]*5%))
I get the following error: "The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it with quotation marks."
I am probably missing something very simple here, but I have been unable to come up with a solution.
Any help would be greatly appreciated.
Regards,
Michael
View 3 Replies
View Related
Dec 12, 2013
I need to clean up data from text file which is huge.
I wonder if Access Expression Builder can mirroring "IF function" from Excel Here is what i am trying to do The data consist of multiple customer and multiple date. But the layout only specified customer ID once eg.
CustID: aaaa
Date
01012013
02012013
03012013
CustID: bbbb
01012013
02012013
When exported the file I used Fixed Width command to separate Date Column and Cust ID column.
Below is the result that I am looking for
CustID: aaaa : aaaa
Date : aaaa
01012013 : aaaa
02012013 : aaaa
03012013 : aaaa
CustID: bbbb : bbbb
01012013 : bbbb
02012013 : bbbb
1. Column A is Date
2. Column B is Customer ID
because Customer ID in column B only appear once, I need to create another column to populate that Customer ID whenever the transaction related with that Customer
3. Column C is the column where i tried to populate Customer ID to each date related to that Customer.
If I worked in excel the formula will be --> IF(AND(C5="",B6=""),"",IF(AND(C5="",B6<>""),B6,IF( B6<>"",B6,C5)))but in access i am stucked.
View 14 Replies
View Related
Jun 1, 2005
I have 5 fields(First,Second,Third,Sum,Sum1). The Sum field is the sum of the first, second and third. In excel I am using the following formula/expression to calculate the Sum1 field =IF(E1<0,E1+10,E1+0 - and it brings back single digit.
e.g.
First=3, Second=5, Third=8, Sum=16, Sum=6
Can I do this in an Access query, if so how?
Thanks in advance for any help.
View 1 Replies
View Related
Jun 4, 2013
1. I have a table called "CONTRACT NAMES AND NUMBERS" with a field called "REDUCED_USERS", this field is a checkbox (Yes/No in the table). This table houses all of the customers with their id numbers and basic info.
2. I have another table called "REQUESTS" which houses their orders. This also has a field called "REDUCED_USERS".
In my form "Amendment Request Tracking" I have tried to do a DLookUp in Expression Builder to check the box, per order, if the customer has reduced users in the "CONTRACT NAMES AND NUMBERS".
I have tried many variations and have just realised that this is probably because it is a yes/no field so may struggle with what to populate with (currenly nothing!).
My most recent variation of expression is (where NAD_NUMBER is the common field in both Tables and Form with relevant relationship):
=DLookUp("[REDUCED_USERS]","[CONTRACT NAMES AND NUMBERS]","[CONTRACT NAMES AND NUMBERS]![NAD_NUMBER]=[NAD_NUMBER]")
View 10 Replies
View Related
Jan 18, 2015
So I have built a form and added a field using the expression builder which is returning #Name? There is a relationship between the table on which the form is based and the record I am adding to a control.
View 1 Replies
View Related
Apr 6, 2007
Hi All:running Access 2000.I have a qry with a formula field that performs some calculation (Extension: CCur(([OnHand]*[Price])/[UnitsPer]))and it works fine. How can I update the actual table with the result of the calculation for every record? The problem I have is that there's some null values in the results therefore when I try to get totals on that field, it errors out saying "invalid null use"thanks
View 7 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
Oct 24, 2013
Why am I receiving 'Syntax error (missing operator) in query expression' error message ? Below is the code.
stFrmName = "Previous Plan"
stLinkCriteria = "(Employee = " & strPreviousTeacher & ") And (Record_Date = " & strPreviousDate & ")"
DoCmd.OpenForm stFrmName, , , stLinkCriteria
View 2 Replies
View Related
Jan 9, 2006
When I enter a date into the [Return Text Date] field on my table and then select a value from the [Upgrade Fee] combo box followed by a selection from the [Upgrade Investment Class] combo box i want the value (or Date) in my Eligibility Date field to be amended by the given amount.
I have an expression, listed below, that i want to repeat (copy + Paste would be good) and amend the values. I have entered 2 and that seems to be all it will allow. I want to have 30 possible permutations to the fields 0 - 300 (in 50 intervals) by 5 (1,2,3,4 & 5)
The Code works below but I cant have anymore code in the expression builder
IDEAS please people.......
=IIf([Upgrade Fee]=0 And [Upgrade Investment Class]=1,[Return Text Date]+180,IIf([Upgrade Fee]=0 And [Upgrade Investment Class]=2,[Return Text Date]+150,""))IIf([Upgrade Fee]=0 And [Upgrade Investment Class]=3,[Return Text Date]+120,IIf([Upgrade Fee]=0 And [Upgrade Investment Class]=4,[Return Text Date]+90,""))
View 14 Replies
View Related
Jan 16, 2007
Hi!
I need some help.
I want to create a formula (using expression builder or something else) who will separate the word id=276616652 lang=FR temp=80 in a new column (for each word)
Id=276616652&Lang=FR&Client=Diesel649&Temp=80
by the way, im using this in a log database
View 3 Replies
View Related
Sep 23, 2006
I've built a query against a form and included a calculation to give me the average sum which looks like this :
=Sum([data field])/31*12.5
This work fine however, what is the best way to overcome the different number of days in the month ie; 28, 30 and 31? I've looked at the Expression Builder but getting lost :rolleyes:
Regards
Richard
View 2 Replies
View Related
Feb 10, 2006
Hi all,
i have a field that uses Date/Time as the data type and what i want to do is to create records that only have the time between the hours of 9:00am and 9:15pm.... i think i need to use the expression builder for this, but im not really sure on the expression to use.. any ideas ???:confused:
View 6 Replies
View Related
Sep 20, 2006
Hi,
I am fairly new to Microsoft Access and can't get to grips with the expression builder. I have several fields where I need to add validation rules and no matter what I try I can't seem to get it right. E.g I'm trying to set a validatio rule for only allowing locations into a certain field. Could someone please give me some help or point me in the direction of a good guide?
View 1 Replies
View Related
Sep 16, 2013
I have created a form based on a query. The funny thing is when I tried to sort fields on the form, the following message box pops up:
Syntax Error (Missing Operator) In (Field Name)
I just did the exact same thing several weeks ago, and that first form could sort fields normally. The only difference between the first one and this is that this second query was based on several tables, while the first was based on a single table, although I doubt that is where the problem is.
I forgot to add that I could sort fields where the field name doesn't have spaces in it. For example, the field name "Customer ID" triggers the syntax error, while the field "S/N", "Company", etc. can be sorted like normal.
View 7 Replies
View Related
Mar 24, 2008
Hi, i am making a stock control program in Access. I have designed several tables, forms and queries etc. but have come stuck at the point where i want to create a button so when i click it, it automatically changes the stock level of all the products that are low in stock, to their "target stock level".
I am confused about how to do this. I tried making a button and going into the expression builder and putting:
=[Product Stock Level]=([Product Target Stock]-[Product Stock Level])+[Product Stock Level]
This would basically change the stock level to the target stock level. But when i press the button, nothing happens.
Any help on this matter is greatly appreciated.
Thanks alot.
View 9 Replies
View Related
Oct 26, 2004
Im looking for a guide to teach myself how to use the expression builder.
I know how to write constraints in SQL, so with a bit of luck it will be similar. :P
A list of every command you can use might be just as good as a tutorial.
Can anyone point me in the right direction?
Thanks.
View 2 Replies
View Related
Mar 7, 2007
Hi,
I am trying to write an expression in my query which, given a condition, it uses the following criteria:
"Carmarthenshire" Or "Ceredigion" Or "Neath Port Talbot" Or "Pembrokeshire" Or "Swansea"
So far I have come up with this:
IIf([Forms]![Main Menu]![cboRegion]="West Wales","Carmarthenshire" Or "Ceredigion" Or "Neath Port Talbot" Or "Pembrokeshire" Or "Swansea")
However the Query builder complains the expression is incorrect or too complex etc. etc...
If I simplify it to this:
IIf([Forms]![Main Menu]![cboRegion]="West Wales","Carmarthenshire")
it works fine but unfortunately this isn't what I need.
Any help much appreciated,
Matt.
View 11 Replies
View Related
Mar 28, 2007
Hi all,
Should you write an expression the criteria field in a query and use IIF, an interesting little bug appears that I have not managed to get around. Should you want the expression to return a range as the criteria (i.e. Between ... And ... ) it can't do it because IIF tries to evaluate the range before it passes the result back to the query. I need it to actually pass the Between And to the query to use as it's criteria. It can't do this, so it passes back a null and of course the query fails to produce any meaningful result.
I need to do this as I have two date text boxes in a form so that people can search through specific dates, but in the table these date fields can be blank. (Basically it's a Date Completed field, so if the job hasn't been completed, the date is left as Null) So i need users to be able to leave both text boxes blank so that the resulting query will display the Null values as well. If they leave one blank, it auto sets itself to the a minimum/maximum date. When both boxes are blank i need to return nothing so the criteria is effectively blank or = Is Null And Is Not Null to display all records.
Example of WIP
IIf(IsNull([Forms]![frmListImgChng]![txtDateComp1])=True And IsNull([Forms]![frmListImgChng]![txtDateComp2])=True,#15/01/2007#,IIf(IsNull([Forms]![frmListImgChng]![txtDateComp1])=True,Between #01/01/2007# And #01/04/2007#,IIf(IsNull([Forms]![frmListImgChng]![txtDateComp2])=True,Between [Forms]![frmListImgChng]![txtDateComp2] And #31/12/2030#,Between [Forms]![frmListImgChng]![txtDateComp1] And [Forms]![frmListImgChng]![txtDateComp2])))
The red bits of code are test values. The first one passes back to the query as it is not a range, the second does not as it is.
Cheers,
Matt
View 6 Replies
View Related
Mar 19, 2008
Basically i have a drop down that i want controlling the Criteria for each field in my query to have an advanced search. If i leave a field blank the query comes up with nothing so i tried this:IIf(IsNull([Forms]![Inventory Report Search]![Model]),Is Null,[Forms]![Inventory Report Search]![Model])this is not working. it is still returning the query blank. if i put valid critera in the dropdown it comes back Fine.how do i make is so if a field is blank it will return it as null or not even there.Note: also tried this;Forms]![Inventory Report Search]![Model] Or Forms]![Inventory Report Search]![Model] Is Nullworks, But comes back as too complex after a few searches and when i open the query there is a million or's in there. so that wont work.
View 3 Replies
View Related
Oct 13, 2006
Now I have been working for a few months with ACCESS but one thing that keeps giving me trouble and that is the "Expression Builder". I look at the Microsoft Help and it is not good in explaining this part of ACCESS. I have Access books and they are not good with explaining this aspect of Acess. Is there anyone with a good source to point me in the direction to learning the Expression Builder better then I know now?
View 1 Replies
View Related
Feb 11, 2008
This seems so simple and it probably is, I've probably been staring at my database for too long doing too many other things but here goes:
I want to create thank you letters for a list of people. I've made my query and gotten all their info together but now I can't make a report for the letter. I can make my text box but after that nothing seems to work out right. I'm not that good with VB code so I have been using Expression Builder which follows logic statements and not real code. I can't get even the address showing in one text box with 3 lines. Once I get that far I can get the remainder of my letter created because its all the same thing but I really need some help to get beyone the address!
Thanks
View 7 Replies
View Related
Sep 13, 2007
I am receiving this error;
"Invalid SQL syntax, cannot use multiple columns in a column level CHECK constraint."
Without explaining my disaster of a situation anyone have any ideas?
View 4 Replies
View Related
Dec 14, 2007
I get the error invalid syntax for the following line : ( summing up figures in case GetBonus = Yes)
Bonus : iif(getBonus = -1;nz(BonusL1;0)+ NZ([BonusL2];0)+ NZ([BonusL3];0)
could you help ?
View 1 Replies
View Related
Sep 21, 2006
I need to implement the following logic in a report column:
IF hist_mis_cds!typgess = "", then calculated field= ""
ELSE
IF hist_mis_cds!typgess <> hist_code_translation! src_code_desc
then calculated field= "X"
ELSE calculated field= ""
I have implemented the following query in Expression Builder for the calculated field:
=IIf(HIST_MIS_CDS!TYPGESS="","",IIf(HIST_MIS_CDS!T YPGESS<>HIST_CODE_TRANSLATION!SRC_CODE_DESC,"X","" ))
When I click on Form View, I get "#Name?" in the calculated field.
Any ideas? Thanks.
View 10 Replies
View Related
