Forms :: Can Use IIF Statement Within SetValue Expression?
Dec 9, 2013
I'm using A2003 (yes, still). An accounting type form requires the "Type" be entered as either (numeric) 1, 11, or 2. On the same form, one of the three Yes/No fields must be checked as confirmation.
My hope is that I can use the "SetValue" action in a macro.
My first attempt (to set the "Assiniboine" field to "Yes" or "On"):
The default value for the "Type" field would normally be 1 (but that presumes the user will not skip past it without confirming it) so I have no default value.
The other two Yes/No fields would also be Set by the same logic: (if Type=11, [Bank]=Yes) (if Type=2, [PettyCash]=Yes)
Alternatively, I've tried the reverse approach: If [Assiniboine]=Yes, SetValue of [Type] to 1 so far without success. I'm not using VBA, since the bookkeeper changes from year to year and there is no Administrator. Although the computer has A2010, and can run an .mdb file with macros, successfully, making changes within A2010 produces an 'embedded macro' that isn't readable in A2003.
Is there a way to increase the maximum characters allowed in an expression of a macro setvalue action ?
I have an expression in a macro setvalue action that adds multiple form textbox values. I need to change the form name from "RATING ENGINE 2" to "E RATING ENGINE 2". When I do this, I exceed the maximum characters allowed in the expression. Is there a way I can rename "RATING ENGINE 2" without this problem. The expression is as follows:
I am having issues with the SetValue macro. Basically, my database is for HR and uses a series of forms to enter a new hire, and to export this new hire to excel for a data upload. The weird thing is that it worked before, then all of a sudden, it gave me error 2950 while trying to run a macro which had a few SetValue functions.
I went to another form to see if that one worked w/ SetValue (because the database is trusted in my system), and it did. So my problem is that I'm receiving error 2950 (trust issues) in one form, but not receiving the same error, from the same function, on another form.
I don't know, I have noticed that the SETVALUE argument is not openly available in the lookup tables when setting a macro. (But that aside) I have set my drives c: and d: as trusted, and also thicked the sub folder option.
I am developing a calender to display HOTEL room occupancy (past,present) and combine with future "outlook" dates and 1/0 values from active registrations that go beyond the present date.
I am working with MS ACCESS 2007. My problem is ONE SPECIFIC QUERY AND IIF STATEMENT. I want to concatenate some text (using &) along with numbers converted to text (using the CSTr function). I am using iif function and I want to use the full text string as a variable argument to be executed in the iif function. The result of the &concatenate is a text field like [p1] or [p2] or [p#] with numbers 1-31.
But, I do not want the final result as the argument. I want the query and iif to use the string expression written into the argument as the variable argument that can be calculated based on OTHER numbers that change everyday in the daily run of the calender.
The field in the statement [calc number] is a date conversion factor that changes everyday.
I want the iif statement to execute using the string as a variable argument. I am writing only within the QUERY to define the query object. I am not writing into any SQL module or code. My field definition and iif statement is below (calc number changes everyday)
I do not have greater-than key on this international keyboard !
When I use this in an update query, I get format conversion error. When I use the same definition in MAKE-TABLE query, it gives the resolved value "[p1]" for the value of [CalDate18] ......... that is not what I want.
I want the string to be taken literally and executed. Seems there may be a special character to precede the argument or WRAP the argument such as done in Excel. Example # "[p" & [calc number] & "]" #
I'm trying to run a very basic iif statement to correct hourly data for sorting. Basically, a trading day runs from 8am - 8am, so I need to adjust the hours to ensure that 1am on the 15th trading day (really the 16th on the calendar), comes after 9am on the 15th trading day (which will actually be the 15th on the calendar).
Here's what I've used. It's driving me bananas, because it keeps telling me that there's a syntax error (comma) in the query expression, but I can't understand why?
SELECT Costs.Costs_Per_Capita, Costs.Period IIF (Period = 1, (Select AVG(Costs_Per_Capita) From Costs Where Period = 1), (Select AVG(Costs_Per_Capita) From Costs Where Period = 2) AS result FROM Costs;
But get "syntax error (missing operator) in a query expression ..."
I've linked my inventory database to a table that holds data for my web site. Using a macro I can transfer the data in several field at the click of a button. I've run into a problem with one field called [SizeW] in my inventory and [width] in the web site db. [sizeW]is in meters and [width] needs to be in centimeters so I made my macro expression [sizeW]*100. The trouble is I get an incorrect result. 0.87 becomes 87.0000004768372 and so on. I have tried setting the format in the web site db to General Number and 0 decimals but this doesn't work. I can't think of anything that could be the cause.
Any possibility this could be caused by a naming issue? I think 'width' might be a reserved terem for access.
I'm trying to create a database that we can use to track Vacation, Sick, and Personal days. It's a very simple setup, for each option I have a start value a place to enter the amount used and and expression field that calculates the time remaining. My query calculates the difference (Expr1: timelosstbl!SickBeg-Timelosstbl!SickAccUsed) but this value isn't stored, since it runs everytime the query is run. So I have another field that (SickTotal). I have a button that on click it runs a macro and I use the SetValue action and it takes the value in the expr1: field and copies it to the SickTotal field. I have another button that on click runs basically the same macro that copies the value in the SickTotal field to the SickBeg field. My problem is that when I click the button everyting works but I doesn't remove the figure that was manually entered in the SickAccUsed field. So what I thought I needed to do was instead of using a macro to run the procedure I'd do it as an event procedure but I don't know how to recreate the macro because when I do either me. or docmd. setvalue isn't an option. So basically what I need is A=starting figure, B=manually enter hours used, C=remaining time, D=stored value for C
I have two Combo boxes which work as selection boxes for the detail form. So, if one Combo box selects Name - the detail form is filled with the information belonging to that Name...
However, I have two combo search boxes: one for Name and one for Clientnumber. They both work fine, but I would like to reset the first combo box if the other one is used and vice versa.
So, basically, my question is: how do I reset a combo box in a AfterUpdate (right?) event of another combo box?
Currently using an IsNull expression to Setvalue of a field to Invisible but rathe than type the condition,action,item,expression form every field i want to make invisible, can I list fields and attach to one ISNull condition, if that makes sense?
Currently have:[Stk Entitlement ID2].[Visible], but would like to add other fields like:[Stk Entitlement ID2],[Stock ratio 2],[Stock Entitlement 2].[Visible].
I have set up a form to use for entering and specifying report criteria. I followed the instructions from general Access help. Everything worked fine in MS Access 2000. Now, I've transferred the database to a computer using MS Access 2003 and I get and error that says, "You entered an expression that has an invalid reference to the property Visible. The property may not exist or not apply to the object you specified." If I change the Macro for the "OK" button from Action="SetValue", Item="[Visible]", and Expression="No" to Action="SetValue", Item="[Forms]![frmParameterQuery].[Visible]", and Expression="False", which I though may be the correct way to enter this for the newer 2003 version of Access, then I get a different error message that reads, "You don't have the license required to use this ActiveX control. You tried to open a form containing an OLE Object or an ActiveX control or you tried to create an ActiveX control. To obtain the appropriate license, contact the company that provides the licensed OLE Object or ActiveX control" Please help!
Ok. I have a database which tracks the sales and wherabouts of my artwork. Mostly I sell works on a sale or return basis, so I have a form which finds the delivery and opens the delivery details in a subform. This way I can tick off individual items in the delivery as they sell.
I also sell things outright. However, when I am paid for these sales, I want to be able to tick a 'sold' check box in the main form which then automatically fills in the date sold field (on both form an table) as the current date for all the items in that delivery, i.e. I want to tick the box once in the form causing the date to be entered into each record in the subform automatically.
I have tried making a command button on the form with a SetValue macro. I cannot get this to work with a date (maybe I have got the expression wrong) and also it only works with the first record on the subform.
Can anyone solve my problem or do I just have to be patient and enter each date individually?
I have a main form FrmQuote that has 2 Subforms. The 2nd subform FrmCalculateQuote is based on a calculation query. In order to save a calculated value I have another Table/Form (FrmSCQuoteOverviewUpdate) that records the quote value calculated in the subform FrmCalculateQuote (text34)
The following forms are open FrmQuote FrmSCQuoteOverviewUpdate and subform FrmCalculatequote.
In the subform FrmCalculatequote I have created a button click event that should set the value of [tot contract cost] in FrmSCQuoteOverviewUpdate to text34 in the subform
Is it possible to use the SetValue macro action to set field criteria in a query? I've done this in the past by creating a form, using SetValue to set a value for a form field, then having the field in the query read from the form field.I noticed in the expression builder under the SetValue action that it allows you to select query fields directly though.
I am currently using the SetValue function from within a macro to set the value of one text box to the value of another. It works fine under full version but when I use it under Runtime which is where the final application will be run from, it brings up the Halt box and will not continue the macro. Does anyone know what can/cannot be done within Runtime. Is it just the same as A full version but without Design functions or are some other functions removed. I am painstakingly having to go through all the vba code that I wrote in full version and I am having to change so many things in order for it to work with Runtime. This is a real pain the backside! Any thoughts would be huuuugely appreciated.
I am building a purchase requisition (PR) database.
The tables are (simplified) as follows:
Table1 - tbl_PRList - contains only 3 fields with one of these fields being the "PR No". Table2 - tbl_PRData - contains all the item data, also with a field "PR No".
The intent of the "item entry" form is to add items to the latest PR that has been generated.
So, whenever an item that is to be placed on the new PR, you type in the item description, qty, etc in the form and then you press a button that saves this item to the main 'data' table (Table1). What I would like to do is, "on click" of this button, I'd like this record, prior to it moving to the next record, to set the value of the PR No field in Table 1 to be the same as the DLast value of the Table2 table.
I have a main form with many subforms. Each subform has couple of ComboBoxes or/and TextBoxes. I want to make sure that end users dont miss completing any of required values. For example, in the subform (Clients), an end user is supposed to put either "Male" or "Female" in the (Gender) ComboBox. In another subform (Job Info), the end user will put info of the job like Wage, Employer, Date of Job Start... ete.
What I want is to get VB IF statement which tests if the textboxes "Wage", "Employer", and "Date of Job Start" in the subform (Job Info) are not null, then the CobmoBox "Gender" in the subform (Clients) should not be null too, with a warning message to complete the missing value.
Iam doing an Access project And i have poblem in calculation in a form. This calculation basically is between a form and a table. I have a form called "Yearly _Amount" in which there is a field called "Total_Hours". Now i want to calculate this Total_Hours as "Sum of Hours" (Hours is record in "Time_Sheets" table) Whenthree fields are equal ie., Employee_#,Fiscal_Year and Project_# in Yearly_Amount form is equal to Employee_#,Fiscal_Year and Project_# in Time_Sheets Table.
Please guys help me out. Iam struggling too hard but iam not finding a solution.
I have an “unbound” form with a list box, a text box, and a combo box. The list box’s Row Source Type is a Query. The query’s criteria come from the textbox and a combo on the form. Once the information is entered into textbox and the combo box a macro is set to run on the combo box’s “On Change” event, which runs a Do Command Refresh Page. This expression [Forms]![Form46]![month] is an example of how I am setting the criteria in the query; which when I put the month “May” in the text box passes that information to the query’s design view “Month column. The combo also works the same way. What I need is to know what type of expression do I use so that I can use “All “ in the text box and combo box.
I'm trying to create a text box on a form which displays a value based on numerous criteria.
Basically I manage a database of employees. Some are external employees, some are internal. Some have left the organisation, and some are still employees.
I have two fields which need to be pulled into this expression:
is_leaver - text box, with value either "Yes" or NULL
organisation - name of organisation (text)... lets say my organisation is called Happy People Ltd
The text box I have is called employee_status
I can see that there are 4 possible combinations:
1. Internal Employee - Is an internal employee and still works here 2. Internal Ex-Employee - Is an internal employee but has left 3. External Employee - Is an external employee who still works here 4. External Ex-Employee - Is an external employee who has left
This displays whether they're an employee or ex-employee. How would I form the expression to combine the two fields, and calcuate whether they're internal or external?
Due to the way the form is set up I don't want to have two separate text boxes and would prefer to combine it.
I have a form used for taking inventory. One table to lookup items, one for writing values. Tables linked by an ID #. I created a text box on my form to lookup a value called PARQTY in the lookup table. I need to be able to subtract that value from the QTY that is entered into the Qty_Counted box on teh same form when they take inventory.
I created a new Text box and in the control source for it, I entered an expresion that basically says subtract The PARQTY from the QTY_COUNTED it reads = [QTY_COUNTED] - [PARQTY]
The box is subtracting the QTY_COUNTED from the ID # that is linking my two tables and displaying it. Its not supposed to be subtracting the QTY_COUNTED at all. It should be subtracting the PARQTY value that is displayed from the QTY_COUNTED. The PARQTY box does display the right value from the lookup table so I can't figure out why it's pulling that ID number or where from. The rest of the form / program works and we've been using it for a few years. I also created that, but like now, I had to muddle through each and every little thing I did.
I've never used DLookup before and I can't get it to work for me so far.
I have 1 table which contains products and different properties of each product, such as the weight of the product.
I have created a query which sums the weight of all products, but only for those that have a value >0 in a certain field. This all works fine.
Now I simply want to display that calculated total weight in a text box on a form. So I thought DLookup could be used for that. But I can't get it to work, maybe because I'm not putting in any criteria? In the control source of the text box I've put the following:
=dlookup("[TotalWeight]","qryTotals")
I don't have any criteria, I just want the value from my qry expression. The textbox on my form now displays #name?
This error message keeps popping up no matter what I do. "Database can't find the field 'QuotationNumber' referred to in your expression." I tried to define a text box by using a DLookUp function, however that did not work. I deleted the function, and now this error message keeps popping up. I have searched everywhere for an expression with 'QuotationNumber' in it, and I cannot find it. What do I do?
