I am trying to create an expression in a query to sum only the # of hours a student attended between two date fields. I do not want to use a parameter because each student has different start and midpoint dates and I need to see all of them in one list.These are the fields I'm using in the query:
Student Name Start Date Midpoint Date Hours
It keeps giving me "0" or if I move the () around it says the correct syntax is [NOT]
Expr1: Sum([Hours]) between [Start Date] & [Midpoint]))
I am using expression builder to specify a field in a report but it is acting more like a filter.So I have a report based on a query. However I want to add a field that is not in the query but is in a related table - called tblAgent.
So using expression builder I select the tblAgent in Expression Elements and then select the field from this table. This creates the expression =[Agent]![AgentAddress] however when I try to run the report it asks for a Agent parameter? Do I need to go back to reports 101?
I created a query with one expression field that updates a user inputted date field on a form. The expression adds a certain amount of time to the field (usually six months) so I know when the next inspection should take place. Everything works great except when I put a parameter in the expression field. It will not return the property dates. If I simply remove the expression, and input the date manually, it works just fine. Am I not allowed to use date parameter with an expression? It returns every date within the correct month, but will give me future years as well.
The expression is - NextInspectionDate: DateAdd("m",12/[InspectionFrequency],[LastInspectionDate])
The parameter is - Between [Forms]![Preventative Maintenance Dates]![Sta
Anyone know how to create a combo box that has unique month and year entries from a table (month in one column and year in another - i.e. 2 columns)?
I've been able to get the textbox of the combobox to output the correct format using a custom format but it does not affect the combobox data. Also, I'm not sure how to separate this by two columns...
I'm new to programming with Access but am attempting to create a new field in a table with an new date based on existing fields in the table.
The current fields are [Frequency], integer, [Risk], text, [Last Audit Date], date/time, and the new field is [Next Audit Date]. [Frequency] is a calculated field based only on [Risk] and is equal to "5" if [Risk] is "Low" and is "3" is [Risk] is "Medium" or "High", and [Frequency] is blank if [Risk] is (thus far it has never been empty).
What I need the new calculated field to do is return "N/A" (or blank, or anything easily separated really) if [Frequency] is blank, or if [Risk] is "Low" or "Medium". If [Risk] is "High", [Next Audit Date] should be equal to [Last Audit Date] plus 3 years. When I try to save the code, I get this message: "The expression could not be saved because its result type, such as binary or NULL, is not supported by the server."
This is my code now: IIf(IsNull([Frequency]),"",IIf([Risk]="Low","N/A",IIf([Risk]="Medium","N/A",IIf([Last Audit Date]="N/A","N/A",[Last Audit Date]+Year(3)))))
I have a database of students that have taken tests. That database includes how many attempts it took them to pass. I have successfully generated reports that sort the exams by type, date, student, etc. I would like to also somehow insert a formula to show the pass rate of those exams on the top of the report. I know I can get into design view and build an expression, but how would I build an expression that would create a pass rate percentage based on whether or not a student passed an exam on the first attempt.
I have a DB with a column titled "Attempt" and the options "First, Second, Third, Fourth". I would like the expression to generate a percentage of students that have "First" in their box (as opposed to anything else) out of the total number of student records.
We need to track student pass rates in my school for a re-certification process.
I have to fields in a form for a membership database that I want to relate to each other, 'date joined' and 'expires on'.
I have been trying to build an expression in the 'expires on' field that calculates the date which would be 12 months from the date entered in the 'date joined' field.
Can anyone please point me in the right direction.
I have a query that I want to return "due by 9/8/14" if the [Grace] field is blank or if the date in that field is before 9/8/14. If the date in the field is after 9/8/14, I want it to return "valid through (the date in the field)"
I wrote: MedWords: IIf([Grace]=Null,"due 9/8/14",IIf([Grace]<9/8/14,"due 9/8/14","valid through " & [Grace])) and all I get is "valid through" and either blank, or whatever date is in the [Grace] field.
I'm sure this is a very obvious question for someone, but not so much for myself. I just need a date range formula for a query. I want the query to ask me the start date and also ask the end date and pull up all dates falling in between.
Hello people - I am building an APPEND and DELETE Query which deletes STUDENTS in my Camera Loans database who have not borrowed a camera in more than two years.
In the query structure, under DATE_BORROWED, I put the expression <=Date()-730 (where 730 = 365 days x 2.)
However, upon running the query it gave back to me the earliest loan date of the student, (so their first loan date and details basically)
It ignores the more recent loans, so deleting this appended record would be a mistake.
Now I need an expression in a duplicated DATE_BORROWED field in the query structure that picks out the most RECENT date out of them all, the other field running the check on this most recent date to see if it is older than two years.
Surely there is some code or expression that pulls the most recent date out only of a whole series in a record? Or a criteria is performed on the rmost ecent loan only?
I am having a problem with a datediff expression.I have a query that pulls the first date and the last date out of a list but are put in seperate fields. I thought I could build an expression for datediff to calculate the number of dates between them. I can't just put in the dates because they are constently changing.The expression I have is:
[datediff("d",[min/max edging unit #2]![firstofupdate/time by month],[min/max edging unit #2]![lastofupdate/time by month])]
I need to be able to add x months to a given date which I'm using in a criteria expression. I've figured out that I can just add y number of days, but the answers aren't quite accurate across different ranges of months.
I'm after something like <[BeginDate] And >([BeginDate]+[3months])
but I haven't turned up anything useful in an hour of googling - finding it difficult to define what I want in search engine terms.
So I have a database of contacts, and it has their birthdays listed as well. What I need to do is calculate the number of Fridays from their birthday up until today's date. How do I do this? Thanks
I have a query that I want to "filter" based on a date derived from a Build statement (Expression). The Build statement works I can even sort by "Ascending".. But when I try to add a "Between [Startdate] And [Enddate] statement in the Criteria box, returns nothing".
I am attempting to create an expression that will change the font to red if it is an overdue date. It will be on a form with the records showing.
My datebase is for entering, changing, and searching for information dealing with orientation dates, contacts, and associations. My data sheet holds the company name, employee name, date of orientation, due date (orientations are completed annually), contact employee, and status. I would like the date, when opening the form, to show red if it is past due. how to create an expresion to return the status as "Current" or "Overdue", as I am still unsure which method I want to use.
I am getting something wrong but I don't know what - date related.
Take this code for example:
Code: Dim varDate varDate = Date If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate=" & varDate) > 0 Then MsgBox "Go Away", vbOKOnly Else Go on and do the real work End If End Sub
The DCount line is being completely ignored, no error message, it just cracks on as though there were no records found (but they are definitely there!) I have tried:
Code: Dim varDate varDate = Date If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate='" & varDate & "'") > 0 Then MsgBox "Go Away", vbOKOnly
But that throws up a Type Mismatch (which is what I would expect). Then I tried
Code: Dim varDate varDate = Date If DCount("RunningNumber", "AllocatedVehicles", "ServiceDate=#" & varDate & "#") > 0 Then MsgBox "Go Away", vbOKOnly
And it gets ignored again.
I am having similar trouble trying to open another form using the same field as the open argument, and getting similar results, so it is clearly me that is wrong.
I am trying to count the amount of records that were created and closed for last month but I am having problems inserting the correct criteria along with the DCOUNT syntax. DCount("*","obsvnofilterqry","(Date_Closed)=MONTH( Date())").Works fine but figuring out how to get the amount of Date_Closed for last month is proving tricky.
I'm trying to create an query that has two separate expression in it and a date selection criteria. The first expression is,
Expr1: DateDiff("s", [Arrival Time 1],[Departure Time 1])/3600
and the second is,
Expr2: IIf([Expr1]>[Hours Per Day],[Expr1]-[Hours Per Day]
When I run this query it asks me for the date (which is fine) but then it also pops up a box asking for "Expr1". How can I get "Expr2" to use the value returned from "Expr1"?
