Data Conversion Help Needed
Apr 24, 2006
Hello I am a relative newbie for access and have searched many different sites hoping to find the solutions to my two (seemingly simple) problems. I thought I had found the answers several times but it is not the case. If you could possibly help I would appreciate it greatly.
First: I have a table with a field with the currency in Euros, i need to convert it into dollars and add 1 to the result. I have searched how to run calculations in access and would take the data out and put it into excel and run the calculation but I have too many rows of data and will be doing this calculation often so was hoping to find one simple solution.
Second: I have one column with text separated by a "-". I was looking to see how I could create two new fields with the first field having the data before the "-" and the second field with the data following. I found a query that allows me to separate first and last name but not a string of data separated by a -. Any help would be greatly appreciated, Brian
View Replies
ADVERTISEMENT
Apr 24, 2006
--------------------------------------------------------------------------------
Hello I am a relative newbie for access and have searched many different sites hoping to find the solutions to my two (seemingly simple) problems. I thought I had found the answers several times but it is not the case. If you could possibly help I would appreciate it greatly.
First: I have a table with a field with the currency in Euros, i need to convert it into dollars and add 1 to the result. I have searched how to run calculations in access and would take the data out and put it into excel and run the calculation but I have too many rows of data and will be doing this calculation often so was hoping to find one simple solution.
Second: I have one column with text separated by a "-". I was looking to see how I could create two new fields with the first field having the data before the "-" and the second field with the data following. I found a query that allows me to separate first and last name but not a string of data separated by a -. Any help would be greatly appreciated, Brian
View 4 Replies
View Related
Feb 26, 2008
Hi Gurus, need your help yet again.
I have a linked table in Access 2007(link back to SQL Server), and when i look into the values of a 'Memo' Column called "Column_1" in Access, it is just fine.
Now, In a query I am doing the following IIF statement:
Expr1: IIF([table_1].[Column_2]="Cost",[table_1].[Column_1]
But when i see the values of Column_1 now, some of the ending characters are being cut off, i only get half of the entire text.
Should i be doing any type-casting or anything to bring the entire text?
Any help is appreciated.
Thanks,
Kon
View 3 Replies
View Related
Sep 18, 2007
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!
View 3 Replies
View Related
Apr 3, 2007
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
View 5 Replies
View Related
Sep 1, 2005
Hi,
I feel a little silly bringin this up but I have gone through the solutions provided on this topic but it all doesnt seem to work.
I am trying to up date my Products table with data from two other tables (Sales and Stock Receipt). I have made sure the data types in all the tables are the same (currency) but I still get this message
"Microsoft Access did not update 5 field(s) due to a data type conversion failure."
This is the expression I'm using in the update query
IIf(IsNull(DSum("[Quantity]","Sales Detail","Sales Detail.[ProductID]=" & [Products].[ProductID])),[Products]![OpeningStockAmt],[Products]![OpeningStockAmt]-(DSum("[Quantity]","Sales Detail","Sales Detail.[ProductID]=" & [Products].[ProductID])*[Products]![CostAmountperUnit]))
View 3 Replies
View Related
Jan 22, 2008
Hi all,
I would very much appreciate help with this one. So thanks in advance if anyone can help.
I have a table which has a Text Field (it has to be, I'm afraid). The data is listed as follows:
5.6%
12.23%
2.45%
etc.
I need to use these values to multiply other figures in queries. I have tried FORMAT and various other ways in the query to convert the data, but to no avail. All I get as a result is an ERROR. Anyone got any ideas?
Ginny
View 6 Replies
View Related
Jul 7, 2005
hello once again,
I need to import a text file into an existing table in Access. The text file has been imported once and is working well and everything. However, since I had to change some of the datatypes to be able to query the table correctly, I now cannot import the text file anymore unless I change the datatype of the table itself. Since someone other than I will be doing the imports from here on out, changing the datatypes everytime is out of the question. I was looking at the TransferText event, but I didn't seem to see anything about converting data types. I can think of two options, and neither are probably possible:
1. import using the wizard. Since I didn't see anything related to the types of data, I don't think this will work...
2.import using TransferText. This doesn't seem to give me any opportunity to change the datatypes either. Is there anyway to programmatically change datatypes, or is there possibly an easier way that I'm overlooking??
thanks in advance,
*j
View 4 Replies
View Related
Jun 15, 2014
I have an old Table with Movies, Actress, Actor, & Director Fields & I have converted it to .accdb. I want to turn those short text fields to an ID number. I have built Tables for those fields(indexed no dups) with an ID field. I have over 5000 records in the original table and dont want to input those numbers by hand. I am using Access 2013.
View 5 Replies
View Related
Jun 11, 2013
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.
View 10 Replies
View Related
Aug 23, 2005
Some of my collegues have been experiencing this problem with a very simple database i knocked up for them. First off some background. I have a main form (frmproperty) with 3 subforms linked to it(frmroomdata, frmlocation and frmsample). by a property code. Two of these subforms are linked (frmlocation and frmsample) these are linked by location code which is an autonumber.
These are laid out so that you fill in room data first, then location data then finally sample data. However after the location data has been filled out and a user attempts to move to the first combobox on the sample subform a message pops up saying that another user has altered the database and would you like to save changes (Impossible as they are using the database on a local drive). After this happens they reopen the form and in all the frmsample fields is "#deleted"
This doesn't happen every time...infact over the last 2 days i have been entering data myself to try and get this error with no luck. Has anyone else come across this and know how to fix it?
Thanks in advance
Matt Collins
View 3 Replies
View Related
Jan 5, 2006
Hi. I am developing a db for juvenile salmon-focussed fishery survey data and have encountered something of a conundrum which I could use some advice on. Apologies in advance for the length of the post.
Background
Juvenile salmon move from freshwater to saltwater. During this transition they require time to adapt physiologically and are thought to seek out nearshore areas with intermediate salinities, or with freshwater overlaying the saltwater. They also experience problems with elevated temperatures.
We are interested in tracking salinity and temperature information at each site where we sample for fish to aid in interpreting our catch results.
Data Collection
Our convention is to collect temp/salinity at the surface and at 3-feet below the surface wherever we beach seine (or just at the surface if the site is shallower than 3-feet). However, we use a depth-temp-salinity data-logger attached to the lead-line of a lampera net for openwater sets. The logger provides measurements of depth/temp/salinity every 5 seconds during the set, down to depths of 20-30 feet.
So, for some 'sets' we have one or two measurements of depth/temp/salinity, and for other sets we might have over one hundred measurements.
Problem
1.How best to get that data entered into the db?
2.I'm just starting to get my toes wet with VBA
Ideally, I could directly enter the values into a subform for sets with only one or two measurements, but could instead 'import' the extensive data for those sets where the logger was used. Entering the logger data manually would be ridiculously time-consuming.
Existing DB Setup
Records for temp/salinity subform/table linked to other set information by a unique Set_ID field.
Subform for depth-temp-salinity information bound to a dedicated depth-temp-salinity table. The subform is currently viewed as a continuous form.There would be one excel file for each set where a data logger was used, but no excel files for sets where no data logger was used..
My thoughts so far.
Somehow create a subform with the ability to enter up to two records manually or else click a button that imports the data from an excel file. One thought is to pop open a window to navigate to the excel file that contains the data for that set. However, I'm thinking that if I place all such excel files into a particular directory and name them using the appropriate Set_ID number convention, that maybe clicking the button with be able to find the file directly, without navigation required, and bring in the records automatically.
Is this possible? How would I go about creating a subform that provides both an 'import data' button and allows for manual data entry of up to two records?
Can anyone show me a similar example for both the data entry (form) and for how to automate the importing of data from excel files to append to an existing database table?
Aim:
The eventual goal of this is to have a command button that could be clicked on the form/subform that would produce a popup window containg a scatterplot graph of salinty versus depth. another button to produce a scatterplot of temperature versus depth. A third button to open a line graph with time on the x-axis, and temperature/salinity series on the y-axis. Before I can get there, however, I need to get the data into the table somehow.
I would appreciate any input/advice on this matter, (especially custom code! ;) ) As, I mentioned, I'm just starting out in VBA and I have a lot to learn. I know how to open a MsgBox, but have no clue on what the command is to open an explorer 'window'.
I hope the problem is sufficiently interesting to generate some response.
Cheers!
View 2 Replies
View Related
Oct 27, 2007
Hi all, help with this would be appreciated.
I have a crosstab query which returns values in a range of categories. I have 5 categories. My problem is that sometimes a category may not return a value in 1 or more of the categories. This means that when I am displaying this data in my report I haven't got the data in the correct columns.
In the underlying queries for this I tried using a LEFT JOIN forcing the category, but when I executed the crosstab I got errors in all fields.
Thanks in advance
Ginny
View 2 Replies
View Related
Apr 26, 2006
http://forums.aspfree.com/microsoft-access-help-18/menu-list-values-won-t-pass-to-db-20951.html
Here's a page that shows almost what I am trying to do, but my question is how do I make selecting an item in my listbox pupulate date in multiple field on my form?
For instance, I want to click on a line in a textbox and have the fields "City" "State" and "Zip" all changed on my form, not just "City"
View 4 Replies
View Related
Mar 12, 2006
hi everybody,
great resource you have here! my employer is tracking around 250 pieces of data for each client at our facility. i am making a new access system based on their existing mysql database and web front end. before i start messing around with forms and reports, i want to see how well this existing structure will work in access, and what kind of approach i should take. i am a newbie with access, but lots of experience with asp/mssql/php/mysql. making web forms is so time consuming that i figured i would be best off moving the whole thing to access and starting from scratch.
client information is stored in eight tables. each table has around 30 fields in it. the first table has a primary key autonumber, and the other seven tables have foreign keys with unique constraints that point back to the first table. that is, for each client record in the first table, there can only be exactly one corresponding record in the other tables.
i did some data massaging, and got the eight client tables into one big table, but the resultant table has almost 250 fields in it, and access doesn't seem to like working with tables that big. so i am thinking that it is best to leave the eight tables separate, but linked in one to one relationships.
i was kind of ideally visualizing a form with eight tabs so that i could edit/update all of the information from the eight tables rather seamlessly.
my question is: what approach to table structure will best suit my needs, and what approach should i take to add/update/delete the info with forms? will i need to do vb for this? any good one-to-one example databases anybody could point me at?
thanks a million,
harry doyle
View 1 Replies
View Related
Aug 21, 2014
I have a sub report on my form to show all data needed for a delivery note.
these fields are :
House Type
Plot No
Product
Delivery Note No
when i use my query to search orders in my subreport - it will show the required data but will also show data i didnt search for ? why is this?
Sometimes my search results in the subreport show further down the subreport than the irrelevant data - all i would like is for it to show the specific data not the irrellevant data.
View 3 Replies
View Related
Nov 3, 2005
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.
View 2 Replies
View Related
Sep 1, 2014
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?
View 6 Replies
View Related
Jun 10, 2005
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 Related
Nov 18, 2005
I'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 Related
Jan 3, 2006
Does anyone have a Jet to ANSI SQL conversion tool they could either send me or recommend:confused:
View 2 Replies
View Related
Feb 17, 2006
We 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
View 1 Replies
View Related
Mar 24, 2006
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
View 3 Replies
View Related
Apr 22, 2006
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
View 3 Replies
View Related
Jul 31, 2006
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.
View 10 Replies
View Related
Feb 11, 2007
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 Related
