Default IIF Statement In A Table
Dec 28, 2005
I am making a default value statement in a table that looks like this
IIF([company name] IN(“name1”, “name2”, “name3”), Date()+14, Date()+30);
this statement says that if the companys name is any of the names in the list, then the date outputed will be todays date +14, otherwise, the date will be todays date +30.
whenever i try to use the statement, access keeps giving me the error...
"you omitted an operand or operator, you entered an inalid character or comma, or you entered text without surrounding it in quotation marks"
what is wrong with my statement??
View Replies
ADVERTISEMENT
Feb 12, 2007
Is it possible to add an Iif Statement to a default value?
IIf([hours],"YES","NO")
This does not work?
View 3 Replies
View Related
Oct 30, 2012
I have a text box with the following statement:
Summary detail: Sum(IIf([dacthc]=0,Null,Fix(100*[dqty]*([dstdhc]*[drate]/[dacthc]-[drate]))/100))
My question is, if it meets the criteria, is it possible to make it default to a "0" in the field.
View 1 Replies
View Related
Feb 13, 2006
Hi Everyone,
I hope someone can help.
I have a form with a combo boxes and a table with relevant list and additional field, fldDefaultDrive (Yes/No Field).
Currently in order to set the default value, I have used the following code for each default;
Private Sub Form_Load()
Forms!frmMediaLabeller!CboDriveName.DefaultValue = """D"""
End Sub
However, I want users to be able to go into the table and change the default value if thier CD player default Drive is anything but D: Drive. I have tried to replace the D above with an SQL statement but with no success.
Private Sub Form_Load()
Dim Drivename As String
Drivename = SELECT tblMediaDrive.fldDrivename FROM tblMediaDrive WHERE (((tblMediaDrive.fldDefaultDrive)=-1));
Forms!frmMediaLabeller!CboDriveName.DefaultValue = """Drivename"""
End Sub
This is definetly not working, can anybody help, I have a feeling it is syntax but not sure where? :confused:
Robert88
View 7 Replies
View Related
Jan 21, 2005
Hi everybody,
Beginner here needs help !
I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?
Thanks in advance !
View 1 Replies
View Related
Jan 13, 2005
Is there any way I can have a default value in a table set to the value in a field in another table?
I have a table that has event information including the number of the event, e.g., this year is the 51st one. This event information table only has one line because after the event I'm going to save the record to an archive table and then update the information for the next year.
I have another table with participants in the event and records for it come from a form. I'd like to have it so that it automatically inserts the event number for each record. In this way when I archive that information, we'll know which event the participant was involved in. The participant could be involved for a number of years.
I'd rather not have the input person have to type in the event number every time. Doing a combo box also involves an extra step for them. It seems like I ought to be able to automate this info. I don't know anything about VBA.
Would one of you help me? By the way, I've learned an awful lot reading on this forum. I can't tell you how many problems you've solved for me. Thank you.
View 2 Replies
View Related
Apr 4, 2006
Hello,
I have a field named dblNumber and I want it's default value to be it's value in the previous record + 1. How can I do this ? If it's not possible in a table, can I do it on a form?
op.: I don't wan't to use the "autonumber" option...
thanks
m.
View 6 Replies
View Related
Feb 7, 2007
Hello, I have been looking around trying to gather as much access info as I can. I could really use some help with what seems like it should be pretty basic to me. I am making database for excavation estimation and have a table with my equipment and a field for default costs. I then have a table where I assign costs to a specific job. So I would choose a piece of equipment from the equipment table and then would like the default values entered in from the equipment table into the job table for the default costs. So a default value is put there that can be edited for the specific job. I am guessing I need to enter something into the default data area on either the table or on a field on a form? Can anyone give me the format for putting this in. The primary key in the equipment table (auto-number) is linked to a foreign key in the job table. Thanks in advance.
View 1 Replies
View Related
Dec 14, 2004
I made a table with a Date and Age field. If I have typed in 12/14/04 for the date and 25 for the age in the first record, how can I make Access display 12/15/04 and 26 in the next record automatically, then 12/16/04 and 27 in the next record after that and so on (as a default value for new records). I have tried the different built in date expressions, but I always get a type mismatch error. Can anyone help me with this? I am sure it's simpler than I think. Thanks.
View 3 Replies
View Related
Jun 23, 2005
Hi all,
I have a table...
Amount........Wholesale...........Commission
Lots of other columns but they don't come in to play.
When an Amount is entered, can Wholesale and Commission auto populate with Amount*.9 and Amount *.1 using default values in the table definition? I couldn't quite get the expression builder to understand.
If it can't, how should I get it to work? Forms are OK. As is code.
Thanks
PB
View 2 Replies
View Related
Feb 15, 2007
I'm using Access 2003 to write reference books. I have a field labeled Title Page and one labeled Source Page. 9 times out of 10 the Title page is the same as the source page. So, how can I put in a default value that the source page is the same as the title page, but then I'm allowed to go in and change the source page in the rare instance when they don't match. I'm fairly new to Access, though not to database programs.
View 2 Replies
View Related
Mar 10, 2015
My DB has one main table where all the records are stored, and one form with multiple tabs allowing for data entry and editing. There are two distinct "Data Types" I have in the tables, so on the two distinct form tabs, I created a text box with a default value for each one (if it's a Type A record, then the box has a default value of "Type A" and the same rules for Type B).
Here's the problem. It doesn't matter if I include the default value in the Form Properties Default Value field or if I do in VBA (using this method), when a new record is entered into the table, it makes them all default Type B records.
View 5 Replies
View Related
Nov 19, 2013
I shouldn't normally record calculated figures into my table. I'm willing to buck proper procedure here for ease of use for referencing in reports; also, it's just one data point that will be calculated only once and not changed.Anyhow, I have an IIF statement that calculates a total price based on a table of costs. I set this as the default value and it calculated properly - but once I set my text box to have a control source, it no longer calculates my cost.
I was hoping to set it so it calculated the cost and then write that total cost to my table.should mention that my calculated control is in a subform.
View 2 Replies
View Related
Jun 9, 2013
I have 2 tables: Product and Sales
In table Product, I have field "Product" and "Price"
In table Sales, I have field "Product" and "@ Price"
And there is form "Sales" which is based on table "Sales"
I would like to make "Price" the default value of "@ Price" where "Product" on form Sales = "Product" from table Product
I put in the following expression in the "After Update" event of Product on form Sales:
Private Sub Product_AfterUpdate()
Me.@Price.DefaultValue = DLookup("Price", "Product", "Product=" & Product)
End Sub
However, it keeps giving me this error:
Run time error '3075'
Syntax error (missing operator) in query expression 'Produk=abc'
View 3 Replies
View Related
Jun 14, 2015
I have a split database ,and I need to update the Table default value of a field.Rather than go into the table I would prefer to use a form.I found this code but it wont work,I presume becouse my data base is split
Private Sub UpdateInvoiceReportNumber_Click()
If Not IsNull(Me.txtDefValue) Then
CurrentDb.TableDefs("PaymentsT").Fields("SelectInv oice").DefaultValue = Me.txtDefValue
MsgBox "Default Value has been changed to " & Me.txtDefValue
[code]...
View 9 Replies
View Related
Feb 11, 2015
I have a basic invoicing setup, with a Form (Invoices) and subform (InvoiceDetails).When in the subform, i have a combo box to choose a Product Code (saved in table as PCode).I want now to auto fill in the NettPrice and (Product Description) PDesc fields in the subform row - by looking these up in the Products Table and entering the data into the relevant fields on the Subform. This lookup will be based on PCode.
I tried all sorts of methods and the one i favour, if i could get it to work, is setting up a Function then calling this function from the Default Value property of each field involved.So, for the Product Description field (PDesc), i created a Function as follows:
Function GetDesc () As String
GetDesc = DLookup ("[PDesc]", "[Products]", "[PCode] = " & Forms!InvoiceDetails!PCode)
End Function
Then i try to call by entering =GetDesc () into the Default Value property for the PDesc field.I seem to have a syntax problem with my function code.I know some of my values like NettPrice need not be fields on my Invoice Details table, but the prices change and I also need to be able to overwrite prices etc when typing invoice.
View 14 Replies
View Related
Dec 26, 2013
I am still new to Access code.
I was wanting to know if there is a way to Add the Username to a table as a default value when a new record is added. I know you can add =Date() to get the date. Is there a simple way to get the ID of the person logged into Window?
View 6 Replies
View Related
Jan 7, 2014
How to change the default message of Exist table. the message shows like as. I wanna change this. How can i will do this?
View 1 Replies
View Related
Oct 31, 2007
Hello:
I'm working on a class assignment and I am in desperate need of help. I am trying to set up a table... the fields are as shown: Type of Registrant, Registration Fee, Number...
I've created another table that has a list of names, telephone numbers, amounts paid, and registration status...
I'm creating the table I talked about in the first paragraph to simply list the status of registration, the amount per registration status, and the total number of registrants per status...
The available registration types are as follows: Academic ($285), Student ($200), Executive ($300), Manager ($225)
What I would like is this: When entering my information in this new table, I would like for the registration fee to automatically appear... almost like a set value... So, if I entered "Academic" in the "Registration Type" field, the amount $285 would automatically appear. I've tried a few "if" statements, and they're not working in my favor... Is there any kind of code that will allow this to happen? Please keep in mind, the reigstration types from which the information would be pulled are in the form of a list box in another table... Thanks!
View 3 Replies
View Related
Apr 7, 2014
I have three tables: First, Second, and Third.The tables Second and Third each have a column with a default value of "X".I'm creating a query that selects fields from First table that JOIN with corresponding fields from Second and Third, so that all records from First are represented and only matching records from Second and Thirdare returned. I also select the "X" fields from Second and Third.
So I run the query: and the "X" column from Second only displays "X" for records that have a match in Second (since I mean for these fields to be quick indicators for if the record also appears in the other tables, this is my ideal result). However, the "X" column from Thirddisplays "X" for all rows, regardless of whether the record has a match in Third or not.
I can get more specific if need be, but I really can't tell why the one is displaying according to matching records but the other is always on. The JOINs are set correctly and the properties for the columns all check out.
View 4 Replies
View Related
Jun 6, 2007
I have created a database that tracks employee adherence. We have employees monitor adherence and when someone is out of adherence we track the time they are out and also there shift time. I am needing Access to know to change a record from 0:00 (midnight) to 23:59. I know it is probably done with an IF statement but dont know how to write it correctly, or where to place it.
View 1 Replies
View Related
Jan 26, 2008
Hi,
I am writing ddl statement for creating tables in access, i would need to provide a default date for a date column, how can i do this in a ddl statement ?
In the table design window in the gui, i can do it by providing the default as "=format(now())" but it seems not to work in the ddl statement.
also, is it possible to generate the ddl sql statement for an existing table in the database, i would need to know how can this be done too.
Thanks for the help
Joy
View 8 Replies
View Related
Oct 22, 2014
Is it possible to use a value of a table row in a "Like" statement of a query ? Below is query code that I want to modify to accomplish what I need. I want to search the "BudgetIn (Description) table for records that are like "ExpensesIn (Accounts) - table. I thought that if I entered the value in the "EXpensesIn" (Accounts) table, surrounding by asterisks, would give me what I needed. Unfortunately, the query criteria placed quotes around the critrea and the query looked for text (i.e. "*[ExpensesIn]Accounts*") rather than the value in the table.
SELECT BudgetIn.Description, ExpensesIn.Expenses, BudgetIn.Previous_Budget, BudgetIn.Budget_Year, ExpensesIn.Accounts
FROM BudgetIn LEFT JOIN ExpensesIn ON BudgetIn.Description = ExpensesIn.Accounts
WHERE BudgetIn.Description Like *[ExpensesIn]Accounts*;
View 5 Replies
View Related
Oct 10, 2005
I am going to attempt to explain the problem:
I design a form and all my work is based on that form.
I have two tables which I join together as following.
The unfitex field is calculating using fields from both tables as shown below.
Dim db As Database
Dim rst As DAO.Recordset
Dim sqlstr As String
v = Combo37.Value
sqlstr = "SELECT postdecgor.N1, postdecgor.Age, postdecgor.UNFITpc, postdecgor.DECENTpc, postdecgor.HHSRSpc, [UNFITpc]*[sample]/100 AS UNFITex, postdecgor.DECENTex, postdecgor.HHSRSex, [Sample size].[Age dwelling], [Sample size].sample FROM postdecgor LEFT JOIN [Sample size] ON postdecgor.Age=[Sample size].[Age dwelling]WHERE ((postdecgor.N1)='" & v & "'); "
Set rst = db.OpenRecordset(sqlstr, dbOpenDynaset)
Set Form.Recordset = rst
I then set up some textbox which shows the results as following.
N1.ControlSource = "N1"
Age.ControlSource = "age"
UNFITpc.ControlSource = "unfitpc"
DECENTpc.ControlSource = "decentpc"
HHSRSpc.ControlSource = "HHSRSpc"
UNFITex.ControlSource = "numunfit"
UNFITex.ControlSource = "unfitex"
DECENTex.ControlSource = "decentex"
HHSRSex.ControlSource = "HHSRSex"
It works, I can see the results on the screen, however I would like to save the results into a table and I am not quite sure how to go about it.
Can you help??
Thanks for your help
View 1 Replies
View Related
May 17, 2007
Is it possible to construct a query that uses information from one table to run a series of Like OR Like... criteria?
I have created a table that contains partial part numbers and would like to query a second table for all the part numbers that contain these partial part numbers.
Meaning, BACB30DX6 would return the desired information for BACB30DX6-7, BACB30DX6-8 and so on.
The TBL_PARTS_FAM_DIA table contains approx. 200 items so 200 LIKE statements can't be the way to go... I'm hoping.
I tried: LIKE "*[TBL_PARTS_FAM_DIA]![PARTS_FAM_DIA] *" thinking that perhaps this would go through all 200 items... but of course that was wishful thinking.
Do I really need 200 LIKE statements or is there some easier way to do this.
Thanks in advance!
View 2 Replies
View Related
Jul 20, 2006
I have tried searching and haven't found an answer to this question.
I have a calculated field in a query that returns a aging date to an invoice. I want to take that aging date and apply it to a multiple If statement. I tried a joining field, but it's not working. A simple multiple If statement should do the trick though.
If AR is:
0-10
Then return:
"Current AR"
If AR is:
11-40
Then return:
"001-030"
If AR is:
41-70
Then return:
"061-090"
There are more, but I can figure that part out once these are solved. Any help would be appreciated.
Thanks,
Jason
View 3 Replies
View Related
