I've been creating a database. I prepared forms to enter records. In these forms there are many fields. I want some fields to stay fixed while going up to next record unless changed. How can I do this?
View RepliesHello all,
I'm trying to export a table to .csv file, but I want fields with null values to be padded with spaces. ie. if the field length is 50, but there is a null value, I want all 50 spaces.
Anybody know if this is possible?
Thanks,
Clanure
I have a spec set up that controls the settings for an export file. the table and the form are set up with fields that are right justified. When the data is entered into the table from the form, it shows in the table as right justified. When I run a query and then try to export the results, I use the spec I set up to deliver the txt file in a fixed width ASCII txt file. The data shows in the txt file as left justified. - I need it to be right justified.
example:
$ amount- in the table, the dollar amount is 11 char long and is right justified do it looks like this: |-------7.50| I need those spaces (-) in the export file yet when I export it, it is left. The wizard shows a preview, yet there is no way to make it right.
Can I add blank spaces in the database or space fill the 7 preceding Char in the field with some sort of invisible char or zeros?
Any help would be great - thanks
I have a range say 12.03 to 13.11.
I need to generate all numbers falling within this range with 0.01 increment.
(This increment is based on the numbers; if the numbers have 2 decimals, it should be 0.01. If the numbers have 3 decimals, the increment should be 0.001. Both the numbers will have the same number of decimals)
The answer is: 12.03, 12.04, 12.05, etc. etc. till 13.10, 13.11.
Is there a way we can do it in MS Access ?
I am trying to export a table (and possibly later a query) into a fixed width text file and I am not finding where to set the specifications. When I select Text File in the export tab, I only get the check box about Export datq with formatting and layout. Is that basically it? I do have the table set up with the correct widths for the fields.
An additional question:
This is a large table with 99 fields for a total width of 804 characters. Is there any limit for the six of a record for such an export?
Is it possible to make a control have a fixed position so that when the form is vertically scrolled the object stays in the same position?
I have a continuous form that shows records based on criteria that the user puts into an earlier form. There is a close button on the page but you cannot see the close button if you horizontally scroll too far along the page. You have to horizontally scroll because of the amount of fields that are on the form.
I am using Access 2007 and 2010. I would like a text box (or label) on a report to autofil using fixed text from a label on a different (closed) form.
View 8 Replies View RelatedI have created a member data base form in Access2010.
That has been populated with 366 member records
I have two text box fields that are currently free type fields, Member Type & Member Status.
What is the best way I can change these to show only the relevant options:
i.e. Member Type ( Full, Family, Pensioner, Life. )
Member status ( Current, Lapsed, Deceased)
I have tried to right click the field but the "change to" option is greyed out & not available.
I have also tried to insert a combo box or list box to show these options. The "use control wizard" option has been highlighted but when I drag either a combo box or list box into the form. the wizard does not activate.
I have a few fields that are the same across a couple of forms and sub-forms (each form/sub-form being represented by a different table). I would like for data entry into one field to ensure that the data is autofilled into the other. ie if I type 'ENG' into field 1 on form 1, it will autofill the equivalent field in sub-form 2 as 'ENG' so that I do not have to type the same thing twice. These entries are not unique or in any order as it is variable depending on the entry and so they can't be linked as primary keys and foreign keys. So how would I do this? I would like to avoid VBA is possible.
View 1 Replies View RelatedI am trying to take the data that is put into a form or report and export it to a certain document in word.
The user will be filling out a Reciept. On the form there will be a to and from box for them to fill out, along with many other options. Once the user enters in all the data required I want them to be able to click a button and it generate onto the attached file, in its perspective fields. Is there a way to match the documents to correspond to my program?
How do I add a new field to an existing table so that the same value is automatically entered for all the records in that new field.
View 1 Replies View RelatedI have a query that gives me a table with one column, say column A.
I need to add 2 columns to the table and the coulmns will have fixed value.
So I need to add column B and column C to my table and all rows in column B will have a fixed value and all rows in column C will have a fixed value.
What I have:
Table with one column
Column A
a
b
c
What I need:
Table with 3 columns:
ColumnA Column B Column C
a 12 14
b 12 14
c 12 14
Any help will be greatly appreciated.
Thanks
In Access, can I hard code a parameter value to suppress the prompt? I'm querying a query that's made up of sub queries, which all require the same parameter.
I'd like to define the parameter value in my SQL statement so there is no prompt. Can this be done directly in my Access query?
Again, the parameter isn't any part of my main query, it's part of the sub-queries that make up the master query if that makes any sense.
Dear All,
I need some help. I am writing a database for my new and small company. I receive internet orders from my ISP in an email. I want to find a simple way of automatically importing these emails into my "Customer Information" table.
The issue is that I have no control on the format of the email I receive from my ISP. The data is fixed width and stores the information horizontally rather than in columns. For example:
Quantity : 1
Price : GBP 199.00
Delivery : GBP 5.00
Total : GBP 204.00
I have been exploring the GetData option and using the advance settings to align fields. It seems (and I am not an expert user of Access) that the GetData function relies on the import source to be aligned in vertical columns rather than horizontally. This makes mapping the fields almost impossible.
Does anyone have a clever work-around? Either in Access or manipulating the data before it goes into the database?
Many thanks, Matthew (matthew@byatt.com)
Hey all, i just would like to know if there is a way to have a fixed number of records in a query.
Let's say, example (a), a query returns 2 records (qry1); i would like to have my query (qry2) to have 5 records, so the first two records will appear and then 3 blank records. (b) A query (qry1) returns 4 records, then this query(qry2) should return the 4 records plus a blank record
.........qry1.......qry2
a)|>record1....|>record1
...|>record2....|>record2
...|*______....|>______
....................|>______
....................|>______
....................|*______
.........qry1.......qry2
b)|>record1....|>record1
...|>record2....|>record2
...|>record3....|>record3
...|>record4....|>record4
...|*______....|>______
....................|*______
Why do i want to do this?
Because i want my report to show 5 records so when i print it i have those blank spaces to fill if necessary.
I want to have this:
______
lrecord1l
lrecord2l
l______l
l______l
l______l
Instead of:
______
lrecord1l
lrecord2l
What im doing now is creating a temporary table and add the records to it plus blank records.
What i used to do was add blank records in the same table meting the criteria of the report, so they will appear. After close the report, those records would be deleted.
I would like to know if theres another way, like a command in sql, or maybe some property on the report. What ever is most efficient and a good database practice. Thanks in advance
I need to show two decimal places on a form based on a recordset of a table that has a field - type = double, format = fixed, and decimal number = 2
I need to show 101.00 and 102.00 so if user insert a record, record would have a sequence id of 101.05.
How do I create that field by code? The table is part of a system loop that it changes everytime thats why I have to recreate the table again and again.
I can do the double, I cant do the fixed and decimal number.
Please please help.
Thanks.
NEVER MIND, I FIGURED IT OUT.
Instead of including it with the code, I tweaked how the form displays it.
Thanks.
I have a column in my table, and its size is set to 10 characters. In a form, i have a combobox based on that column, which contains the values "Corrective", "Preventive", and "Supplier". both Corrective and Preventive are 10 characters, and fill up the field to its max, however, Supplier is only 8 characters. When I look back in the table data, there are two spaces after Supplier. Basically, I can use the arrow keys to "see" the two blank character spaces. Why is that? Are these extra blank spaces taking up space they shouldn't?
View 3 Replies View RelatedHey guys,
I searched the forums and couldnt find anything that directly related to my issue. What I am trying to do is export a fixed-width text file, but the issue is that the file has to be line feed only. By default Acces creates CR/LF and the client's import specification only allows for Line feed.
I am currently using a macro, with export specification, to create the file. Due to time constraints and other obligations, I am really trying to avoid VBA programming.
Thanks in advance,
Jahaines
1. how do i import a word document as a template to ms access Report file i did Ctrl + A to select all copy then paste it pasted it well u can say good but when u preview its not an actual page size its much smaller and theres no way to re-size it is there a way to copy or import it perfectly to the report file in access?
and the second question
2. in a form where you choose source code (i think) i merged in 1 table the FirstName and LastName i remember doing something like
Code:
[FirstName] & " & [LastName]
now when i preview the report after selecting my data from combo box it prints fine i mean merges the FirstName and LastName to 1 box but its not a fixed position i mean i placed the text box above the line "Fullname:_______"
but when i preview it depending on the lenght of the table it moves too left or too right and i cant control it is there a way to fix its position? because if the name is too short or too long the text just dances and i changing the position of the textbox doesnt
I have a table I need to export most fields are left align but there are two that are right align, I have tried to use the Space Function but it does not work well, I have also tried using the Rset in a vba module and thought it was working but now it is not.
View 10 Replies View RelatedI've setup some crosstab queries with good success and set column headings that appear with blank spaces when there's no data as expected, but I have 1 report that is formatted with set row headings (its a specifically formatted report that i need to produce, that i can't just change).So I need to basically do the same but with the row headings set (I have got the crosstab working, just not showing the rows with no data as yet).
View 3 Replies View RelatedThe previous problem stands solved i.e. ReportTime field shows 9:50 always.But it has created another problem as under
The Formula for ReportTime : Format(Date(),"m/d/yyyy") & " 9:50:00 AM"
The formula for ReportedAt then becomes as ReportedAt: Format(Now(),"hh-nn ampm")
And the next step LateByMinuts:=IIf([StatusID]=1,DateDiff("n",[ReportTime],[ReportedAt]),0)
Where stutusID is
1. Present,
2. AbsentExcused.
3. LeavePrivelege etc.
is correct for the first time.That is when at the first time the Reported time (system time) is recorded it works fantastically but when next time the Form is opened the saved "LateBYMinuts" field is changed with the system date thus disparaging the previous save data.
I hope someone can help with this one. After many years of using Access for ad-hoc data conversion this has beaten me.
I need to produce an ascii text file with fixed column widths, separated by commas, strange I know but the customer is always right. As it is fixed width I have inserted the commas by using a separate column for each one.
Numeric columns need to be left padded with zeros. I have constructed a query to do all the column selection and reformatting into a new table which I then export using a fixed length export file spec. Everything works fine except for 3 columns which are calculated by subtracting one column from another. I can get the data to look fine in the output table, the datatype is text, but when I export the table the leading zeros are stripped.
This is my expression: String(9-Len(FormatNumber([FULL_FARE_EQUIV]-[TAX_EQUIV],2,0,0,0)),"0") & FormatNumber([FULL_FARE_EQUIV]-[TAX_EQUIV],2,0,0,0).
The result in the table is exactly what I want: 000200.00 but when I export it I get a left adjusted 200.00.
I've tried using format with a "000000.00" mask which gives the same results.
I've tried removing the preceding comma column and including the comma as a prefix using the format mask ",000000.00" and also by concatenation. This looks fine in the table column ,000200.00 but I get an error when I export the table which blanks the column. Error attached.
I want to import a fixed length file into access 2003, it can only be imported directly into access 97/2000. This site, link http://www.ams.usda.gov/science/pdp/03data.txt
has the idea, but the problem is I can find the "2003 Data zip file" which this
site is refering to. - I have looked for it on the ms office 2003 cd,but I still
can find it.
Please can someone tell me where I can find de "2003 Data zip file"?
I am trying to export a text file to be imported in to excel but whenever I export as fixed width all by number columns get cut to 2 decimal places, does anybody know how to get around this?
Thanks
I have a table ("Student Info") that looks like this:
ID....Student.....Student #......Teacher Name......Teacher ID
1........A...............432...............Alice.. .................62
2........B...............674...............Alice.. .................62
3........C...............875...............Alice.. .................62
4........D...............934...............Katie.. ................18
5........E...............345...............Katie.. ................18
6........F...............134...............Dan.... .................4
7........G...............734...............Dan.... .................4
The above table suits me well for data entry.For analysis reasons, I want it to have another table or query or something (let's call it "Teacher Info") that looks like this:
Teacher ID....Teacher Name....St1.....St2......St3...St#1...St#2...St#3
62......................Alice...........A......... B........C.....432.....674.....875
18......................Katie...........D......... E........-.....934.....345......-
4........................Dan............F......... G........-.....134.....734......-
Is there any way that I could do this? I have been struggling with this for a while.Just so you know how this data is connected - At the moment, I have a form where I put in new Teacher information. I have a subform attached to that, where I put the student names and numbers. This way, the students are linked with their student #s, and each of them are linked to a teacher. Unfortunately, the table ("New Teacher Registration") that this creates looks like this:
id....Teacher Name
62...... Alice
(+ tab).........A.........432
...................B.........674
...................C.........875
18.......Katie
(+ tab).........D.........934
...................E.........345
4........Dan
(+ tab).........F.........134
...................G.........734
I created the "Student Info" table (above) from this to work with data entry (drop-downs and such). Now I'd like to create a "Teacher Info" table (above) for analysis.