If I remove the order by clause, it works. Now, I have been programming for 30 years and for the life of me, I cannot figure this one out. It is my first use of 'order by' on this ISP so I am assuming there is a server thing? Can anyone suggest to me what this might be? Thank you very much.
Just put some buttons together, and I notice when I click on them I get a box appear on the button itself. Strangely, some of the boxes are solid likes and some are dashed.
Just wondered what they are, and how to change/get rid of them.
:confused: Hi, I'm pretty new to this VBA stuff and do not know the correct foramt to access a table.
I have a form based on table1 which has a large number of records each with a unique primary key.
Very occasionally I want to add a new record and want to create a primary key within a specific range.
I don't want to use autonumber, so I have a single control record in a table (tblParams) with the LastRecordNo stored in it.
I want to programatically access this record along the lines of:
Private Sub Form_Current() Dim NewID as Integer If isnull(me.ID) then NewID = tblparams.LastRecordNo.Value me.ID = NewID + 1 tblparams.LastRecordNo.value = me.ID End If End Sub
Obviously this does not work, what should be the correct context please.
I have a form that requires date, picker id, no of pallets, and a unique identifier (which is date + no of pallets). On my form, there is an unbound text box (displaydate) that takes the date field and converts it into a number, format([date], "mmddyy") so that 04/15/05 looks like 041505. And lets say the no of pallets would be 1961.
In the unique identifier field, my code is: me.identifier.value = str(me.displaydate.value & me.nopallets.value) which should give me 0415051961. First problem is it doesnt put in the first 0 in the date, it puts 415051961. And I also wanted it to put in an - between display date and no of pallets to look like 041505-1961.
I tried the code several different ways such as str(me.displaydate.value & "-" & me.nopallets.value) which gave me an error.
So could you give me some suggestions? Thanks Noreene
I have a group of customers in a database (aprox 600) and a small group (<10) of people who service those customers. I am able to run a report for each service person which lists all the necessary customer info for any particular day and gives me a total on each report of the number of customers being serviced. What I want to do is generate 1 report that lists each service person and the total customers for that person. I would then like to have the total customers being serviced also on that report. I think part of my problem stems from not knowing enough about access when I started. All my customer info is in 1 table. Several of my service people are handling 2 groups of customers (distinction between groups is geographical). I over came that issue by combining my customer groups when I created my queries for each service person. I feel like I'm too far in to vevamp the whole system as I have forms created to input data and print reports with the help of macros. I did figure out how to create a report that has the customer totals, but the report shows those totals by geographic area and not necessarily by service person. The commands I used to get this are in header area of the report. Because of this it seems that I can't get a customer total. Any suggestions would be helpful. Thanks!
I have a form with a test list. Each test is a record with a yes/no checkbox, and controls for testdate and examiner. I don't want the user to be able to exit a record if the yes box is checked and either the testdate or examiner fields are blank. The code I am using is:
If IsNull(Me.Examiner) And Me.SelectTest = Yes Or IsNull(Me.TestDate) And Me.SelectTest = Yes Then MsgBox "You must enter an examiner and a test date for each test you select.", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data" Me.Examiner.SetFocus Cancel = True Else Cancel = False End If
I am having trouble with which event should be used with the code, however. In both before and after update I have had the following problem. If a user accidentally checks the wrong test and then unchecks it, they still get the message as if the box were still checked yes. Can anyone tell me why and how to avoid that?
Hi, I am using a wet and cold holiday weekend to learn about Access. I want to create a simple sales order entry function.
I have set up 2 tables Orders and Order Details. The Orders table holds order number, customer, due date, etc and the Order Details table is to hold the line items (product, quantity, price, etc). So far, so good.
I have created 2 forms and made the Order Details form a subform of the Order Header form. So far, so good.
I have 2 questions arising from this. 1. How do I get the cursor to tab from the last field of the header form to the first field of the details subform? When I get to the last field of the header form it just goes to create another order.
2. Is it possible, and if so where do I get info on how, to enter (and show) many detail lines for the one header. This is a normal sales order situation, customer stuff followed by 5 or 6 lines of products on the order.
I have two old (Windows 95) MS Access tutorial manuals, beginners and advanced but can't find the answwers there. I am using MS Access 2000.
Any help or pointers to where I can get the help would be appreciated.
Does anyone know of a listing or a publication/website where I can place an ad for an expert to develop an Access database? We need someone to do this work for our organization.
I work for a property management compnay and they have put me in charge of coming up with a way to go some what paperless. What they are wanting is a way to enter a work order for a particular client (i.e description of work to be done for maintenance). This would then need to be printed in a report to be faxed over to the workers. This info would then need to be pulled out and placed into an invoice where, where items could be entered such as items bought ets.
My company is willing to spend thousands for software, but I think it is insane. to do so.
I was thinking that surely this could be done in access. I have an understanding of how access works (forms, reports, tables, etc.) but I have never designed one like this.
It does not have to be complex and it does not have to have a lot of formulas, I was thinking that the total could be placed in field when the invoice info is entered. Only really calculation would be adding the total of the items.
Anyway is this something that can be done relatively easily by someone with my experience, or is this to complex.
I am an inexperienced user, part of my job is to up date data files daily, have been doing so for 7 months. Problem arose when I accidentally deleted a link and required IT assistance trying to locate where it had gone-- that's when we discovered that I was not updating the company-wide server but a separate shortcut that a previous (and briefly employed individual had created).
Now IT and I tried a few tricks to combine or join the data I had entered (this could be history of up to 2000 assets), but have not figured out just how.
I have backed up my data on a thumb drive to prevent loss in case of my hard drive accidentally failing or crashing due to weather, now I need advice on how to put that data into the main Access data base--w/o having to do it all manually again.
I have a situation. In completing our Annual Report for our Accrediting body for my school, it requires us to complete a 'Completion and Placement Chart'. This chart was provided to us in Excel. The problem was that we needed a system to automaticaly update this chart as the year progressed so we can visually see our completion and placement rates. With this, I created an Excel sheet with the Completion and Placement Charts and made it so it can be updated as I entered a students information in.
Although it does calculate the data properly, the process is extremely ugly. It is terrible in appearance, function, and by all means, far from user friendly (I understand it, but it takes some explaining to do for someone else). This Workbook that I created houses almost all the information I need with the exception of it being entirely unsafe and far from being secure and data entry is a nightmare.
So, this brought me to Access. I created a database for this purpose (first time using access as well). It works like a dream in storing all the data that I need and keeping it safe. I needed to have a 'Placement Tracking Document' for each student outlining the students information for the accrediting body. This was done wonderfuly in Access and could not be accomplished through Excel (that I know of). However, I NEED to have Access tally all the information in the 'Completion and Placement Charts' like the Excel Workbook does. I need to keep the exact format of the 'Completion and Placement Chart' because it is a standard form in Excel and I can not steer away from it.
How can I get my DB in Access to comunicate with the 'Completion and Placement Charts' and have it automatically tally like my workbook???
I have attached some screen shots of my database and the entire Excel Workbook (Its edited because of confidential data) so you can understand a little of the chaos.
I rarely used Excel and never used Access until now. It took me nine months to create what I have because I was learning as I went. The books that I purchased to help me out do not touch this subject nearly enough (Access Cookbook, Access for Dummies and VB & VBA in a nutshell).
My point is, any help or direction in where to get thorough information on how to acomplish this is more then welcome. Any comments on what I have as well is more then welcome (negative or positive).
Thank you thank you thank you thank you in advance!
Oh, and use the buttons in the Excel Workbook to navigate.
The question is whether or not to normalize the fields "Place of Birth/Place of Death" in my 100'000 records famous people data base. So far, 15'000 different birth places are deployed. Only 3'000 of them are used more then once, that is to say from 2 to a maximum of 1000 times (New York City). 12'000 birth places are therefor used only once up to now. So, a table of 15'000 places would need a 3-digit (all characters used) or even a 5-digit (numbers only used) identification. In the latter case the ident would often be longer than the returned value (Wien, Oslo, Rom, it's in german, you know). Furthermore, instead of just entering a birth place like "Novodny Chomarowsky" I had to search the table of places whether or not the entry has been used yet. Please supply strong pro-normalization-arguments.
Good Morning, I am new to Access and I welcome expert guidance on the relationships of my database. The database will be used to track employee training and although I've enjoyed limited success so far I'm hoping that eventually I'll get it right.
The database has to do just a few things so it doesn't have to be very complicated. It basically has to store employee, training event, and completed training event information. The attachment shows the tables and their relationships.
The are basically two types of training that the database needs to store. The first is training that applies to everyone and the other is training that only applies to certain job specialties. These types of training are identified by the ProjectMandatoryCode and the JobCode respectively.
The ProjectMandatoryCode is a default value that is automatically entered everytime a new employee is entered into the database (EmployeeInfo table) and it is also a default value when a training event is entered into the (TrainingEventInfo table) that applies to everyone. The employee JobCode is a selection when a new employee is entered into the EmployeeInfo table.
When the user wants to enter a training event that is job specific, they are able to select a JobCode from a combo box/option group and this code is written to the record. Selecting one button from the option group writes a default value (ProjectMandatoryCode) to the TrainingEventInfo table while selecting the other button enables the combo box so that a specific JobCode can be entered against the event.
Everything works fine except... so far I've been unable to produce a report that lists every training event that pertains to the employee whether it be ProjectMandatoryCode or JobCode related. I can query and produce completed events or a list of what events are required but nothing that is complete. Ideally, I should be able to produce a complete training plan for the employee which shows all necessary events and where the employee has completed the task I should be able to show the DateCompleted.
This failure has led me to doubt my table relationships since all my efforts at various query joins have failed. Is there anyone who could enlighten me so that I could learn from this, complete this task, and apply this new knowledge to future databases? To the person with the answer, thank you in advance for your assistance. Respectfully, Dale Gagnon
I am trying to execute the SQL below (tested in other programs, works fine) but access is giving me the "join expression not supported" error. How can this query be used in MS Access? I have tried breaking some of the joins clauses into separate queries, but I can't get it to work and it is making things much more complicated. Also note that moving the join conditions to the where clause could impact performance (this is a very large DB) Could I execute this via code somehow? Thanks.
SELECT lp.loan_id, lp.days_delinquent, lp.current_balance, pc.product_name FROM mtgwl.loan_payment lp inner join mtgwl.deal_loan_relation dlr on lp.loan_id=dlr.loan_id and lp.time_out='9999-12-31-23.59.59.0' and lp.as_of_date='2006-04-30' and dlr.time_out='9999-12-31-23.59.59.0' and dlr.type='DEAL'
inner join mtgwl.deal_loan_relation pdr on dlr.deal_id=pdr.loan_id and pdr.time_out='9999-12-31-23.59.59.0' and pdr.type='PRODUCT'
inner join mtgwl.product_control pc on pc.product_id=pdr.deal_id and pc.time_out='9999-12-31-23.59.59.0' and pc.product_name='GSAMP 2003 AHL'
I am trying to execute the SQL below (tested in other programs, works fine) but access is giving me the "join expression not supported" error. How can this query be used in MS Access? I have tried breaking some of the joins clauses into separate queries, but I can't get it to work and it is making things much more complicated. Also note that moving the join conditions to the where clause could impact performance (this is a very large DB) Could I execute this via code somehow? Thanks.
SELECT lp.loan_id, lp.days_delinquent, lp.current_balance, pc.product_name FROM mtgwl.loan_payment lp inner join mtgwl.deal_loan_relation dlr on lp.loan_id=dlr.loan_id and lp.time_out='9999-12-31-23.59.59.0' and lp.as_of_date='2006-04-30' and dlr.time_out='9999-12-31-23.59.59.0' and dlr.type='DEAL'
inner join mtgwl.deal_loan_relation pdr on dlr.deal_id=pdr.loan_id and pdr.time_out='9999-12-31-23.59.59.0' and pdr.type='PRODUCT'
inner join mtgwl.product_control pc on pc.product_id=pdr.deal_id and pc.time_out='9999-12-31-23.59.59.0' and pc.product_name='GSAMP 2003 AHL'
I have a basic Access Form that contains a small number of questions.
Question 4 is a Yes / No combo box
If the user enters Yes, I want the focus to go to Question 5 If the user enters No, I want the focus to jump to Question 4.1 Completing question 4.1 will take the focus to question 5
I'll be hornswaggled if I can figure out how to do this.
I have a receipts table with columns- Customer ID, Receipt #, Amount Paid, Date and in another table- customer Id, First name, Last name, Contact # ,Total Cost. How can I get the balance owing by a customer who have made several payments.
I hope someone can help me, I have a database compiled from different sources which means that information in fields that need to be linked are written differently, meaning that I can't just simply make a relationship between them.
The two tables I would like to link are, Table 1 has the fields OCCUPATION and AMOUNT and contains over 740,000 records. Table 2 has the fields COMPANY_NAME and TICKER and has 500 records.
I need to find a way for all COMPANY_NAME fields in table 2 to be cross-referenced with the OCCUPATION field, so if COMPANY_NAME is part of the string in the OCCUPATION field then the TICKER (of that company) can be attached to the record in table 1 (specifically to AMOUNT).
The problem is that the OCCUPATION field is not written in a standard form and can include either only the occupation, only the company name, or both in either order.
I can make a seperate query for each company by using as criteria "like "*[COMPANY_NAME]*", but then I would have to do this 500 times!!! Is there a way to automate this?
The final purpose is to link the AMOUNT to TICKER so as to find the sum of all the amount associated with a company.
I really hope you can help, I have little programming knowledge and it will save me the time of making 500 seperate queries. The final use is for my thesis studying private contributions in the american elections.
I've been here for two months and answered over 400 questions. I'm seeing a pattern here, and I have decided to post the best ways to make sure you are never answered.10) Blood, sweat, and tears are for losers. Spend no time figuring things out on your own.9) Announce almost ceremoniously that you have no VBA experience.8) Fail to use the search functions of this forum.7) Try to put every example you're given into a macro.6) Believe that this is the only source for answers, because Access Help provides nothing. Nothing, I tell you!5) Have us write your homework for you and then complain when you don't understand it.4) Post in German.3) Have a belief that your particular problem is the most important issue ever, and post in every sub-forum possible. Then bump it.2) Be vague, and then be belligerent.1) Have no appreciation for the people that are answering your questions in the first place. I know this sounds cold and mean and otherwise uncaring, but we are here to answer questions, not give you fish, as it were. This is a place to say, "I've done most of the work, but I need help over this hump," not "I have this crazy project/idea and I want you to write it for me." I've written over a dozen example DBs for people, and I know that I know what I'm doing. But, myself along with perhaps the majority of others that answer the majority of the questions, I'm asking on their behalf to please take the time to figure some of this out. If you can't do VBA, you are using 25% maybe of Access. If you can't understand your own DB, then asking us to fix it is a solution, but not a learning opportunity for you. If you can't understand what I'm getting at, it's that so many posts are the same. Again, I'm not trying to be an ass, but if you want to be a decent Access programmer and a programmer in general, then please, take the time to learn it. There are plenty of books (look at the bottom of this forum) and there are plenty of resources. I don't think any of us are teachers by trade, but the best teacher for something like programming is always trial and error. Programming is a language, a passion, a "something you figure out" if you will. If you're spoon-fed the entire time, you don't learn. I will continue to answer as I always do and I apologize if anyone was offended, but jeez, make sure you've tried everything offline before asking for help. And don't forget to search first, both here and Google.Sorry for the long read. :)
I currently have a database and the powers at be want me to enable a button that will count how many times in the database a particular month is used. So for example how many times did we visit a particular place in September/october etc.
Is there a way of doing this that i can't see, I have tried searching the forum but to no avail.
Please Help. I am tring to get the Month to date, year to date and week to date total in a query. I added a parameter for a date range. Instead of giving me the mtd, wtd, ytd, I get the whole months daily total. Can someone tell me what query caculation do I need to just get one number instead of daily numbers?
Why so many repeats of the same answer Thank you MWalt for your reply about finding the stock in hand. It works, sort of. It gives the answer but I end up with each item being repeated about 141 times. So instead of ending up with a list of about 140 items, I have a list of 20000+ because each item is repeated. I'll include the code of the first query that you suggested called qryNotItem...where do I go from here.
This part works, but when I do the second part I run into an error.
SELECT [Equipment Inventory].EquipmentID, [Equipment Inventory].[OT Equipment type], [Loan details].LoanOccID, [Loan details].[Return date] FROM [Equipment Inventory] INNER JOIN [Loan details] ON [Equipment Inventory].EquipmentID = [Loan details].EquipmentID WHERE ((([Loan details].[Return date]) Is Null));
