Hello,
Its been a long time since i've been in this forum. I have a question about a conversion error i received not sure whats its about
i'm converting from access 97 to 2000. i ran the access 2000 conversion utility and then the following appeared and the conversion error table
Object TypeObject NameError Description
TableMSysObjects-1611: Could not find field 'Description'.
It looks like the database is fine
i am importing from excel and i have a column that has account numbers and at some point i have an account like this... 2C1C18100
on the import i get an error.
i set the field to memo, numner or text and still get the same error..
any suggestions?
thanks in advance
I have a text column of alpha-numeric fields and some start P123456, P111111, P222222 etc. I want to remove the P from these fields.
I thought the following update query would do this but gives a type conversion error, regardless if the new column to copy them to is type text or number. Can someone help explain what is wrong.
UPDATE 2006 SET D2 = CLng(RIGHT(D1,LEN(D1-1)))
WHERE LEFT(D1,1)="P";
Thanks,
Andy
I am importing an Excel file of production data into a table using the TransferSpreadsheet method. Here is a snippet of the code:
DoCmd.TransferSpreadsheet acImport, , "tblMPSDATA", _
"" & stFilePath & "", True, ""
Everything works fine except the column which contains 'Material' holds both numeric and alpha-numeric values. For example these are both material numbers: 156952 and 1238707-202. The data in this column is formatted as General. The data type for Material in tblMPSDATA is Text, 18 character length. The alpha-numeric materials are all at the end of the file. When I import, an error table is created listing the alpha-numeric materials with the error 'Type Conversion Failure'. But if I have an alpha-numeric material in the first row of data then everything is imported just fine.
I have set up a nice little popup form with a file path and command button for controlling the process of bringing in this data. I really do not want to have to add special instructions about making sure the Excel data is sorted in a certain manner prior to importing. Any thoughts on why Access is not treating everything in this column as text?
Thanks,
JAB
Good afternoon all,
I have a macro that I run. In this macro, I've changed the "SetWarnings" to No. This works since I do not get a "Are you sure..." when I do a makeTable query and the table already exists.
However, I do get a type conversion failure error message.
Does someone know how I click Yes on this without any intervention on my part when the macro is running? Would entering keystrokes in the macro work? I don't think that they will since the next command is not called until the previous has executed ... and I'm getting hung up on the dialog box before the make-table command has finished execution.
I've attached the error message text that I'm seeing.
Thank you for your time and help.
I keep getting conversion errors, even though my field lengths and formats seem to match up. How to pursue a diagnostic for this?
View 1 Replies View RelatedI am trying to import an Excel spreadsheet into an Access 2007 database. Each time I import, Access creates a 'type conversion failure' error database.
I set up the first field in my .db to be a TEXT field because the Field will have both numeric and text characters. The field name is [estimate number] with numbers such as 656111 or 65611A being imported. All the fields with the numeric and alphabetical combination (i.e. 65611A) are not being imported (which results in the creation of the error db).
Here's my Goal: To open a saved query that has a parameter, setting that parameter via a VBA sub.
Here's my Problem: I was getting various errors, but after debugging my program a bit, it comes down to a "Data Type Conversion Error"
Here's my Code:
Set db = CurrentDb
Set qd = db.QueryDefs("qryMY_DATA")
qd.Parameters(0) = Me.txt_ReferenceID
Set rs = qd.OpenRecordset("qryMY_DATA", dbDynaset)
Code:
'*** Database Variables
Dim db As DAO.Database, rs As DAO.Recordset, gq As DAO.QueryDef, prm As DAO.Recordset
I've been all over the forums and tried several different approaches, all to no avail. The Query runs fine in the QDT, but kicks back an error when I try to run it from my sub.
I'm in the process of migrating my Access database to SQL, but am encountering problems. Firstly when I exported it I discovered that all of my Access queries had been converted to Tables. After deleting these, I attempted to cut and past the SQL code from the Access Queries to SQL's "views", which I had been taught were effectively the same thing.
However, now I come to test them I get an error that says it cannot be displayed because the query is a "view object".
What is happening here, am i doing the correct thing?
Thanks.
I have thousands of PDFs of which I want to present a number as thumbnails on a form and allow the users to select any one and have the full PDF displayed.
The only way in which I can see this working is to have the thumbnail as a JPG image and set the On Click property to display the relevant PDF. This part is quite simple, the problem I have is converting the existing PDFs to JPGs.
Any way of converting PDFs to JPGs using VBA code?
I have a numeric month, as in 1 for January. I want to convert 1 to January. Any advice on the syntax?
View 1 Replies View RelatedI'm imported data from a csv file. The dates stored in there are in a dd-mm-yyyy time format. How can I work with this since Access's date format is just mm/dd/yyyy? I imagine I need to do some sort of conversion? Does Access provide anything for me to do this?
View 6 Replies View RelatedDoes anyone have a Jet to ANSI SQL conversion tool they could either send me or recommend:confused:
View 2 Replies View RelatedWe have an mdb back end on our server with read / write permissions for several approved users. One of them (don't know who yet) has converted the mdb file from 1997 to 2000. Is there a way to undo this? :o (a few have both versions on their machines which is why this was possible to begin to with)
We have had this set up for almost four years now, with no issues might I add but always knew in the back of my mind something like this would happen.
Thanks
Hi,
I have been tasked with updating an Access 97 database with several forms that would take me a good while to recreate.
I wondering if there is a way to convert them from 97 to either VB6 of VB.NET that is free of charge to me. All the solutions I have come across thus far cost money, and it is not possible for me to do this.
I have tried exporting the forms to an external file but it won't let me save them as a file type that my VB6 installation can open.
Cheers
Murray
Hi guys, I hope somebody can give me a quick step-by-step.
I've nearly completed a program written in Access and have started to venture into marketing the program to a local company. When asked what other modifications they would like to see i was sent an email stating;
"A major item would be to deploy in MS SQL. You should be able to do that free of charge with the low tier version of SQL."
I had a feeling this would come up, but I don't want to sound like a total idot when talking to these folks... I really have no true understanding of what they are asking, but I suspect it refers to conversion from jet. Question: Is this a reasonably easy modification? What do I need to purchase? Would I be better off telling them the program is 'as-is'? Can this be easily done by someone who has never done it before? I'm sure other threads addressed this issued but I would truly appreciate a full understanding of my specific problem. Thanks, Scott.
Considering the difficulties in packaging Access software, I am wondering about the feasibility of converting my Access project to VB and distributing that instead. Has anyone done this and if so how difficult would it be. Does it mean a complete rewrite or can I use existing forms, reports and coding etc.
View 12 Replies View RelatedI am in the process of building a table on Access 97 where one column will have a list of various buildings. The problem is I will need to import a spreadsheet from Excel 97 into the Table, and although the spreadsheet will match the Access table in terms of column headings, it's data is numerical ie a number has been assigned for each buliding instead of the building name. I need the Access table to show the building name instead of the number.
Is there a validation rule I could use when creating the Access table where a number will = a specific building name. ie 1= Lunar House 2= Apollo House 3 = Mackenzie House etc. So when it does come to importing my spreadsheet the numbers will show as buildings.
Hope you can help....many thanks
Russell from UK
I have a numeric month, as in 1 for January. I want to convert 1 to January. Any advice on the syntax?
View 2 Replies View RelatedI have 2 questions that I think maybe similar.
First Question
When building a Select query through query builder, is there a function or a way I can convert the field, fldWebSite, which is of the datatype Hyperlink to text? The problem I am having is that when I export the results to a csv or txt file, the field instead of saying "www.website.com" turns into "www.website.com#http://www.website.com#"
Second Question
When building a Select query through query builder, How do I convert a field that is of the type currency into a fixed 2 dec. number? Inother words, I get "$1,234,567.00" when what I really want is "1234567.00".
Hello
I'm trying to convert my exsting LIKE Command to search Contians a word.
Example currently it will search for word starting with word in the search box.
I type in 2006, it will only look for project names starting with 2006.
I would like to Motify the search to "Contain" 2006 in the field anywhere.
Here's the code I like to convert.
"SELECT * FROM [qryProjects]" & "[PROJECT] LIKE """ & Me.txtSearchString2 & "*"" AND "
I have a time stamp field from an Oracle database that I want to convert to a regular date field in my Access query so that I can pull data from the table base on start and end date.
The time stamp field is formatted as: 09/19/2006 03:16:00 PM
In my query I have tried the following formatting:
1. DateRcvd: Format((Left([time_stamp],10)),"mm/dd/yy")
or
2. DateRcvd: Format([time_stamp],"Short Date")
Without criteria I get all the records in the following format:
1. 09/19/06
2. 9/19/06
Dates in the table are from 06/01/06 to current date 07.
Using the following criteria - I get varing results but never what I want. For example, using format #1, if I enter 09/16/06 and 01/10/07 I get everything for 07 and nothing for 06
If I use format #2, I get everything for 9/16/06 (no other records) and everything for 07
Criteria:
Between [Forms]![frmDateRange]![StartDate] And [Forms]![frmDateRange]![EndDate]
Any suggestions?:confused:
Hi Guys,
I am using MS Access 2007 and have the following query:
SELECT HoursLostThroughAccident
FROM Accidents
WHERE AccidentDate LIKE ? +'%'
The error I get after selecting values from a drop down list like 2001 or 2002 is:
'Failed to convert parameter values from a string to a datetime'
Is there a way that access converts the values like 2001 to something like
01.01.2001 and the values which have dates that are in the year 2001 will be displayed?
Thanks for any suggestions,
Phil
I have a table with data in the following format:
OppID Year 1 2 3 4 5 6 7 8 9 10 11 12
1 2007 $1 $2 $1 $3 $2 $3 $1 0 0 $3 $4 $2
I need to convert the data into:
OppID Date Amount
1 1/1/2007 $1
1 2/1/2007 $2
etc.
What is the best approach to complete this conversion 'on the fly', so that as the data is modified in the table, later the user can run a report (PivotTable) that uses the converted data? (Note that the day of the month is not defined, so I plan to assign each the 1st of the month).
Really would appreciate any help you can offer!
I have this code in an Access97 database that works just fine. But it doesn't like it in Access2003. Any ideas of what the problem is?
Thanks in advance - John
_______________________________________
' this adds date checking
If Not IsNull(Me!StartDate) Then
If ArgCount > 0 Then myCriteria = myCriteria + " AND "
myCriteria = myCriteria + " [Main Table].[Date] >= #" + Me!StartDate + "#"
ArgCount = ArgCount + 1
End If
If Not IsNull(Me!EndDate) Then
If ArgCount > 0 Then myCriteria = myCriteria + " AND "
myCriteria = myCriteria + " [Main Table].[Date] <= #" + Me!EndDate + "#"
ArgCount = ArgCount + 1
End If
I have an Access 97 database that I’ve converted to Access XP. The font end database is on each user’s computer and the backend is on a network server. The database is updated with data from a mainframe db2 databases. I have 87 queries that pull the data from the db2 data base and import the data into an Access database that resides on a net work server. All these 87 queries are append and update queries that clean up the data and set it up in Access.
In the Access 97 database it takes 9 minutes to run all these updates but in the Access XP database is takes about 32 minutes. Both the front end database and network database are converted to XP. I have found that if you link a 97 table to an Access XP database, you slow everything down in the XP database ???, so I’ve made sure that all linked tables are XP. I’ve looked at my queries and tried to optimize as much as I can but nothing has worked.
I have several parameter queries that really seems to run extremely slow. I’m updating fields in on table with data from a field in another table. I using the query execute method with a loop to set the parameter each time and then execute the query This works well in 97 but in XP it’s extremely slow. I’ve changes indexes in the tables and at one point and at one point removed all indexes except for the index on the joining fields in the table.
All of the forms and reports in the database also run much slower.
My company has also upgraded. When I was using the 97 database I was using windows NT. With the XP database I’m using Windows XP.
If anyone has any advice or ideas, I would appreciate hearing from you.