I have a main data table and my users will input their information on a main form. One of the fields is called "IsSkyBlue", which is a Yes/No checkbox field. On the form (which has multiple tabs), it asks the question "Is the Sky blue", with instructions to check the box if the answer is "Yes". I have a "Summary" tab, and on it I want to provide the user's answer in written form. I want it to say: Do you think the sky is blue? YES
So, I have a text box on the Summary tab with the question, and for the answer, I tried =IIf([IsSkyBlue]=YES,"Yes","No"), but I get a #NAME? error in that field. I have tried using YES, TRUE, 1, 0, -1, Not Null.... nothing works in the IIf statement.
Does anyone know how Access stores a "YES" answer to a YES/No field?
Is there any way to count how many lines of text are stored in a particular table field? How about counting how many times was the "enter" key pressed?
I am using a predefined query to define the sql statement that will serve as recordsource to a form. Syntax is fine. The predefined query does not have the primary key of the table. The selection in the recordsource is done using this key. So I add this field to the design of the query. I save the query. It says the query is saved. But it is never saved. Run the query again it is not changed.
I have a table with a list of different government programs that products can take advantage of. Each of these programs has criteria such as "must use less than 1000W" or "lasts for 100 hours". Rather than have a column for each possible condition, I've created 3 fields that will accept any type of condition; Var1Condition (example: watts) , Var1Requirement (example: >=), and Var1Value (example: 50). I figured this would be the more efficient database design than to add 15-20 columns.
I then built a form that would where I could enter product attributes and would hopefully query my database and only return the programs for which the product would qualify. So I would have a field named "Watts" in which I would put the wattage of the product and then I would see which programs it would fall in.
In theory, it should be simple. I figured I could just find a way to combine my 3 criteria fields into one string "Forms!Search!watts >= 50" and then use that as a query filter, but I can't find a way to do it.
With microsofts article, I have made to store multi options value of a list box in a text box with comma. However, since these are IDs being stored, I want these values to run a query and get results also.
But I am confused since have never used comma like in query
I have several projects with different tasks for each. I have 3 fields [fkProjectsID], [TaskOrder] and [Duration] in a table for project tracking with that structure:
I want to calculate cumulative values stored in [Duration] field (represent a number of days). I'm using the field [TaskOrder] to order different tasks within each project. With some testing, I was able to calculate cumulative [Duration] with 1 project using the DSum fucntion as following:
I was having the sequence: 5, 13, 28, 34 for respectively Task 1,2,3,4. However, when I add a second project (and then a third...), I need to be able to filter based on [fkProjectsID] as well (i.e. a specific DSum by ProjectsID). I add this criteria but I get the sum of [Duration] on each row instead of the cumulative:
CumulDuration: DSum("[Duration]","[tblProjectTracking]","[TaskOrder]<=" & [TaskOrder] And "[fkProjectsID]=" & [fkProjectsID])
I have a question regarding the display of a list of numbers that are stored into a field of my database. Currently the numbers are comma-delineated which is fine with me, but I was wondering if there is a way to change the display of those numbers on the form so that it is easier to read.
Example:
current format - 1,2,3,4,7,8,9,10 desired format - 1-4, 7-10
I have developed a database but have had difficulty with sorting data within subform of a main form. The subform displays the related tasks that correspond with main form that has been selected. Within the main form I have also created a text field that defines a particular sorting sequence of the tasks found within the subform which is titled Task Sequence. What I would like to do, is use the Task Sequence field to sort the order of tasks within subform. I have tried: IIf([ID] Is Null, 0, Val([Task Sequence])) within the Advanced filter/sort but either it shows only the first task defined in the Task Sequence or it wants to filter the main form and not the subform.
I have a parent form that has the following [Category],[Event],[Name]
In the sub form i am also storing the same values [Category],[Event],[Name] & [Contestant ID], [Score1],[Score2]
I do not want to enter the [Category],[Event],[Name] each time i enter a record into this sub form i would like the value that is in the parent form to be picked up and stored as the values for these fields.
Hi, I need to write a stored procedure that sits between sql server and MS access. This procedure will make sure that the queries run in access against the data in sql server will not access data for more than two years back. This is so that the queries run against the sql server do not hang up the server for too long.
I've read the posts regarding not store calculations. However, I was told by IS to store the mileage IRS deduction as it changed 3 times last year.
Any idea's how to handle this? I need the table to remember how much $$ was paid out in mileage expense. I had set up a field called mexpense and mileage, where =[Mileage]*0.375.
I apologize that I am not a very good VBA programmer yet but I'm really trying....
I am attempting to calculate a value by adding two fields and storing the result into a third field - - My code only works if both of the fields are not null.
Private Sub PartialDepositAmount_AfterUpdate()
Dim PartialDepositAmount As Currency Dim FinalDepositAmount As Currency Dim DepositAmount As Currency
I have an Access mdb that has a bunch of tables linked from a SQL Server. On the SQL Server I have some stored procedures that do inserts into various tables. In the Access db I have some local (lookup) tables, queries, and other things that for various reasons can't/don't need to be stored in the SQL server. I'm using Windows authentication to connect to the SQL server because the security is managed through our domain.
I want to write a form that has unbound text fields that, when I submit the form passes all of those values as parameters to a stored procedure in the SQL server. I have written just such a form in an .adp file, which handles the integrated security nicely.
However, I'd rather do this in a regular .mdb file. I've read up on how I can use an ADO connection to do this, then create a SQL string to EXECUTE the stored proc, grab all the values off the form, and build the SQL.
But the problem with this solution is the authentication - I can't use the SA user and password in this connection string, I'd have to utilize the logged-in user's information.
Is there an easier way to execute the stored procedure from an .mdb? Using the integrated authentication?
Failing that, is there a way that I can create "local" (to the .adp) tables, queries, and so on in an .adp?
I come from a SQL Server background and am very familiar with store dprocedures and input parameteres.
Is there a way to use a similar method in Access. or example, passing parameters from a button on a form to a query, which can change depending on the input parameter? If so, how ?
Also, how much scope do you have in this for running SQL IF statements and such in the query? It seems that many Access queris can only have one statement at a time.
I'm running a access 2000 adp front end working from a SQL2000 database. I've got a sproc P_insertsessions i can get this work from a cmd button on a form but i'm having problems setting the paramerters i want it to use controls on the form. @contractid = contractid @start_date = startdate @end_date = enddate @hours = duration
Sometimes when I send a copy of an Access 2003 application to a remote user, a stored procedure or function will disappear. What causes this, and how can I get the entire application over to the users?
I have combo boxes on a form which are pulling values from a one table and being stored in another table. However they are being stored as '1' or '2' in the other table once selected on the form, as instead of being stored as their literal values ie 'car' or 'van'. Is there any way of making this happen, as it makes reporting a nightmare! Thanks in advance...
I have and access form that has a list box with dates in it. I need to pick a date and then run a stored procedure in SQL based on that date. Can someone help me with the login to pass on the date?
So if I have listbox1 which is a list of dates from another table and my procedure is simply
select * from main where date = *******
How can I pass the list box value to the stored procedure? My procedure works with WHERE date = (SELECT(LEFT(GETDATE(),11))) but I have since learned that todays date will not always be used so I need to pass a value.
I'm working on and Access 2003 front end with a SQL Server Back End. I would like to take advantage of the speed of Stored Procs to populate a list box on a form. Here's the code I'm trying to use.
Set cmd = MakeStoredProc("StoredProcName") Set prm1 = cmd.CreateParameter("ParamName", adInteger, adParamInput, , Me![ID]) cmd.Parameters.Append prm1 rstSource.CursorLocation = adUseClient rstSource.CursorType = adOpenKeyset rstSource.LockType = adLockOptimistic Set rstSource = cmd.Execute
Me![ListBox].Recordset = rstSource
Access doesn't like this. What is wrong. I get the message "Object doesn't support the property or method"
Sorry if this is an easy one and I just missing it, but I have a form in an Access2003 database with option groups for ratings for 18 individual tasks ie: Safety: 1. Satisfactory 2. Marginal 3. Unsatisfactory 4. N/A The value stored in the table is 1,2,3, or 4 for each one, obviously. How can I change the stored value in the table to "Sat" "Marginal" "Unsat" etc? Or, if easier, make the report convert the numerical values to names?
