This is what I have so far
Def: IIf([30 day notification]<"1/1/2005","h")
What I need to accomplish is, any records less than 1/1/2005 show me "H". It does show up in some but not others. This is a date field. What is it that Iam doing wrong.
Doing my best at learning code. I don't know how you people learn this stuff
Thanks
hi. I am trying to write a code to sum the total cost of all jobs that are selected as 'yes' in a combo box.
Once the user selects 'yes' in a combo box, they enter in a cost for that specific job. Each client may have 10s of jobs. I need to write a code that will sum up all the 'yes' selected jobs for a client. I know it should be an 'if' statement but I can't seem to get it to work. I need this value for a report. How would I do this and where would i put the code.
Help is appreciated.
Here is the code that I was thinking of, I don't think its right but it might be a start.
Dim Count As Variant
Dim CountImplementedTotal As Variant
CountCost = 0
CountImplementedTotal = 0
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordSet("ECM Details")
Do While Not rst.EOF
If rst![AUDIT ID] = Forms![audit info]![AUDIT ID] Then
If rst![Has Measure been Selected] = "YES" Then
Count = Count + rst![Total Measure Cost]
End If
End If
rst.MoveNext
Loop
Text47.Text = Count
creating a small loop to run code.For each record from AREA with PLANTPROGRAMID = VARIABLE Then...In this example, my table is called Area, my field is called plantprogramid, and my variable is defined earlier in code.
View 5 Replies View RelatedI have tried the count(field name) and the sum(field name) but neither of them are giving me the grand total only the count/sum of that row. Here is my code, please look - I'm sure its something small and stupid
SELECT [qry_Refi_Yield].[LOAN NBR], [qry_Refi_Yield].[BORROWER], [qry_Refi_Yield].[PURPOSE], [qry_Refi_Yield].[FUNDED STAMP], [qry_Refi_Yield].[HSS], [qry_Refi_Yield].[UNIT TEAM], [qry_Refi_Yield].[Cycle Time], [qry_Refi_Yield].[Total Yield], IIF([Cycle Time]<=30,1) AS Yield
FROM qry_Refi_Yield
GROUP BY [qry_Refi_Yield].[LOAN NBR], [qry_Refi_Yield].[BORROWER], [qry_Refi_Yield].[PURPOSE], [qry_Refi_Yield].[FUNDED STAMP], [qry_Refi_Yield].[HSS], [qry_Refi_Yield].[UNIT TEAM], [qry_Refi_Yield].[Cycle Time], [qry_Refi_Yield].[Total Yield]
ORDER BY [unit team], [hss], [funded stamp];
past tries: count([qry_Refi_Yield].[Total Yield])
count(borrower)
Hi,
I was using access 2000 to build a small office app. It will be on a network and will generally be used by only 2 or 3 people(max). I was wondering if the default jet technology would be good enough for this. If it isn't, what should I use? I looked at other options such as msde and ado but it's a bit confusing when you're new to this stuff.
Thanks,
scratch
There are times when it is just necessary to have a little rant, a canniption if you will.
For the last few weeks I have been working off-and-on with a new database for a salmon hatchery. One of the forms I designed contained a subform in continuous form view that flickered inordinately when first loaded. Now, there's a lot of code going on behind controls etc to run sql updates or deletes or appends depending on what the user clicked. There was also some code to alternate the row color of the continuous form. I spent literally days trying to find what part of my code was causing the problem.
I swore a lot.
I searched.
I eventually gave up as other priorities began to arise at work. I resigned myself to my fate.
Then I discovered, quite by accident, that the problem arose by the use of some unassociated labels being present on my form. I removed them, replaced them with labels associated with a control, and presto: the form flickering/reloading magically vanishes.
Great.
But what a colossal PITA for something so bloody stupid. There's absolutely no reason why this should occur IMHO. Honestly: it's hard enough to learn VBA, SQL, and relational db design without having to deal with poorly documented glitches like this. There are days when I think I'm finally starting to get the hang of Access, then something like this comes along and I want to send a lynch mob to redmond! :mad:
Ok, end of rant :) Hope the solution to my problem comes in handy for someone else someday.
Hi,
What i want to do is have a field called "monthlyhourcount" in my query that totals all the hours in a field i have in a table, the field in the table is called "hours worked" were a figure is entered, i need my query to give a total of all the figures entered. so far in the expression builder i have :
monthlyhourcount: [JobListMonthly]![Hours Worked]
when i run the query with this it gives me the seperate values, i need one total value.
any ideas guys?
Thanks
Conor
I'm fairly new to Access (2000 is the version im using), and I'm not sure how to do several things within a form:
1) make cursor in a text box jump to the front (left) when clicked on
2) Lock (and grey out) a text box once data has been entered into another
3) assign a field to correspond with another, and to have this shown on the form (ie: having a list of names and their corresponding phone numbers assigned to each; then on the form, having a combo or list box with the names that a user can select- once a name is selected, their phone number appears automatically in another text box)
4)Locking screens or subforms when moving on to another (either by pressing a button to open another form, or by selecting a field in another subform)
thank you in advance for your help and advice
Hello,
This will probably generate a few good laughs but here goes!
I have been given an Excel file with 6 fields about 20 chars in each field.
This file is a whopping 250 records. I would like to be able to add, change, delete and search records.
The DB would need to be shared by 2 users on a home network. Both are using Windows XP.
What would be(heres your chance!!!) the easiest solution for this?
This is for someone who wants to keep it simple. If it was simple though, I guess I would not be posting this!!! Haha.
They best part is they have MS-Works on their machines.
Any help or better yet a completed solution would be much appreciated.
Thank you
Take Care
Tom
hello all
i need a small thing to change in a module but am not too familiar with vb so i reached a dead end, here's the story:
i have a 'dog pedigree' database that i was working on, now there's a report where a dog's parents, their parents, and their parents need to be shown
that turned out to be out of my league so i paid a guy to do it for me, it worked except he didn't do exactly what's need and i can't get to him now :S
anyway, in the report page,
you can notice that the name of each parent is shown, but i want the registration number of the parents to show as well,
i tried modifying the module but it didn't work (am not a vb user), so can anyone help me add the reg number to the name of the parents?
also, in the report, the dog's color is appearing as a number instead of an actual color so if that can be fixed as well ...
to understand better:
record 1 in dogs, click the preview diploma button
you'll notice "tat" as dogname and "ry245" below that as registration number
now sire is "pat" and dam is "titu", i want their registration numbers to show as well (and same for the rest of the parents)
i uploaded the file here for a closer look:
http://www.designcrafts.org/dogs1.zip
thanks :)
Okay, brief overview.
We currently have 2 MDE front ends to and oracle box. Database is about 25Gigs and have been minimising the traffic by optimising the queries. However we have always noticed some very strange problems now that our user base is about 150. They seem to become more apparent when the MDE files reach a certain size. I have implimented an autoshut down and inactivity shut down protocol into the access front ends but I would really like to know why the MDE files are getting so large. They are normally about 5MB but get to in exccess of 130MB. The autoshut down is forcing everyone out which compacts it overnight!
When these databases reach critical mass, peoples data seems to be getting mixed up and one persons form is mixing data up with anothers!!!!!! What is casuing this? We are using the front ends over citrix too, is this the root of my problems. I am going to be copying the MDE file to each citrix box to speed it up and reduce the number of users on the mde. Ideally I would prefer an mde for each user but that is impractical.
Really my question is this, Why does an MDE file with no tables except linked via ODBC increase in size so dramtically? Is there a leak or somthing somewhere?
Thankyou in anticipation of any replies!
Marc
I made an Access DB program in my PC. I have 17 inches monitor and so I designed everything to fit in that screen. When it was deployed in the user's computer, it did not fit in his screen as he has only 14 inches monitor. The command buttons and other things were not visible and were out of view of the screen. I changed the screen resolution but it made the fonts look much smaller. I have used 12 pt fonts in my Prg. Is it possible to correct my forms so that it automatically fits in all sizes of screen?
View 2 Replies View RelatedHi Access World Forums,
I'm an audio/video technician at a college and wish to use Access for a simple camera booking system, but do not have time to go through all the extensive help menus and tutorials about this program, and none I have viewed seemed to help.
I want four columns in the table. First, the user inputs a student ID number. Now my first problem is that I want the student name to appear in the 2nd column after entering their number. I don't know where to create the list of names or how to link them to their student ID numbers.
My second problem is I would like the 4th column simply to display the date seven days from the current one (as in whenever the db is used) for the return date without having to input it manually.
Any help towards these 2 problems would be most appreciated, and thanks in advance.
Edit: I'm using Access 2003.
a link is created between 2 sites
a circuit can travel over any given site
dataflow is from the left going towards the right
if site b fails, so does site c, d, e, f
but A still lives on
so how do I create a relationship between the tables to answer the queries correctly?
a site has many links, and a link has many circuits.
a circuit has many links
its a many-to-many relashonship.
I created a junction table to provide for the link circuit relationship.
I just need to know how to ask if site c is down, give me the link and circuit that is effected.
Or give me all the links and circuits effected.
The other site_1 table is there to eliminate data duplication for start abd end site which is just a site in the sites table. Its a form of normalization.
Any help would be appreciated.
Thanks,
-Jon
Hi all
I have the following query that will show the date, studentname, number of classes marked absent for that date.
i also want to show the total number of classes in the day, this is needed to make a quick comparsions to the total number of classes missed per day.
This is the code
SELECT Attendance.Date, Count(Attendance.Attended) AS CountOfAttended, Student.StudentName, Count(Attendance.ClassID) AS CountOfClassID
FROM Student INNER JOIN (Groups INNER JOIN (Classes INNER JOIN Attendance ON Classes.ClassID = Attendance.ClassID) ON Groups.GroupID = Attendance.GroupID) ON Student.StudentID = Attendance.StudentID
WHERE (((Attendance.Attended)=' Absent'))
GROUP BY Attendance.Date, Student.StudentName, Student.StudentID
HAVING (((Student.StudentID)=[Forms]![Frm]![cboStudent]));
This shows the date, the number of classes the student was absent for, the student name, the total number of class in that day.
But my problem is that it only shows the total number of classes marked absent again..... IS there a way to exclude the where clause from the Count(Attendance.ClassID) AS CountOfClassID part of the code...
Help is is welcome
Thanks
Chris Lynch
well i created a relationship between 2 tables where:tableA = invoice datatableB = company IDsi have 2 questions that i have been unable to resolve on my own. 1. i want to create a query where i am able to find all of the invoices from a single company using it's ID and add up all of the account payable/recieveables i learned how to add all of the values on a field but i don't know how to add the values on a specific row. so while i can add all of the ap/ar on the table with all of the information, i can't add the ap/ar of a single company despite the fact that my table sorts them apart (because i don't know how ><)would a code like iff([company id]= "#",add) be close to what i'm looking for? because that's all i'm been fiddling with to try to get my query to do what i want. so far i'm stuck with: AR: IIf([company ID]=1,[AR$$],0) whereas 1 is the ID of the specific company i'm trying to pull records for and AR$$ is the field that i'm trying to pull the information from.2. find a way so that i can get my table to print all of the records for a specific company based on the ID. so for example, if i wanted all of the invoices from company #9 then i would pull them out and print them. i tried expanding the + sign on the company ID table and printing it, but it obviously failed -.-"i would really appreciate any help that can be provided to me! i just started access so anyone that can link me to an article related to this would be greatly appreciated because i don't know the terminology/keywords that i should be searching for in the article page that i'm using for this case.
View 7 Replies View RelatedHey all,
just have a small question...
Normally in a form if you use the '&' in front of a objectname or something like that it will underline the first following letter meaning you can use the 'ALT + letter' combination to use it.
Now I want to use the '&' sign in a label, without having it do anything like that. I just want it to display the sign, anyone have any ideas on how to do this?
Cheers,
Thomas
My client wants a company name that appears on multiple forms and reports to show as font name Garamond which is fine... however, he also want the font to appear as "Small caps". In Word and Outlook, under the Font submenu, I can check Small caps and get what he wants... lower case letters are shown as Small capital letters. Is there a way I can make the same settings on labels in Access without installing a new font on each workstation that runs the database.
View 3 Replies View RelatedOk here is the problem I have, I need to have a way for an end user to select infomation on a form that is pulled from a table and displayed in a way that they can select more than one item (I am assumeing a list box will be best) then write those multiple values to another field(s) in another table.
Here are some of the problems with that:
1. The user is defineing the amount of possible selections
2. The user can select anywhere from 1 to any number of selections from that list box.
In case I confused you on what I am trying to accomplish let me give an example.
User1: fills out form 1 with 5 different records, which get written to table 1.
he then goes into form 2, form 2 has a list box displaying the 5 records from form 1, he selects 3 of those use the click and ctrl key method.
He then saves that record and Access writes all the data collected in form 2 as a record in table 2.
Any help would be appreciated.
http://img64.imageshack.us/img64/5269/menubalkem9.pngdoes anybody know how to change the picture of the database to somethng else. Now you see that pictyue of 3 forms I want to set something of my own.please assist
View 2 Replies View RelatedHi,
i seem to be geting this message ( attached ) everytime i load the form for the first time. once i click ok, i can the enter data into the field and save as normal. if i load the form and got to a different field, it seems to be ok and the mesaage does not come back but it is definately there when i first click in the same field.
i have checked the form properties, field properties and code but i cannot see anything.
what usually causes this?
the text is the right size
the form is not in design view
i cant seem to find if the field is read only but after the box, i can enter data so that should remove any read only issues.
its vv frustrating.
regs,
Nigel
I’ll do the best I can to explain what I would like my program to do, maybe you can give me a little bit more guidance.
Alright – I have a database with the following 3 tables
Assets – Asset ID (PK)
Inventory – no PK
Preventative Maintenance – no PK
I created corresponding forms for each table, each with the same name.
Here is what I can do as of now:
Assets form - I am able to enter the asset #, description, serial #, hours between PM (preventative maintenance), and starting hours (same as an odometer) which I have set to 0.
Inventory form – Able to enter Part#, Asset ID (this allows the user to distinguish which asset this part was made for), Part Type, Part Description, Vendor, Location, Quantity, Individual Price, Total Price, Tax (Of the currency fields I just mentioned, I have it setup calculate the Total Price by multiplying the quantity * individual price. I also set it up to calculate 7% sales tax within the form)
Preventative Maintenance – The first field is PM# (auto number), Asset ID (distinguish which asset is being worked on), Date Checked In, Time In, Time Out, Current Hours, PM Performed, Part #, Quantity Used.
Well hopefully I haven’t just thrown a lot of information your way. So now that I have given you what my project can do, let me try and explain what I want it to do. And please keep in mind that I have been trying every which way I can to create the proper relationships, or foreign and primary keys, but that concept still passes by me at times.
I want to be able to know that if I have 10 tires for asset 1500, and I perform a PM on Asset 1500 and use 2 tires, that I will have 8 remaining. Maybe I just am missing the simple solution here, but I have been trying for days. Also, that value 8 should be the new on hand value within the inventory. I need it to update.
If I have 10 tires with part # TIR1500, and then I receive 2 more a few days later, how can I get the quantities to correspond? It seems as though I was able to work this out through queries to get the correct info, but due to the relationships I tried setting up, when I got to the PM and the quantity used, it threw my totals off.
Hey, I'm doing Computing as an A-Level and we have project to do over Christmas and I need some small help writing the queries.
Basically I have to do an Examination's Board Database. It has to store the Candidates that are going for exams, what their Centre (School) Number is, what exam they're taking and so on. It has to be able to produce four reports.
One of these reports have to show every examination that is taken for a particular centre. I am using the variable "Ce_No" for Centre Number. And I have five different Centre Numbers being used. I want it so that when I open my Query it asks me to type in a centre number, then when I've typed it in and clicked "ok" it will show me every exam being taken by people in that centre. I'm hoping this makes sense.
In my query I have added all the information I want to use, I know I have to type something in for "Ce_No" in the "Criteria" bit, but I don't know what... I'm hoping this makes sense.
Basically, I want it so that when I type in a number, everything thing with that number as it's primary key comes up but nothing else.
Please help! :D
Thanks,
Daryl ^_^
I have a combo with the LimitToList set to yes so I can add new data to the underlying tbl. This works fine with one exception. When I enter the data I occasionally want to enter it in all Capitals but when I select Ok to add it to the tbl the first letter is a capital & all others change to small caps. I'm a little rusty on Access at the moment so I need a prompt pointing me to what I need to change to fix my problem. I'm thinking this is a formatting problem? :(
View 2 Replies View RelatedHi All,
I have finished up a database that I will be using to store code snippets. But I have a couple of nagging questions that have been bothering me.
http://img63.imageshack.us/img63/7752/untitled1cg.jpg
In the right hand portion of my dialog I use a subform to display key words for the code. Is there a way to turn off the column and row headers for the form? Also is there a way to have a transparent background for the subform?
In order to add key words I need to open up another form, enter the words there, close that form and then select them from this dialog. It would be nice to be able to add keywords from this form. FYI, there is a many to many relationship between the key words and the code.
Is there a way to show just my dialog when the database is opened and not the rest of the Access application space? I have unchecked everything in the startup options already.
The last thing is I would love to be able to format the code section so that certain pieces of text are formatted. I am going to start messing around with the some of the free rich text controls but is there an out of the box solution that can handle this task? I was thinking about formatting the code in html and then displaying the html. Is this possible from within Access?
Thanks all,
Steve
Hello I have a database that opens in maximized form.
I also have another form that opens when a button is clicked.
What I want is for the second form to open small and not maximized like the main form.
Can this be done?
Thanks