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!
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.
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.
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"
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*;
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.
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??
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.
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.
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 have created several queries that in turn create a report. I am trying to write some VBA script to make them execute correctly, however am having trouble with the loop function.
DoCmd.OpenQuery "Qry_SubjectColleaguesByDivision" Do Until Tbl_ReportSubject.RowCount = 0 DoCmd.OpenQuery "Qry_DataToTrainingReport" DoCmd.OpenQuery "Qry_DeleteDataToTrainingReport" Loop DoCmd.OpenReport "Rpt_TrainingDue28Outstanding" DoCmd.OpenQuery "Qry_ClearTrainingReport" Qry_SubjectColleaguesByDivision populates Tbl_ReportSubject with the relevant colleagues. Qry_DataToTrainingReport calculates the report data for the first user in the list and copies to Tbl_TrainingReport, Qry_DeleteDataToTrainingReport deletes their record in Tbl_ReportSubject. Qry_ClearTrainingReport clears Tbl_TrainingReport.
My issue is getting the DO UNTIL statement to work. I am trying to get the loop to finish when there are no records in Tbl_ReportSubject.I know the data structure does not seem robust based on how the data is handled, however this is to do with a) Legacy issues, and b) data importing from other systems.)
I'm trying to run this very very simple code but I get a Run-time error '3450' Syntax error in query. Incomplete query clause. For sure the problem is the variable I used as Table parameter but I cannot understand where my error is :
Private Sub cmdRunCheck_Click() Dim strSQL As String Dim strTempTbl As String strTempTbl = "tblCheckDoubles" strSQL = "DELETE * FROM " & "'" & strTempTbl & "'" CurrentDb.Execute strSQL, dbFailOnError End Sub
What is clear is that if I use this line the Sub is working smoothly
strSQL = "DELETE * FROM tblCheckDoubles"
Please do not tell me that it is not possible to use a variable as Table parameter....
I cant figure out a fairly simple IIF statement I need for a table validation rule. I'll try to explain:
I have 2 fields in a table that are controlled through a form, which sets the fields either true (-1) or false (0). Lets call them Field1 and Field2. These fields need a validation rule which is dependent on FieldX and FieldY.
When FieldX = FieldY, I need Field1 to be either 0 or -1
if Field1 is 0 -> Field2 is -1 and the other way around.
So I have this If statement that goes something like this;
Code: IIf {ttblEQAnswer_FCLIT.ActionType} = 'Answer Affir Defense' andnot isnull({ttblEQAnswer_FCLIT.METPrep}) and not isnull({ttblEQAnswer_FCLIT.AttyInstr}) and ({ttblEQAnswer_FCLIT.METPrep})> date(1900,1,1) and not isnull({ttblEQAnswer_FCLIT.AttyInstr}) and {ttblEQAnswer_FCLIT.METPrep} < currentdate - 10 then 'Y' as [MET InstructNeed?],
I want to use the above formula to calculate a column. However, do I need to join the tables referenced above in order to get this formula to work? As it stands I'm getting an error that says "Malformed GUID in query expression".
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 table called login and inside that table is three columns: username, password and admin.
I have the username saved in a global variable called GsUser. How can i find the record in that table with the same Username as the string stored in GsUser and use that record for an if statement which sees if the value of the admin column is "Yes". Im trying to do it using VBA. Im not using a form where everything is bounded.
I have a form with two list boxes on it: listAllBANANA and listActiveBANANA
The listboxes are populated from tblBANANA. Active is defined by the tblBANANA field "ResponseDate" being Null.
Also on the form is a CloseBANANA command button. When pressed with no selection, it opens frmCloseBANANA to the first record. If a BANANA is selected in listActiveBANANA, the form opens to the selected record.
Now comes the tricky part: if a BANANA is selected in listAllBANANA, I first need to verify they have an Active BANANA selected, before opening the form. I've poked and tested and searched and cut and pasted my little fingers off with no joy. What I've deduced is that I'm either messing up the syntax of the DLookup or I'm incorrectly referencing the selected record. There's also the third option that I'm going about this in entirely the wrong way.
All other code below works. If I delete or comment out the line in red, I have no issues other than I may open up a blank form because I'm trying to close a BANANA that is already closed.
Code: 'If a BANANA is selected in Active BANANA or All BANANA, open Close BANANA to that record. If not, open form to most recent record Private Sub cmdCloseBANANA_Click() If Not IsNull(Forms!frmGUI!listActiveBANANA) Then
I run a merge process from VBA code to create word documents. I would like to add 2 word tables to a document and only show them based on some parameter or mergefield in the data.
Can a table be identified? Like Table 1. And can it be made invisible?
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))
I have an AfterUpdate event where I want a list box to be populated based on three different criteria based on a table in my database
1. Complete = False AND 2. Supplier on form = supplier in table AND 3. Status in table = "SUPPLIER_RFQ FOLLOW-UP" OR "SUPPLIER_RE-RFQ TO OTHER SUPPLIER"
I am having trouble with the last OR statement criteria, i cannot get this to return values correctly. Here is my code:
SQL stored procedure which is simple INSERT statement on a single table 'tblSOF'
Code: -- ================================================ -- Template generated from Template Explorer using: -- Create Procedure (New Menu).SQL -- -- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter values below.
[Code] ....
I am stumped with the following error.
Error: Msg 102, Level 15, State 1, Procedure InsertINTO_tblSOF_sp, Line 80 Incorrect syntax near ')'.
