I currently have a very basic table/query/report set up to calculate monthly revenue. The data type in my table fields are "Currency" and my existing default value is "0".
I would like to change the "0" default value to a "-" (dash) for non existing values and still be able to calculate totals.
How do I incorporate this dash line and still be able to calculate figures??
Thanks in advance for your time and consideration!!
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));
If I have a field in a table called "AddedBy", is there a way for this to default to the CurrentUser that's logged in to the database when a record is added to the table? I know you can use "Application.CurrentUser" both in the VB code and within SQL queries, but it doesn't seem to work if I put it in the "Default Value" box with the table editor. Any thoughts on this would be appreciated. Thanks!
I need to break up part of the value in one of my fields. I can do it in Excel by identifying the position of the dash with eg. =FIND("-",D13) And then using it in a LEFT function. Or using the text to Columns wizard.
How can I do it in a query, there is no find function. If there is an equivelant what is it?
I've built a macro with wildcards that replaces multiple spaces of varying numbers after perods, commas, and before and after numbers. Now I want to add a search for the same before and after en dashes and hyphens. (Pretty soon I'll have Word streamling a lot of documents I edit!)
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?
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.
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...
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.
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.
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
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"
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.
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.
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.
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
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.
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?
I'm developing a database for the hospital I work in. One purpose of this is to keep track of patients with temporary invasive devices (there are many types such as urinary catheters, ventilators, etc.), specifically how many days each patient has a device for. Each device is associated with one patient only, but one patient may have many devices. Here's how the associated tables are set up:
1. tblPatients - PatientID (PK), LastName, FirstName, DOB,... 2. tblLocations - LocationID (PK), LocationLabel,.... 3. tblDevices - DeviceID (PK), DeviceType, Device, DeviceDesc 4. tblDeviceUse - DeviceUseID (PK), DeviceID (FK), PatientID (FK), LocationID (FK) (where in the hospital was device inserted, e.g., operating room, bedside, etc.), DeviceStartDate, DeviceEndDate 5. tblDeviceDailyLog - DeviceLogID (PK), DeviceUseID (FK), DeviceDate, PatientLocID (FK) (area in the hospital that patient is in)
All primary keys except for PatientID & LocationID are Autonumbers; and the tables are linked appropriately.
Whenever an entry is made into tblDeviceUse, I want there to be new records to be automatically generated in tblDeviceDailyLog for each date between the Start and End Dates. For example, patient A123 had a urinary catheter from 1/1/2000 to 1/10/2000 that was inserted while the patient was in the ICU, but the patient was moved to the Medical Ward on 1/7/2000. So tblDeviceDailyLog should have 10 new records associated with this device, one for each calendar day, with the appropriate location for each day.
I'd like to learn how to do them quickly and effectively in Access. One of those things seems like it would be an incredibly simple, intuitive operation, but it's not, at least not to me.Say I have some data that really should only be on one table, but it's currently on two tables in Access: "List Main" and "List September Adds." They both have the following fields: "Full Name" "Email" "Address" "City" "State" "ZIP" "Phone."
The first table is my main list of contacts. The second contains new info, consisting of 1. a few new contacts, and 2. updated info for a few of the contacts already in table "List Main."
I would simply like to put all the new contacts from "List September Adds" into my "List Main" table, and I'd also like to fill in a few missing e-mails in "List Main" with newly gathered e-mails for those contacts, info that is in my table "List September Adds."I'm sure there is built-in functionality to do something as basic as essentially turning two pages of the same spreadsheet into one. In fact, I know I could import data from an Excel file and have it "append" to a preexisting table if the fields are all the same.
However, I'd like to know how to do it when the tables are already in Access (without having to learn any SQL, mostly because I'm fairly certain I don't need to know SQL to do something like this). I've heard of Append and Update Queries and given their names, it sounds like they'd be useful, maybe with use of "Totals" and "Group By" to get rid of duplicates; however, I can't seem to get any of this to work right.
Mostly, when I think I am doing an Append correctly, it doesn't add new data at the bottom of a table; it just wipes out all the data that was there, and replaces it with data from the source. For instance, when I've been running an Append Query to get my "List September Adds" rows into my "Main List" table, what I end up with is only the "List September Adds" rows, and all the "Main List" rows gone.
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.
