I have an append query that I input a 14-digit PIN number and it populates the PIN field (works fine). In addition populating the PIN field, I also want it to populate the ParID field. However, the ParID field only needs 9-digits (excludes the first 3 and the last 2 - i.e. xxx123456789xx). Is there a way my append query can handle such?
Thanks,
SKK
In Access 2007, in a table “Source” -> "UserName" column, I'm trying to truncate and extract value in the string:
a. Value within the parenthesis preceded by "".
b. Value outside the parenthesis preceded by “”
“Source” table contains the column ‘UserName’
“Final” table contains the columns ‘UserName’, “Expected_Output_1” and “Expected_Output_2”.
I tried using the following expressions to extract the output results, but unfortunately there’re not displaying the results in correct format.
For extracting results of “Expected_Output_1” I used: Mid([UserName],InStrRev([UserName],"")+1)
For extracting results of “Expected_Output_2” I used:
Mid(Mid([UserName],InStr([UserName],"(")+1,InStr([UserName],")")-InStr([UserName],"(")-1),InStrRev(Mid([UserName],InStr([UserName],"(")+1,InStr([UserName],")")-InStr([UserName],"(")-1),"")+1)
Attaching the database for your reference.
Expected results should match the snapshot attached or table “Final” in the attached DB
Example:
UserName = C103E000001 then the “Expected_Output_1” = E000001, “Expected_Output_2” = nothing
UserName = NAHoM\_INTRA_MTS (NAHoMDNAGAIntranetAdmin) then the “Expected_Output_1” = _INTRA_MTS, “Expected_Output_2” = DNAGAIntranetAdmin
In the attached Database (Access 2007), there are 2 tables - "Source" and "Expected_Output"
1: Table "Source" contains values CONCATENATED in a single row with concatenation string ' OR name = ' With limited knowledge in Access, i tried to perform text-column trying to see if some delimiter could be removed, it messed-up the expected output.
In the attached DB, table "Expected_Output" contains the required output.
2. DB should have the ability to: the values that we obtained in the "Expected_Output" should be converted from multiple rows to single row with concatenations as in the "Source" table Once again i tried using the ' OR name = ' in the query for concatenation and failed again.
I an trying to create a data entry form (IndividualsEntryFm) to input data for fields such as (First Name),(Birthdate) etc., these to be saved to the (IndividualsTbl)
I also have another table (NamesTbl) which has family names etc. The two tables are linked by a (MainID) field. I want a combo box on the individualsEntryFm so that I can select the family name. Then I wish the empty fields for the IndividualsTbl to be available to enter data.When I press the save button I then want this data saved, together with the MainID from the combo box to the IndividualsTbl.
I have set the IndividualsTbl with a (PersonID) field as an auto number each individual therefore has a unique PersonID but may well share the MainID. I'm trying to link many people to the same address.
I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
IIf(IsNull([Forms]![Form2]![MaxDiffInput]);[Maximum operational pressure (bar)];[Maximum operational pressure (bar)]>=[Forms]![Form2]![MaxDiffInput])
However, is not providing any result when the input field (MaxDiffInput) as a value.
I was assigned by my manager to design an Access database system that is able to import all data from excel file monthly and creating charts & tables to analysis how each sales people and industry perform.
We originally have a big excel master sheet that has more than 10 sheets. I tried to import the current excel into access, but then i realized that this is not gonna work. because for next month, there will be new data and I can't do the whole import process over and over. Plus, after this system is designed, the users will be someone who has no knowledge in access, so i need to create a user-friendly system for them to use.
My questions is:since the data is always cumulative number, if I imported current excel file into access, when the next month comes, how to update the new data into excel. p.s. EXP. Mike's sale volume is different each month, and with the access system, for that column, it will be a cumulative number, like the total from the month of November to this month. how do i achieve this kind of update/import goal?I tried to link the excel to access, but by doing that, I will not be able to set relationship or change the attributes of any data type in access.
I want to input data number such as 0.5 in my table, but it doesn't work. I already fill field size : integer with format : Standard with Decimal : 2, but the result is always 0.00 not 0.50 as my expectation. How to define that in my table?
View 5 Replies View RelatedCan anyone please advise how I may be able to achieve the following?
The database in question is used to store info about members of a radio control model flying club.
I have a table (members) containing members personal details names, addresses etc and a second table (club_membership) containing membership info in the Fields year, membership_cat, insurance_cat. Each member has a record in the second table for each year they have been a member.
To simplify the renewal process (typically most members renew at the same time at our AGM) I would like to use a form which lists all members names with a check box (renew) which is checked if the member renews his/her membership. For every member checked I would like to automatically create a new record in the club_membership table for the next membership year (2007) by copying over values for the fields membership_cat and insurance_cat from their record for the previous year.
Many thanks
Russell
Hi, I try to simplify what I did in Access as follows:
Name Of Company----Jan----Feb----Mac----Apr
------A---------------3-------3------4------5
------B---------------2-------3------3------2
------C---------------5-------6------6------5
------D--------------
Note: No data inserted for Company D for Jan-Apr
Question:
1. If the list of companies are longer (say thousands), how can I make a query to show company that make no transaction in Jan - April ??? AND
2.How can I make a query to show all the companies (with and without transaction) together?
FYI: For Qusetion 2, I can only get the filtered result shown for Company A,B & C while no Company D listed (coz no input/data??). Pls help me, TQ
Max
Hi,
Just a short introduction. I am rather new to this forum and also a beginner to Access development. I am currently working on developing a database for CRM purposes for an organisation.
I have created a form for ease of viewing and inputting data. However, the problem I am encountering is that some of the fields does not allow me to add new entries. All the data seems to be drawn from the right sources and any changes made to the existing data is reflected in the underlying tables but it just doesnt allow me to add new data for some fields.
I have attached a compressed version of the database and I would very much appreciate if anyone could offer any assistance on this issue.
Go to Forms (tabbed)
Fields where data cannot be inputted :
Relationship Info - Service, Relationship Info
Meeting Info - All fields
Client Info - RelationshipNum
Fields where you can input data
All the rest are working fine
Thank you very much for your help!
In my data input form I have several boxes. In one case i wish to allow data to be input if a certain box is marked. that mark will allow any of six boxes to be checked. This works for me. However, in the event that the certain original box is marked in error, how do I allow the box to be unmarked and make the six other boxes closed to input?
such as: (box) do it
(box) one (box) two (box) three
box do it is marked. It allows any of 1,2,3 to be chosen. Box do it is marked in error and reclicked to blank. Boxes 1,2,3 need to close up so they cannot be marked. How do I get them to close up?
Thanks
I have developed an access database whereby multi users are using via front end application. I have split the database into back end and front end.
Problem is each time i make a change to the front end, I have to go to each pc to update. Apart from this, any other programmer can easily see all my workings and can copy my database.
I want an option whereby when i make any changes to the front end, then the user will get the change upon the next login. I was thinking of using the front end over the local intranet web browser. will have one front end and all users will be connected to that. any changes i make will be on that front end and they will see the change upon next login.
My other problem is that I do not want the front end to be stored at the user's end as any programmer can access my tables, forms, queries, reports, macros even if i disabled the navigation option.
Hello there,
In Table A I have a field called Pin Number.
I set an input mask for this field, Pin Number.
Now I have imported a huge number of records into Table B from Excel which also contains Pin Numbers.
I need to link all fields with Pin Numbers, so my question is:
How can I change newly imported data fields to conform to a previous input mask?
many huge thanks, :)
I have excel spreasheet that have dates on them. But the dates are formatted as general so they are really only numbers to Access when I link the spreadsheet to a table. I was hoping that I could create an input mask that would make Access recognize that the numer 20070912 is really September 12, 2007. I can't change the structure of the spreadsheet because the data comes from a data service. :mad: But maybe I can translate this number into a date in Access? Can you geniuses help me create either an input mask or some process besides using:
transdate:
DateSerial(Val(Left([ddate],4)),Val(Mid([ddate],5,2)),Val(Right([ddate],2)))
to change the number into a date? I was hoping that I could just create an input mask yyyymmdd and then Access would recognize it as a date, but it seems more complicated than this. I need to use the date() function for further analysis so it has to recognize the number as an actual date. Thanks for your help.:)
I have a query which is pulling its data from a form, which in turn is pulling data from a table. When I select 0.82 on my form, my query runs fine. But when I select 0.826856 from my form the query does not return any result.
0.826856 is available in the table and the pull down list in the form. The data format for all my table, form and query is General and decimal places is set to 8.
Could you plz tell me why I am not able to run the query using 0.826856 ????
Hi,
I try to input data in table 1 with a form that is based on a join from table 1 and table 2.
I do a right join, so if there are no records for the join condition in table 1, null values are created. My idea is to change these null values, so 'real' values exist in table 1.
Can anybody help me, or show an alternative way to do this?
Thnx in advance.
Freak
I have a button that will do two different functions 1 to open a form and 2 input data into the classType field. In order to do this would I need to use a Macro with setValue? How do I do this?? When the button is clicked input CPP in to the classType field of the EqTable.
Please Help!!
Jessie
Hiya guys! This time around I need to know how to use a command button to input the date/time into a text field by the event OnClick.
I would have thought by inputting: VisitDate.Value=Date() it would have worked but no such joy.
Sorry if I sound really stupid! ;)
Thanks!
Jay
I have a form that sets criteria in a series of make-table queries that go on to power a report. I've set up a macro to first open the form and then run the make-table queries and then open the final report. My trouble is that before I can set the criteria in the form I'm getting prompted by the make-table queries.
How can I stop the macro so I can input data and then re-start it? I tried using a stop macro command but that didn't work out for me.
Thanks
I have a form, and on the form there is a Provider Rate which is a combo box, if the user select a zero rate, then it has the description please enter manual rate in box below. There is another box which the user can enter a manual rate.
how I can limit entry into these boxes, as currently a user can select a rate in the provider rate box and still enter something into the manual rate box. I want it so that if a rate other than zero has been selected in the provider rate box, then they can't enter anything into the manual rate cell.
I am having to develop a form that we can use in case in our web based inventory systems crash. I am not good with forms at all.
I'd like the data entered to go into a table I've created with 4 fields.
So Field1 and Field2 will stay constant until we hit a button that does a whole new person we are issuing equipment to.
Field 3 and 4 though will need to generate a new record each time we scan data into both those fields.
For example the data table would look like this
Field 1 Field 2 Field 3 Field 4
Person1 IDNumber SerialA SerialB
Person1 IDNumber SerialC SerialD
Person1 IDNumber SerialE SerialF
PERSON2 TheirID SerialG SerialH
Person2 TheirID SeriaiI SerialJ
If it makes a difference this form will be done in Access 2010. Once I get the data into the table, I can do whatever I need to do with it.
I am trying to create a database that will narrow the potential inputs you can have as you submit the data. For example; in field one I will have a column titled "Types of Food", in which the user will be able to select from "Fruits, Vegetables, and Meats". The next field will be "Raw Ingredients". I want to make it so that if the user inputs "Fruits" into the first fields the second field will only present the options "Apples, Oranges, and Pears", and so on.
View 4 Replies View RelatedI have two tables that have a list of user info. One table has a status field. I want to update the status of users that is in table 1 but does not match any user in table 2 with "text". How would I do that? I do have an unmatch query of both tables.
View 3 Replies View Relatedhi all,
got a wee problem... i have a number of graphs showing volume (x axis) and pressure (y axis) for particular pipe sizes.... (it's for pneumatic conveying)
depending on the resolution of the hand drawn graph there could be 100's of points along a single graph line (x and y points)....
My program basically needs to select a point(x and y) closest to the graph line that it intersects. (come on mech. engineers)
Problem is how should i store / input the graphs into the db file?
I'm trying to avoid have to literally input every x 'n y point per line per graph... there are about 20 lines per graph and a 100 odd graphs!!!
I was thinking of inputing a start and end point then use some sort of formulae to draw a logical line and pick up all the xy points i need, then store them...
Any thoughts?
Thanks
Hi guys,
not sure which section to post this so i hope here is ok...
ive made an input mask for a postcode field. problem is its really annoying having to click the beginning of the field to enter data in the correct area of the input mask. Is there a way to automatically set the cursor to the beginning of the input mask/field when it is clicked?
thanks, James
Not sure if anyone can help but I have an issue I would love to sort out.
Each week I load several excel spreadsheets into an access database (one table) in order that I can check for duplicates across previous weeks and that week (with in excess of 20,000 records on each excel sheet). I created a find duplicate query to identify the records so I can use it to obtain credits. Unfortuantely I am not in control of the data coming to me (or else I would prevent duplicates at source)
Im not sure if this is the best way to try and do this or not. Any comments are greatly appreciated.