Help With Inset Statment
Jan 12, 2005
I have a select statement that returns information into a sting
Then I try to insert this value into a table but I am not sure of the correct syntax. The SQL select statement works but not the insert into...
sRequirement1 = category1.value & ">" & subcategory1.value & ">" & Me!Label1.Caption
selectRequirementid1 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement1] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "
DoCmd.RunSQL "INSERT INTO Hours ([RequirementID1]) VALUES( " & selectRequirementid1 & ");"
not sure how to treat selectRequirementid1 in Insert into statement
I tried the following yet non work...
'" & selectRequirementid1 & "'
'" + [selectRequirementid1] + "'
View Replies
ADVERTISEMENT
May 22, 2015
I am trying to add 30 or 45 days to a certain date and insert that date in a table. The field is declared as datetime on the SQLServer and has been tried with all date formats, and none, on the access side.The problem I am encountering is that the date is being inserted as 12:00:00 in the table, no date is being stored.When I print out the SQL statement to the immediate window, the date in question shows the correct date.
Code:
Dim a As Date
Dim b As Date
a = DateAdd("d", 45, Me.DateOfExit)
b = DateAdd("d", 30, Me.DateOfExit)
strSQL = "INSERT INTO MyTable ( No, Name,DueDate, OriginatedBy, ID, Status) " & vbCrLf & _
"VALUES (" & Me.No & ",'" & Me.Name & "', " & b & ", '" & Me.Facilitator & "', " & Me.ID & ", '" & "Required" & "');"
I have tried adding the date add code to the SQL string, but that produced the same result.
View 4 Replies
View Related
Sep 7, 2006
I have typed this SQL in a query; however, access doesn't seem to like it. Does anyone have any idea why please?....
View 13 Replies
View Related
Dec 28, 2006
I am having issues with a multiple option on my If Statement. I do not want to create and If statement for each if I can combine what I want it to do.
If [cboType] = "Class II with 16K / 16K" Or "Class III with 16K / 16K" Or "Class III with 32K / 16K" Or "Class III with 32K / 32K" Or "Class IV" Then
The code just die when it is combined. Is OR and option.
Thanks
View 3 Replies
View Related
Oct 15, 2004
Dim mysql
mysql = "insert into [OwnerOccupierDetails] (owner_no,owner_name,owner_address,owner_phone,own er_confirmation,owner_reps,owner_reps_planner,owne r_herd_no,owner_area_status,owner_date,owner_detai ls,occupier_no,occupier_name,occupier_address,occu pier_phone,occupier_confirmation,occupier_reps,occ upier_reps_planner,occupier_herd_no,occupier_area_ status,occupier_date,occupier_details,source_name, details) VALUES ('" & Me.[txtownerNo] & "','" & Me.[txtownerName] & "','" & Me.[txtownerAddress] & "','" & Me.[txtOwnerConfirmation] & "','" & Me.[txtOwnerReps] & "','" & Me.[txtownerRepsplanner] & "','" & Me.[txtOwnerHerdNo] & "','" & Me.[txtOwnerAreaStatus] & "','" & Me.[txtOwnerDate] & "','" & Me.[txtOwnerDetails] & "','" & Me.[txtoccupierNo] & "','" & Me.[txtoccupierName] & "','" & Me.[txtoccupierAddress] & "','" & Me.[txtOccupierPhone] & "','" & Me.[txtOccupierConfirmation] & "','" & Me.[txtOccupierReps] & "','" & Me.[txtOccupierRepsPlanner] & "','" & Me.[txtOccupierHerdNo] & "','"
& Me.[txtOccupierAreaStatus] & "','" & Me.[txtOccupierDate] & "','" & Me.[txtOccupierDetails] & "','" & Me.[txtSourceName] & "','" & Me.[txtDetails] & "');"
DoCmd.RunSQL mysql
im writing this really long query in vb so it needs to go on two lines, but whats the syntax for going on the next line(theres a quote or something but i cant remember for carrying on from one line to the next)
View 6 Replies
View Related
May 2, 2007
Hello again all... Today's problem is as follows.... I have an "after update" combo box that is coded as "CurrentDb.Execute "uno", dbFailOnError". I have an SQL Query named "uno" that is showing the following: "UPDATE [Input] SET [Input].Loc_Lng = [Loc-1L].Combo8, [Input].Loc_ID = "1", [Input].Loc_Desc = "xxxx";" I am trying to have the combo box update the values from the combo box into a blank table. When I try to run this, it comes up with the error: "Too few parameters. Expected 1". Any idea what that means, and then how to fix?
View 2 Replies
View Related
Aug 15, 2006
Hi there!
I've got the following code on my database:
strcriteria = "[date] = #txtDay1.value# "
Set rst = dbs.OpenRecordset("tblPlanner", dbOpenDynaset)
rst.FindFirst strcriteria
The problem is when I run the code it have the following error:
Run time error '3077'
"syntax error in date in expression"
I've formated boths date field and txtday1 on short date.
Is there anyone that could tell me what wrong am I doing?
Thanks
Jonybravo
View 3 Replies
View Related
Sep 29, 2004
I am very new to ASP
I would like to learn how we can insert a data from a web form into MS access file.
This is the situation
Well i have a db.mdb file located at c:/www/db.mdb
db.mdb contain only one table that is named as students which in turn contains only three columne i.s StudentsID , FirstName and LastName
StudentsID is autonumber field in db file and FirstName & LastName are text fields
Now i have a self.asf file where i have a form with only two input fields
i.e
<form action=self.asp method=post>
<input type=text name="first" value="">
<input type=text name="last" value="">
<input type=submit value=submit name=submit>
</form>
Please let me know how i can write a SQL insert statement and database connection so that i can insert the form data into the db.mdb file
With Regards
View 2 Replies
View Related
Nov 8, 2006
I was wondering if anyone knows how to insert more than one record at a time with a SQL statment? I have a form that asks the person how many records they would like to put into the system, this is a text box that they can enter a number.
My SQL for input one record is this:
[CODE]
SQL = "INSERT INTO tblTemp ([Name],[Number]) VALUES (TName,Len)"
DoCmd.RunSQL SQL
If the person wants to put in more than one I want the system to take the Number field and add 1 to it for ever record the person asks to be put into the system. the Name and Number fields will be that same except the Number field will increase by 1 for however many they choose to put in.
View 11 Replies
View Related
Nov 20, 2007
Hello guys and Girls
I am trying to setup a query that will return only the records in a date range that is user specified. In the Criteria of the date field I entered this
Between [Forms]![Report Switchboard]![txtStartTrend] And [Forms]![Report Switchboard]![txtEndTrend]
But the query does not work. Is the syntax correct? Both txtStartTrend and txtEndTrend are format Short Date.
Thanks for any Help
Anthony
View 1 Replies
View Related
Jun 13, 2005
hello all,
i have a pretty simple problem related to ACCESS query.
I have two tables having similiar columns ( i had to do this to compare the values given to me, because both the table data came from different sources)
The tables named are
OFFICERS
token
full_name
division
EMPLOYEE
token
full_name
division
Now the table EMPLOYEE contains more than 5000 records and the table OFFICERS contains around 2400 records which are already present in the EMPLOYEE table.
Now I want to build a query that will return me all the records in the EMPLOYEE table that are not present in the OFFICERS table. The criteria for comparing 'full_name'. so the query should return me 2600 records that are not present in the OFFICERS TABLE.
I tried doing this
Code:" SELECT DISTINCT EMPLOYEE.full_name,EMPLOYEE.token FROM EMPLOYEE,OFFICERS WHERE EMPLOYEE.full_name<> OFFICERS.full_name"
But this query returns me records that is not present in the OFFICERS table as well as some more 1000 records which are present in both tables.
please do help
thanks a lot
View 1 Replies
View Related
Jun 22, 2005
problem with this insert statment in vba
Hi guys i got vba code that suppose to write table name ,column name , rquired ,feild type and feild size
to an external db. The first part of this code write tables naem to external db and it is working well.
I get the following error when i press the button on my form :
Code:Run-time error '424':Object required
and when i click on debut it points to this part with yellow collor.
Code: metadb.Execute " Insert Into SysColumns(tablename,columnname,required,type,leng ht) " & _ " Values ('" & TableDef.Name & "','" & Feild.Name & "'," & Feild.Required & ",'" & FieldType(Feild.Type) & "'," & Feild.Size & ")"
I be happy if some one help me fix this erro.Thanks
my complete code
Code:Sub InsertSystemCatalogPopulation(db As Database, metadb As Database)'''now locating all the non system tables in current db and then writing it'''to systables tableFor Each tbl In db.TableDefs''' excluding the system tables If Left(tbl.Name, 4) <> "MSys" Then ''' writing the tables name to systables metadb.Execute " Insert Into SysTables(TableName) Values ('" & tbl.Name & "')" End If Next tbl MsgBox (" All tables names coped to systables system cataloge ") '''############################################### #################################### ''' now we go find all non syste feilds in the current db and then writing ity ''' to syscolumns For Each TableDef In CurrentDb.TableDefs'''this if statment remove the system feilds If Left(TableDef.Name, 4) <> "Msys" Then For Each Field In TableDef.Fields metadb.Execute " Insert Into SysColumns(tablename,columnname,required,type,leng ht) " & _ " Values ('" & TableDef.Name & "','" & Feild.Name & "'," & Feild.Required & ",'" & FieldType(Feild.Type) & "'," & Feild.Size & ")" Next Field End IfNext TableDefEnd SubFunction FieldType(intType As Integer) As String Select Case intType Case dbBoolean FieldType = "dbBoolean" Case dbByte FieldType = "dbByte" Case dbInteger FieldType = "dbInteger" Case dbLong FieldType = "dbLong" Case dbCurrency FieldType = "dbCurrency" Case dbSingle FieldType = "dbSingle" Case dbDouble FieldType = "dbDouble" Case dbDate FieldType = "dbDate" Case dbText FieldType = "dbText" Case dbLongBinary FieldType = "dbLongBinary" Case dbMemo FieldType = "dbMemo" Case dbGUID FieldType = "dbGUID" End SelectEnd Function
View 1 Replies
View Related
Apr 29, 2008
Here is my CASE WHEN statement in SQL. What would the If statement be in MS access?
CASE WHEN LEN(Serial) = 9 THEN Serial
WHEN LEN(CAST(Serial as Varchar(255))) = 8 THEN '0'+Serial
WHEN LEN(CAST(Serial as Varchar(255))) = 7 THEN '00'+Serial
WHEN LEN(CAST(Serial as Varchar(255))) = 6 THEN '000'+Serial
ELSE 'Error'
END
AS NewSerial
Thanks for your help!
View 5 Replies
View Related
Dec 9, 2006
Hey All,
Im using MS Visual Web Developer with an Access Database, i am trying to enter a new row of data into a database via a select statment attached to a button_click event. the code i am using looks something like this.
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.AccessDataSource2.SelectCommand = "INSERT INTO [Tblissues] ([IssueDate], [IssueTime], [PlantLocation], [MachineName]) VALUES ('12/9/06', '3:00 PM', 'Gville', 'Snack2')"
End Sub
that being said. when i click the button, NOTHING happens, no error and no insert!!!
I did test the connection to the Database and its fine, i can do an update and i can read the data that exists in the database i just cant insert a new row.
This is not the first time i have delt with INSERT statments but this has me at my whits end and any help would be great.
Thanks
View 1 Replies
View Related
Feb 1, 2005
"ALTER TABLE MOVIE ADD ( MULTIPLEX NUMBER(3))"
while i'm trying to execute this query ...
Its displayed as 'SYNTAX ERROR IN FIELD DEFINITION'
Pls help me.... :confused:
View 1 Replies
View Related
Jun 1, 2006
I've got an unbound form which has a field that I need to relate to from a query, I've got no problems relating to specific records, but I've not quite cracked the ability to select all other records if the field is left at 0.
I've tried using in the query under the 'table' field (for selecting a specific group of people seated at a particular table)
Iif([Forms]![TablePlannerFrm]![TableSel]=0,>0,[Forms]![TablePlannerFrm]![TableSel])
Which I thought would evaluate a true/false with the two results coming from the two options, so if the Table selected on the unbound form was set to 0 then it would set the criteria to >0 (everything table 1 and up), or if anything else was selected other than 0, it would use the number from the unbound form from the TableSel field.
I'm getting blank results from both positive and negative results. I assume that I'm not using the Iif statement correctly, or that there is another way of approaching the problem that my headache is blocking :)
Thanks everyone
View 1 Replies
View Related
Nov 21, 2006
Anyone knows if I can predefine decimal places in a query statement? For example, when I run the select query below, I want the MedicalPremAmt to have two digits after the "." It should look something like this: 12.38
Thank you.
SQL = "Select [Barg Unit], First([Medical Option]) As Carrier, First([Medical Coverage Tier]) As Tier, Sum([Medical Premium Amount]) As MedicalPremAmt,Sum([Total Grant]) As TotalGrant,Sum([Health Allocation]) As HealthAllocation,sum([Medicare Allocation]) As MedicareAllocation FROM RetireeCensus Group By [" & Category & "];"
View 1 Replies
View Related
Jan 22, 2008
Hello All,
I'm trying to build a recordset joining two tables together.
I can get the join to work, but I need to use an "or" statement to
fill my rs.values
Here my statement so far:
mocommand.CommandText = "Select JUNK.[MS], JUNK.[CNT]," & _
" LINK.[FROM], LINK.[TO] from JUNK, LINK " & _
" where JUNK.[CNT] = LINK.[FROM] or " & _
" where JUNK.[CNT] = LINK.[TO]"
I'm getting an error with the syntax.
Basically, I'm trying to either find one matching value or another. I need to find the first matching value (junk.cnt = link.from), or the second one where (junk.cnt = link.to) using an "OR" statement.
Can anyone guide me as per the correct syntax?
....CementCarver
View 2 Replies
View Related
