I exported about 190,000 records (about half of all records in the table) from Access DB to Excel. I then updated two fields (that were previously blank on the database) on the Excel sheet with values. What is the easiest way to import these new values back into the database table so that the records receive their respective values?
View RepliesMy form contains the field Bill_Date, which may or may not be input during data entry. What I need is to run a query (I think) that allows me to input a specific date that will automatically populate all Bill_Date fields that are currently null with the date specified. Eventually this will end up on the front end where a biller will click on a link when they open the db, input the date into the field, and then process another report.
I have absolutely no idea how to even begin and was not able to locate any specific information in Help or here on the forum.
Many thanks.
I'm trying to update the account number via an input box with a do loop but I don't have the code right. The at the input box, the user will type in a 4-8 digit number and it will update all the account number fields in a table that are null.
View 1 Replies View RelatedHi,
I'm new to this. I'm trying to enter data (it's actually Latitude and Longitude co-ordinates) from an existing Excel source into an Access database which has input masks of 00°00'00.00"L;0;0 (Latitude) and 000°00'00.00"L;0;0 (Longitude) in the respective fields. However I cannot get the information to import or display correctly. I did an "export data" of the respective table (hence fields) to Excel to try and get the correct entry format. An example of the Lat exported was 24°49'41.81"N and Long was 067°01'44.02"E (but with a very small ' in front but only visible in the data entry line in Excel, not in the actual spreadsheet table???)
However when I try to enter the data (even using the exact same little degree symbol, apostrophe, and quotation marks) it does not enter the access fields correctly. On closer scrutiny of the exported Excel format I note a small ' at the very beginning of the 24°49'41.81"N or 067°01'44.02"E string. But as I said previously only visible in the data entry line next to the formula button. Not on the spreadsheet cell.
However even when I "Paste Special" "values only" my new co-ordinates into the same entry location as one exported, it will still not import, or display correctly. If I go into the Access database directly there is a form where if I need to enter the new co-ordinates (using lat example above) I only have to enter 24 49 41 81 N (spaces between) and it will show correctly as 24°49'41.81"N
I'm getting desparate as I don't want to have to change all the details manually. Anyone know what my correct format from an Excel spreadsheet should be?
Apologies for lengthy story! Difficult to describe problem with degree symbols etc
Is there an easy way to get user input like the parameter value box in an update query, where you want the user to specify the table name and field name to run the query on?
View 1 Replies View RelatedFrom Excel 2007 to Access 2007 I want to keep my Access database sync with my Excel SpreadSheet. I not to often change Excel but when I do Access will be updated.
Error #: -2147217887
"Field cannot be updated."
My connection string works fine and recordset is up and running!In my Excel file:
Code:
rs.MoveFirst
For k = 1 To Sheets("2013").Cells(Rows.Count, "A").End(xlUp).Row - 4
If rs!Index <> k Or rs!total <> Sheets("2013").Cells(k + 4, 5).Value Then
rs!Index = Sheets("2013").Cells(k + 4, 1).Value
rs!DatePaid = Sheets("2013").Cells(k + 4, 2).Value
rs!WhatPaid = Sheets("2013").Cells(k + 4, 3).Value
[code]...
Is it possible in Access to use an input value (text) as a field name? I want to access a field based on what the user enters.
View 6 Replies View RelatedI would like to use the "Collect and update via email" function within MS Access 2010 which sends out and collects emails. I know how to use this function, but what I would like to do is something slightly different. Instead of emailing out a data entry "form" that has the employee/team member enter information for data collection purposes about their project, status, start date, end date, priority, tasks, etc. Instead, I would like send an Excel/Access table with all of the current project information displayed in the email data collection form and then have the employee reply to the email and overtype edits to displayed information and then have those changes automatically entered into my table. For example, one project table can have up to 50 rows and then column headers such as project status, start date, end date, priority, task description, etc.
View 1 Replies View RelatedI need an input mask for a general date field. When I add the date "11/01/2012 10:00:00" it works fine.
When I add an input mask of 00/00/0000 00:00 it then doesn't work.
I have an Access crosstab query that I have exported to an Excel Spreadsheet. I have the spreadsheet formatted using conditional formatting and I'd rather not have to reset it every morning. It's a single spreadsheet (the columns/rows will not deviate greatly day to day) and should be very simple, but I'm not getting it for some reason.
So if I have "Test.accdb" and it contains "qryX" as my crosstab and "Sheet1.xls" is my formatted Excel spreadsheet, how do I code for the latest "QryX" to go in and replace the old "QryX" data in "Sheet1.xls" ?
I have a main form and a subform.
Both forms have the field called JobID in common.
Both forms have a field called JobStatus.
Any easiest solution so that After I Update the field called JobStatus in the subform, it changes the field called JobStatus in the main form to the value which was selected from the subform?.
I'm relatively new to Access. I've created a database where we track product information and testing done on the products. I have the Product Code set up as a combo box with five other fields auto populating based on what is entered into the Product Code field. However, when I export the table or form to Excel, the Product Code changes to another number, which I think is an auto number but does not directly relate to the product entered (either one up or down from the product).
View 2 Replies View RelatedI have an excel sheet, sent to me by someone else. It contains a column of unique information (site IDs), followed by three columns which each contain either a unique value or a blank, something like this:
Code:
sID|Code1|Code2|Code3
011|1234A|1234B|
021| |2345B|3456C
031|3456A|3456D|
041| | |7890E
The ID represents a particular business with which we interact, whilst the codes represent particular types of contract that we have with them.
This is then followed by a lot of columns containing information about each entry, name, address, post code, contract data etc.
I need to relate this to the employees at those businesses, whose data I have recorded in a separate spreadsheet, so that addresses and such are also related to people, not just the business unit.
This spreadsheet records each person by employee ID (a unique value for each entry) and Code, without taking account of whether it is a code of type 1, 2 or 3. Like this:
Code:
eID|CodeX|FirsN|SecoN
011|1234A|Teddy|Bears
021|1234B|Harry|Horse
031|3456A|Edwin|Eagle
041|3456A|David|Tyrex
I figure the best way to create a relationship between these tables once imported into access is via a third table containing a column with all the Codes in a single field and a second field with the sID related to each code.
Code:
sID|CodeX|
011|1234A|
011|1234B|
031|3456A|
041|7890E|
My question is, how do I get all the Codes from the three different fields into a single field and relate it to the relevant sID for each?
Is it possible to have a value in an excel 2010 cell be used to update an access 2010 db?
For example, if in a spreadsheet "test" cell A2=3 then in a db "test" a column "value" is 3. However, if the value in the spreadsheet changes so does the db.
Hello,
I need to create a field in an input form that is simply the concatenation of two other text fields. I have tried all sorts of things, but when I look at the data in the table that field.
I have a field called ID that I want to be created like this:
=Format([UniqueID],"00000") & "-" & [Mosque]
This works well in my output fields, but does not work the same way on the input form. It needs to be based on the currently input values from the current record. Anyone have any ideas?
Thanks in advance,
--Robert
I need to update a field with a Sum() on event.
View 12 Replies View RelatedI am the new for the vb little bit know the access, while exporting excel from access query, i require save field name as file name.
View 2 Replies View RelatedI actually have an ms access database with the following format(assuming)
title1title2title3title4title5title6
1a1b1c1d1e1f
2b2b2c2d2e2f
3a3b3c3d3e3f
4a4b4c4d4e4f
for each row in the ms access database above i need to retrieve an excel in the format below
title11a
title21b
title31c
title41d
title51e
title61f
title12a
title22b
title32c
title42d
title52e
title62f
and so on....
Where I can proceed, what tool can I use??
I have a simple MyAddress Project. It consists of one main table, a couple of queries, some forms and reports. Most fields in the table are typical text fields. The project allows me to select records from the table and print labels for those selected records. One field is a "yes/no" field. If the field value is true, the label prints; otherwise it does not. I have a form which allows me to scan the list of records and mark those I wish to print. So far so good. I am trying to add two buttons to the form to enable me to 1. Clear All and 2. Check All. I am trying to use an UPDATE statement to activate when I click the appropriate button. However, when I execute, I get the error message "Compile error: Sub or Function not defined".
The name of the table is tblMyAddresses.The firld I am trying to UPDATE is PrtLbl.The update value will be true or false depending on the button clicked. Since I want to change the values in all records, there is no WHERE required. The code I am trying is as follows:
Option Compare Database
Public Sub CheckAll_Click()
Update tblmyaddresses
Set PrtLbl = False
End Sub
After testing it, the database got corrupted. I had backed it up just prior to using this.
It was something like = Sum(Abs[AmountPaid], [Paid] = "X")
I have a continuous subform with an "AmountPaid" column. The total is displayed in the subform footer. I need it to display the total for only the fields with an X in them denoting that they were paid. This total should match the statement we receive.
After clicking the button to put the X in the Paid field, then I used the formula to update the AmountPaid field.
Joe..........10..... X
Al..............5.....X
Flo.......... 25
.....Total = 15
I have 2 tables like this
TableA
FromDate
ToDate
BatchType
TotalBatches
TableB
Date
BatchType
NumBatches
Is their a way to update the TotalBatches in TableA with the sum of NumBatches from tableB that have the same BatchType and falls between the FromDate and ToDate of TableA?
Below is a sample of the table with the data. I manually added the 1 and 0 to the hc_Year field. However, I would like to create an Update query that will add a 1 to the hc_Year if its the first instance of PIDM & regsYear and add a 0 to the records that are not the first instance.
PIDM | regsYear | hc_Year
52 | 2009 | 1
52 | 2010 | 1
201 | 2007 | 1
201 | 2007 | 0
201 | 2007 | 0
201 | 2008 | 1
I have two tables, one called 'Company' and one called 'Person'. Both tables have several fields, but they both have the same primary key, i.e. 'Naam'.
When I type in a name in the 'Person' table, I'd like the 'Company' table to automatically display the name too. So for example if I type in 'John Doe' in the person table, I want to be able to switch to the 'Company' table and have the same name displayed there, automatically.
What query would I use to build a date in m/d/yyyy format using month, day and year values from another field?
View 2 Replies View RelatedI am new to Access. I am after a vba script to run an update query.
I have a table named MYOB_Invent.
I have a Query Named MYOB_Update.
The query is showing records where my field named Test1 is null.
I want to update records with null in the field to today's date (the date of the day that it is run) formatted as 18/08/2014 and the field next to it is named Updated (which is yes/no) from un-ticked to ticked for the updated values.
I would like a vba script to automate the process as I want to automate it via a commandbutton for users.
Please can someone tell me how to go about adding to the code below. Currently the user enters the date criteria in a form. This works great and the data is exported to excel. But I can't seem to figure out how to get the input from the user to also be exported to Cell A1 in excel. Example: if the user enters starting date and end date, that information should be placed in the A1field in excel. Thanks for your help.
Public Function ExportDataExcel()
Dim strFilePath As String
Dim strFileName As String
Dim strFileTemplate As String
Dim strMacroName As String
If (MsgBox("You are about to generate the LAR Monthly Report. Are you sure you wish to continue? You cannot cancel this procedure once started.", vbOKCancel) = vbCancel) Then
Exit Function
End If
'''''''''''''UPDATE THIS DATA WITH YOURS''''''''''''''''''''''''''''''
'Fill in the following with your files and path
strFilePath = "R:Call CenterCall Center DepartmentsMortgage DeptMortgage Statistics & Tracking"
strFileName = "Output.xls"
strFileTemplate = "Template.xls"
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''
'This deletes the old file
Kill strFilePath & strFileName
'This recreates your file with the template
FileCopy strFilePath & strFileTemplate, strFilePath & strFileName
openexcel strFilePath & strFileName
ExportData "qryHoeqDotApproved", "HOEQ DOT APPROVED"
ExportData "qryHoeqDotReceived", "HOEQ DOT RECEIVED"
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''
xl.ActiveWorkbook.Save
'The Application.Run will run the Macro(s) that you saved in your spreadsheet
xl.Application.Run "'" & strFileName & "'!" & strMacroName
xl.ActiveWorkbook.Save
'Uncomment/Comment these to close out the workbook
xl.ActiveWorkbook.Close
xl.Quit
DoCmd.Close acForm, "frmLar"
Set xl = Nothing
End Function
Private Function ExportData(strQuery As String, strSheet As String)
Dim intR As Integer
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim qd As DAO.QueryDef
Application.SetOption "Show Status Bar", True
vStatusBar = SysCmd(acSysCmdSetStatus, "Formatting export file... please wait.")
'After you open that Object/Workbook, you refer to that workbook now as 'xl'. You will
'use it later, but now you have to access your queries through this code and to do so
'you need to use a recordset.
'strQuery is the name of the Query that you passed with the Function. You can also
'use an SQL string.
Set dbs = CurrentDb
'QueryDefs (0)
'QueryDefs ("name")
'QueryDefs![name]
Set qd = dbs.QueryDefs("" & strQuery & "")
qd.Parameters![txtStartDate] = [Forms]![frmLar]![txtStartDate]
qd.Parameters![txtEndDate] = [Forms]![frmLar]![txtEndDate]
Set rs = qd.OpenRecordset
'Set rs = CurrentDb.OpenRecordset(strQuery)
rs.MoveLast 'moves to the last record
rs.MoveFirst 'moves back to the first record
'You can use record count to make sure there are records in your Query/Recordset
If rs.RecordCount < 1 Then
'There are no records
MsgBox "There are no records for " & strQuery
Else
'There are 1 or more records. Now Select the sheet that you will be exporting to
xl.Sheets(strSheet).Select
'Now you need to loop through the records. 'intR' was dimmed at beginning of this
'function and will now use it to create a loop or 'For, Next'
'Starts with record 1 and gets the count of records in the recordset so it knows where
'to stop.
For intR = 1 To rs.RecordCount
'Now we need to export the recordset/query to the workbook/object we opened earlier.
'Remember 'rs' refers to the recordset & 'xl' refers to the workbook
'xl.cells(ROW,COLUMN).VALUE = rs.fields(INDEX).
'This is how you will fill in the value of a cell on the workbook. For the ROW you
'will want to add + 1 if you have Headings on your sheet. The INDEX for rs.fields
'refers to the columns of the recordset/query. The first column of the recordset
'starts with the index of zero.
xl.Cells(intR + 3, 1).Value = rs.Fields(0)
xl.Cells(intR + 3, 2).Value = rs.Fields(1)
xl.Cells(intR + 3, 3).Value = rs.Fields(2)
xl.Cells(intR + 3, 4).Value = rs.Fields(3)
'Moves to the next record
rs.MoveNext
Next intR 'Loops back to For and enters data for the next row
'Once the export is done, this just puts the cursor to A1 on each sheet
xl.range("A1").Select
'Clears the recordset
rs.Close
Set rs = Nothing
vStatusBar = SysCmd(acSysCmdClearStatus)
End If
End Function