How To INSERT INTO Values And Select...
Apr 28, 2007
My thinking must not be right as I can't get this to work but i have a table with
Date AMT
02/05 50
02/06 20
and soforth....
I am summing up each week and putting this in a table with values
WeekBeginningDate WeekEndingDate SumofAmt
I am trying to do
INSERT INTO tblTwo ("02/05/2007", "02/09/2007", SumOfAmt)
SELECT sum(Amt) FROM tblOne
where TDate >=#02/05/2007# and TDate <= #02/09/2007#
The two dates in the INSERT INTO are just values and the SumOfAmt is from the select. Can someone help me with this? Thanks in Advance!!
View Replies
ADVERTISEMENT
May 20, 2013
I want to insert obtain marks of the subjects. Find the attached my db. when i select combo value my requirement is to insert the all records which associate with the combo. but when i select combo value it will happen nothing.
View 7 Replies
View Related
Aug 19, 2005
i have a multiselect listbox in my form.
The multiselectlistbox contains the names of different persons from tblUsers.
it's allready possible to write the id's of the names to another table (tblPresent).
But what I can't manage to do is re-select the values in another multiselect listbox. This multiselectlistbox is located on my editform.
I can display the values using a valuelistbox, but i need to see the non-selected items too..
hope someone can help me out
View 1 Replies
View Related
Feb 19, 2006
I work with project access (*.adp)
I want to know if it possible to take the last ID after the sql INSERT QUERY
my code:
strSQL = "INSERT INTO missionFather ....."
DoCmd.RunSQL strSQL
thanks!
View 2 Replies
View Related
Nov 2, 2005
I`m trying to insert some rows of the table [Produse finite dbo_SC03XX00] into the table [Loturi dbo_SC33XX00].
So, I tryed to run the following queries:
A) INSERT INTO [Loturi dbo_SC33XX00] ( [Loturi dbo_SC33XX00].SC33001, [Loturi dbo_SC33XX00].SC33002, [Loturi dbo_SC33XX00].SC33003, [Loturi dbo_SC33XX00].SC33005)
VALUES (select [Produse finite dbo_SC03XX00].SC03001,'01','000000000999',5000 from [Produse finite dbo_SC03XX00]);
But, when I want to save the query I get the following error:
"Syntax error. in query expression 'select [Produse finite dbo_SC03XX00].SC03001'"
B) INSERT INTO [Loturi dbo_SC33XX00] ( [Loturi dbo_SC33XX00].SC33001, [Loturi dbo_SC33XX00].SC33002, [Loturi dbo_SC33XX00].SC33003, [Loturi dbo_SC33XX00].SC33005)
VALUES select [Produse finite dbo_SC03XX00].SC03001,'01','000000000999',5000 from [Produse finite dbo_SC03XX00];
and I get the error:
"Syntax error in INSERT INTO statement."
I thought that the select query might be wrong, so I tested it:
"select [Produse finite dbo_SC03XX00].SC03001,'01','000000000999',5000 from [Produse finite dbo_SC03XX00]"
and it runs correctly.
How can I solve the problem? How can I insert the specified rows from the table [Produse finite dbo_SC03XX00] into the table [Loturi dbo_SC33XX00]?
Thank you in advance.
View 1 Replies
View Related
Mar 1, 2007
I was wondering if you can an INSERT SELECT statement for 2 tables w/ different data source?
Like one's from Oracle and the other's from Access?
Please help :D
View 4 Replies
View Related
Aug 1, 2006
hi guys. i was hoping you guys could help me, i have a combo box "cbocontract" which gets populated according to a selection from another combobox(cboAll). now i have another combo box(cboStatus) which according to what the user selects in cbocontract list box it should display active or inactive, now i have the row source from the cboStatus like this:
SELECT DISTINCTROW Test.Status FROM TEST WHERE (TEST.Facility=forms![Change of Status]!txtInvoice.value) and (TEST.PM_Contract_ID=forms![Change of Status]!cboContract);
it works perfectly, however is there a way to make put this code in a text box? how do i insert the select distinctrow into a textbox??? it should only display one value according to what the user selects in the cbocontract combo box... also, the user should be able to edit this textbox.
:o
View 4 Replies
View Related
Mar 4, 2014
I have an append query that contains an IIF statement. I want to code that into a VBA function. The SQL view of the query looks like this:
Code:
INSERT INTO tmpAvailInv ( NUID, Inv_Name, F_Name, M_Name, L_Name, Role )
SELECT tblPeople.NUID, tblPeople.[F_name] & IIf(IsNull([M_Name])," "," " & [M_Name] & " ") & [L_Name] AS Inv_Name, tblPeople.F_Name, tblPeople.M_Name, tblPeople.L_Name, tblPeople.Role
FROM tblPeople
WHERE (((tblPeople.Role)="Investigator") AND ((tblPeople.Archive)=False));
What I wrote for the VBA code is this:
Code:
Dim strSQL As String
Dim db As Database
Set db = CurrentDb
[code]....
Where it chokes is on the IIF statement with the double-quotes in it. I've tried several combinations with single quotes and double double-quotes. I'm just not getting it.
View 4 Replies
View Related
Jul 4, 2005
Hi all,
I have a table which contains business details (name, address etc) and also a field for clients. I then have a table which contains client details. Is there anyway I can click a button on a form containing business details which brings up a form containing the clients which would allow me to click the clients I want to be inserted onto the clients field on the business details table?
Does anybody have an example of this.
Thanks for your help!
View 1 Replies
View Related
Jan 23, 2007
I am having difficulty getting a query to work, there's probably something really simple I'm missing. . . I can get the Union function to work but I can't get it to create a table for me. The basic query looks like this:
INSERT INTO test
SELECT * FROM [Design] WHERE (Design.CID Not Like "*-*")
UNION
SELECT * FROM [Release] WHERE (Release.CID Not Like "*-*")
It works fine and shows me the table I want without the first line, but I would like it to input into a new or existing table. Any help would be greatly appreciated!
View 1 Replies
View Related
Mar 29, 2005
I have a UNION of two SELECT queries. It works very well but I need the end-user to be able to modify the data so I am using INSERT INTO tablename. This will work for one query at a time but if I try to use the UNION it reports “Syntax error in FROM clause”. Can you use UNION in this case or can anyone see my error?
The full statement is rather complex, I have added a few spaces and linebreaks to make it readable as follows:
INSERT INTO TableCompleteForMailing
SELECT [Address List].[FamilySurname] AS Surname,
[Address List].[DEARFirstnames] AS FirstName,
[Address List].[Mailing] AS Mailing,
[Address List].[Christmas Mailing] AS OtherMailing, [Address List].[Address 1] AS Address1,
[Address List].[Postcode] AS Postcode
FROM [Address List]
UNION
SELECT [Names].[LastName] AS Surname,
[Names].[FirstName] AS FirstName,
[Names].[MailingList] AS Mailing,
[Names].[Selected] AS OtherMailing,
[Address List].[Address 1] AS Address1,
[Address List].[Postcode] AS Postcode
FROM [Names],[Address List]
WHERE [Names].[AddressListID]=[Address List].[AddressListID]
ORDER BY Surname, FirstName;
View 3 Replies
View Related
Nov 8, 2013
what is wrong with the following statements. I'm new to access SQl. This would work in ms sql.
Code:
DELETE from Table1
INSERT INTO Table1(email, productid, datecreated, datesend) values ('adf', 5, '10/10/2012','10/10/2012')
Select * from Table1
View 1 Replies
View Related
Nov 8, 2004
Hi, I was wondering why the following code would give me an invalid SQL statement message:
Dim Rs As New ADODB.Recordset
Rs.Open "Manzanero # 450", CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
The error message is:
"Invalid SQL Statement; expected 'Delete', 'Insert', 'Procedure', 'Select', or 'Update"
I'm just trying to open up the table "Manzanero # 450" so that I might add to its contents. I have Microsoft Active X Data Objects 2.6 library included as well. I find it strange since this is basically a line for line copy of a sample I found in a MS Access book. Please help. Thanks! =)
G
View 2 Replies
View Related
Jun 18, 2013
I have a sub form with staff records on it within a main form. I am trying to allow the user to select a record from the sub form and add it to a table, here is my code which, to me, looks correct. However it gives me an error saying "Syntax error in INSERT INTO"
Code:
Private Sub Command3_Click()
Dim dbs As Database
Dim sqlstr As String
Set dbs = CurrentDb
Forename = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_forename, "")
Surname = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_surname, "")
[Code] ....
View 1 Replies
View Related
Jan 18, 2007
Hi
I am creating an employee db, with fields including employeeID, surname, firstname, partnerSurname, partnerFirstname, partnerID. As some partners work at the company, I want the partnerID to list (thorugh a value box) employeeID's that match surname to a partnerSurname and firstname to partnerFirstname.
Is this possible? Any help greatly appreciated.
Thanks in advance.
View 1 Replies
View Related
Nov 22, 2006
Hi, this is my first post in this forum... so thanks all for your answers...
Ok, the problem is this...
I've got a database with some tables, but in that database i've got 3 important tables....
Lets refer that the tables have this names [Table1,Table2,Table3];
In the Table 1, there are 3 important variables, one of them its a primary Key, ok all i wanna know how to do... its this:
When i insert a new regist in this table, the first 3 values from the first three colunns must be replicated to table2 and table3.
Can you help me PLEASE!! :(
View 10 Replies
View Related
Apr 9, 2007
Hi All
I have a list box. Below it lies a text box.
I'd like to click on multiple values in the list box which then populates a text box with each value to form a sentence.
currently my code looks like this
Dim Comment As String
Comment = Me.lstComments.Column(0)
Me.txtComment = Comment
Using the above method only inserts one value. How can I modify this to insert multiple values into the text box?
Much appreciated
View 4 Replies
View Related
Oct 19, 2012
I have a table which holds the status of peoples orders. It holds information on order_id, order_status, order_desc, letter.
A letter is printed for each of the different statuses. However for one status i need two different letters to be printed.
How can i insert two values into the same cell.
So far i have done
insert into order_status (order_id,order_status,
order_desc, letter) values('025', 'Dispatched', 'Black tailored coat ','DPT notification');
This prints the DPT notification fine but i need another letter to print along with this.
View 11 Replies
View Related
Jun 7, 2013
I'm now trying to speed up data entry within my database and have hit a brick wall with one part.Basically, this is within a form (for 'clauses') of which there is a one-to-one relationship with 'the Applicability' table (a series of fields with 'Yes/No' values for each and a related key field) - i.e. for each clause there are a series of circumstances when it will apply. I've set this up to create a record when one doesn't already exist using the default values (i.e. all applicable).
The method to speed up data entry is to have a pop-up form with unbound fields to list common Clause fields - including 'Applicability' as a subform. When entering a series of Clauses you tend to find they have the same 'Applicability' as their neighbour, so I would like to create a corresponding record in the Applicability table with the values set on the 'ClauseQuickAdd' form (Technically the 'ApplicabilityQuickAdd' sub form).I've edited an existing code within my database to provide two global variables (strAppFieldList and strSubAppFieldList) to put into the following statement:
Code:
DoCmd.RunSQL "INSERT INTO Applicability ( AppRelClause, "" & strAppFieldList & "") VALUES ("" & Me.Clause_ID & ", " & strSubAppFieldList & "")"
Bringing up Error 2498 "An expression you entered is the wrong data type for one of the arguments". So I presume that I haven't quite got the format right for transferring yes/no values (strSubAppFieldList). See below for an extract of strSubAppFieldList:
Code:
Forms('ClauseQuickAdd')![ApplicabilityQuickAdd]![Manufacturer], Forms('ClauseQuickAdd')![ApplicabilityQuickAdd]![Supplier], ...
View 6 Replies
View Related
Mar 17, 2014
i have a subform which contains a lot of calculated values. fields which calculate values are located both in master form and subform. i use those calculated values in various reports where i have to insert formulas again. so i have decided to insert calculated values in table directly. i can use update query but i am unsure how to trigger it; and how to keep fields updated everytime a value is changed.
main form fields = [gsm] , [rate]
sub form fields = [pages], [qty], [gsm]*[rate]*[pages]*[qty]
View 1 Replies
View Related
Jun 2, 2014
I have a button on a subform that becomes visible if there is no records in the source of the subform. When clicked I want to run a query that will insert a record on to the source of the subform. There is one field in the query that I need to get from the parent form.The first part works OK - the button is visible when the source file to the subform for this main form record, is empty.
If I run the query against the source file it inserts the new record after it has asked for the value of the variable field.My problem is that when I try to run the query when the button is clicked It can't find form![ClientFileFrm]![ClientId]..This is my code on the subform
Private Sub Form_Load()
If Me.RecordSource <> "" Then
If Me.Recordset.RecordCount = 0 Then
Me.AddSettingsButton.Visible = True
[code]...
The ClientFileFrm is the main form.I can't seem to reference the clientId variable back to the main form.
View 1 Replies
View Related
Mar 11, 2015
I am working on a timesheet application which allows users to insert multiple timesheet entries in a grid style format. the first row is visible and to add another row users click on a command button which makes the next row of fields available and ready to fill in and so on and so forth. I need the fields to be unbound and then when the user clicks on a save button for example then it inserts those values into the relevant table. Table name is tbltimesheet, field names are id, companyname, project, activitydate, activityhours, activitynotes, username, userid
View 2 Replies
View Related
Sep 22, 2006
Hello guys,
Does anybode has any idea, of how to do that ? I can do it very easy in VB.NET, or C#, but in Access I give up.
So, I have to take data from the form , and send it to Sub.
With data type of String , I have no any problem, use Nz funciton, end everything, goes well.
But with Date and Integer, I can not find solution.
So :
Date
Dim PensionerFromDate as Date
PensionerFromDate = IIf(IsNull(Me.txtPDPensionerFromDate) = True, ????, Me.txtPDPensionerFromDate)
(instead of ???? i tried everything .. dbNull, vbEmpty, vbNull,sqldatenull, and somtimes it works but int the table stores "12/301899")
Integer
Dim CompanyID as integer
CompanyID = IIf(Me.cboCompany.Column(0) = 0, ????? , Me.cboCompany.Column(0))
I tried here instead of ????, tu insert "", ",," , " " , Cint("") itd. itd. . but nothing works.
Has anybody any idea ?
100 x thanks in advance
View 7 Replies
View Related
Jan 27, 2015
I'm trying to write some Code with the target to take a value from a textbox (out of a form) and a pregiven value (in this case = 2) and enter them in a new record in a table. But only if there is not already a record with the exact same combination of these to values. When done, the same button should proceed you to the next form (but this code is not already implemented in the fragment below) even if no values are added because they already exist in this combination.
Code:
Public Function GetID_PatientStudiesGroup&()
GetID_PatientStudiesGroup = [Forms]![frm_PatientStudiesGroupTZP]![ID_PatientStudiesGroup]
End Function
Private Sub BPRS_T1_Button_Click()
Call GetID_PatientStudiesGroup&
Dim strSQL As String
[Code] ....
As you can see, I tried to use a function to refer to the value out of the form, because I didn't find out how to refer to a form in an SQL Code.
Just to give you a better overview: The table where the information should be added is named "tbl_PatientStudiesGroupTZP". The form where you find the Textbox "ID_PatientStudiesGroup" (with the value I need to transport) is named "frm_PatientStudiesGroupTZP". Every part (except the WHERE NOT EXISTS part) worked perfectly for itself, but not when thrown together.
View 10 Replies
View Related
Nov 19, 2012
i am trying to insert multiple values that i have selected in my listbox to my database access table when i click the "add record button" but the values does not appear in my database table.
i have 2 listbox, when i select the first list box(businessNature) it will display the records in the 2nd list box(lstCuisine). However, the records in the the lstCuisine list box is not entered into the table in my database.
(ps: in my property sheet for my lstCuisine listbox its multi select is simple)
Here is my codes:
Private Sub Add_Record_Click()
If IsNull(Name) = True Or IsNull(Mobile) = True Or IsNull(Email) = True Or IsNull(CompanyName) = True Or IsNull(BusinessNature) = True Then
MsgBox "Please fill in Business Nature, Name, Contact, Email and Company Name"
Else
DoCmd.GoToRecord , , acNewRec
End If
Dim conceptValue As String
[Code]...
View 10 Replies
View Related
Nov 28, 2007
Hello!
I've good a very very strange problem...
I do an insert of a new row into the table TRA. That insert creates an id for the new row.
Right after that I do a select of the new row, trying to get the new id created.
Well, the insert is working fine, but the select sometimes gives me values, sometimes not.
How is it possible?? I've tryied closing the database and opening it again, but is not working anyway...
Any idea??
My code right now is the following: (closing the database, setting recordset to nothing... and still not working properly)
sql = "insert into tra (semana, empleadoid) values('" & semana & "'," & empleadoId & "); "
DoCmd.RunSQL (sql)
db.Close
Set db = OpenDatabase(database)
sql = ""
Set rcset = Nothing
sql = "select * from TRA where (semana='" & semana & "' and empleadoId =" & empleadoId & "; "
View 10 Replies
View Related
