There is a standard company form currently in Word format (could use a different technology) with fields.
How do I extract the data into my Access DB from this fields?.
I am also open to advice of which technology would suit my requirements better with the requirements being:
Translate that form into Excel. Use "Collect Data" under the "External Data" tab on the ribbon to collect data from customers. To pull data onto your spreadsheet from Access do something like this. Use an Excel spreadsheet for the form. Then make a command button with the following event on click:
'"cmdbtn1" is the name you assign your command button.
Private Sub cmdbtn1_Click()
Dim appExcel As Excel.Application
Dim wbook As Excel.Workbook
Dim wsheet As Excel.Worksheet
Set appExcel = New Excel.Application
appExcel.Visible = True
Set wbook = appExcel.Workbooks.Open("C:\PathToYourDocument\YourSpreadsheet.xlsx")
'"YourSpreadsheet" is the name of the actual sheet in your workbook.
Set wsheet = wbook.Worksheets("YourSpreadsheet")
With wsheet
'The numbers next to ".Cells(" correspond to locations on your spreadsheet. A1=(1, 1), B1=(1,2), A2=(2, 1)
.Cells(10, 1).Value = txtThing1
.Cells(10, 2).Value = txtThing2
'This is how you combine multiple cell in excel into one Access field.
.Cells(10, 3).Value = txtThing3 + " " + txtThing4 + " " + txtThing5 + " " + Thing5
End With
End Sub
It seems to be Windows UAC reliant. As our application run in compatibility mode, UAC visualization is active and causing may some problems. The reason for this is, that the COBOL databse is a file based database, and the client where are coding for uses these files in ODBC DSN...
DAO might be a little faster, but not materially. Instead, use an IN clause in your SQL Statement so you only have to do it once. Sub test() Dim vaIds As Variant Dim sSql As String vaIds = Split("1 2 4 7 200 205 654", Space(1)) sSql = "SELECT [Sales]...
The ###### is shown in MS Excel when the data in a cell is too long for the column width.... the data inside the cell is still correct, as you can see if you select one of those cells and look at the value displayed in the cell content bar...
Redis would probably be fastest, especially if you don't need a durability guarantee - most of the game can be played out using Redis' in-memory datastore, which is probably gonna be faster than writing to any disk in the world. Perhaps periodically, you can write the "entire game" to disk....
If you are looking to find the records matching with both the criteria here is a way of doing it select `item_id` FROM `item_meta` where ( `meta_key` = 'category' and `meta_value` = 'Bungalow' ) or ( `meta_key` = 'location' AND `meta_value` = 'Lagos' ) group by `item_id` having count(*)=2 ...
Try the following which uses the SpecialCells() method to select only cells that are currently visible on screen (i.e. not filtered out). count = Application.WorksheetFunction.CountA(Range("A:A").SpecialCells(xlCellTypeVisible)) ...
The method you're looking for is QSqlQuery::lastInsertId(). To quote the documentation: Returns the object ID of the most recent inserted row if the database supports it. An invalid QVariant will be returned if the query did not insert any value or if the database does not report the id back....
The Cognos _days_between function works with dates, not with datetimes. Some databases, like Oracle, store all dates with a timestamp. On a query directly to the datasource, try using the database's functions to get this data instead. When possible, this is always preferable as it pushes work to the database,...
If you have two columns of the shirt numbers and the corresponding player names then vlookup() will do this, but a warning : are shirt numbers unique i.e. one player one number... With a list of numbers in D2 to D8 and corresponding names in E2 to E8, then =VLOOKUP(A2,D2:E8,2,0)...
If cell.Value2 = "FOUND THE CELL" Then cell.Offset(0, 1).Value2 = "changed the next right side cell" cell.Offset(0, 2).Value2 = "changed the second right side cell" End If ...
Well, Both the actions actually means update, where PUT is full update and PATCH is partial update. In case of PUT you already know the identifier of the resource and the resource already exists, so it is not a create and delete action per se. Infact, you can make do...
Why don't you just do it in one single query? Just replace the necessary table and column name, and variables/values/parameters to be bind in your query: $query = mysqli_query($conn, "SELECT first_name, last_name, description, role FROM `wp_usermeta` WHERE `first_name` = '$first_name' OR `last_name` = '$last_name' OR `description` = '$description' OR `role`...
$query = "INSERT INTO `myDatabaseForAll`.`users` (`id`, `firstname`, `lastname`, `username`, `password`) VALUES (NULL, $firstname, $lastname,$username,$password)"; you need single quote around text feilds in sql queries change above query to $query = "INSERT INTO `myDatabaseForAll`.`users` (`id`, `firstname`, `lastname`, `username`, `password`) VALUES (NULL, '$firstname', '$lastname','$username','$password')"; ...
This is what I mentioned in my comment Note: in future, you can using for loop to go through the column index. Option Explicit Dim WB1 As Workbook Dim ws1 As Worksheet Private Sub copylog3() Dim lRow As Long Dim NextRow As Long, a As Long Dim i As Integer...
Try this SELECT count(receiver_id) as total_receiver FROM gr_group_memberships INNER JOIN gr_group on gr_group_memberships.group_id = gr_group.id GROUP BY gr_group_memberships.receiver_id ORDER BY gr_group_memberships.receiver_id DESC I think it will worked what you want...
I think You'r missing some classes "UnsupportedFileFormatException" try to change the poi versions to the same and dont use the 3.11-beta2 You can use both in version 3.12 http://mvnrepository.com/artifact/org.apache.poi...
If data in your first table starts at A2, and your other column starts at D2, then use in E2 =VLOOKUP(D2,$A$2:$B$17,2,0) Copy down as needed....
User the formula =LEFT(TEXT(A1,"+0.000000000;-0.000000000"),12) where A1 contains your number. This will be a text cell type but will format the cell exactly how you want it....
mysql,sql,sql-server,database,stored-procedures
The only way to do this is to manually run a count(*) on all of your tables filtering on the particular date field. The reason for this is because one table might have a column "CreatedDate" that you need to check if it's >30 days old, while another might have...
You explained everything very well, and the images you uploaded helped What your code is doing seems to be correct, but the error is complaining about one of the parameters, and it could be the 2nd one: .BeginConnect ConnectedShape:=firstRect, ConnectionSite:=1 ConnectionSite: "A connection site on the shape specified by ConnectedShape....
A boolean would most likely not yield better performance than integers, since the Excel formula engine is dynamically typed. To significantly improve the performance of your spreadsheet, you should probably consider other options. Excel PowerPivot comes to mind, as it can easily handle millions of records with hundreds of calculations,...
You could of course always use Excel VBA, as you are already working with Excel. The following sub exports the used range of the current sheet as valid JSON into a .js-file: Option Explicit Sub jsonex() Dim fname, q$, str$, lineend$, na, va, ur As Range, i%, j%, ncols%, nrows%...
I got the solution after going through many tutorials and hence posting here for reference of any one who needs help. Sub testSort() Dim CurrentSheet As Worksheet Set CurrentSheet = ActiveSheet lastRows = CurrentSheet.Cells(Rows.Count, 1).End(xlUp).Row lastCols = CurrentSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set sortA = CurrentSheet.Range(Cells(2, 1), Cells(lastRows, lastCols)) CurrentSheet.Sort.SortFields.Clear CurrentSheet.Sort.SortFields.Add Key:=Range(Cells(2, 2),...
The logic here is: (1) Find the date for each subject that is the principal date, and return it for each row; and (2) subtract this date from the current date in col B. (2) is easy, but (1) requires a way to match the value in B on both...
Given your criteria -- that 322 is represented as 3 and 2045 is 20 -- how about dividing by 100 and then rounding towards 0 with trunc(). time_24hr <- c(1404, 322, 1945, 1005, 945) trunc(time_24hr / 100) ...
I can't see anything wrong with the code, as long as your text is in column C, and the values are in column H I've also taken the liberty of rewriting the code to make it clearer: Sub test() Dim x As Long Dim y As Long Dim TotalValue As...
change $username = "'rylshiel_order"; to $username = "rylshiel_order"; and you should be through. You are passing on an extra single quote here. ...
mysql,sql,database,stored-procedures
You forgot the () after the procedure name. Also, you should set a delimiter: DELIMITER // CREATE PROCEDURE sp_profit() BEGIN SET @v1:= (select sum( cost_price * current_stock) from product); SET @v2:= (select sum( selling_price * current_stock) from product); SELECT (@v2 - @v1); END; // ...
excel,vba,excel-vba,excel-2010
You have: Dim RangeNOut as Double Dim RangeNOut as Integer While the IF statements in there are a nice idea, VBA will not allow you to do that. It doesn't do conditional 'compilation' since it isn't a compiled language. When VBA runs your code, all your variables are declared (no...
It's because your moving forward through the rows - if you delete row 4 then row 5 becomes row 4 and the code will jump to the new row 5 - which is in fact row 6. Hope that made sense. :) The solution will be to use a For...
You have to find a suitable formula for entering in the target cell. Then you would build such formula with string concatenation, etc., for entering it via VBA. One option for the formula is to use OFFSET, as in =SUM(OFFSET($A$1,D3-1,COLUMN()-1):OFFSET($A$1,ROW()-3-1,COLUMN()-1)) This sums all values from Cell1 to Cell2, in the...
Use the LEFT() function, as shown below: lastRow = Range("A" & Rows.Count).End(xlUp).Row colNum = WorksheetFunction.Match("Number", Range("A1:CC1"), 0) For Each c In Range(Cells(2, colNum), Cells(lastRow, colNum)) If LEFT(c.Value,3) = "614" _ Or LEFT(c.Value,3) = "626" _ Or LEFT(c.Value,3) = "618" _ Or LEFT(c.Value,3) = "609" _ Or LEFT(c.Value,3) = "605" Then...
In order to filter for "any" column, you could combine a Find result and Filter like this: Sub DateFilter() Dim nRow As Range Dim toSearch As Range 'hide dialogs Application.ScreenUpdating = False 'filter for records that have June 11, 2012 in column 3 Set toSearch = Range("A1:C4") 'detect row that...
You can do it like this: SELECT IIF(DAY(@A) >= 25, DATEADD(d, 25 - DAY(@A), @A), DATEADD(d, 25, EOMONTH(@A, -2))) Here's a sample fiddle as well: sqlfiddle Note: EOMONTH requires SQL Sever 2012 or above - it returns the End-Of-Month date given a start date and a month offset....
sql,asp.net,asp.net-mvc,database,entity-framework-6
Ideally what you want is a many-to-many relationship between your Shop and Product entities: public class Shop { public int ShopId {get; set;} public virtual ICollection<ShopProduct> ShopProducts {get; set;} } public class Product { public int ProductId {get; set;} public string Name {get; set;} public virtual ICollection<ShopProduct> ShopProducts {get; set;}...
The answer is no, tables won't be locked, database won't be down. But, if your database is large and it takes long time to backup it, you can sometimes expect performance degradation(slow SQL queries from your application).
excel,vba,excel-vba,loops,doevents
How about changing your 'do until' loop to a 'for next' loop? Something like?... Sub rowinput() Dim lngInputStartRow As Long Dim lngInputEndRow As Long Dim row_number As Long lngInputStartRow = Range("A1").Value 'specify your input cell here lngInputEndRow = Range("A2").Value For row_number = lngInputStartRow To lngInputEndRow DoEvents Next row_number 'Then a...
In an object oriented language, would you make a class for every user? Or would you have an instance of a class for each user? Having one table per user is a really bad design. You can't search messages based on any field that isn't the username. With your current...
excel,vba,excel-vba,user-defined-functions
You need to use Application.Caller. This will return the value in cell A1 of the sheet the function is entered to: Public Function DisplayCaller() As String DisplayCaller = Application.Caller.Parent.Cells(1, 1) End Function This will return the name of the calling sheet: Public Function DisplayCaller() As String DisplayCaller = Application.Caller.Parent.Name End...
Try getting all the columns out first, then add it to the sql query, no need to loop the database query. $mark = $_POST['mark']; if (isset($_POST['mark']) && is_array($_POST['mark'])) { echo "<table border='1'>"; echo "<tr>"; for ($i = 0; $i < count($mark); $i++) { echo "<th>" . $mark[$i] . "</th>"; }...
You want create relationship in two table Refer this link http://www.c-sharpcorner.com/Blogs/5608/create-a-relationship-between-two-dataset-tables.aspx...
This will do what you want, it polls through from the bottom up, if it encounters a number in C and it is > 1 then it will insert the number of rows equal to column C number - 1 then copy the data from the host row. This will...
In your calling code: ThinksCommerciallyInt = 1 should be ThinksCommerciallyInt := 1 similarly for the other parameters...
cell.row and cell.column should give you the addresses you need. Here is what I got from the documentation: https://openpyxl.readthedocs.org/en/latest/api/openpyxl.cell.html?highlight=.row#module-openpyxl.cell.cell It gives you the list of attributes of a cell object.
sql-server,excel,reporting-services,excel-formula,ssrs-2008-r2
Excel Formulas support as ended since SSRS 2008 (see Breaking Changes in SQL Server Reporting Services). No Formula Support in Excel In earlier versions of Reporting Services, there was limited support for translating expressions in RDL to Microsoft Excel formulas. In this release, when you export a report to Excel,...
One example of a working function would be: =CONCATENATE("INSERT INTO TABLE MyTable (Col1, Col2) VALUES ('", A2, "', ", IF(ISBLANK(B2), "NULL", CONCATENATE("'", B2, "'")), ")") This assumes that you are inserting two values from the same Excel row, adds quotes around values to allow you to insert text strings, and...
typically, you would store the password as some sort of encrypted hash. It is best if this is one-way, so it cannot be decrypted. When authenticating, you check that you can generate the same hash from the provided password; not decypt what is stored. Your hash should also be "salted"...
string,excel,if-statement,comparison
We need an Array formula. In G2 enter: =NOT(ISERROR(MATCH(1,--EXACT(F$2:F$7,E2),0))) and copy down. Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. Note: The curly brackets that appear in the Formula Bar should not be typed....
c#,database,generics,inheritance,abstract-class
The key here is to step back and think about the problem from another angle. You are duplicating lots of code because you are creating instances of the database and command classes within the method. So inject them instead: public class SomeDBClass { static DataTable exec_DT(DBConnection conn, DBCommand cmd) {...
In barely any case would you get direct access to another company's database, even if you are affiliated. The most common way would be an API (which might be public or licensed): https://en.wikipedia.org/wiki/Application_programming_interface If the data is publicly available on a website, some do content parsing although this is not...