I have to import a table into a government database. I've got the data lined up in a table and exported to csv. The problem is that the government database doesn't allow empty fields, or the commas created by empty fields. The empty fields are create by criteria that changes with each employee. Is there a way to get rid of them without manually deleting them? I would imagine there is but can't find anything about it or maybe I don't see the forest for the trees? Sample csv below with surplus commas.
I have two tables identical with all text fields within the same database. One table I export as a csv, delimited with commas separating my fields and quotation text qualifiers. Where the fields are blank there are text qualifiers as well ("" - begining and ending quotations). This is what I need. The second table is exporting as a csv, delimited, commas separating the fields with text qualifier as ""- begining and ending quotations, EXCEPT the blank fields are not putting the text qualifier, and I need it.
First Table: *What I need* "142358", "PK", "15132678", "", "123.45"
Second Table: *What I need to fix and do NOT want* "142358", "PK", "15132678", ,"123.45"
I work on a pre-created Access database, and the other day I was working on it, and was trying to export something to Excel to sort it and do some Pivot analysis.
Anyway, I must have pressed something, because now every time I open the database, rather than saying "record 1 of 20463" and showing the data from record 1, it shows "record 1 of 1" and all the data fields are blank. If I go to "Records" and "Show All Records" they'll all come up, but I don't want to have to do that every time, and as I import and export all the time, I'm worried that the next time I try it it'll mess up the years of data I have.
I am a firefighter who has been tasked with creating a database to track repairs and inventory on breathing apparatus(SCBA). There are several(5) related pieces of equipment that I think should go on the same table. Three of these will have nearly identical fields. The other two will have additional field specific to them. Should I create one table including all five pieces and just leave the extras fields blank or is there a better way. Thanks.
Hello there, I'm having a problem very much like the one described by this user (http://www.access-programmers.co.uk/forums/showthread.php?t=113742&highlight=SQL+blank+spaces) where an SQL statement I am using to read data from one table and copy it to another is filling the remaining field space with blanks.
I didn't notice until a VBA module I worked on later on which was to read data from the new table and copy the selected record into a form said it could not find the records. The code:
Do Until blnFound = True Or rs.EOF 'DEBUG: answer = MsgBox("ok", vbYesNo) 'DEBUG: MsgBox rs("Account Name") If rs("Account Name") = strAName Then MsgBox "DEBUG: FOUND " & rs("Account Name") blnFound = True End If
rs.MoveNext Loop
The record selector just goes straight to EOF because rs("Account Name") never matches strAName (as strAname has all those damned spaces after the useful data).
I wondered if perhaps there was some way to use the code in the other thread to read the length of useful data and then chop off the garbage spaces afterwards, but I'm worried it would be incredibly slow when running through the thousands of records. Also, the field has legitimate spaces in between words (most of the account names are two or three words in length).
strASQL, strField and strValue are arrays and this SQL statement runs in a loop, as this is part of a search form for multiple possible entries. It all works fine except it always adds those damned spaces :D
I am in the process of building Append Queries for new records, and I know I can do an Update Query to enter specific information. However, how can I update multiple records from a (externally sourced) linked table to fill in blanks of an existing table? I have created a query to identify records with 1 or more matching criteria which contain the blank fields. I now want to update those blank fields. The data in each blank field is different for each record (same type, just different data).
I am trying to get an average from several numbered fields, which some may occasionally be blank.
=Nz([JanUS],0)+Nz([FebUs],0)+Nz([MarUS],0)
I get an error message that says "The expression =Nz([JanUS],0)+Nz([FebUs],0)+Nz([MarUS],0) cannot be used in a calculated column. This is a hypothetical expression being used.
I am creating a table in access 2010 for my consumable and bench stock report. I made a 12 fields which I name it the month of the year and another 1 field to add the total disburse materials in one whole year. I did this formula to add the 12 fields
But the problem is its just adding the complete consecutive months that I disburse and the row with blank section the total disburse doesn't show on the total disburse for the whole year. I try to use the code =Nz([Total Disburse],0) but it shows on the screen i cannot be used in calculated column.
I have a table tROE with a field listing all dates starting 1/1/10 to date (populated), and three fields for currency exchange rates [USD], [RSD] & [EUR] which are empty and need to be populated. I have another table tROEPartial that has the exchange rates for some of the dates starting 1/1/10 but not all. Their structures are identical. I want to add the exchange rates from tROEPartial to tROE where the dates match, leaving the unmatched fields in tROE blank.
I designed and used an Access 2003 for some time but I have recently updated to Access 2013 and want to rejig and improve my database. My query is that I need to print invoices from my database, there is a header section with my details, then a section with the customers name and address at the left hand side, and the invoice details such as invoice number, invoice date, PO number, credit terms, etc. at the right hand side. I would like to remove any blank lines from the customers address but to still have the invoice details at the right formatted and spaced correctly. Is there any way of doing this, the canshrink property will not work due to the invoice details which still need to be printed to the right?
I've been using a series of 5 tables to export an XML file using Access's native XML export. In order to keep the XML intact, there are relationships setup to link each sets of elements together via an ID number(link) field in each table.
Access does a great job at exporting, but then we need to rely on another program to delete these link fields that links the tables together so our XML validates. Additionally, we use this program to reorganize some of the XML, as one of our tables puts itself at the end of the XML element and not in the middle where it should be. This is not a problem when I'm dealing with a few hundred records, as the 3rd part program does the trick. The problem is when I have enough data to generate a 30+MB file that my other program chokes on when trying to manipulate the XML.
So my question is...how can I export these 5 tables to XML while omitting the linking member fields and organizing it how it should be? I have examples of code below. How Access Exports it
I have a query that I made for about five fields, where the criteria is
Like "*" & [Forms]![DATA SEARCH]![Box] & "*"
Where box is the name of the field that I am searching for.
However, some of the fields in my form are left blank, which makes this refuse to give any results when I try to query it with a form. The other problem that I have is that the fields are bland in different parts of the 1,000 some-odd row table, which was imported from MS Excel.
First off I apoligise if this is a clear answer. I have looked on the internet for the last two days and can't seem to find this anywhere, either that or I am just entereing the search parameters in wrong :confused:
I have filtered selection in a form which I want to export to excel.
Simple enough ;) : Created a macro with the export to command. This dus everything I want to do.
Well not quite. :rolleyes:
How do I select the fields I want to export.Something like Select Id, name, adres from query soandso
Hopefully there is a simple solution to this. If there any existing posts. Could you post the link for me.
My database only has one table of data so it's not complicated.I would like to create a form that can create customisable Excel exports of the data based on set conditions and exporting only select fields. URL...I have one table of data (tblCustomers) which contains all of the fields in the box above.
I would like export the data from the table to excel showing only the fields that are ticked in the box (frmCustomReport).The check boxes are named chk then whatever the field name is eg. chkLocation, chkStatus. The labels are the names of the fields in tblCustomers. Is there a way to do this in SQL or VBA?
Would it be possible for me to build an interface for a customer to use for importing data. So that they customer could choose from a drop down list, or input into a text box where they want to export from, and where they would like the export to be imported?
I have inherited 1000 records which need to fit into new criteria selected from combo boxes and to do this individually by the forms will take forever so i want to copy and paste the data in the table, but when I do it doesn't fill in the form.
This may be a problem with the cascading script in the form as the fields fill in when I press f9 but disappear every time I open the form.Finally I eventually want to export the data as a table but displaying the text value rather than the key value.
Basically in my order details table i have the following fields
Product Unit Size
At the moment i have the Product field with a dropdown that gives me all the products from my ProductT. But once i choose the correct product in the unit field it gives me all the possibilities of every product not just the units associated with that product. ie
ProductT Grasshopper Box1000 Adult Grasshopper Box1000 Subadult Worm 10pz Big
When I select the grasshopper product and move on to the unit field i also get "10pz" option but this is not a product available.
How do i set up validation of the fields Unit and size based on another fields data?
I am using calculated field as a data type in access 2010.
They are working fine.
However, I added a new field and now the final calc won't work.
I have Subtotal adding loads of fields together. Works fine.
Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.
The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?
I need to match the data in all the fields between 2 tables.
eg. Field 1 (Table 1) = Field 2 (Table 2) Field 2 (Table 1) = Field 2 (Table 2) so on......
Using the wizard, I'm only able to match 1 field at a time and this is very time consuming as I have 45 fields to match. Any advise? Can this be done via SQL?
I have a field that has a description of the project I'm working on, which has client information on it. I want to take all 600 of those records and copy them to fields in the same table, so that I can remove the client information, which will allow me to pull whichever of those two I need when I run a query.
Now i want to make a thrid table that contains a field for all purchases in table 1 & 2, (P1,P2,P3,L1,L2,L3,L4,.....), if i make a lookup wizard that will retrieve only from one table but i want to retrieve data from the two tables.
need to create a database for work. there would be a way to select an id based on a previous table and have parts of it populate in this new table.
For example:
Table A - Webinars (Webinar ID, Webinar Title and Client) Table B - Sessions (Session ID, Date, Webinar Title, Time, Facilitator, Session Occurred, Reason, Conference Call) [Webinar Title I used a look up field and just select the corresponding Webinar Title and it works nicely] Table C - Users (User ID, Status, Prefix, Last Name, First Name, Email Address, Component, Role and State) Table D - Registrants (User ID, Session Date, Webinar Title, Attended).
So in Table D I would like to add Role and Component, so when I select User ID #1 the Role and Component Field populate with the info based on Table C.
I can not, or not allowed to enter data in fields in a query built from three tables that are jointed by a common field with the same name. The parent table is linked to another Access data base as is one child table the other is local data. Also, I am not able to view the child table data when viewing the parent table.
I have a query that pulls data from the following fields in 2 different tables:
Area1FloorPrep (tblFloorPrep) ex. remove ceramic tile Area1Size (tblInstallationAreas) ex. 20 s/f, or just 20 Area2FloorPrep (tblFloorPrep) Area2Size (tblInstallationAreas)....
All the way to Area20 (Floorprep and InstallationAreas) for both tables. I have created an installer invoicing form that pulls the data from the workorder that these fields are located in, but the problem I'm having is that I don't know what kind of query to create to concatenate the data in the 20 fields and concatenate the size of the areas next to the appropriate concatenated floor prep description. Is there a way to do this without coding?
