AutoNumber Field And SQL String INSERT INTO Statement
Apr 4, 2008
In my statment below the ID is an autonumber. I placed it in the first part ofthe SQL string..but not where I have to variables referenced...
I have to add it to the SQL statement...Its not a varible although I can make it one by getting the largest number nad adding one...
Do I have to make it a non-autonumber and do it like I said above
Or is there another way to treat the autonumber (ID) in the SQL INSERT INTO statement...
So, I have a table with an autonumbered key field. I started the autonumbering at 1000 thinking that I may want to manually backfill some old records at a later date. Now I’m stuck.
Is there a way I can insert a new record into this table, filling this field as “950” for example?
I have a 3rd party database where I have no control over how the data is entered. I've been given the task of creating a Crystal report that would need to gather data from two databases. The link between the two databases in my report would be on a quote number. In one database, the quote number is in it's own field. In the 3rd party database, the quote number is stored in a memo field along with other data. Here's an example:
The data I'm after is C7875 T7761 T6895 T7152A *No data from the 5th row since the data after the "T" isn't numerical
So far I'm thinking of using an IIF statement to check for the existance of a C or T, then if true, use a nested IIF statement within the first to check for numeric, if true, use the Mid function to pull out the quote number.
My first concern is this could become a complicated IIF statement and was wondering if there was another direction I should be looking in acheiving this.
My second concern is if I go with this method, some of the quote numbers have a space after them, some of them have no space after them, some may even have a "/" after them. How would I go about accomplishing this?
I import a CSV field which has not preserved the CR/LF when it was exported from BCM Remedy. There is no setting for that. The memo field prints on my report like this:
A custom solution would be developed, that once implemented, could become the standard product in similar situation. Normally would assign to Network Engineering, but will work with Ray and the IPT Team to cost out the solution and get approval to proceed. 2012/05/24 10:44:28 AM PCOLLINS Sent to Ray Massie for review to determine if a solution needs to be proposed, or if they can wait for the National IPT solution to be ready in 2013.
I want to add VBA code that inserts a CR/LF in the memo field before all but the first occurance of a string that looks like a date, the first occurance doesn't need it. I will do it right after I import the CSV file into the table, so it happens only once, and it always prints and displays the CR/LF.
The memo field is called "NBS Update" and the table is called "CCRR Remedy Data"Here is what I have, but don't actually know what to put in to find the date and add a CR/LF:
Code: Dim db As DAO.Database
Code: Dim db As DAO.Recordset Dim srtSQL As String
I am developing a calender to display HOTEL room occupancy (past,present) and combine with future "outlook" dates and 1/0 values from active registrations that go beyond the present date.
I am working with MS ACCESS 2007. My problem is ONE SPECIFIC QUERY AND IIF STATEMENT. I want to concatenate some text (using &) along with numbers converted to text (using the CSTr function). I am using iif function and I want to use the full text string as a variable argument to be executed in the iif function. The result of the &concatenate is a text field like [p1] or [p2] or [p#] with numbers 1-31.
But, I do not want the final result as the argument. I want the query and iif to use the string expression written into the argument as the variable argument that can be calculated based on OTHER numbers that change everyday in the daily run of the calender.
The field in the statement [calc number] is a date conversion factor that changes everyday.
I want the iif statement to execute using the string as a variable argument. I am writing only within the QUERY to define the query object. I am not writing into any SQL module or code. My field definition and iif statement is below (calc number changes everyday)
I do not have greater-than key on this international keyboard !
When I use this in an update query, I get format conversion error. When I use the same definition in MAKE-TABLE query, it gives the resolved value "[p1]" for the value of [CalDate18] ......... that is not what I want.
I want the string to be taken literally and executed. Seems there may be a special character to precede the argument or WRAP the argument such as done in Excel. Example # "[p" & [calc number] & "]" #
I have two tables each with an ID field (autonumber/PK/No Dup etc).
I want to append two fields from one table to the other table. I have set up an Append Query to do this but it won't work - I get the following error - "The INSERT INTO statement contains the following unknown field: 'FiID'...."
I have two tables linked to each other in one to many relationship. Instead of auto number, the date and shift (Text) is being used as the primary keys (Composite Primary Key). Here is the tables structures,
The tables Payouts and Bills has one to many relationship. One payout row can have many bills. The problem is that I want to start the Autonumber in bills table everyday from 1. As date and shift are different for every day so even if i start bills from 1 everyday, it wont make same primary key. I can do it manually but I want to make it automatically.
Have searched the net with no luck. This is my problem.
I am trying to use a multiple INSERT string to create records in a table, from a VB application and using ADO to do it.
If I send the single INSERT it works fine, but if I try to send more than one in the same string I get a 'MISSING SEMI COLON AT END OF STATEMENT' error.
I have tried putting one at the end of each statement, then just at the end of the INSERT statements string, but still get this error.
Can Access actually handle more than one SQL instruction at a time?
Hey, I have a problem with my application......when the user trying to insert string with apostrophe into txtDesc (text box), the code returns error...
Run-time error '3057'
the database inserts any records excellently, but not with the apostrophe....
after many hours of reading access help, for my update command button, not sure what i did wrong...
when I on the update button, its give me blank space on the table where all my contacts are stored,
I trying to updated existing client contact info, without changing contact name, I have contact form with drop down menu, where I select clients name and view and clients information,
Dim ContactID As String Dim rst As DAO.Recordset ContactID = "SELECT * FROM Contacts " & " WHERE ContactName = '" _ & Me!cboContact.Value & "'" Set rst = CurrentDb.OpenRecordset(ContactID, dbOpenDynaset) If rst.EOF = True And rst.BOF = True Then Call MsgBox("The Contact Name you entered does not exist") Else Me!Telephone = rst!Telephone Me!TbxFax = rst!Fax Me!TbxComp = rst!Company Me!TbxFloor = rst!Floor Me!TbxStreet = rst!Street Me!TbxCityStateZip = rst!CityStateZip Me!TbxEmail = rst!Email Me!TbxAcctMgr = rst!Manager End If
Set rst = Nothing
End Sub Private Sub Close_Click() DoCmd.Close End Sub Private Sub Form_Load()
'Set Record Source for Forms as Contacts Table Forms!Contacts.RecordSource = "Contacts"
End Sub
Private Sub Update_Contact_Click() Dim QrySQL As String
I am trying to perform an INSERT into a linked Oracle Table, using the following code:
INSERT INTO JTCS_OWNER_VISITS SELECT * FROM Visits;
Seems simple enough, there are 10,000 records in the visits table, but when i try to run this query i get an error message that simply says 'OverFlow' but no error code. This query worked earlier when i only tried to insert 1,800 records, can anyone tell me if there is a limit to the number of records i can insert. I have linked the oracle tables using ODBC drivers (SQORA32.dll) and this has worked fine against all of the other tables except this one...
I've created a new table to hold information about female customers and it would be nice if I could glance through it in alphabetical order of their surnames.
INSERT INTO TBL_FEMALE_CUSTOMERS SELECT * FROM TBL_CUSTOMERS WHERE GENDER='F' ORDER BY SURNAME ;
In MS Access the resulting table data is NOT sorted by surname, but it works fine when I'm using my trusty SQL Server database. Can this be done in Access? ...and yes, I know I just need to use a query to sort the data, but I'm more interested to find out why it works in SQL Server and not Access. Cheers, Sam
Is it possible to have an insert and update in the same sql statement using MS Access 2002? I could split it into seperate statements but for code purity I would like to have it in one :cool:
hi guys i have a form that comes from a query with a parent and child table. Main table pk is pricingid and then the foreign key in tblHistory is pricingid.
I am trying to do an append table but i keep getting an error message Pricing ID is the pk autonumber in tblPricing (parent table) historyid is the pk in tblHistory and pricingid the fk(child table) oldprice should be a currency value the other fields are text.
this is what I have with all the syntax problems on the insert statement
Private Sub Save_Click() 'On Error GoTo Err_Save_Click
DoCmd.SetWarnings False
Dim EntryTime As String Dim lsupervisor As String Dim sSemester As String
Dim selectRequirementid1 As String Dim selectRequirementid2 As String Dim selectRequirementid3 As String Dim selectRequirementid4 As String Dim selectRequirementid5 As String Dim selectRequirementid6 As String Dim selectRequirementid7 As String Dim selectRequirementid8 As String Dim selectRequirementid9 As String Dim selectRequirementid10 As String Dim selectRequirementid11 As String Dim selectRequirementid12 As String
Dim sRequirement1 As String Dim sRequirement2 As String Dim sRequirement3 As String Dim sRequirement4 As String Dim sRequirement5 As String Dim sRequirement6 As String Dim sRequirement7 As String Dim sRequirement8 As String Dim sRequirement9 As String Dim sRequirement10 As String Dim sRequirement11 As String Dim sRequirement12 As String
I'm kind of new to web development but not so much programming. I went to school for 2 years for C and C++ programming, as well as some database stuff... anyways , I'm having some trouble doing a simple INSERT INTO statement...
What I'd like to do is to insert a new row using two session variables, first of all I'm not entirely sure if this works? but I've tried a bunch of different things but I just cant seem to get it to work...
Here is my insert statement:
sqlInsert = "INSERT INTO Events (Participant_Code,Date) VALUES ('" & code & "','" & date & "')"
Now I've tried both, where I have a variable that has Session("code") and another with Session("date") as well as just using Session("code") where the variable is in the insert.
Here is the whole code, I have a file called database.asp that has function that can be called to open, read, update or close a connection.
call connection("writeyourselffree2.mdb")
sqlInsert = "INSERT INTO Events (Participant_Code,Date) VALUES ('" & code & "','" & date & "')"
call rsUpdate(sql, conn)
call close(conn)
Heres the error that I have right now:
Error Type: ADODB.Recordset (0x800A0BB9) Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
Set mdb = DBEngine.OpenDatabase(StrDestDB) Debug.Print strSQL Call mdb.Execute(strSQL) mdb.Close Set mdb = Nothing DoEvents MsgBox ("The Geodatabase tables have been successfully appended!") End If Exit Sub
ErrorHandler: strTemp = Err.Description & " [Update_SystemTab]" Call MsgBox(strTemp, vbCritical, "Contact Help Desk") End Sub
What is the syntax error in this Insert Into statement ?
INSERT INTO RawData(RunID,fullName,name,category,type,subType, numberOfLines,virtual,date,namespace) SELECT 257 ,fullName,name,category,type,subType,numberOfLines ,virtual,#1/3/2007#,namespace FROM RawData WHERE namespace ='customer.demo' AND RunID =256
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.
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:
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:
I need my INSERT statement to DLookup tbl_module_repairs and insert the information from a field based on a WHERE condition of primary key matching on the form and table. Here is what I have but it will not work on the last value:
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.
