I guess I need to create an if else statement for one of my fields in a query. The query basically calculates a dollar amount from certain fields in one of my tables and sums them together at the end with an expression.
I also have a true/false field in the same table. and if it's true then the expression in my query must equal 0, if false then it sum up the fields. Do I need to create a module for this or is it possible to do this directly in query? And if so, how?
I have an access database that contains information on donations made to our non profit organization.
I'm wondering how I query for people who have donated in categories (a,b,c) between 01/01/05 - 01/01/07, but have NOT donated in categories (a,b,c) in >01/02/07.
In other words I'm trying to sort out the people who have donated to us in certain categories in the past, but have not yet done so this year.
There are fields in my tables for donor id, as well as donation type, and donation date.
I'm relatively new to Access and having some difficulty using conditional statements - if anyone could help I'd really appreciate it.
Basically I want to take an order summary table (OrderData) that shows total orders over different date ranges and expand them to show the average order per day for each product.
I'm using two queries to do this, the first finds the average per day for each record (Demand) using this formula: Expr: Sum(([OrderData]![orderamount])/([OrderData]![EndDate]-[OrderData]![StartDate]+1)) That part works fine.
The second query then needs to lookup each individual date of the year against the date range on the original data table, and if the date falls within the range, places the daily average from the prior query in a column.
I had been trying to use this - Expr: iif([Output]![Date] Between [OrderData]![Start] And [OrderData]![End], [Demand]![OptimalAverage],"0") but it's not working.
I'm relatively new to Access and having some difficulty using conditional statements - if anyone could help I'd really appreciate it.
Basically I want to take an order summary table (OrderData) that shows total orders over different date ranges and expand them to show the average order per day for each product.
I'm using two queries to do this, the first finds the average per day for each record (Demand) using this formula: Expr: Sum(([OrderData]![orderamount])/([OrderData]![EndDate]-[OrderData]![StartDate]+1)) That part works fine.
The second query then needs to lookup each individual date of the year against the date range on the original data table, and if the date falls within the range, places the daily average from the prior query in a column.
I had been trying to use this - Expr: iif([Output]![Date] Between [OrderData]![Start] And [OrderData]![End], [Demand]![OptimalAverage],"0") but it's not working.
how do i use conditional statement in sql? im using access 2003 as frontend and backend.
i would like to count the number of records in a table (ex. ENROLL) where there is only 1 instance of any value in a field (ex. STUDENT_NO) plus another condition where a given value (ex. 1) should exist in another field (ex. SECTION).
I am trying to create a conditional expression in a table.I am trying to say the following:
If [KIND] is "DEER", return "70", Otherwise, if [KIND] is "ELK", return a calculation that is [WEIGHT]*.7.
I am hung up on the calculation for the "ELK." If I write IIF([KIND]="DEER","70", IIF([KIND]="ELK", "[WEIGHT]*.7","OTHER")).
I can enter the expression, but when I open my Form and select "ELK", the field that is supposed to have the calculation just shows [WEIGHT]*.7.....it does not perform the calculation.
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.
CREATE TABLE Jobs (jobname LONGTEXT(1000) NOT NULL, [desc] LONGTEXT(1000) NOT NULL, [req] LONGTEXT(1000) NOT NULL, [specialreq] LONGTEXT(1000) NOT NULL)
I have an online Access database and dont want to have to take it offline (download, update, upload) to create a new table.
Therefore I want to use a CREATE statement that I can run via an ASP page to create any additional tables.
I don't want to have to create the statements by hand so im looking for a way to create a table on my local version of access and export the SQL statement that would be used to create this table.
Hi, I'm fairly new to Access and I'm trying to figure out how to create a validation rule via SQL. I searched through the forums but didn't find anything. Hope someone can point me in the right direction. For simplicity say I have the following extremely simple CREATE STATEMENT.
CREATE TABLE GAMES ( Name TEXT PRIMARY KEY NOT NULL, Amount CURRENCY NOT NULL, );
How could I rewrite the above query to specify that the only valid amounts allowed are say between 1 and 10? I'm familiar with the Constraint statement and the Check clause but I can't get these to work in Access.
I can create a select query with a combo box in it by changing the property of one of the fields - making the display control to combo box and adding the values. No problem there.
What I want to do is to be able to create a query dynamically from vba using a CreateQueryDef statement.
The problem is that you can only pass an SQL string to CreateQueryDef, and the combo box values are not part of the SQL code, but are saved with the query (presumably the same way column layout gets saved).
Is there a way to create a query with VBA and include non-SQL layout criteria so I can have combo boxes?
I want to create a button that executes an SQL statement in a form. The reason I can't just save a query and run the query from the form, is because the SQL statement needs to use a combobox value from the form. I've learned that docmd.runsql will not work for a simple select query.
Hi guys i tried to run this create table statement and each time i get syntax error. I pasted the code in sql view windows of access 2000 and pressed the run code and i get this error massage saying there is syntax error. Could any one help me write correct create table statement that does not give me this error.I know u might tell me why u do not create table in design view or .. but i want to do this since i want learn this method as well.thanks
CREATE TABLE PLAYERS (PLAYERNO SMALLINT NOT NULL CHECK (PLAYERNO >0), NAME CHAR(25) NOT NULL , INITIALS CHAR(5) NOT NULL , BIRTH_DATE DATETIME, SEX CHAR(1) NOT NULL , JOINED SMALLINT CHECK (JOINED >=1980), STREET CHAR(15) NOT NULL , HOUSENO CHAR(4), POSTCODE CHAR(6), TOWN CHAR(10) NOT NULL , PHONENO CHAR(10), LEAGUENO CHAR(4), PRIMARY KEY (PLAYERNO) )
What is the syntax of create table and insert statement for access 2000 db.
I want to paste the create table and insert statement to access 2000 "sql view window". Therefore i want the correct syntax and format for these statements. The reason that i want do this is that I created an application that generates create table and insert statements for access db in text file and this way i want to test my application if it generated the statement correctly.
Hi guys i tried to run this create table statement and each time i get syntax error. I pasted the code in sql view windows of access 2000 and pressed the run code and i get this error massage saying there is syntax error. Could any one help me write correct create table statement that does not give me this error.I know u might tell me why u do not create table in design view or .. but i want to do this since i want learn this method as well.thanks
Code:CREATE TABLE PLAYERS(PLAYERNO SMALLINT NOT NULL CHECK (PLAYERNO >0),NAME CHAR(25) NOT NULL ,INITIALS CHAR(5) NOT NULL ,BIRTH_DATE DATETIME,SEX CHAR(1) NOT NULL ,JOINED SMALLINT CHECK (JOINED >=1980),STREET CHAR(15) NOT NULL ,HOUSENO CHAR(4),POSTCODE CHAR(6),TOWN CHAR(10) NOT NULL ,PHONENO CHAR(10),LEAGUENO CHAR(4),PRIMARY KEY (PLAYERNO))
Details: I have a Profile form that tracks the expiration date for each client's various certifications. These dates are set up in the Short Date format in the table design of Access.
Problem: When a user accidentally presses an alphabetic key while updating an expiration date, an Access error message is triggered. This is confusing to my users as these messages are written in Access lingo. I would rather that nothing occurs at all. I wrote a case statement to disable each letter of the alphabet and applied it to the On Key Down Event for each expiration date control on my Profile form to solve this problem, but this must be applied to 28 separate controls. I would rather call a function that disables alphabetic keys for each date control in my form when called.
Questions: How do I transform my Disable Alphabetic Keys Case Statement into a function that I can call for each expiration date control? I know that when writing a function certain variables have to be declared and/or initialized.
Also, will I need to create a function to re-enable alphabetic keys or is this unnecessary because the disable alpha keys function will only be called for specific controls, not the entire form?
What I Have Tried: I have tried copying and pasting my Disable Alphabetic Keys Case Statement into a module to attempt to create a function, but it needs work.
Below I have included 2 types of code: (1) The original On Key Down code applied to each date control on my form (2) The same code written as an attempt at a function
Original Profile Form Code to Disable Alphabetic Keys in the On Key Down event for each date control
Private Sub txtCert1ExpDate_KeyDown(KeyCode As Integer, Shift As Integer) Select Case KeyCode 'All message box text is for me to test the code, not for the user to see Case vbKeyA MsgBox ("you pressed the A key")
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
I have a query with the following criteria in one of the fields:
>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())
fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.
>=DateAdd("m",-12,fom()) And <=fom()
Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.
Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())
I have also added the column name to each expression and it still doesnt produce any results.
I am creating a multi-search form for a student database, where after I enter my search criteria I hit a "Run Query" command button and then it opens a query form with all of my criteria.So far I can search using last name, first name, and middle name. When I try to search with a start date and end date I am have issues.The start date and end date is for the class date. In the query form under the field, class date, for criteria I wrote:
Between IIf([Forms]![Search Form]![Start Date]="",1/1/10,[Forms]![Search Form]![Start Date]) And IIf([Forms]![Search Form]![End Date]="",4/25/15,[Forms]![Search Form]![End Date])
I want it when I write a date in the start date and end date I want it to give me a list of all the students who took the course between those dates. Also, if I leave the dates blank I want it to search all dates. The dates 1/1/10 and 4/25/15 are just the dates I gave because that is far back as my database goes.
I am trying to create a form to enter data in a table. I would like to make it pull in info from a switchboard. If the record already exists I would like it to find it and allow me to edit the info. If the record doesn't exist I would like to be able to add a new record with the data input. What is the best way to accomplish this?
I created a database and I manage to split it into front end and backend. now I'm going to make an EXE of the front end.My question is when I open the front end, I need the database to be empty, and them create a button that will make the user select which project he wants to open (backend).I also need to create a button that will create a new empty backend and save it as a new project.
I have an unbound box on a form and, within the box are 25 strategically placed "X" marks (text fields) named X1 - X25. I want only one of the X marks visible based on the value of two fields, i.e., if field "Impact" = 0.5 and "Probability" = 0.3 then X23 will be visible.
Could someone please get me started with the code to accomplish this?
Trying to setup a conditional format for a field on a form based on conditions from another field.
I have a form with [Field1] and [Field2]
I have a 3rd field called [New SMF]
What I want to do is have conditions on [New Field] so that if the value is greater than Field1 or less than Field2 the background of new field turns red.
I've a question and I hope you can help me out....... I'm trying to use Conditional Formatting Options on a Text box called "Date". I'd like that this box become Red if its value is between value1 from Text box "Start Date" and value2 from Text box "End Date" or Green otherwise (in this way the color change dynamically every time I change the values of Text Boxes "Start Date " and "End Date"). How can I do that? What I need to type in the Conditional Formatting Windows?
