ex. total: [rate]*[dys] (working)
ex2. total: [rate]*[dys]-[late] (not working when one of the field(late) doesnt have value)what i want to happen is even if the late field doesnt have value i want it to still calculate
just like in excel =a1*b1-c1 (even if the c1 doesnt have value it still works properly but in access total: [rate]*[dys]-[late] (if the late doesnt have value, nothing shows up in total field, even though rate and dys have.what i did was i assigned the default value of late field properties to 0.but the 0 is just so annoying to see when i have to many data.can i calculate 3 fields in access just like excel even if one of the field doesnt have value? if my only option is to set the default value of late to 0 , is it posibble to hide the default value?
I have a split form design. I want to hide a field which I have already set a default value for. But when I make it invisible, the default value is not being recorded.
I have created a query that will create a table automatically. I want one of the fields that I have defined in the query, not to show up on the table. the purpose of that field is just to check a minimum value so that the field next to it can use the value. when I uncheck the "show" box in design view the field completely disappears and Access asks the user to enter a value for that name since the value is used in the field next to it.
I have a query that selects different values from different tables.The values of these fields change all the time and I would like the result of my query to exclude - hide the fields that are zero. However, I only want to hide the specific zero fields not the hole record.Non zero fields should still be visible.
Here's an example of the a duplicate, all 4 first rows are duplicates, but the last value is null for the last duplicate. I want to hide the one the duplicate that has null value!
I have a question on hiding duplication record using query.
The fields in the query are:
full name(trainee), NRIC(trainee), gender(trainee), preferred language(trainee), company(trainee), course name(course), course date(course), competent(course), class(course), L3 survey(trainee), L4 survey(trainee), num of month(course)
When the query is being run, it will show all the people that have not done the L3 and L4 survey after 3 month. The the query will be convert into a form. However the problem is that the record will show a few same name due to one person can take more than 1 course. therefore, the data in the course table will always be different.
How can i make the record only show 1 name even though they have different course name.
I had tried putting 'yes' for unique record and unique value but it did not change the result.
current SQL query:
SELECT DISTINCT trainee.[Full Name], trainee.NRIC, trainee.Gender, trainee.[Preferred Language], trainee.Company, course.[Course Name], course.[Course Date], course.Competent, course.Class, trainee.[L3 survey], trainee.[L4 survey], DateDiff('m',[Course Date],Date()) AS [Num of Month] FROM trainee INNER JOIN course ON trainee.NRIC = course.NRIC WHERE (((course.Competent)="c") AND ((trainee.[L3 survey])=False) AND ((DateDiff('m',[Course Date],Date()))>=3)) OR (((trainee.[L4 survey])=False) AND ((DateDiff('m',[Course Date],Date()))>=6));
I want to automate a query so when the user views it they see only the columns that have data. Columns that do not have data are not there.I am using MS 2007
My Access tool is setup to import data into tables and then the user views the data imported. However, if their's no data (and no column to import) I want the query report to not include the column.
Is there a way to hide a field in a union query? I need to keep the field in the SQL statement because I need to order by it. The field is "Rank," but I don't want it showing up.
Currently I have a query where the criteria is dependent on the combo boxes on my form. I would like to add checkboxes to my form which determines which fields are shown or hidden. For example if I had a checkbox for address, selecting it on my form will show the address column in my query results.
I have created a simple login form that is based on User Name and Password.the table contains UserName, First Name, Last Name, Password and User Role(dev, admin...ect.)How can I change who can access different parts of the database?I want all the Development tools only accessable to People who have "dev" as a "User Role".Admin can only access the forms related to data entry and managers can view all forms but not the tables, querie and tools...ect.
I have a query that simply pulls back each tenant from a tenant table. Each row has a bit field that I use for check box's. This check box is true or false depending on the balance they owe. I currently use it to print off late reports and people that have this field set to true have a check box that auto populates on my datasheet.
I copied the query but I want to change it a little. I want to be able to show all tenants, but I want the bit field to show on my datasheet defaulted to checked for everyone (even if the table shows false) so that I can print all tenants without having to check the fields that are false in my table.
I tried to create an expression instead of using the bit field, but I no longer have the option to make that column into a "Check Box" under Lookup-Display Control.
Linked field is shown both in Master and child tables, but not in the default subdatasheets made by when a relationship is made between a Master and Child Table.
I got a combo box with a few columns for locating query activity. However, how can I make use of it to locate a particular record according to predefined values, for instance in a loop, to locate records. The combo box got 3 columns, just want to know the syntax to define the combo box according to some values. I need to define only the first colmun or all 3 columns??
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.
Expressions in Access have given me some trouble before. Mainly due to inexperience. I hardly ever work with them. What I am trying to do is make the default value of a form textbox control the minimum value of a field A in a table A. The datatype of Field A is Date.
So far I've got:
=Min([table A].[field A])
In the Default Value of the form's property sheet, but this just returns a blank value. I've had a look in the table and there is no value that is blank in field A.
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));
Goal - Show how many policies (dbo_Policy22.Policy.Number) were cancelled during a certain time period (Invoice Date).
Problem - a policy can be cancelled more than once and I don't want to count it after the initial cancel status (885).
Question - Below is my query. I'm thinking I type "Distinct" somewhere, but I have no idea where.
Please help :) Thank you.
SELECT dbo_Producer22.Number, dbo_Producer22.Name, dbo_Invoice22.Invoice_Date, dbo_Policy22.POLICY_STATUS, dbo_Policy22.Policy_Number FROM dbo_Producer22 INNER JOIN ((dbo_Insured22 INNER JOIN dbo_Invoice22 ON dbo_Insured22.Insured_Key = dbo_Invoice22.Insured_Key) INNER JOIN dbo_Policy22 ON dbo_Insured22.Insured_Key = dbo_Policy22.Insured_Key) ON dbo_Producer22.Producer_Key = dbo_Policy22.Producer_Key GROUP BY dbo_Producer22.Number, dbo_Producer22.Name, dbo_Invoice22.Invoice_Date, dbo_Policy22.POLICY_STATUS, dbo_Policy22.Policy_Number HAVING (((dbo_Invoice22.Invoice_Date) Between [Forms]![PfrmYearToDate]![txtStartDate] And [Forms]![PfrmYearToDate]![txtEndDate]) AND ((dbo_Policy22.POLICY_STATUS)=885));
I was wondering if there is a way to hide 1 record from a query
say I have 3 user accounts and on a form I only want to display 2 of them form the query,so the other record is still present but cant be deleted by accident or viewed
I have a form with various fields, two of these fields are "Date Purchased" and "Lease End Date". When there is no value entered originally, I have made a default value of 12/30/9999.
The problem is that when you open the form and these two fields are next to each other, the user says it confuses them seeing a date even though it is a dummy date.
Is there a way for me to create a expression that will be something like:
