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)
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?
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...
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.
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.
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.
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
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
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
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.
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)
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 :)
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 & "];"
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.
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code: Between [StartDate:] And [EndDate:]
And
Code: Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
I'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"
I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =
Code : DateAdd(d,2,[IncidentDate])
so I removed the parenthesis and nothing happened
Code : DateAdd d,2,[IncidentDate]
I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either
Code : DateAdd d,2,[ContainDueDate]
I'm not sure if I'm even using the right function to get what I want.
Can someone tell me how to get year to date totals, month to date totals, week to dates in a query? I need to get all three for three different fields.
I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.
I am creating an online post, similar to a guestbook. When a user submits an entry, I am storing the date and time in a column titled "Timestamp", which is formatted 8/4/2005 9:16:58 ("General Date"). I am running the table through a query which is then posted on the web.
What I would like to do is this: I would like to use Timestamp to display the date that the entry was submitted, but not the time (basically, "Short Date"). Can I exclude the time using a query without changing the actual data stored in each record of the table? I understand that the time stored is completely different than the time displayed, even in the table. How can I do this?
I have a form with Date of Death (DOD) field. I would like update DOD from a table dbo_patient into Z_Patients table.
I have set the datatype as Date/Time in the form for Date of Death.
Code: Private Sub Update_DOD() Dim rcMain As New ADODB.Recordset, rcLocalDOD As New ADODB.Recordset Dim DOD As String rcMain.Open "select distinct PatientKey from Z_Patients", CurrentProject.Connection
[Code] ....
However I am getting some error Run-time error '-2147217913 Date type mismatch in criteria expression in section below.
Code: CurrentProject.Connection.Execute "update Z_MAIN_Processed_Patients set DateOfDeath = '" & rcLocalDOD!date_of_death & "' where PatientKey = " & !PatientKey
Using access 2010; i have a form that includes a date field. Is there a way to force the user to only choose a month end date? When the user clicks the date from the popup, they may use 9/1/2013 when the mgr. want them to use only 8/31/2013. I am thinking validation field to put a msg but want to be able to force it not the option.
I have a report that displays simple date fields. One of the fields is "lease execution". On the report, I want "lease execution" to display differently depending on the date the report was run.
So:
- If the "lease execution" date is more than 120 days away from today, I want it to display as Q YYYY. - If the "lease execution" date is between 120 and 90 days away from today, I want it to display as MM/YYYY - If the "lease execution" date is 90 days or less away, I want it to display the normal date MM/DD/YYYY
I was thinking I would need to do DateDiff() to figure out an amount of days that's between Now() and [Lease_Execution]. Then based on that amount make the report show it differently. Pseudocode would be: if DateDiff() = 40, then display [lease_execution] as MM/DD/YYYY
Table 1 has all the items I am trying to sell with sell by date after which I cannot sell this item. Then in Table 2 I have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.
So now somehow I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with sale by date 23/09.
So based on the attached example, I can see that on 16/09 I will consume only 5 cases from sell by date 16/09 and another sale is 18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.
Ideally I would like also to include the logic that if Item is out of date it would move to the next sell by date.
So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10
For Item 2 I can see that units with Sell by date 30/09 will be consumed on 25/09 and I will start taking stock from next sell by date which is 14/10.
I create a database through ms access and there have a birth date box and admission date. Another box for Age.I want to see the age in month or year figure in to the age box when I go next field. Which will be calculate from admission date to birth date.
