I have a statement as follows:
Code:
ohc: IIf(([oh]/[csqty])>1,[oh]/[case_qty] & " (LTSU?)",[oh]/[case_qty])
The intention was that it would add " (LTSU?)" to the calculated number where it was less than 1 (or not a whole number). Stupidm, me didnt account for fractions greater than 1 (ie 1.566). Just wanted to know how I can identify all 'non-whole' numbers in the above IIF statement.
I have a field that sums pounds. I need to convert this to tons (rounded to the nearest 3 decimals), then multiplied by $67.50. This will calculate a fee payment.
This is what I have now:
Code:
=Round(Sum([Hazardous_Waste]/2000*67.5),3)
The total pounds is 2675.
After dividing by 2000, Access generates a number of 1.3375. It rounds 1.3375 to 1.337 which generates an incorrect final total. Not sure how to alter this to round properly.
I currently have a calculated field that will result in either a whole number or a decimal. From there, I need to set up a field that identifies if the previous field is either a whole number or a decimal.I have tried several different formulas, but always seem to get an error or a query that does not return any results.
View 3 Replies View RelatedI have a query (A) as the data source for a report. That query (A) has a another query (B) as one of it source files.
How can I tell if query (B) has no output, so I can stop query (A) and the report attempting to run.
I have two tables. One table is a list of classes with the number of enrolled students:
Class............StEnrolled
English 1A........6
English 1B........12
English 1C........20
English 1D........25
Reading 1.........4
Reading 2.........15
And the other table is a list of "combined" classes (meaning they are, essentially, the same class and should be counted as such):
Class...........CombinedWith
English 1A.......English 1B
Reading 1........Reading 2
Using the first table, I currently generate a query (and build a report) that displays class enrollment levels. What I need is for this query to identify combined sections from the second table and display them as such:
Class........................StEnrolled
English 1A/English 1B........18
English 1C.......................20
English 1D.......................25
Reading 1/Reading 2..........19
Using Access 2007
Win 7
Total_Time: Format(nz(DateDiff("n",[Start],[End])/60))
This is my expression in a query.
I need to only show 2 decimal places in the results field.
I want to print the results of a query where some values are whole numbers, some have 1 decimal place, some have 0.3333333 recurring etc. Problem is that in order to fit all the columns on a page the column width does not allow all the 0.33333... to display. So what I see is like this (there are lots of columns, I am just giving an example from one column)
891
833.5
######
I don't want to fix decimal places to 1 because then I would see 891.0 instead of 891. How can I format the number field so that whole numbers are shown without decimal place, numbers with 1 decimal place stay like that i.e. 833.5 is fine as it is. But display something like 313.666666666667 as 313.67 rather than ##### getting put in as not enough room for all the decimal numbers.
So, I am inserting new records into a table. The values in the decimals are being stored as zeroes.
Field Properties
Long Integer
Standard (I also tried Currency)
2 Decimal Places
When I remove the "INSERT INTO" part of the query, the decimal data is in the results. I started out not using the FORMAT function. Then I tried it using "Standard", "Currency", and "#,##0.00"
When using the carriage return how do I format the data so it will export with 2 decimals?
In the example below I need the Estimated Material Cost to export in in format: 500.00 --- NOT 500.
MTLCost: [Estimated Material Cost] & Chr(13) & Chr(10) & [C]
Access 2013
I've created a table for a college project and realised that one of my calculated fields isn't working. It doesn't produce an error message, it just doesn't calculate.
IIf([CycleSpeed]<10,[CycleTime]*236,IIf(10<=[CycleSpeed]<12,[CycleTime]*354,IIf(12<=[CycleSpeed]<14,[CycleTime]*472,IIf(14<=[CycleSpeed]<16,[CycleTime]*590,IIf(16<=[CycleSpeed]<=20,[CycleTime]*708,IIf([CycleSpeed]>20,[CycleTime]*944,0))))))
So I have this If statement that goes something like this;
Code:
IIf {ttblEQAnswer_FCLIT.ActionType} = 'Answer Affir Defense' andnot isnull({ttblEQAnswer_FCLIT.METPrep}) and not isnull({ttblEQAnswer_FCLIT.AttyInstr}) and
({ttblEQAnswer_FCLIT.METPrep})> date(1900,1,1) and not isnull({ttblEQAnswer_FCLIT.AttyInstr})
and {ttblEQAnswer_FCLIT.METPrep} < currentdate - 10
then 'Y' as [MET InstructNeed?],
I want to use the above formula to calculate a column. However, do I need to join the tables referenced above in order to get this formula to work? As it stands I'm getting an error that says "Malformed GUID in query expression".
I have the following Select Statement:
SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],
[code]....
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
I have a query with the following criteria in one of the fields:
>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())
fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.
>=DateAdd("m",-12,fom()) And <=fom()
Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.
Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())
I have also added the column name to each expression and it still doesnt produce any results.
I have a query which returns a calculated percent. I have ordered that in descending order, and now want to see the top 50. So (In Access 2010) I entered 50 into 'Return'. But it returns all of the records!
Is this because pct is a calculated field? How can I correct this? The SQL seems to be correct.
Code:
SELECT TOP 50 HeciFail1.POHECI, HeciFail1.POQTY, HeciFail1.FAILQTY, IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) AS PCT
FROM HeciFail1
ORDER BY IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) DESC;
I have made some accounts with the user-level security wizard, and i was wondering is there a way to know who logs in?
Im wanting to give a person preset information on forms and stuff.
Hi
I have my table set up to add today's date as a "date last updated" field. this works for all new records but I need to flag instances where a user has amended any existing records, so that on a daily basis I can filter out anything new based on the "date last updated".
Any ideas how I can flag changes to existing records?
Apologies if this is a straight forward question I'm fairly new to Access
Be advised that I'm a complete novice with Access, and have used it only to view Excel files with more than 64,000 records. Any help would be appreciated.
I have an Excel file of 10,000 records entered by salesmen of possible prospects, with salesmen, prospect name, address fields. Some salespeople have entered the same prospect and address information; the only thing different in these records are the salesman name.
I know how to import the file into Access. But from this point, how do I construct a query and generate a report that shows me ONLY THE DUPLICATE RECORDS? We would like to use this report to identify the double entries and the salespeople so they can clean up the database. I know Access (I have Access 2003) can create this query, I don't have a clue on how to do it.
One hint: A post on another forum suggested " Make a group-by query (grouped on all fields except salesperson). Add a count field. Select if count >1. This should do the trick."
I'm sure this is the answer, but I don't know how to "make a group-by query", how to "add a field", etc. Like I said, I know how to import the data. That's it.
This is extremely frustrating to be this close to the answer and not know how to finish it. Any assistance would be appreciated. Thank you.
Apologises if the answer is out there somewhere but I have searched to no avail.
I have a number of Forms which the user can jump around from one to the next. All the forms remain open with the form they are currently viewing showing on top. If they make a change on one particular form then when they return to a previously viewed form I want to disable or hide certain certain controls etc.
I know this would be simple to do on the on open command but as previously mentioned as I am not closing the forms after they are viewed, if the user returns to a previously opened form then this is not picked up.
Soooo, basically I'm after something like
When the user moves from one form to another already opened form for the new form to immediately know that it now has the focus and for it to do something along the lines of on open.
Any help will be appreciated
Is there any function or code that would display the workgroup in which the current user belongs simular to the function "currentuser()" which displays the user logged onto the db?
View 9 Replies View RelatedI am new to Access and just inherited a very large database to work with. Is there an easy way to figure out the relationship between each table and how they are linked? Thanks.
View 5 Replies View RelatedI'm familiar with screen.activecontrol but what I am trying to do is to activate an event relevant to the control currently triggering the mousemove event.
In the attached db, if you open the MMHighlight form and move the mouse over the boxes, as they are moved over they go yellow (mousemove event), whilst still hovering, click on a box and the first box displays the first and last boxes selected (mousedown event). release the click to trigger the mouse up event which clears the boxes. Broadly this is working as intended
The problem is the mousemove event which has to be 'personalised' for each control - highlighted in yellow
In the real application there are several hundred controls, so I'm looking for a timesaver - one option being to build a form via vba, but ideally some equivalent to screen.activecontrol and perhaps a third where I can replace [text0] with a generic name - a bit like 'Me' to refer to the form.
I have a table (Imported from Excel) with In and Out records.
Here are the problems I have:Each record has either In or Out data. I have an identifier to know which ones are In and which ones are Out
It might have some misses, either no In or no Out record for the specific work time. A worker can get in at the evening and get out at the morning of the next day. Might have more then one entrance/exit on the same date.
I want to have the record sorted so I'll have an In and Out on the same row (record) including holes for the missing ones.
I don't know if this can be done. I'm using Access 2010 and I have users updating information on a hourly basis.
Can I have a field that basically shows the last user that edited the form or saved it?
Hello
I have some data I need to clean.
It is in an address field the majority of the data is fine but there are some records with words that have been spelt with numbers:
2 9RAMBLEGATE
2 Forest V1EW
2 KEM8LE Close
2 BELLEMOT4TE Road
Is it possible using a query to find instances where this has happened so I can flag these?
Any help would be greatly appreciated
Tim
:confused:
I have a checkbox on my form that basically deletes a record when the user ticks the checkbox. What I want to know is how do i check whether the person is "ticking" or "unticking" a checkbox before any action is carried out?
View 3 Replies View RelatedI have a large database. I want to identify which objects take up the most space. Any way of identifying a list of each object with their relative size?
I can do this in sql server, but no in Access?