Wrong Format While Importing
Jan 19, 2006
Hi,
i experience the following problem::confused:
When I import a xls sheet into an existing table in Access, which has all columns formatted as text, sometimes it goes wrong.
When in one of the xls sheets' columns there is no text for a number of rows, but just zeros (as text) then Access believes it is a number format and will not import any text in later rows. So Access checks according to the first few rows wether or not it is an interger or text. How can I make sure it only sees it as text?
Thanks in advance.
Abe
View Replies
ADVERTISEMENT
Nov 26, 2012
I have a table in my database which I want to update on a daily basis from an Excel workbook. Normally I would just use the TransferSpreadsheet command but in this case the format of the source sheet makes things a bit more complicated (It's not my workbook so I have no control over its layout, unfortunately)
My formatting issues are as follows :
-the header row is not on row 1, but row 3 (the first two rows are free format comments)
-there is a gap row between the header and the data region
-the source sheet is laid out with dates as the headers (left to right) and my field headers running down (top to bottom) - so would need some form of transposition
-Basically, looking for some direction on how I should approach this while maximising efficiency. One approach would be to have my own local Excel sheet with VBA code added, to open the source sheet, copy / edit / transpose as necessary and then import my local version and append to the existing table. But this adds an extra step to the process which I'd like to avoid if at all possible.
I would prefer to 'customise' the import within Access itself, to account for the above obstacles, so that I could just point to the source document directly but import according to my own 'non-standard' rules.
View 1 Replies
View Related
Jul 24, 2005
I am importing data that has been put onto Notepad into an Access table - this works fine except the date in notepad comes across as a date and a time. When I put the table into a query and ask for the date to be Between [Specify date 1] and [Specify date 2] - does the user have to then put 7/02/2005 00:00:00 AM and xxx. I have tried this but it doesn't return all values. I have also tried just the dates but this doesn't return all values either - any ideas?
View 6 Replies
View Related
Feb 6, 2013
I have a series of fairly large csv files which I need to consolidate by importing them to a single Access database. There are of the order of 50-60,000 'lines' per csv and about a dozen different files (all comma de-limited and text-qualified with inverted commas).The problem I have is, there are several date fields within the csv which are not formatted to Access' liking. Namely <Mmm_dd_yyyy_hh:ssAM> with underscores used here to represent spaces.
So for example, today's date appears thus :
Feb 6 2013 12:00AM (i.e. two spaces between 'Feb' and '6')
And this day next week would appear thus :
Feb 13 2013 12:00AM (i.e. one space between 'Feb' and '13')
I will obviously need this field recognised as Date/Time so I can query appropriately but I have no control over the source data and need to find a way of converting the format.One option would be to run a script on the csv's themselves prior to importing but I'm not sure how to do that.import this field as Text and then edit the contents after import (VBA?), such that the field can be redesignated with a data type of Date/Time.
View 3 Replies
View Related
Apr 5, 2012
I work at a hospital and I have been able to copy and paste data from a patient list into an Excel spreadsheet into a list I can customize to track patients I work with. I had a macro that would take the data copied and pasted and organize it in the way I needed it in a design that works for me.
I'm trying to convert this system to Access to give me more control over the data and to try and make the process of creating the list a little more automated. I also have an Access database for "tracking", which is required by hospital management so I was hoping I could use the patient list database to streamline the process of entering interactions into the tracking database.
I'm not sure how to get the copied and pasted patient list into an Access in a way that will let me manipulate it in Access to do what I want it to do without having to add several steps of a) pasting it into Excel, b) importing the spreadsheet into the Access database, and c) cleaning the data that ends up in the final Access table.
Is there a way to import data in a spreadsheet or table format directly from the clipboard, which would allow me to skip Excel altogether? I'm using Access and Excel 2010.
View 5 Replies
View Related
Feb 17, 2014
I wanted to assign the field "Number of magazine" with special format based on date/time format but showing only year and month in the format: "yyyy-mm".
So in property of this field in format I put yyyy-mm and in input mask I type 0000-00;;-
I also created the form based on the table containing above field and I defined format and input mask for corresponding formant in the same way like at the table.
But if I try to type date for example 2014-01 in text box of the form it comes up with the full date 2014-01-01. Why does it do like this? What do I do incorrectly?
View 2 Replies
View Related
Jul 30, 2013
I have a list of dates in the mm/dd/yyyy format and I am looking to get it into the fiscal format of yyyyww. I am able to do this with the datepart and format functions, but I need to make it so that the fiscal month begins in January but the first week starts if there are three or more days in the week. For instance if Jan 1st is a Friday then this stands as the first fiscal week, if it is a Saturday then it does not count as the first week.
datepart and format functions have the Use the first week in the year that has at least 4 days for the firstweekofyear option but I need it where it has at least three to make it work.
View 2 Replies
View Related
Jul 7, 2015
I want to format the text using format function. how i format the word apple to "apple" (With Quatation mark).
str = Format(Me.word, xxxx)
View 3 Replies
View Related
May 4, 2012
I am exporting data from access 2007 to Excel 2007 using VBA code. I have a whole number, which I want displayed as whole number. But after the export, the number is using the 1000's seperator in Excel. So for example if my original number in access table is 12000, it is showing up as 12,000 in the excel file.It has something to do with the NumberFormat property. I have tried doing this but doesn't work.
objSheet.Range("A1:A7").NumberFormat = "General"
View 2 Replies
View Related
Mar 2, 2012
We're using Access 2010 on Windows 7.We have a large database design where we send reports as e-mail attachments. We need to change the way the file is automatically created when sent. I goes to snapshot form every time we attach it to an e-mail. How can we get rid of the snapshot .snp file format? How can we change it to pdf or accbd?
View 3 Replies
View Related
Sep 21, 2005
Ok Guys,
Here goes...
all of a sudden now, when i open my form, its not displaying any of the previous data that i stored in it!! All the data is still in the table, and when i add data it goes into the table!! I checked properties and the record source and everything is ok there, what have i done wrong guys...HELP!!!!
View 3 Replies
View Related
Jun 9, 2006
I purchased a copy of Office 2003, on Ebay, for less than half the price it normally sells for. I created databases, which work well on my computer. However, when I copy them over to the office computer from my computer, via Flash memory stick, the program does not run. The message seems to indicate a problem with registration. When I installed Access, it asked me for a registration key, which I did provide. After that it worked-not activation process. Does this mean that you can only use the computer you created the database on to run it, or that I do not have a proper version of Access 2003? I cannot transfer, from my home computer, to the office computer. Is this Microsoft paranoia again?
Subsequent uninstalls and reinstalls of the program did not ask for the installation key.
What should happen? I would like to resolve this major problem and your help will be greatly appreciated. The program is flaky. Also, Switchboard does not work-must use Option Box.
These questions most user must know about initial installation and copying mdb files to other computers -please reply.
View 2 Replies
View Related
Oct 16, 2006
Hi,
Can anyone tell me what's wrong with this:
DoCmd.RunSQL "INSERT INTO tblPersoaneJuridice (Denumire,Localitate,CodUnicInregistrare,NumarInre gistrareRegComertului,Telefon,Fax) VALUES ('" & Forms!frmPrincipal!subfrmTipVersus!txtDenumire & "','" & Forms!frmPrincipal!subfrmTipVersus!txtLocalitate & "','" & Forms!frmPrincipal!subfrmTipVersus!txtCUI & "','" & Forms!frmPrincipal!subfrmTipVersus!txtNumarInregis trare & "','" & Forms!frmPrincipal!subfrmTipVersus!txtTelefon & "','" & Forms!frmPrincipal!subfrmTipVersus!txtFax & "')"
cui = DLookup("[CodUnicInregistrare]", "[tblPersoaneJuridice]", "[NumarInregistrareRegComertului]='" & Me!subfrmTipVersus!txtNumarInregistrare & "'")
i have a string variable denoted by cui and i need it to take value from table tblPersoaneJuridice from field CodUnicInregistrare. CodUnicInregistrare and NumarInregistrareRegComertului is text.
View 3 Replies
View Related
Jun 14, 2007
Have not coded for about 5 years and need a little refresher. Can't remember what I need to do. Thanks
Dim SelectedCompany As String
Dim rs As ADODB.Recordset
Dim Cn As ADODB.Connection
Dim sSql As String
Set rs = New ADODB.Recordset
Set Cn = New ADODB.Connection
SelectedCompany = Me!cboCompanyName.Text
sSql = "select Customername, AddressLine1, AddressLine2, AddressLine3, AddressLine4, AddressLine5, AddressLine6 from customers where "
sSql = sSql & "CompanyName = '" & SelectedCompany & "'"
rs.Open sSql, Cn, adOpenStatic, adLockReadOnly
this last line fails with error "No value given for one or more required parameters."
The code is executed when someone selects something in a combo box on a form in an access database.
Thanks
Pete
View 4 Replies
View Related
Oct 4, 2007
I think I'm just tired, and not seeing what to do here.
I have my main table.
MainID PK
bunch of other fields...
I also have a 'created by table'.
Createdby_ID PK
User ID
Main ID
Date_created.
I also have a user table, with a list of all the users.
User_ID PK
User_Name.
Is this the right way to setup these tables?
Only 1 person can create any paticular record.
I want it to be required tha whoever creates the record selects their name and dates it.
View 11 Replies
View Related
Dec 13, 2005
strSQL = ("SELECT onespace2(MeetingMinutes!MinutesID, '.' ) & " - " & [MinutesTitle] AS Item, MeetingMinutes.MeetingCode, Minutes.*, MeetingMinutes.MinutesCode, MeetingMinutes.MinutesID ")
strSQL = strSQL & ("FROM Minutes INNER JOIN MeetingMinutes ON Minutes.MinutesCode = MeetingMinutes.MinutesCode ")
strSQL = strSQL & "WHERE MeetingMinutes.MinutesCode= " & MinutesCode & " AND MeetingMinutes.MeetingCode= " & [Forms]![MinutesMFnew]![ComboMeetingNav] & ";"
cant get it running.
i got the same query running made in the querybuilder, but somethings change, so i need to adjus it in vba. But it doenswork, and get a type error
please advice
View 1 Replies
View Related
Jul 13, 2007
DoCmd.RunSQL "INSERT INTO tblAccessLog " _
& "(DateLoggedOut, TimeLoggedOut) VALUES " _
& "(#" & Date & "#,#" & Time & "#) WHERE " _
& "(LogID = '" & LogsID & "');"
Could anyone possibly tell me why this isn't working? Probably something stupid i've done bus still.
When i run it, I am getting the error message "Query input must contain at least one table or query" yet i've told it to use tblAccessLog so i'm just confused.
Thanks
View 8 Replies
View Related
Aug 14, 2007
StrSQL = "INSERT INTO LV (Directory_Name:,Filename:, Total_Files,Total_Records:,Total_Batches:, Total_YH) VALUES ('" & DirectoryName & "', '" & FileName & "', TotalFiles , TotalRecords , TotalBatches , TotalYH);"
Have tried loads of combinations of the " ' and ' " to get it working but just keeps giving me the damn syntax error!!! :mad:
What's wrong with it? Thanks
View 2 Replies
View Related
Oct 11, 2007
strSQL = "INSERT INTO TBL_History (Filename, Agent, SentRecieved, Accepted, Notes) VALUES ('" & [Forms]!FRM_ImportSubmission.submission_filename & "','" & [Forms]![FRM_ImportSubmission]![cboAgent] & "','Recieved','True','');"
CurrentProject.Connection.Execute strSQL
i've posted it as the sql string is built in the code
i get a 'catastrpohic error' when running it from vb like this and when i build put the string into a query and run it through the access user interface it worrks
View 14 Replies
View Related
Oct 23, 2007
I made a number of changes to a DB that was working fine (normalised further). So what's wrong? I have a number of forms (for my sample DB I have stripped down to just one, if I can resolve for this I can apply to others).
qryENTRY_COMPUTERS
ENTRY COMPUTER FORM
The user adds a new computer then selects the Computer Make (qryLOOKUP_COMPUTER_SUPPLIER) & Computer Model (qryLOOKUP_COMPUTER_MODELS).
This now returns error 'recordset is not updateable'.
The query on which the form is based is incorrect (qryENTRY_COMPUTERS). Previously I was able to add tblSupplier & tblSupplierProducts to the query then add field ProductDescription fom tblSupplierProducts to the query. This allows users to select a value from cboComputerModelID (ProductID), field ProductDescription then displays the related descriptive value for the ProductID selected (value remain on screen when user naviagtes between records.
I have attached a sample database (access 2002-2003 format).
I am sure the answer is staring me in the face but I have now spent hours trying to resolve!!
Help very much appreciated.
Phil.
Update: One difference I can see is that on the query 'qryENTRY_COMPUTERS' where i have added tblSupplierProducts & tblSupplier the join properties are in reverse i.e. Left Table Name = tblSupplierProducts Rigth Table Name = tblComputers Left Column Name = ProductID Right Column Name = ProductID ????
I can't edit this.......................
View 14 Replies
View Related
May 6, 2005
i cant seem to get this to work.
any help would be greatly Appreciated
=sum(iif([SADate]>=DateSerial(Year(Date()),Month(Date()),[p]))
thanks in advance
View 3 Replies
View Related
Jul 20, 2005
I am trying to create a form that has an unbound text box which row source is a field in a table. I have created a subform that gets the records from another table.
Both tables are linked in a one to many relationship. When a user selects a record in the cmbo box i want the table to show only those records which are related.
This obviously didnt happen. So i changed the subform instead of a table as its source but a query, then in the criteria of a query just set it to look at the cmbo on the form. After the cmbo was updated i got the subform to requery.
This worked fine, but i was unable to make any changes or updates but with no particular error.
When inserting a subform using the wizard i do remember coming across a section of a wizard that asks me if i would like to tie in the subform with something on the form. However this part of the wizard just doesnt show. Maybe i was using a different version of access for this wizard?? i am currently using Access XP.
Am i really going MAD!!?
View 3 Replies
View Related
Aug 30, 2005
I'm trying to mess with a combo box where when one user selects a value a form opens up. This is the code I'm typing in but I keep getting a syntax error
DoCmd.OpenForm(Phone,[View As AcFormView=acNormal],[DataMode As AcFormOpenDataMode=acFormAdd],[WindowMode As AcWindowMode = acWindowNormal],[OpenArgs])
View 5 Replies
View Related
Aug 15, 2006
Hi,
Trying to make the selection in a combo box on frm1 be the filter on frm2.
I found this example by searching the form, but it's not working for me
Option Compare Database
Private Sub Command3_Click()
On Error GoTo Err_Command3_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "form_lastnametotal_exludeholidayform"
stLinkCriteria = "[Employee_Name]=" & Me!cboMyCombo
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command3_Click:
Exit Sub
Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click
End Sub
I get an 'the openform action was canceled' error?
Any ideas?
The second form is based on a query
Thanks
View 10 Replies
View Related
Sep 26, 2006
Hello once again. I have been working on a small database that records all IT records (Desk Info / Software / License Info / Hardware etc...) Somebody out there might even find it useful!! Will post to DB forum when working :)
Where to begin. I thought it would be a good idea to normalize a little more........(still not 100% but an improvement). I seperated Software/License information. I now have tblSoftware & a tblLicenseDetails.
Basically the clux of it all centers around a form 'frmDeskSetup'. You can search via three methods (Desk ID / User ID or Computer ID). I would like to amend the Installed Software Form (last form on frmDeskSetup) to include the License Information.
I have also tried to create a query linking tblComputerSoftware tblSoftware + tblLicenseDetails. My idea was to create a form that would allow users to Add Software/ Add a License for that Software and then allocate the licensed software to a computer all from one form. The query run OK but would not allow data entry via the form!!
Sorry for the length of this post. I have attached a stripped DB for forum in Acess 2003 format.
Thanks in advance,
Phil.
View 5 Replies
View Related
Sep 3, 2004
IIf([Count: ERName]>=5,"Yes","No")
What is wrong with this line? I am entering it in the following query:
ERName Group By
Department Group By
Date of Call-In Group By
Type of Call-In Group By
Count:ERName Count
The other thing I tried is just doing another query based on the above query and doing a totals (sum) line on Count:ERName. That doesn't work, it prompts me for count (as if that is a parameter I want to choose.
Any ideas? Sorry to be a pain. Thanks!
KellyJo
View 1 Replies
View Related
