Hi
I'm on the verge of successfully using a DLookup function (with the help of previous posts) in a query in order to obtain the value (volume1) of the previous record based on a primary field which happens to be a date (iddate).
The following results in sporadic success:
Expr1: (DLookUp("[volume1]","tbldailylog","[iddate]=#" & [iddate]-1 & "#"))
The iddate field is sorted and contains consecutive days.
Any help would be greatly appreciated.
I have a table that will have ITEMID as unique field but location could have save values. I tried using DLOOKUP to look at the value for the previous record to see if I need that value is same as current, however, since my data query are text formats, I am unable to. I get a message of mismatch data type. apply my query to look at sorted table by Location and ITEMID and if previous location = location, then my area=0?
View 6 Replies View RelatedI have attached a sample database. Basically I want to have some lines of code that generate the result table, which is tbl_readmit_result.
As you can see, the difference is the addition of a new column called re_admit_status.
Rule is:
Status = "y" when the admission date, compared to the previous discharge is less than 7 days, otherwise "n", for the same pt_id. You cannot compare the two dates on different pt_ids.
Is there a way that this can be done automatically without having to go through the record manually?
I have a report that looks up a previous date then finds the data that goes with that date. I went to run the report and the data now is blank. I am thinking it is because of the formula is looking for 1/28/2013 instead of 1/31/2013.
Code:
DLookUp("[Net Insured]","[CashFlow Link]","[RCNAME]=" & "'" & [RCNAME] & "'" & " And [Date1]= DateAdd('m',-1,DateSerial(Year(Date()),Month(Date())-0,0))")
I have a large table which is updated everyday. Each record will have a different date but there will be many with the same account number. I have a query which will pull one record from each account number according to a user specified start date, another query which will pull by end date, and a third query which uses both to do a calculation on another field. The problem is, if one of the first two queries does not find a record with that date then the calculation will not be done because there is no record. What I need is a way to go back one day if the specified date does not exist. I have tried using an iif statement in a report with the DateAdd function in the true part. Here is what I have
IIf([blinks_test_end]![Reading_Date]=Null,DateAdd("d",-1,[blinks_test_end]![Reading_Date]),[blinks_test_end]![Reading_Date])
[blinks_test_end]![Reading_Date] is the user specified date. When I run the report it asks for blinks_test_end, I think it doesn't recognize that this is the user specified date.
Cost can fluctuate through the year on a month to month basis. I want to create a report January - Feb... that has the cost for each month Table CostChange has [Date] [Item] [Cost] with a record ONLY when there is a change so: Item X has a cost in Jan and a new cost in Mar
I want report:
Jan Dlookup([Cost],[CostChange],[Date]<=1/31/2104 (should return Jan Cost)
Feb Dlookup([Cost],[CostChange],[Date]<=2/28/2104 (should return Jan Cost)
Mar Dlookup([Cost],[CostChange],[Date]<=3/31/2104 (should return Mar Cost)
What am I missing?
Very simple question: I just moved a simple daily rainfall record spreadsheet to my Access gardening database. Fields are date, rainfall in mm, temperature etc. and a single record is added for each day. How do I get the date field to increment automatically to 'previous day's date +1'. (No, I cannot use the shortcut key [Cntrl-;] to insert today's date in the table because records are only entered periodically, several days at a time, from a notebook.) Advice appreciated.
View 12 Replies View RelatedHi All,
I am on a mission to get this out but having muchos problemos! Hope someone can hand me a little tip or pointer.
My table has two coloms:
Date_stamp(datetime), product_code(text(25))
I need to show this data in a report with a third colum that shows the difference in seconds between each record. IE how long it took for the next date-stamp to occur.
Can anyone help.
Thanks in advace
Hi everyone,
I have a form with a date field on it. I would like the date field to automatically enter today's date, which it does now, but also I would like it to retrieve the previous record's date and if it is different from today's date, I want the form to use the previous date as the default value.
Does anyone know of how I would go about this?
Thanks,
Chris
Hi,
I'm trying to return records from an orders table where the date of the order is >= to the date created on parts in a parts table. The syntax I'm using now is: >=DLookUp("[Date created]","[Parts]","[date created]") in the order date criteria field. I don't get any errors but the data returned is not correct..eg
Part number 802 was created on 07/10/2005 and there is an order with this part created on 07/04/2005, this order should not show but it does.
You probably need to know why this part is in the order but has been created after the order date....I'm adding inventory to an exsisting database and need to show parts on hand that are now in stock so I need to only do my calculations on parts sold that were created in the orders table with the date greater then the part created date.
Thanks for any help.
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"))))
Hi There,
I was wondering, in a form (to add records in the Table) with a date field, can I limit that field to only previous dates. This will reduce errors in the table, no future dates anymore.
Thanks in advance,
My table shows
ID ID_Clients EndDate
A particular value of ID_Clients can recur many times, but with different EndDate value each time.
I want to create a select query that displays
ID_Clients EndDate LatestPreviousEndDate
Any help with this would be much appreciated.
-Thanks
I would like a field's default value to return the date of the previous Monday.
For example:
Now = Tuesday, 08-Apr-14
Return Monday, 07-Apr-14
Further example:
Now = Saturday, 12-Apr-14
Return Monday, 07-Apr-14
I am trying to use a query to find the previous record of a transaction and provide an additional field that computes the difference in the dates in days. (The days from TransferID 23 to TransferID 24)
TransferID Job Number Tool Number TranferDate DaysOnRent
22 1 10 9/1/13
22 1 12 9/1/13
23 2 10 10/18/13
23 2 12 10/18/13
24 3 10 12/15/13
24 3 12 12/15/13
I have seen other posts that reference the following link.
[URL]
Hi
I have a table named PO table...How do I get the fiscal month from the date column I dont want to search a particular date but the date field column and retrieve the fiscal month using DLookup function?....
Can anyone help me ASAP?....
Sriram
At the moment I have a form (InputFrm) for operators to enter production processes into the system. Each one of these has a certain weight which the operators enter and it all stores in InputTbl, which works great.
However I am trying to have a running total of the kilograms inputted on each day displaying when a new record is opened. To do this I have created a query (DailyDataQry) which provides me a running sum of how much input has gone through the process, and I can clearly see the data in this query.
I am attempting to have a text box in the upper right of my form which displays this number as after a certain weight of input the operators are needed to perform checks.
This is the code I have at the moment in the control source of a text box (DailyInputTotalTxt), but it is just returning a blank:
Code:
=DLookUp("[Total Input]","DailyDataQry","[Work Date] = #" & [Forms]![InputFrm]![WorkDateAtxt] & "#")
The work date is generated automatically when the new record is created by having Date() in the default value of the work date (WorkDateAtxt) on the form.
Is there any way which I can use this value to lookup the current total of input contained within DailyDataQry?
I'm having trouble with DLookup() using dates as part of a multiple criteria. I'm using the following;
Code:
If IsNull(DLookup("ExchRate", "TBL_DDPExchRates", "CurID = " & Me.Combo4 & " AND ExchDate = #" & Me.ShipOBDate & "#")) And Me.Combo4 <> 2 Then
It seems that if ShipOBDate is any date between the first and tenth of the month the DLookup fails to locate the appropriate record. I can't understand why as ShipOBDate and ExchDate are both formatted as Short Date and ExchDate is being populated via an OpenArgs which is derived form the field ShipOBDate..Is the fact the the date is getting converted from a date to string and back to a date some how upsetting things
Hi everybody!
First of all, I didn't know how to search for the precise info regarding my question, so, If it is repeated, feel free to close this thread or delete it.
So, here it is my question:
I have a table with values taken once monthly, that is, for each person on that table, I would have a maximun 12 records per year. In that table I have the Id of the person, the date field where I store a date when I get the record, a field where I store a value and a control number field. The values on the control number field go from 1 to 20 (or less).
What I want to achieve is, given a range of dates, show the last two controls of every person, something like:
id, last control date, last control number, value of last control, previous control date, previous control number, value of previous control.
As you could see, I want to show in the same row the last two controls for each person, only if the happend to have two controls, if they have only one, it wouldn't be necessary to show them, so, how can I do this?.
Thanks in advance! :).
Hello All,
I do hope somebody can help this newbie :)
Please except apologies if I sound a bit ignorant with this but I'm complete novice with Access...
Basically I have a table with the fields Name - Date - Location I need to extract info from the table based on the location field.
i.e. return Name if a specified location exists and another specified location does not. This was real pain in itself and in the end I had to run two seperate queries which returned NAmes where each of the chosen locations exist. I've then created an additional query which compares the two sub-queries and iliminates Names that appear in sub query 2.
I do hope this make's sense I'm starting to confuse myself.....
Anyway, my problem is with the date field... I have to specify a date in order to get the correct info as the results may change on a daily basis... i.e. somebody may use both locations one day, but just the one location the next. However the eventual report which I need to generate is based on criteria over the previous 7 days, although if I use this criteria in my query in completely messes up the results.
So, what I was hoping that I could do is somehow automate my query to run 7 times (once for each of the previous 7 days) and then to combine the results of the 7 queries into a single query or report...
Failing that, could anybody think of any simple way of achieving this... or will be a case of having settle for a seperate report for each day?
Any help would be hugely appreciated... once again apologies for the explaination... It porobably makes no sense at all.
Regards,
Andrew
I'm setting up some analytical reports and i'm having a hard time with some date criteria.
I've gotten monthly averages already and what i want to do is take those monthly averages, and average them throughout the year. (the year being from the specified date on a form control back one year)
Month([Forms]![Report Runner]![Date1]) and Year([Forms]![Report Runner]![Date1]) worked great for specifying a certain month in a certain year, but what i'd like is to show all the monthly numbers for every month going back one from [Forms]![ReportRunner]![Date1].
I tried this:
Code:
>(Year([Forms]![Report Runner]![Date1])) And <((Year([Forms]![Report Runner]![Date1]))-1)
and it didnt work. How to do this?
I have table [table1] which have 4 fields
EMPID - primary key
EMPFirstname
EMPLastname
EMPDOB
Input box as txtempid as string
I am trying to learn how I to display other field based on one field in my case last three field based on EMPID
I am actually getting syntax error when I dry to display DATE Field
Following are my attempts
Dim Verfirstname as string
Dim Verlastname as string
Dim VerDOB as date
Verfirstname = dlookup("[EMPfirstname]","table1","[EMPID]='" & me.txtempid & "'")
Msgbox Verfirstname
works fine. But when I use for Date, I am getting syntax error
VerDOB = dlookup("[EMPDOB]","table1","[EMPID]='" & me.txtempid & "'")
Msgbox VerDOB ---- now I am getting syntax error
I tried to understand over net and I believe i need to used with "#" before and after the date field but my criteria...
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?
Im working on a make-table query that harvest records for calculation that span a "rolling" 13 week history... my problem is that the query will work if it doesn't have to retrieve work weeks that are prior to the new year. For example, right now is the tenth week of this year so i need to grab the previous 9 weeks of history plus 4 weeks of the prior year. then next week when the query is ran we will be in the 11th work week of the year therefor i will need to harvest the past 10 week plus 3 week of the preivious year. the query works if you don't have to span accross calendar years. here is the SQL of the query below the problem area is in underlined in bold type:
SELECT PORVPHIS.VENDOR_ID, PORVPHIS.VEND_NAME, [Expr2] AS [Fiscal Year], DatePart("yyyy",[DATE_RECV]) AS Expr2, DatePart("ww",[DATE_RECV]) AS Expr1, PORVPHIS.QTY_RECVD, PORVPHIS.PO_UNT_PRC, [PO_UNT_PRC]*[qty_recvd] AS [Total Price], [Expr1] AS [Fiscal Week], IIf([fiscal week]=[LowWeek] And [Expr3]<>0,[total price],0) AS 1, IIf([fiscal Week]=[LowWeek]+1 And [Expr3]<>0,[total price],0) AS 2, IIf([fiscal Week]=[LowWeek]+2 And [Expr3]<>0,[total price],0) AS 3, IIf([fiscal Week]=[LowWeek]+3 And [Expr3]<>0,[total price],0) AS 4, IIf([fiscal Week]=[LowWeek]+4 And [Expr3]<>0,[total price],0) AS 5, IIf([fiscal Week]=[LowWeek]+5 And [Expr3]<>0,[total price],0) AS 6, IIf([fiscal Week]=[LowWeek]+6 And [Expr3]<>0,[total price],0) AS 7, IIf([fiscal Week]=[LowWeek]+7 And [Expr3]<>0,[total price],0) AS 8, IIf([fiscal Week]=[LowWeek]+8 And [Expr3]<>0,[total price],0) AS 9, IIf([fiscal Week]=[LowWeek]+9 And [Expr3]<>0,[total price],0) AS 10, IIf([fiscal Week]=[LowWeek]+10 And [Expr3]<>0,[total price],0) AS 11, IIf([fiscal Week]=[LowWeek]+11 And [Expr3]<>0,[total price],0) AS 12, IIf([fiscal Week]=[LowWeek]+12 And [Expr3]<>0,[total price],0) AS 13, DatePart("ww",Now())-13 AS LowWeek, IIf([fiscal Week]>=[LowWeek] And [fiscal year]<>DatePart("yyyy",Now()),0,[LowWeek]) AS Expr3, [LowWeek] AS week1, [LowWeek]+1 AS week2, [LowWeek]+2 AS week3, [LowWeek]+3 AS week4, [LowWeek]+4 AS week5, [LowWeek]+5 AS week6, [LowWeek]+6 AS week7, [LowWeek]+7 AS week8, [LowWeek]+8 AS week9, [LowWeek]+9 AS week10, [LowWeek]+10 AS week11, [LowWeek]+11 AS week12, [LowWeek]+12 AS week13, PORVPHIS.DATE_RECV, IIf([Expr4]="AS8819" Or [Expr4]="CO1870" Or [Expr4]="GV2510" Or [Expr4]="MC3105",[VEND_NAME],Null) AS ID, PORVPHIS.VENDOR_ID AS Expr4 INTO Tporv
FROM PORVPHIS
WHERE (((PORVPHIS.VENDOR_ID)="AS8819" Or (PORVPHIS.VENDOR_ID)="CO1870" Or (PORVPHIS.VENDOR_ID)="GV2510" Or (PORVPHIS.VENDOR_ID)="MC3105" Or (PORVPHIS.VENDOR_ID)="IM2680") AND ((DatePart("yyyy",[DATE_RECV])) Between DatePart("yyyy",Now())-1 And (DatePart("yyyy",Now()))) AND ((DatePart("ww",[DATE_RECV])) Between DatePart("ww",Now())-1 And (DatePart("ww",Now()))-13));
I hope some one can help me......you can email me by markphillipsii@bellsouth.net or mphillips@dccabinetry.com
thanks Mark
I have managed to develop a database for my small business and am feeling very proud of myself. However, on fine tuning I would like to introduce a Date Picker for ease of use. I have a Check in Date and a Check out Date. At the moment they are working perfectly using the Date/Time field. I want to change that field to a datepicker. I have found out how to insert the date picker and it works great when inserted on a new record BUT all the previous dates in the database change to the current date. Is there a way I can still display the previous dates in the field and also introduce a date picker?
View 3 Replies View RelatedOk, so I have this table (tblPayPeriod) with the fields Start Date, Stop Date, Pay Period & Percent FY. The Percent FY field is a Number field that gives off the percent of the fiscal year (FY) that we are in.
Then I have a report (rptDistrictMgr) that in the header I want it to display what percent of the fiscal year we are in based on the Auto_Date that is in the header.
So, if the Auto_Date is December 17, 2014, we are (according to tblPayPeriod) 24.1% into the fiscal year and I want the 24.1% to display in the text box which called Text99.
I would just like a simple dlookup that I can use as the control source of the text box or an expression to put into the On Load sequence.