Queries :: Dlookup Value From 1 Table With Date Ranges From Diff Table With A Single Date?
Mar 12, 2015
i have 2 tables.. 1 with bookings and 1 with a BonusPeriod range.
in the Bookings table there is a field called [ServiceDate]
in the bonus table i have a field called [Period] which is text i also have 2 dates, [StartDate] And [EndDate] i need to Dlookup the Bonus table to return the value of the period table based on where the [ServiceDate] falls.
i dont want to use VBA (i really need it to work within a query that inserts it's data into another table)
i've tried everything but had no joy
Expr1: DLookUp("[Period]","BonusPeriods","[ServiceDate]" Between "#[StartDate]#" And "#[EndDate]#")
Expr1: DLookUp("[Period]","BonusPeriods","[Staff_BookingsAndQuotes_Master].[ServiceDate]" Between "#[StartDate]#" And "#[EndDate]#")
Expr1: DLookUp("[Period]","BonusPeriods","(FormatDateTime([ServiceDate]),"yyyy-mm-dd") Between (FormatDateTime([BonusPeriods]![StartDate]),"yyyy-mm-dd") and (FormatDateTime([BonusPeriods]![EndDate],"yyyy-mm-dd"))))
View Replies
ADVERTISEMENT
Aug 23, 2005
Hello,
Am posting this message (also on reports forum) as i dont know whether the problem is at report or query level. Hope you can help.
Hello all, this is probably simple but im new to all this!
I am trying to produce reports that show details for only the past year, at present my reports are bringing up details of all the previous years also. Is there any way you can put conditions on reports ( or should it be in a query) that tells the report to only include dates i.e. today - 365 days. At present i have to change the dates every day both a start and end date. Is this possible?
Thanks
View 2 Replies
View Related
Oct 17, 2013
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])]
View 4 Replies
View Related
Aug 10, 2015
I need a query that will relate values to given date ranges. For Example:
Table: TaxTypeRange
Values:
TaxType; FromDate; ToDate; Contact
W2; 1/1/15; 3/31/15; Tom
1099; 4/1/15; 6/30/15; Tom
W2; 7/1/15; 12/31/15; Tom
Table: Bills
Values:
Contact; BillAmount; BillDate
Tom; 1000; 3/31/15
Tom; 1100; 4/30/15
Tom; 1200; 5/31/15
Tom; 1300; 6/30/15
Tom; 1400; 7/31/15
Requirement: For every period where the TaxType = W2 the query should multiply the Bill amount by 1.20, so the result should be something like this:
Query: BillsAndTax
Values:
Contact; BillAmount; BillDate; TotalBill
Tom; 1000; 3/31/15; 1200 (TaxType = W2 so 1000* 1.2)
Tom; 1100; 4/30/15; 1100 (TaxType = 1099 does not apply 1.20)
Tom; 1200; 5/31/15; 1200 (TaxType = 1099 does not apply 1.20)
Tom; 1300; 6/30/15; 1300 (TaxType = 1099 does not apply 1.20)
Tom; 1400; 7/31/15; 1680 (TaxType = W2 so 1400 * 1.2)
Not sure how to set this up the right way.
View 3 Replies
View Related
Mar 17, 2015
I have a table, tblVisits, holding patient's pre and post surgery visits:
Code:
PatientID VisitDate
1 1/5/12
1 3/10/12
1 9/1/13
2 ...
And another table holding patient's surgeries (each patient will have only one surgery)
Code:
PatientID SurgeryDate
1 4/1/12
2 ...
I need to compare these two tables and create a variable that indicates which pre-surgery visit date (i.e., VisitDate < SurgeryDate) is closest to the surgery date. In the above example, it would return:
Code:
PatientID VisitDate ClosestToSurgery
1 1/5/12
1 3/10/12 Yes
1 9/1/13
2 ...
I've tried various MIN and MAX approaches and can't seem to get it right.
View 2 Replies
View Related
Aug 7, 2014
I'm using the following function to calculate date diff in network days (excluding weekends)
Public Function NetWorkdays(dteStart As Date, dteEnd As Date) As Integer
Dim intGrossDays As Integer
Dim dteCurrDate As Date
Dim i As Integer
intGrossDays = DateDiff("d", dteStart, dteEnd)
NetWorkdays = 0
[Code] ....
So when using this function in a query to get number of networkdays between 2 date columns ...it works fine but throws a #Error where there is blank entries in either of 2 date columns...
I need to find a way to display Null instead of #Error...
I have tried this expression but no luck...
IIf(IsError(NetWorkdays([Date1],[Date2])),"",NetWorkdays([Date1],[Date2]))
View 7 Replies
View Related
Jan 1, 2015
I have tried search through some forums and but still not able to get desire results.
Table Customer Agreed Transport Rate
Customer > Eff Date > Route > Rate
A > 1 June 2014 > AAA > MYR 99
A > 15 Aug 2014 > AAA > MYR 88
A > 1 June 2014 > BBB > MYR 77
A > 1 Sept 2014 > BBB > MYR 66
B > 5 June 2014 > CCC > MYR 88
B > 20 Oct 2014 > DDD > MYR 100
C > 1 Oct 2014 > EEE > MYR 222
If there is order from Customer A for Route AAA loading on 2 July 2014, I can easy to get answer "MYR 99".
How if I wish to make Route as primary key? (as I understand, Primary key must be unique and no repeat).
View 10 Replies
View Related
Jan 30, 2014
Front end Access 2010, back-end SQL-server 2008 R2.
Normally I retrieve a certain value by Dlookup("myvalue", "mytable",...)
or
strSQL = "SELECT myvalue FROM mytable...;"
Set rs = CurrentDb.OpenRecordset(strSQL, 4)
But is there any faster way to retrieve a single value from an SQL-server table, beside doing doing the select by a stored procedure running through a pass through query, then open a recordset
Set rs = CurrentDb.OpenRecordset("mypassthroughquery")
just to retrieve ONE value?
I could not find something like DLookup("...) for an SQL-Server or in T-SQL.
View 3 Replies
View Related
Sep 3, 2014
I am trying to return a single value from a table and assign it to a string to be used later but Dlookup isnt working at all. below is the code im using and the error message im recieving is "wrong number of arguements or invalid property assignment"
Code:
Sub boo()
Dim result As Integer
result = dlookup("Definition", "Config", "Parameter = 'Mail Folder'")
End Sub
View 13 Replies
View Related
Jan 29, 2008
hi all
im looking at doing a query to check records for absense
the 1st will be checking if the person has been absense for 4 weeks (28days) so i will have to check from absense date to today does that equal or greater than 28 days but lesss than 91 days (13 weeks) and is the return to work date blank
is that possible???
View 6 Replies
View Related
Nov 7, 2005
Hello,
I have a form on which I have places a textbox named txtDOB which stored a date of birth.
I have then another text box which has the following function:
=DateDiff("yyyy",[txtDOB],Now())+Int(Format(Now(),"mmdd")<Format([txtDOB],"mmdd"))
Is there a way I can show the age + months?
Thanks.
View 5 Replies
View Related
Nov 6, 2006
I run various queries on a daily basis for multiple date ranges (previous day, week to date, month to date, and year to date). The below criteria allows me to get WTD numbers but it has a flaw. When I run the reports on Monday, this criteria gives me Sunday and Monday's data when I actually want the previous week's data. Does anyone know of an iif statement that basically, says, "If today is Monday, give me last week's data, else give me wtd of the current week?" Right now, I manually change the criteria on Monday morning to hard code the dates and then revert to the formula on Tuesdays but I need to have this completly automated because there are a large number of queries and I don't have time to change them all. Thanks for the help!
Between Date()-Weekday(Date()-1) And Date()-1
View 2 Replies
View Related
May 19, 2005
I received help on previous threads. Basically, I have built a query to group by course title and then select the number of students that were assigned a course during a specifc time frame. Is there a way to show multiple columns in the query. For example my query now shows, enrollment for March, I would like to column next to it to show enrollment for Feb and then another column for Jan.
This is my current sql view.
PHP Code: SELECT tblContent.Title, Count(*) AS [Students Assigned]
FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName
WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Robins") AND ((tblContent.Code) Like "NH*") AND ((tblProfile.Groups) Not Like "*Professional Development Center*" And (tblProfile.Groups)<>"All Users,Professional Development Center") AND ((tblProfile.Delete)<>Yes) AND ((tblContent.Type)="Learning Track"))
GROUP BY tblContent.Title, tblContent.[Date Assigned]
HAVING (((tblContent.[Date Assigned])>=#3/01/2004# And (tblContent.[Date Assigned])<=#3/31/2004#));
Thank you in advance for any help you can offer.
View 6 Replies
View Related
Feb 18, 2013
I have a table with employee names and there start time and stop time for their shift. There is also a start time and stop time which they cant work during their shift. So I have shiftstarttime and shiftstoptime and codestarttime and codestop time. I have to create a table with the employee names and generate a time called outboundtime. This outboundtime starts at a specified time (e.g. 9am) and runs for a specified time (60mins). This outboundtime has to be within the range of the employees shift and cant be within the range of the cant work time. If it was, then it would start after the codestoptime for that employee.
I have been trying "if then statements" but I can't seem to compare a range of time to another range of time. Only a specified time to a range which is making things confusing.
View 1 Replies
View Related
Mar 19, 2008
Hello,
I am having problems creating a Exp to show the difference in time.
I am currently using :Expr1:DateDiff("n",[TimeReported],[TimeDefectGiven]) which is returning a figure in decimal.
If the answer was to be 90 minutes i need it to return the figure as 1.50 instead.
I have looked on the site using Date Diff and Time without success
Any help given would be great
View 3 Replies
View Related
Sep 18, 2007
I have a date field, I am trying to have my query only pull the records that have a date entry that is within and up to 90 days from today or Now. For this query all other records are irrelevant.
THis is what I have found so far
=(Abs(DateDiff("y",Now(),[ls_Exp Date])))
Can someone show me how to add the 90 days in there?
Thanks.
Fen How
View 3 Replies
View Related
Oct 11, 2007
I'm using the date diff .... DateDiff("h",[Start time],[End time]) in a query and then I report the results in a report. I have run into a problem for the first time. I had a start time of 7:00pm, and an end time of 7:00am. This was calculated in the query as (-12) which is messing up my cost formula.
How do I resolve this?
Jerry
View 3 Replies
View Related
Apr 27, 2006
Here we go:
My overall goal is to find out a worker’s downtime (count of weeks between assignments)
I have a qry_Downtime that pull in a worker and his current and future assignment. Each assignment is associate with a start and end date.
Here is how the results look:
Employee (joe schmo)ProjectONE ProjectONEStartDateProjectONEEndDate
Employee (joe schmo)ProjectTWO ProjectTWOStartDateProjectTWOEndDate
Employee (joe schmo)ProjectTHREE ProjectTHREEStartDateProjectTHREEEndDate
Employee (joe schmo)ProjectFOUR ProjectFOURStartDateProjectFOUREndDate
Downtime would be defined the diff in weeks between ProjectONEEndDate and ProjectTWOStartDate, ProjectTWOEndDate and ProjectTHREEStartDate, etc.
Any suggestions.
T
View 5 Replies
View Related
Jun 14, 2006
Friends,
I have a problem with a date diff calculation:
On my form I have placed two controls, a text box and a check box.
The textbox contains is a date field (mm/dd/yyyy).
What I need is a code that checks the checkbox if the date in the textbox is within the last 12 months.
This is the code I am using but not working:
If DateDiff("yyyy", textbox, Now()) >= 1 Then
Me.check1 = "yes"
End If
Any help? Thanks.
View 11 Replies
View Related
Dec 15, 2005
I am attempting to create a parameter query to show a specific range of dates of service for my customers. Example: I want to see data from my table from Jan. 1st, 2006 to Feb. 15th, 2006 for any customers serviced in that time frame. In the query I have under my Date field, I have the following in the criteria field: Between [Input Start Date] And [Input End Date]. When I go to the while in the design mode of creating the query on the main menu under Query then to parameters and get my Query Parameters what should be put into the Parameter column and the data type columns and will this allow the user to input any start date and end date and get the information from the master table?
I also need to create a parameter query that will allow viewing of only each months table data. Example: Jan. 1st, 2006 through Jan. 31st, 2006.
Thanks in advance!
View 2 Replies
View Related
Jan 12, 2007
I must be having a bad day, as I can't get something really simple to work. I have a query, that amoungst other things, I want to select all records between two dates. For instance, the table has a 'ScheduledDate' field, and I have set up two parameters in the query that prompt for 'From' and 'To' parameters.
In design view, I have tried the following in the 'criteria' box for the 'ScheduledDate' field;
>=[From] <=[To]
This doesn't work ~ can anyone point me in the right direction. I've searched the forum, perhaps not using the correct keywords?
All help appreciated.
View 5 Replies
View Related
Dec 27, 2007
Calculating dates between TWO date ranges:
I've read alot of good things on here regarding the DateDiff function and some other things that have helped me out finding the difference between two dates within the same entry on a table. I have a question regarding finding the difference in days between two entries within the same table.
Example:
test table:
Date ID # Name
1/18/07 1234567 Smith
1/20/07 1234567 Smith
1/23/07 1234567 Smith
Is there a way to find the difference in days between the lowest and highest dates? I want to know how many days are between the first entry and the last entry? In this cae it would be five days. I can do it if the days are in the same entry with the datediff function, but having trouble finding it in this situation. Any pointers?
(Thanks for all the good information on this site, BTW. I've seen a few posts similar to this, but it didn't really answer my question)
Don
View 3 Replies
View Related
Sep 26, 2007
I am trying to put together a quick database for my HR team. I need to be able to pull multiple date ranges to show which employees are up for an annual, 6 month and 90 day review.
Is it even possible to put multiple date ranges in one query, or is this going to get complicated? I have no real database background, and know absolutely no SQL so I'm flyin by the seat of my pants here...
View 2 Replies
View Related
Jan 26, 2014
I have been working on a query in my database and I would like to look up a Number Value from another table based on a Number ID field and the Number Date in the Number Values table, where the Number ID field in the table agrees to the Number ID Index field in the query, and the Number Date field in the table agrees with the Initial Date field in the query. The Dlookup syntax I currently have is as follows:
DLookUp("[Number Value]","Number Values","[Number ID]=" & [Number ID Index] & "And [Number Date]= [Initial Date]")
The Dlookup shown above does not return an error, but it returns a blank field. I know that individually, the Dlookup for the Number ID works, but when I add the And for the Number Date the Dlookup returns a blank. Also, I have verified that there are existing records where the Number Date and Initial Date fields agree.
Also, I changed the format of the date fields in the tables to be ShortDate, thinking that the Timestamp was causing the Dlookup to not find a match. I also tried to use the DateValue() function within a query to reformat with not luck.
Why my Dlookup is returning a blank?
View 3 Replies
View Related
Jan 23, 2008
Hi I have two fields one which represents the time participants went to sleep, one when they woke up however I dont have any dates - it refers to what they do usually. Is it possible to minus sleeptime from waketime to determine duration of time in bed without dates? Or do I assign dummy dates to use date diff function?
View 4 Replies
View Related
Jun 6, 2013
I want to create the date from multiple columns to single colums. Just for example
table 1 (local purchase)
Itemname Date
Apple 12/01/2013
Mango 13/01/2013
Table 2 (Import purchase)
Item Name DAte
Apple 12/04/2013
Mango 08/06/2013
Now i want to make one query, which can you the date as follows when we give criteria = apple
Item Name Date purchase mode
Apple 12/01/2013 local
Apple 12/04/2013 Import
Means two dates from different table into one query column... One way in my mind to make one table for both tables.
View 9 Replies
View Related
