I have a query built by someone else that I think is the root of my problem. The query runs and selects Officers of an Insurance Company based on the dates in a table stating when they were officers. The problem is one of the date (enddate) is blank since he/she may be a current officer. The query reads ">[RPT_NAIC_AF1]![BeginDate] And <=[RPT_NAIC_AF1]![EndDate]" It is not returning all records after the start date which has no enddate. If I change the "And" to an "Or" I get the records I want, and a lot more so I know the problem lies here. Help please! I am a novice user but willing to learn.
I am trying to run a change an existing query in real time to allow date filtering for 4 different categories. I can get two of them to work. Calibration Date and Icepoint Date. But for The two calculated fields Calibration due date and Icepoint date I cannot get it to filter properly e.g. for 2013 dates it also includes 2014 dates and just does not work properly. I am thinking its due to it being a calculated field but don't have a clue how to fix it. see pasted code for calibration due date filter where Todate and Fromdate are the 2 dates used. I also have the on current code and the exit code to reset the query to its original status.
Public Sub SetDate1() 'Apply date filter and rebuild query in real time On Error GoTo Err_SetDate1 If IsNull(Me!ToDate) And IsNull(Me!FromDate) Then MsgBox ("Please Enter Date First"), vbExclamation GoTo Exit_SetDate1
I am trying to work out how to find the difference between two dates, in years and days. I have a [date_of_birth] field and another field [date] and wish to work out the difference in years and days as opposed to decimal years. Is it possible to do it?
I have set up an Attendance database at work, where a record is created for each member of staff when they are absent. The tables are set up as follows:
What I want to be able to do is set up a query to search for a reason for absence on a certain date (btw my date format is set at dd/mm/yyyy). The problem I have is if the user needs to check a date in between the start and end date i.e. Start Date is 11/11/2005 end date is 11/12/2005 and the user checks on 01/12/2005 then this record will be found.
I know there is a simple way to do this but I can't seem to figure it out. Any help would be greatly appreciated!
I have a situation where I need to extract the date from a text field to use in a select query. The date in the text field is listed at the beginning of the text field like this.
S12345678 3/21/2008 adittional text beyond here blah, blah...
the first series of numbers can be either 9 or 10 digits long, and then the date is always in this format, the length of the characters change accordingly with the date. So trying to use the Mid function only wont work.
I supposed I need to find a way to get the position of the space character in front of and after the date to use the Mid function for each record.
I need to be able to extract this date to do a DateDiff against another date.
I would like to count the number of working days between two dates, NOT the calendar days, if the workers only work Monday thru Thursday. I know we could use the DateDiff function, but HOW do I count excluding Fridays, Saturdays, Sundays?
I am trying to carryout working days between two dates (Excluding weekends Friday & Saturday) but unable to fix it correctly, However i did search a lot over internet also about built in function but all is showing i have to add it module or macro etc.So, i select the module for Access 2010 but also not finish with function.Table name is "LeaveSettlement" and column name is "Total_Wdays", what field type i have to select for result column.
Public Function LeaveSettlement(ByRef Leave_Start As Date, _ ByRef Leave_End As Date _ ) As Integer ' Returns the number of weekdays in the period from Leave_Start ' to Leave_End inclusive. Returns -1 if an error occurs. ' If your weekend days do not include Saturday and Friday and ' do not total two per week in number, this function will ' require modification.
The below function returns correct time difference between workdays. However, it is excluding Saturday as per the code.
It is calculating 06:30 am to 22:00 pm time for weekdays but I also want it to calculate the time from 10:00 to 13:30 on a Saturday.
I am trying to use the NetworkMinutes function to achieve this. However, there is a problem getting the time for Saturday.
Code: Option Compare Database Option Explicit '--------------------------------------------------------------------------------------- ' Procedure : NetWorkMinutes ' Author : Rod ' Date : 13/12/2012 ' Purpose : Returns the number of work minutes between two date-time arguments.
I have searched the forum for this answer but no luck. :(
I'm trying to calculate the amount of WORKING time between two dates in an Access database. At the moment i am just subtracting one date from another but this gives me all of the time in between including weekends and evenings.
I need this time to be calculated in hours.
In Excel i know there is a NETWORKINGDAYS function which does something similar but with days rather than hours.
At least if i could get the working days i could then convert it into hours.
Any ideas on calculating future dates based on # of working hours. For example... If a task is suppossed to be completed in 32 business hours, when would that be? Considerations include standard working hours M-F, no working hours on weekend. I have to believe that someone has written this before and I don't need to reinvent the wheel. ANy help is much appreciated!
I hope someone is able to help me. I've been working on this for days. I have a report which has a 2 subreports in it. I have the subreport bound and have a query working in the recordsource. Here is the recordsource:
I have one report with the Master/Child fields filled in and set but the sponsors subform I cannot fill in the Child field. It says I need to put SponsorID in the recordsource. I thought I did have this(see above.) That's my first problem.
My bigger problem which I think the previous problem is contributing too is my subreport shows up blank. When I run my query it works perfect. But, when I run it with the rest of the program the subreport shows up blank.
Anyone know how I can go about fixing this?? Thanks!! Nick
I have a problem with a delete query i am trying to set up and hopefully one of you experts can help me out.
I have a table called [PMI] and a table called [Referrals]
The tables have a one to many relationship and are linked by the primary key field Patient_No.
I want to delete all records in [PMI] that have no records in [Referrals] and this is what i'm having problems with. I have created a select query where i can return the records i want to delete but its the removing of them from PMI that i cannot figure out.
Hope that makes sense, all help is greatly appreciated.
I'm sure this is easy to do but for some reason i just cannot get it working. Hopefully someone here can tell me where i'm going wrong.
I have two tables in a database and the have a linking unique field. I want to update a column in one table from a column in another where the linking ID field matches.
Here's what i have at present.
Update Table_1 inner join Table_2 On Table_1.ID = Table_2.ID Set Table_1.[CHI Number] = [Table_2]![CHI_No]
This is just returning the CHI Number column as blanks instead of what i expected would be the same as Chi_No.
we have a main table for tracking pickups....some of the fields are combo boxes with relationships to other tables....one such relationship is units (ml, gallon, ounce, etc)...in the main table everything works just fine....a user enters the location info, the amount and the corresponding units. thus, an example would be a user picks up something from building X, room 101, 10, Gallons.....the 10 refers to the gallons, the user picked up 10 gallons...
now for the query: i'm creating a query for which i will base a report on...in query design if i just select the above info everything works fine....it comes out on the report as 5 Gallons....however, what i'd like to do is concatenate the two fields so that they take up less space on the report and are more visibly appealing...here's what i tried in query design: Amt: [amount]&Space(1)&[units] this does not work....in the units table that provides Gallons as a selection in the combo field, Gallons has a key value of 5.....thus, when i run the query using the above code i get 10 5 - where 10 is the amount the amount and 5 is the key from the combo box...
i've tried bringing in the units table but then i get a type mismatch....how can i concatenate the two fields and show the proper units? this has to be simple but i cannot get it to work... :mad:
I have a a table that changes monthly, and each month I need to find the records from the old table that are not in the new table and visa versa. I made the entire row in the table the key. and wrote two find unmatched queries. However, the queries are not finding all the information that is no longer there or that has been added. Can someone please take a look and see what I am doing wrong? Any help appriciated!! Thanks
Ive posted similar in the forms section but it has gone unanswered so I've pulled out the offending query and hope someone here can offer insight into this frustrating problem.
I have a form [Training Record] that has 2 combo boxes.
Box A (course_title_combo) has the titles of the various training courses. It pulls 2 columns from the training_courses table, namely courseID and course_title. Only course_title is shown in the box. The control source of this is set to CourseID as this is the field to be written to the training_record table, showing who attends what course.
Box B (course_date_combo) is MEANT to pull through the dates that each course is on, by pulling through the course dates for each course where courseID (as selected by combo box A) is equal to the courseID in the course_dates table.
Here is the current rowsource query for box B - which is basically showing blank fields...
SELECT tbl_course_dates.course_date, tbl_course_dates.CourseID FROM tbl_course_dates WHERE forms![training record test form]!course_title_combo=tbl_course_dates.CourseID
I cant see what is wrong with the query, but Im guessing it must be wrong as it is the rowsource which determines what is included in combo box B.
Any help would be appreciated no end - im totally stuck now :(
hi im trying to do the following if statement in a query but its not working, anyone know what im doing wrong? any help would be great.
------------------------------------------------------------------------- New: IFF [(TABLE-SALES]![PERIOD]<=[TABLE-AVERAGE]![ACTUALS_TO_PD] then [TABLE-SALES]![ACTUALS_TO_PD]) else [TABLE-UPDATE]![FORECAST_1_BASE_VALUE]
------------------------------------------------------------------------ im trying to say if the field "period" in the table: "TABLE SALES" is the same or less than the value in the field "actuals to pd" in the table :"TABLE-AVERAGE" then the value displayed in this field ("new") should be the value: "actuals to pd" in the table "table-sales" otherwise the value in the field "forecast1 base value" in the table "table update"
UPDATE tbl_master SET tbl_master.COLLRAW = Iif(tbl_master.PSTATE = "WV","WV", Iif(tbl_master.PSTATE = "MA","MA", Null)) WHERE tbl_master.EXCLUDEREASON Is Null
but when I add this line:
UPDATE tbl_master SET tbl_master.COLLRAW = Iif(tbl_master.PSTATE = "WV","WV", Iif(tbl_master.PSTATE = "MA","MA", Iif(tbl_master,MSPBANK in ("751","752","753","854","855"),"GS", Null))) WHERE tbl_master.EXCLUDEREASON Is Null
It give me an error message that says "Wrong number of arguments in Query Expression." Can anyone tell me what is wrong with the query above?
SELECT tblProjectDetails.projectid, SubType.SubType, tblProjectDetails.status, tblProjectDetails.product, tblProjectDetails.description, tblProjectDetails.type, tblProjectDetails.producttype, tblProjectDetails.confidence, tblProjectDetails.flatamount, IIf(IsNull([reduction]),(([currentprice]-[newprice])*[volume])/(13-[cutinmonthnmbr]),(([reduction]*[volume])/(13-[cutinmonthnmbr]))) AS Calc, ([flatamount]/(13-[cutinmonthnmbr])) AS Calc2, IIf(1>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Jan, IIf(2>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Feb, IIf(3>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Mar, IIf(4>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS April, IIf(5>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS May, IIf(6>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS June, IIf(7>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS July, IIf(8>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Aug, IIf(9>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Sept, IIf(10>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Oct, IIf(11>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Nov, IIf(12>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS [Dec], tblProjectDetails.cutinmonth, tblProjectDetails.DMO, tblProjectDetails.fcstloc, tblProjectDetails.projecttype, tblManagers.last, tblManagers.managerid, tblProjectDetails.currentsupplier FROM tblManagers RIGHT JOIN (SubType RIGHT JOIN tblProjectDetails ON SubType.SubTypeID = tblProjectDetails.subtypeid) ON tblManagers.managerid = tblProjectDetails.managernew WHERE (((tblProjectDetails.status)<>"Concept" And (tblProjectDetails.status)<>"Tabled") AND ((tblProjectDetails.producttype)<>"3") AND ((tblProjectDetails.confidence)="1") AND ((tblProjectDetails.cutinmonth)<>13) AND ((tblProjectDetails.DMO)<>Yes) AND ((tblProjectDetails.fcstloc) Is Null) AND ((tblProjectDetails.projecttype)<>"Other") AND ((tblProjectDetails.cutinyear)=2006) AND ((tblProjectDetails.currentsupplier)<>"Venture"));
Everything works great except for the very last criteria <> "Venture". I have one project that this query should leave out but it eliminates any project with a Null in the supplier field instead of just supplier "Venture". Why would it do that and what would be the solution?
