I'm just to work with Fox Pro, and I am therefore puzzled with how to define the exact number of digits in a field in a table in Access. If I need 9 digits + 2 decimals in a number filed, how do I define that in design view?
Also, I need to know how to export a table into a text file, with a format without any spaces, and each record is divided with a new line. This an old IBM text format file.
Thanks, Torsan
I want to know if there's a way to sort alphanumeric fields where the number of digits aren't always the same.
Example: I have the following: 2a, 10a, 3a.
Currently, it sorts: 10a, 2a, 3a.
Is there a way to get it to sort 2a, 3a, 10a without adding a 0 before 2a and 3a?
have a query with a field that contains numbers with 3,4,and 5 digits. I want to format it so it displays all as 5 digits by adding 0s to the front of the number. EX:
324
4856
45634
change to:
00324
-4856
45634
Help please. Dont want to do it in VBA either. I would rather build it as an expression field or something.
dear frnd,
i have a access program for printing cheque. i want to use a text box which automaticaly convert number ito digits from user input text box.
I need a field to permit only numbers. Upon selecting number for the data type I select Long Integer for a longer number... But it only allows 9 digits.. I need it to allow to enter in longer numbers... Any help?
Thank you very much in avdance
Hi there
Im working on an update query to add 2 zeros to the front of a field where it is less than 8 digits long. I'm not sure how to go about this, can anyone help me out?
Cheers
Hi guys,
I have a text box, which requires a 8-digit number entry. I have wrote the code to check that the length is 8 and if it isn't a label will turn visible next to the field telling the user entry must be 8 digits.
This works for when i enter a number less than 8-digits but when i input a number with more digits as it does not fit into the text box it turns it to a scientific format value and when the code runs it shows the length is actually 8! And therefore the entry is valid!
I.e. when i input 123456789 the text box shows 1.23E+08 which is 8 charecters!
I dont want any error messages to pop up as i handle them by code and use labels to show the error message next to the problem field and i would like to keep this consistent. Is there any way i can do this by a function or a property in the table or form?
I appreciate any help/advice
cheers
hello,
simple problem. how would i grab just the last six digits of a social security number?
example: 123-45-6789 to 456789
I'm guessing that I should use the Format function, but I'm not quite sure how to write it. Thanks!
G
I'm trying to add filds to a table, and when I try to save I get the error message: cannot define fields more than once. The problem is, there are no duplicate field names. I've run compact and repair (several times) with no change.
Any ideas?
Thanks,
Mary
I have a report to print that only shows the last 4 digits of the Social Security field. How can I do so for the entire list of social security #'s.
Thanks in advance.:cool:
I have a form with an unbound textbox. I want to be able to enter a sequence of digits and spaces (e.g., 02 950 4187); however, when I paste this into the box, I always have to delete the two spaces until the number is 9 digits long. Then it will accept the number and go look for the prize. How do I get the textbox to accept the number I paste in?
View 5 Replies View RelatedThe field SECL DDI has the users phone number unfortunately over time these have been entered in different formats so there are 5 digits, 6 digits, 7 digits etc...Can I run a query that counts the number of digits in each filed and then tell me how many of each exist
Ie
4 digits 3412
5 digits 5000
I have a table which is formatted as shown:
ID, My_Var, My_Value, Notes
This table holds variables that I want to declare to use throughout my application. I have been told in another thread that tempVars are the best way to do this.
I have written the following code, which works on a limited basis:
Private Sub btnSetVAr_click()
TempVars.Add "udvVar", Me!My_value.Value
End Sub
This defines a single variable on each button press, fine to work out how the code works, but not much use. What I really need to do is when the initial menu screen loads to call a routine to assign all the variables stored in the table using a loop to do this. The idea is to make all variables values easy to edit or add to, rather than have to edit code each time we need to change them.
The variables table holds 14 records so far, such as:
My_Var, My_Value
EuroRate, 0.885
ConDisc, 0.9
MollDisc, 0.8
As well as holding numbers, they hold strings and date values.
So, on loading the initial form, use an event to assign the variables from the table using the tempVar name as the value held in "My_Var" and it's value as held in "My_Value"
I would like to define a field (mailingaddresscounty) as type lookup using a field (county) from another table (Zip). But instead of the user selecting from a long list, I would like the selection to be automatically made based on the value of another field (mailingaddresszipcode).The two tables are Organizations and Zips. The user enters the 9 digit zipcode in the organization table (mailingaddresszipcode). I would like the county field in the organization table (mailingaddresscounty) to draw from (link to?) the corresponding 5 digit zipcode in the Zip table (zip) and return the correct county for that zipcode.
View 1 Replies View RelatedI would like to declare Global or Public variables from a table so they can be added to or edited easily. I had the following function to do this with Alpha Five but at the moment my lack of knowledge of Access VBA is making this task difficult.
Here is the function that I use in Alpha:
FUNCTION udVars AS A (udTabName AS C )
dim codeStr as c
t=table.open_session(udTabName,file_ro_shared)
t.fetch_first()
while .not. t.fetch_eof()
[Code] ....
It opens a table reads in the records and then makes them into a string the string would look something like this:
"Public EuroRate as Single = 0.885"
I would then need to use this string to declare the variables but not sure what command to use - I was looking at the Eval function last night but couldn't get this to work...
The table would hold strings for all the above so you would have:
My_Var (variable name)
My_Typ (type of variable e.g. Single)
My_Val (value e.g. 0.885)
The table would hold as many variables as required, some would be dates, some paths for making directories and some would be numbers.
I have a table of companies, with fields that contain data for 2008, 2009, 2010, etc.
I'd like to design a query that allows the user to define on a form the field, or the year, they want to query. (by text string or some other way, I am good enough with the VBA that I can figure this part out once the beginning part is figured out)
I want 2009 data, I type in 2009 and get 2009 data from a table with many years' worth of data. But I only need one query for all the years.
I notice that this is easy with reports, just use SQL in the the wherecondition, argument, but I can't find the equivalent for queries. I tried putting the text field from the form in the SQL in the query, but could not get that to work.
My boss has a table with tons of fields on it, he designed it this way because it was easier to add data to one place, but now the table keeps crashing, does ms 2k have a limit to the number of fields in a table??
Thanks in advance.
I have run into a problem with an ODBC table connection. The table I am connected to exceeds the number of fields allowed in an Access table. I am using Access 2003. When I looked on Microsoft help site I think the max number of fields in a recordset is 242 so I am unable to see all of the fields in the original table I am linking to. Does anyone know if this limitation has changed in the newest version of Access? I checked the Filemaker www site and that program does not have the same limitations. Thanks in advance for any help!
View 4 Replies View RelatedHello there.
Is it better to have many fields in one table or to split the data between a number of tables and link them?
Thanks.
# gingette
I have a 9 digit ID that is inputted on a form and stored in a table but the 0 keeps getting dropped.
I am using an input mask similar to another db but it is not working for this one
mask I am using is 000000000;0;_
So for ID's that start with 0 (EX:012345678) would display and stored as 12345678 instead...
I am having a table for invoicing and vouchers, and I need to have two fields that will have auto incremental numbers. How I can have two fields in the same table with autonumbers and how can I get one of them to start from a different value.
Eg. Record 1 - [inv No] 1 [Vno] 0005
Record 2 - [Inv No] 2 [Vno] 0006
I have a table that has fields for number input
On my form I have in a TAB form 3 fields to input a figure, in the table these are set up as a Text NOT a Number. If i set it as a Number it keeps rounding up? even after i enforce 2 decimal places it still keeps rounding up.
Field 1 - 42988.62
Field 2 - 0.00
Field 3 - -14330.84
I then created a unbound txtbox so I can add these up
=[ResBF]+[ResRefReC]+[ResSurDef]
what i get in return is this :- 42988.620.00-14330.84
I also tried =Sum([ResBF]+[ResRefReC]+[ResSurDef])
and that just comes up with #Error
I even tried in the table directly to have a calculated field and still having the same problems.
I have a query that is pulling from a single table of expenses the sum of expenses for each quarter. I am using only three fields: the quarter, expense type and amount paid. I am using - Qtr: DatePart("q",[PostDate]) - to obtain the quarters, grouping by quarter and then by expense type. However, the record returned for the 4th quarter shows this result: "3.0026" It should not have four digits after the decimal.
Results for the other three quarters in the same query return correctly with two digits after the decimal point. The query below was suggested to me in order to make the sum returned have only two digits after the decimal:
AmtPaid: Sum((SELECT FORMAT(PymntAMOUNT, 2) FROM tbl_expenses))
The result of this was a curious information box that said: "At most one record can be returned by this subquery." The query returned nothing. I have checked the formatting in both the table and the query itself and the format is "Standard" for both. So, I can't figure out what is producing this 4 digit problem, and why it is only in one record while all the others have two digits after the decimal.
I have a table where there is information stored about certain workorders.. like location, description, etc.
The thing is that location is descripted as "03-LZ-.." where the 03 part stands for the departement. I have another table with all the numbers and departements and now I'm looking for a way to link the two. So I have to find a way to look at the first to digits of the "location" in my workorder table and link that number with the table where numbers and departements are stored.
I have an ... interesting issue. I am wondering if others have dealt with this. I have a solution, but am not the happiest with it.
I basically have 2 databases here. (Or I will when I'm done making them) and one is dependent upon the other. In fact, the 2nd's tables are dependent upon the fields in the first database's table.
This database is for quality control checking purposes.
First off,
Database 1:
Basically a list of standardized audits and what needs to be checked for those audits. What needs to be checked can change as things progress, but stays pretty standard.
Database 2:
This will hold the checks that the QC branch will do, based on what type of Quality Check they are doing, they have an audit standard. This audit standard exists in database 1.
What basically ends up happening is that the data in Database1 become Table fields in database2.
Example:
Database1:
Radio Check Audit Standard.
Needs to check following:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.
Another Random Check Audit Standard
Needs to check the following
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Did something specific for this Audit
Didn't do something else, also specific for this Audit.
Etc.
Continue on with other Audits.
Database2: Will keep track of all the quality checks that have been preformed.
Today we will check Radio Protocol
Did they:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.
Yes, No as necessary.
2 Days later, we need to do another QC on the Radio Protocol
Need to check if they did:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.
Etc.
But what will be checked for in Database2 depends on what the standard is for the "Radio Check Protocol," or etc.
Right now, i see 2 options:
Option1
Make the 2 databases, and when a user "Picks" the Radio Check protocol, a linked table is filled with the Name / object that needs to be checked with a field in the same row to hold the data if the object was done / not done etc.
The fields in Database1 are automatically put into this specific instance of Quality Check, etc.
Only problem with this: Requires coding to auto update. Easy to do, but if errors occur, things will screw up.
Option2:
When the user picks the Radio Check protocol, simply allow a subform with a refreshed drop down box. Allow user to pick items they need (Limit it to only this Audit list, etc) to set to true. Assume false for other items not specifically chosen by user.
Problem with this: If an Audit standard changes to remove an item, or add item. Any quality checks done will also change for the assumed false items. If another Item added in, then all old QCs will now also have that item added in / reported assumed false. And if it is deleted, all old QCs will loose the item that was reported false.
I'm seen the first option as the only viable one.
Am I missing this, is there some other way to do this that would be better / wiser? Etc?
Kelemit
Hi all, I've searched this with no success.
I have a ODBC (Firebird) table linked using a DSN connection which has >300 fields. In Access, the linked table only show 256 fields (max for Access). Otherwise the link works fine. I have no control over the source Firebird table.
What I'd like to do is limit which fields from the Firebird table are shown in Access. I don't need all the fields, but I need some that are at the end and are not linked in the current setup.
I'm very much a novice at using ODBC, but can I somehow use SQL or some other method to specify which fields to show in the linked table?
Thanks for any suggestions,
John