So I am trying to setup a default value in the tbl design. The Following two fields are the two in question(StartDate & ReportDate). It already has relevant dates properly formatted in the StartDate Field. The ReportDate will always be the day before or minus 1 day from the Startdate. My question is that I can't seem to get the syntax right to make it work.
I am setting up a query to display a records of products within certain sizes.
I want the width to be input, eg; "500" and i want all records - plus or minus 50 of this record to be displayed. I know i could use the "BETWEEN" "AND" command but i want the user to only enter 1 size if it is possible.
Hi This is probaly really easy, I want to identify just minus figues in a query ie -5.00. I have tried iif([TotalPrice])<0,[TotalPrice]) this shows no result. Can any one advise. Thanks in advance.
It's unknown for me why Access hasn't got MINUS implemented and Unmatched Query Wizard doesn't help me much. So I'm wondered if anyone had to confront the following task:
Table1
Book Agent ---------- b1 a1 b2 a2 b3 a3
Table2
Book Agent ---------- b1 a1 b3 a3
The problem is I have to eliminate duplicates but I need to find unique row, which is Book and Agent. From my example it's row with (b2 a2). I tried using Select * From Table1 Where (Book, Agent) Not In (Select Book, Agent From Table2) didn't work I also tried EXISTS didn't work Unmatched Wizard compares only one field, not two.
Hi, I've searched this forum and can't quite get what I need. I have a subform on a form, in the footer of the subform I total an amount owing field. Then I get the value from the footer control to the main form. I know that the total should be 0 but on the main form I get a -0.0119. How can I get this to show 0. Thanks
I have 4 tables, 2 entities, 1 relationship and 1 lookup
Entities are tblOrg and tblForm Relationship is tblVentureParticipation Lookup is tblInfo (Has many different lookups)
When an organization (these are listed in tblOrg) turns in a form, a new record is inserted into tblForm with the ID of that organization and the ID of the form
I want to identify the forms that have NOT been turned in.
So I figured
Step 1: Identify all organizations who are participating (WHERE tblOrg.OrgID = tblVentureParticipation.OrgID)
Step 2: Join all of the previously selected organizations with all of the forms (FROM tblOrg, tblInfo WHERE tblInfo.LookupID LIKE 'F##')
Step 3: Remove all of the entries in tblForm that match the result of Step 2.
I have verified that both of these individual queries return the right information ...
SELECT [qry_1].Month, B_Division_Group.Grouping_Name, Sum([qry_1].Month_Client_Count_from_B) AS Monthly_Count, Sum([qry_1].Month_Assets_from_B) AS Monthly_Total, Sum([qry_1].YTD_Client_Count_from_B) AS Yearly_Count, Sum([qry_1].YTD_Assets_from_B) AS Yearly_Total FROM ([qry_1] INNER JOIN tbl_branch ON [qry_1].BranchCode = tbl_branch.BranchID) INNER JOIN B_Division_Group ON tbl_branch.BranchName = BDivision_Group.N_Br GROUP BY [qry_1].Month, B_Division_Group.Grouping_Name;
While all monthly and yearly values were positive, the query produced exactly the results expected (i.e. one row of data for each Grouping Name/Month combination, containing the overall totals for each field).
Now, however, some Grouping Names have minus values and the query is showing an extra row (one for positive values, one for negative). It's as if having one or more minus values is being treated as a new Grouping Name/Month combination.
Is Sum() the correct method to use, when dealing with negative values, or should I be using some other function?
I am creating a report and I need data about parts that have not been used in the past 12 months. I'm not sure how to get just numbers from the past 12 months. How do I get the current date minus 1 year?
I have a datasheet subform that is acting like a timesheet. I have a TimeIn field and TimeOut field (both fields formatted as Medium Time); and a field for Hours to total the time. I used the expression =HoursAndMinutes([TimeOut]-[TimeIn]) that I found in another post, and it didn't work. All I got was #Name. Any thoughts as to why that's happening? I would preferably like the hours to total in tenths of hours, ie. 10.5.
I have a report which is a statement. I want to be able to have a field that calculates rent, and minus payments. There are a few rent types, Pro Rated rent, Rent brought forward and rent changed, then there is 1 payment type. I am trying to write the sum a few ways, but have not had success. If rent type i= 1 or 2 or 3 I want it to add, if rent total = 4 I want it to minus.
=Sum(IIf([RentType]=1 Or 2 Or 3,[RentAmount],0))-Sum(IIf([RentType]=4,[RentAmount]))
I'm working on a form that I want to insert a plus button/minus button.I am aware of the vba code numbers 107 and 109 if you want to use the actual keys but I would rather use a button.
I made a button and here is the code I used now. This code is numeric box specific. So it only adds one to the MileageStart numeric field. (Additionally you can put a minus in to subtract one).
Private Sub Command153_Click() MileageStart = MileageStart + 1 End Sub
Instead of having a bunch of these buttons all over boxes on my form is there some code that I can use to "select" a text box and just have the Plus/Minus button on the form header that I would hit and it would add or subtract (and then I could go to another box select it and hit Plus/Minus and so forth).
I am trying to make a simple form to tally my total products ordered.
So I wanna add a plus and minus button on my continguous form, but i cant get it to work. How do i update the current record when clicking on the button next to the field ..? It seems only the first record gets updated ..
I am trying to limit the results of a SQL query by date. I would like to take the current date from the system clock, move back one day, check to make sure it's not a weekend or holiday, then use the result date to limit the results in my Query. If the current date minus one day happens to land on a weekend, the date picked should be the Friday before the weekend. If the current date minus one day lands on a holiday, it should be the date before the holiday as long as it is not a Saturday or Sunday (on another holiday) .
Here is my SQL query right now.
SELECT DISTINCT (Mid(ClientDiv.Client_Division,1,3)) AS ABC, RTIClientTracker.EMB_OOB, RTIClientTracker.OOB_Fixed FROM ClientDiv INNER JOIN RTIClientTracker ON ClientDiv.ID = RTIClientTracker.Client_Division WHERE (((RTIClientTracker.Division_Region)='RTI') AND ((RTIClientTracker.Cut)>=Date()-1)) ORDER BY (Mid(ClientDiv.Client_Division,1,3));
It limits what it selects by using the current Date minus one day.
I need to skip over Saturdays, Sundays and Holidays.
I have a form with a combo boxes and a table with relevant list and additional field, fldDefaultDrive (Yes/No Field).
Currently in order to set the default value, I have used the following code for each default;
Private Sub Form_Load() Forms!frmMediaLabeller!CboDriveName.DefaultValue = """D"""
End Sub
However, I want users to be able to go into the table and change the default value if thier CD player default Drive is anything but D: Drive. I have tried to replace the D above with an SQL statement but with no success.
Private Sub Form_Load()
Dim Drivename As String
Drivename = SELECT tblMediaDrive.fldDrivename FROM tblMediaDrive WHERE (((tblMediaDrive.fldDefaultDrive)=-1));
now i want to split it up. i want to count all the records for each month. my field name is datein_now but its just a normal textfield and not a date field. example in field: 05/02/02 10:24:31 AM. also got a datein textfield:05/02/02 i had Count(*) where datein_now between 05/05/01 and 05/05/31
I'm trying to add a couple of fields to the Contact database in Access 2010.
In the Contacts table, I created a field called "Sobriety Date" that has dates formatted like 12/27/1995
I am trying to add a calculated field called "Years Sober" which should be the current year minus the year in the 'Sobriety Date' field (1995 in the example above).
Hi can anyone help i dont really have a clue about access but im tryin to make some tables but i dont know how to do somethings with them. I have these 2 tables 1 called order with fields stock and order quant and 1 called fills with fileds called stock and lots. They are linked by the filed called stocks. What i need is in another table a field with remaining Shares where it takes fills from order quant. is this possible if so how would i do it.