Dlookup Two Fields With Dates

Apr 3, 2015

I am trying to Dlookup two fields with dates in them. If the Dlookup returns that today is in the range (e.g. 06-03-2015 in the first field and 10-03-2015 in the second) then i want the system to display a messagebox when im opening up a form. If today is not in the range then the messagebox should not pop up.

Code:
If DLookup("HollidayFrom", "tblBuildingManager", "[HollidayFrom] >= Date()") And DLookup("HollidayTill", "tblBuildingManager", "[HollidayTill] <= Date()") Then
msgBox "A building manager is on holliday"
end if

The table looks like this :

BuildingManager
ManagerID
Phone1
Phone2
Phone3
HollidayFrom
HollidayTill

George Bush
1
06-11111111
06-11111112
06-11111113
1-4-2015
9-4-2015

Bill Clinton
2
06-22222222
06-11111114
06-11111115
30-8-2015
19-9-2015

View Replies


ADVERTISEMENT

DLookUp, Dates And Grouping Data

Jun 10, 2007

Hello!

Is it possible to group data in form from table within two dates with aggregate function DLookUp,

Thanx

View 1 Replies View Related

Queries :: DLookup Not Working With Certain Dates

Aug 30, 2014

Can't figure out why dlookup will not work for me in a query for certain dates?

Simple example to highlight the problem:

Table1 -

Read_Date----Read_Value
31/07/14-------10
01/08/14-------20
03/08/14-------30
20/08/14-------40

Query based on this table with the following calculated field:

Expr1: DLookUp("[Read_value]","Table1","Read_Date=#" &[Read_Date]& "#")

Query output -

Read_Date-----Expr1
31/07/14--------10
01/08/14
03/08/14
20/08/14--------40

Will not return a value for 01/08/14 or 03/08/14 (dd/mm/yy)

If you try it with many dates its skips several and I cannot see a pattern.

View 7 Replies View Related

Dlookup Using 2 Fields

Jan 8, 2006

Hi everyone,

I have a form that I use to enter information onto a table.
Lately I have noticed that there are few duplicate names been entered into the db.
The form has 2 txtboxes for the persons name. The table also has the 2 fields.

How can I make a Dlookup check both fields before allowing the new entry?

Thanks for your help.

View 6 Replies View Related

DLookup All Fields With Same Criteria

Aug 5, 2007

Hello,

I have an inquiry about using dlookup to retrive all fields from a table that are using the same criteria. Here's the table structure

Table1:
ID (Autonumber)
Number
Code
Grade

I have the following data:
Number Code Grade
4 15 68
4 52 78
4 17 69
5 85 83
5 28 17

I am using this to retrive values for number 4 but I only get the first row
DLookup("Code", "Table1", "[Number] = 4")
DLookup("Grade", "Table1", "[Number] = 4")

I get only as a result 15 and 68, how can I retrive all the rows that have Number 4?

Thanks in advance for your help

View 14 Replies View Related

Dlookup In Qry Based On 2 Numeric Fields

Nov 12, 2006

i have tblDates that has two number long integer fields named FiscalYear and FiscalMonth. then there is my field PerSeq is number long integer.

in my Query1, i have 2 fields that use a question in the field part of the qry like:
Desired FiscalYear: [Enter Fiscal Year] EG 2007
Desired FiscalMonth: [Enter Fiscal Month] EG 2

And now the Qry question. The field in the qry "Desired BaseSeq". i want this dlookup to get PerSeq out of tblDates based on my response to the Desired FiscalYear and Desired FiscalMonth.

I start it out here but it is wrong i know.

Desired BaseSeq: DLookUp("PerSeq","tblDates","[Desired FiscalYear] And [Desired FiscalMonth]=" & [tblDates].[FiscalYear] And [tblDates].[FiscalMonth])

Thanks you.

View 4 Replies View Related

Forms :: DLookup - Using Two Fields (number And Drop Down Text) To Query Table For Data

May 29, 2015

DLookup function. (this is for a stamp collection database).

On my form ("InventoryInput") I have a text box called "Catalog" for a numeric entry and a text combo box for selecting a "Country" in drop down list.

I want to query a table called "CatNameList" to get the "StampName" of the item (based on the entries of Catalog and Country) and populate that name in the text box. The fields in this table are called "StampName", "CatNumb" & "CName" respectively.

I have successfully placed the following expression in the control source of that textbox and able to populate the StampName I need based solely on the catalog number alone.

That express is :
=DLookUp("StampName", "CatNameList", "CatNumb = Form![Catalog]")

So it will populate the "StampName" data to match the "Catalog" number entry just fine.

However, I need to add a second layer to incorporate the Country.

Example : There is a catalog "1" for "USA", and a catalog "1" for "Canada" but both have different "StampName".

I have been attempting to get that second piece added with no success. Here is the expression I have been trying to get to work :

=DLookUp("StampName", "CatNameList", "[CatNumb] = " & [Catalog] & " And CName = '" & [Country] & "'")

Right now, the text box is just blank with the above expression. I thought it may be because there was no match found, but I have triple checked to ensure I have the spelling correct on the country name in both places.

Basically, I just need the dlookup to take the "catalog" and "country" off the form and match it to the "CatNameList" table fields of "CatNumb" and "CName" to give me "StampName" field back on the form.

View 4 Replies View Related

DLookup - Using Two Form Fields (Number And Drop Down Text) To Query Table For Data

May 29, 2015

Having problems getting dlookup to work in the control source field of a text box.

My form has fields : Catalog # (numeric value) and Country (drop down text selection).

I would like to query a table CatNameList for a name (text) if the catalog # and country find a match on the table.
My field names on the CatNameList table are : Name, Number (to validate against the Catalog # entered on the form) and CName (to validate against the Country drop down on the form).

I am successfully able to populate the name from the CatNameList table on my form using lookup of the catalog # using this :

=DLookUp("Name","CatNameList","Number = Form![Catalog #]")

However, I will eventually have several catalog numbers that will be identical in the table CatNameList, thus why the country is important as the second criteria to be added into the dlookup.

I have tried for a few hours unsuccessfully to add the second portion to my dlookup.

This is what I have currently (not working) that I have been playing with, I'm sure I'm missing a quote mark, & or something simple.

=DLookUp("Name", "CatNameList", "Number = Form![Catalog #] And CName = ‘”& Form![Country] & ”’”)

View 14 Replies View Related

Due Dates Based Upon 2 Fields

Jan 31, 2007

I'm creating a db that provides project listings in the work queue, along with the due dates and a bunch of other data.

I have created a table [recurring projects] that stores the projects that are done frequently. Once I do some field manipulations on this table, I append them to a master table with all projects [projects] - ad hocs, non-recurring, etc.

Most, not all, recurring projects are due 14 days from the date of assignment, however it varies. I've created a query that populates the due date 14 days or whatever the user has inputed for working days from the assignment date.

All works good with this functionality.


Here's my problem:

There are some projects that are due on the 1st, 15th or some designated date each month. Typically the projects are assigned a due date prior to the month due (i.e. February projects are assigned in January). Since the due dates change each month, is there a way to code a query to look for the first process - 14 days - and if it is null, then populate with a day of the month due.

For example, the field [days_to_complete] = 14 so the query will populate 14 days from 1/31/07 resulting in [due_date] = 2/14/07 OR [days_to_complete] is null, but [day_of_month] = 1 , which I need to create the [due_date] = 2/1/07

I'm racking my brain and pencils are being cracked!

Thanks,
Nathan

View 5 Replies View Related

Is It Possible To Add Extra Fields To Dates?

Dec 3, 2007

Is it possible to have an extra field that is added to the calculated weeks left? Such as what if I want 2 extra weeks instead of the 12?

I know this is probally an advance question! Thanks in advance!!!


This is the awesome example from sbenj69:

Expr1: 12-(datediff("ww",[join date], date())

What I want to do is add another column which will have an additional number. The additional number would just be added to the 12- part of the code. So if I have a field with 2 in it, the 12- would become: 14-(datediff("ww",[join date], date())

Is this truly possible or is it something that can't be done in access

View 5 Replies View Related

Calculate Fields Between Dates

Aug 10, 2015

I have a database that I need to calculate a query that is based on 3 dates. For example...I have an admission date and a discharge date in my database.

If there is no date in the discharge date then I need it to calculate the number of days between the admission date and "today's date." If there is a discharge date then I need to calculate the difference between admission date and discharge date.

Basically i need a length of stay figure. I can figure the formula in excel but cannot get it to work in access. Here is my formula in excel

=If(ISBLANK(DischargeDate),Sum(today()),sum(Discha rgeDate-AdmissionDate)

View 2 Replies View Related

Queries :: Multiple Dates Fields

Nov 15, 2014

I have a DB that consists of Movie titles and the multiple dates ( as many as 10) on which they will be used in the coming year.I built a flat table with 10 date fields.Then tried to build a relational table with just movie title and dates linked to ID.I cannot work out an ability to SEARCH the Database for a SPECIFIC DATE and get returned a listing of ALL Movie titles that will air on that date,

View 9 Replies View Related

Forms :: Compare Dates In Two Fields?

Jun 18, 2014

I would like to change the colour of field on a form if the date in that field is later than the date in another field.

View 9 Replies View Related

Queries :: Query For Same Fields / Different Dates

Jul 3, 2013

I have a query that is based on three queries. 2 of those queries have the same fields but return data for different dates. The 3rd query returns the dates needed. In other words, I am trying to return all results from both queries. these are the results I want:

Report_ID Report_Date Contents Approvers
Standard ops 5/1/13 daily operations rpt tom tomlin
royer manuf 7/1/13 syndication rpt rob davis

etc.

These are for year-t0-date. there is a YTD query that returns all dates needed. The other two queries return records on different dates, on is for dates <5/5/13 and the other is for dates >5/4/13. I have listed the SQL below. It returns the correct records, but for the Record_ID it returns -1 in each field instead of the report_ID text. So, I need to get the other fields to show up correctly.

Code:
SELECT [Daily_rpts_YTD_due_before_5_5_2013]![Report_ID] Or [Daily_rpts_YTD_due_after_5_4_2013]![Report_ID] AS [Report ID], due_dates_Daily_YTD.Due_dates
FROM (due_dates_Daily_YTD LEFT JOIN Daily_rpts_YTD_due_before_5_5_2013 ON due_dates_Daily_YTD.Due_dates = Daily_rpts_YTD_due_before_5_5_2013.Due_dates) LEFT JOIN Daily_rpts_YTD_due_after_5_4_2013 ON due_dates_Daily_YTD.Due_dates = Daily_rpts_YTD_due_after_5_4_2013.Due_dates
WHERE (((due_dates_Daily_YTD.Due_dates)=[Daily_rpts_YTD_due_after_5_4_2013]![Due_dates] Or (due_dates_Daily_YTD.Due_dates)=[Daily_rpts_YTD_due_before_5_5_2013]![Due_dates]));

View 2 Replies View Related

Queries :: Selecting All Date Fields Between Two Dates?

Jul 31, 2013

I'm trying to select a range of relevant dates for an amortization calculation (see my earlier thread on this subject here), but I'm having a bit of trouble making the SQL work.

I have a table called "t_AllMonths" that has only one field: MonthStartDate which contains the first day of the month for a very wide range of months over something like a ten-year period. I'm calculating amort for assets which will be amortized for some subset of those months (defined by the asset's Amort Start Date and Amort End Date). Further complicating matters, the amortization may be suspended during certain "hiatus" periods when the asset it not planned to be in use, and may differ by which business units make use of the asset.

So, I have three tables.

Table: t_Assets
Fields: AssetID (autonumber; primary key), Asset_Name, Asset_Cost

Table: t_AllMonths
Fields: MonthStartDate

Table: t_AmortPeriods
Fields: AmortPeriodID, AssetID, Amt_Period_Num (which I don't expect to use in this), StartDate and EndDate

Right now, I'm just trying to pull the range of dates between the earliest amort start date and the latest amort end date. (Min of StartDate and Max of EndDate, respectively) for a given AssetID.

My sql looks like this:

SELECT t_AllMonths.MonthStartDate,
Min(t_AmortPeriods.StartDate) AS MinOfStartDate, Max(t_AmortPeriods.EndDate) AS MaxOfEndDate,
t_Assets.AssetID
FROM t_AllMonths,
t_Assets INNER JOIN t_AmortPeriods ON t_Assets.AssetID = t_AmortPeriods.AssetID
WHERE ((t_AllMonths.MonthStartDate) Between [MinOfStartDate] and [MaxOfEndDate]);

I keep getting an error message that reads "Run-Time Error 3122: You tried to execute a query that does not include the specified expression MonthStartDate as part of an aggregate function."

View 5 Replies View Related

Queries :: Expression Summing From Fields With Dates

Aug 3, 2015

I am involved in centrally managing a project , a part of which involves employees of certain companies being given a weekly subsidy for study on academic courses. I have successfully set up a table for all the learners and a related table for companies.

Each learner will receive a fixed weekly rate subsidy in the first year, and a lower fixed weekly rate subsidy in the second year. Somebody (not me) will need to check their paychecks to ensure that employees have been paid and all is above board. The learners/employees can start on the programme at any time. I need a way to track this (total claimed amounts, where they're up to etc).

My theoretical solution, is to have a field for each week a learner is on the programme (52 first year, 52 second), and instead of entering a currency amount in the field, the check-person can enter the date they saw the evidence and are happy with it. I thought then if I create a query that searches any dates that fall within the current claim period (eg >01/01/2015, <=Date()), and multiplies field count that fall within those parameters by first/second year subsidy rate if they're in that table. I think my logic is sound, I am just not sure how to write the query/expression, or if it's possible, or if there is a much simpler way to do it.

View 4 Replies View Related

Calculating Number Of Work Dates From 2 Date Fields

May 29, 2005

In my Query I have a Date Received field and a Date Approved field.

I would like to calculate the number of work days, which excludes weekends, between Date Received and Data Approved fields.

It would be more desirable to calculate the number of work days, excluding weekends and governmnet obsereved holidays.. but I don't know if that can be done... If not I can stick with the number of work days excluding weekends.

How would I go about doing this? I would need to add a new column to my query?

What would I put for the field and criteria? What kind of expression do I use?


Any help will be appreciated, thanks

View 14 Replies View Related

Forms :: How To Get Dates To Show In Last Updated And Last Viewed Fields

Mar 18, 2013

how can I get dates to show in last updated and last viewed fields..I did get last updated working but for some reason when I tried to do last viewed, last updated vanished :/

I did try writing code (but im a novice lol, I think that's why last updated vanished).I have got 2 unbound fields called HiddenLastViewed and HiddenLastUpdated thought I might need them.

View 14 Replies View Related

Combine One Table With Three Date Fields (all With Short Dates)

Feb 14, 2013

I have a table called Packages, in this table I have multiple fields with just short dates example of fields (ID), (Home_1), (Home_2), (Home_3) and (Home_4). What I was wondering is there a way to comine all of the Home fields into one for the purpose of counting the number of dates for all the records. Total number of dates of all the Home fields? I am using Access 2010

View 1 Replies View Related

Queries :: DateDiff Function - Calculate Difference In Dates Between Two Fields

Apr 22, 2013

I am trying to calculate the difference in dates between two fields. How do I find the difference in days between field one which contains the date 04/12/2011 and field two which contains the date 04/12/2013? I have tried to use the datediff function, but it keeps telling me it doesn't recognize the field name, even though the spellings correct.

View 3 Replies View Related

Find Matching Dates In Tables A And B / Then Populate Blank Fields In A From B

Apr 30, 2012

I have a table tROE with a field listing all dates starting 1/1/10 to date (populated), and three fields for currency exchange rates [USD], [RSD] & [EUR] which are empty and need to be populated. I have another table tROEPartial that has the exchange rates for some of the dates starting 1/1/10 but not all. Their structures are identical. I want to add the exchange rates from tROEPartial to tROE where the dates match, leaving the unmatched fields in tROE blank.

View 3 Replies View Related

Queries :: Return Records Between Dates Based On 2 Date Fields In A Table

Apr 24, 2013

I have a table which includes a start date field and completion date field for housebuilding.

I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.

It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.

View 7 Replies View Related

IIF Statement - Calculate Value Based On Comparing Current Date To Dates In Fields

Jun 27, 2013

In my table I have the following 4 fields with the associated date field:

Bronze: 11/1/2013
Silver: 5/1/2014
Gold: 11/1/2014
Platinum: 5/1/2015

I am trying to calculate a value based on comparing the current date to the dates in these fields. I am using the below formula. However, using 6/27/2013 as the current date, my formula keeps resulting in "Bronze" when it should result in "Standard" Am I doing something wrong?

=IIf(Date()<[Bronze],"Standard",IIf((Date()>=[Bronze]) And (Date()<[Silver]),"Bronze",IIf(Date()>=[Silver] And Date()<[Gold],"Silver",IIf(Date()>=[Gold] And Date()<[Platinum],"Gold","Platinum"))))

View 5 Replies View Related

Queries :: WHERE Statement - Enter Dates Into Date Reported Fields For Results To Show

Sep 11, 2013

I use this

'WHERE ((OperationalRiskEventTable.DateReported)>=Forms!U pdateForm!UDateBegin And (OperationalRiskEventTable.DateReported)<=Forms!Up dateForm!UDateEnd)'

in a query by form.

The problem is that you have to enter a date in the between values for results to show. If I don't enter information into a different field such as Full Name but I enter in 40 into Age then everyone that is 40 years old will show. On the other hand if I enter 40 into the Age field but I leave the Date Reported fields empty then no results will show.

How can I change it so that I don't have to enter dates into the date reported fields for results to show?

View 4 Replies View Related

Reports :: Get Data Fields From Another Table Based On 2 Dates In First Table

Oct 29, 2014

Anyway, I would call myself intermediate level at best with Access. I never expected to have to do so much with it, but when my bosses found out I could do Access basics, they began demanding more and more.

I manage an EMR from which a datapull occurs on discharge to various access databases.

They wanted me to add a triage patient data pull to track what procedures are being done to triage patients. So I built a database with the following 2 tables.

tblTriageVisits and tblTriageProcedures
tblTriageVisits has the following fields
-Patient ID
-Export Time

[Code].....

The tblTriageVisits stores all the patients triage visits. The other table stores what was done to each patient on those visits

It should also be noted that this EMR exports times in number of seconds since 12/31/1975, so TimeProcFMS is the number of seconds since that date for example.

The pulls work great and the duplicate record elimination method works great.

I have to design a couple of different reports based on this data and one in particular has pretty much flumoxxed me.

I need to make a report that lists each patient by date they were there and then shows what was done to them. I have tried several different variations of this as both queries and reports. The report breaks down when I try to get it to show just those procedures that would be for that triage visit. I got to the point where it would group by day and then sort the patients alphabetically. Then I added in the visit fields. And it would put all triage procedures for that patient from all their visits in each visit.

So, I was like great, let me filter out those that don't occur between the two dates, admission and discharge

I used the following two formulas (the first one on the procedure name field the second on the procedure date field)

=Switch(DateAdd("s",[TimeProcFMS],#12/31/1975#)>=[Admission DateTime OB DateTime] And DateAdd("s",[TimeProcFMS],#12/31/1975#)<=[LD Discharge Date/Time DateTime],[Procedures FMS])

=Switch(DateAdd("s",[TimeProcFMS],#12/31/1975#)>=[Admission DateTime OB DateTime] And DateAdd("s",[TimeProcFMS],#12/31/1975#)<=[LD Discharge Date/Time DateTime],DateAdd("s",[TimeProcFMS],#12/31/1975#))

And they work, sort of. They do eliminate those procedures that occur outside of the date range I am looking for. However, the access report still leave spaces in the report where the filtered out items would be.

Did I approach this problem correctly? And if so, how do I get rid of those spaces?

Note, I can read and mostly understand SQL, but am not comfortable working in it yet. And the Access SQL box gives me a headache, it is so jumbled up.

View 14 Replies View Related

Forms :: Dates As Column Headers To Update Table With Dates As Rows

May 12, 2014

Any way to have a form with Dates as column headers to update a table where the dates are stored in rows???

The table set up is like this:
tblOpHdr
DiaryID (PK) - OpDate (Date)

tblOpDetail
DiaryID (FK) - CostCode - MachineNumber - MachineHours - etc

I'm just wondering if there's any way I can do this with a datasheet or a crosstab type setup?

It's Access 2010.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved