Queries :: Using DLast With Criteria

May 15, 2014

I'm trying to generate a list of employees who haven't had a review since Dec 2013.I'm using this expression in my query:

Last review: DLast("[ReviewDate]","[Tbl_OutcomeHistory]")

which brings up the last date the employee had a review.(I tried using DMax but it didn't seem to like dates?)How do I get it to just show those who haven't had a review since 1st Dec 2014. I thought I could use "<01/12/2014" in the criteria but it returns no values. I've tried to drop the date into the expression but that returns no values.

View Replies


ADVERTISEMENT

Need Help In Using Dlast Function

Feb 5, 2006

Iam traying to get result of my record as below

If next record is biger show "1"
If next record is smallest show "0"
If next record is the same Show "---"

I have used the following formulla in query

Res: IIf(Nz([Price]-DLast("[Price]","[tt]","[id]<" & [id]),1)=0,"-----",IIf(Nz([Price]-DLast("[Price]","[tt]","[id]<" & [id]),1)<0,0,1))

When I use the query it gave me wrong result.

I have attaced the DB for your reference and correction :

the query will ask to enter a code : Please enter code: 1020,1030 or 1010

any one can give help to solve this issue?

thank you

View 14 Replies View Related

DLast Not Getting Last Record?

Jul 9, 2013

I have used a form for years and DLast worked. But all of a sudden DLast is returning a record in the middle of the table from 2 years ago. One is a text box. One is a List Box. Both are getting the wrong record. All other forms using DLast on different tables are working OK.

I haven't changed the form.

=DLast("[DebitCredit]![DCDate]","[DebitCredit]")

What happened?

View 5 Replies View Related

Using DLast Or DMax Functions Within SetValue Action In Macro

Oct 28, 2012

I am building a purchase requisition (PR) database.

The tables are (simplified) as follows:

Table1 - tbl_PRList - contains only 3 fields with one of these fields being the "PR No".
Table2 - tbl_PRData - contains all the item data, also with a field "PR No".

The intent of the "item entry" form is to add items to the latest PR that has been generated.

So, whenever an item that is to be placed on the new PR, you type in the item description, qty, etc in the form and then you press a button that saves this item to the main 'data' table (Table1). What I would like to do is, "on click" of this button, I'd like this record, prior to it moving to the next record, to set the value of the PR No field in Table 1 to be the same as the DLast value of the Table2 table.

View 2 Replies View Related

Queries :: Method For Combining Crosstab Queries With Same Criteria From Multiple Fields?

Dec 2, 2014

I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?

QUERY1

Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT

[code]...

View 2 Replies View Related

Criteria In Queries

Mar 5, 2006

Hi group members

I guess this is a fairly basic question but bear with me.

As with all newbies 'how can I' type questions come thick and fast and this one involves using Criteria in a Query. I am comfortable with the concept of prompting for a variable within a query using the square brackets ie.[Enter Post Town]. This of course relies on the person entering a valid value or spelling it right.

So I thought, what if I make Post Town a Combo box (I am working with a small sub set of places). Which then brings me to my question.

Can I create a query (which runs a report) that when activated will prompt for variable (in my case Town) and display the drop down list ?

Thanks in anticipation

Les Wilcock

View 3 Replies View Related

Criteria Help With Queries

Aug 1, 2007

I Currently have a query that has various information on it. WHen i click to open the query, It states to "enter line number", where i can enter a line (e.g. line43). If entered EXACTLY as it is found in the table where i took the information from, the records will show. However if i type in 43, or L43 or line43/44, nothing will show up. How can i make it so that there are multiple ways to show records, when i enter different information? (There are about 8 different lines).

Thanks to anybody who takes the time to help me solve this problem.

View 4 Replies View Related

Queries With Over 100 Criteria

Apr 18, 2008

Hello everyone

I am just trying to write a query by a code of table
i would like to pick up any record with id number betwee 1-99 and 200-299
i try typing them in manually but access wouldn't cope

any idea of how i can achieve it please
Many Many Thanks
Si

View 2 Replies View Related

Queries And Multiple Criteria

Jun 2, 2007

Is there a way to produce one query that will produce several results that display in a report that is generated from a button? The only difference is the criteria.

EX:
SELECT tblDownTime.dtDate, tblLine.lineName, Sum(tblDownTime.dtDowntime) AS [Total Time Down]
FROM tblMachCent INNER JOIN (tblLine INNER JOIN (tblCategory INNER JOIN tblDownTime ON tblCategory.catID = tblDownTime.catID) ON tblLine.lineID = tblDownTime.lineID) ON tblMachCent.machID = tblDownTime.machID
WHERE (tblDownTime.dtDate) Between [Forms]![frmDTGraphs]! And [Forms]![frmDTGraphs]![end]))
GROUP BY tblDownTime.dtDate, tblLine.lineName
HAVING (tblLine.lineName)="[B]name of line");

criteria being name of line. Choices being line 1 or line 2

What I am looking for is one query to somehow generate the info on the two different lines in two different reports.

Is there a way or do I actually have to write the different reports?

View 4 Replies View Related

Crosstab Queries With Criteria

Feb 8, 2008

New to this site. very impressing

I am trying to create a crosstab query, but can get it right.

Table1 = input Table
__________________________________________________ ___________
name StartDate date Present
A 1201 1201 P
B 1202 1205 P
C 1203 1206 P
D 1204 1207 P
E 1206 1209 P


Can get this result with Crosstab Query;

TRANSFORM NZ(First([Table1].[Present]),"a") AS FirstOfPresent
SELECT Table1.name, Table1.StartDate
FROM Table1
GROUP BY Table1.name, Table1.StartDate
PIVOT Table1.date;

__________________________________________________ ____________
name StartDate 1201 1205 1206 1207 1209
A1201 P a a a a
B 1202 a P a a a
C 1203 a a P a a
D 1204 a a a P a
E 1206 a a a a P


But am looking to add NA for StartDate > Header Date(1201,1205,1206,1207,1209). See below. Does anyone have a solution or an alternate method


Looking to get this type of results
__________________________________________________ ____________
nameStartDate12011205120612071209
A1201 Paaaa
B1202 NAPaaa
C1203 NAaPaa
D1204 NAaaPa
E1206 NANAaaP

A=Absent
P=Present
NA=Non Applicable

Thanks Tony

View 2 Replies View Related

Multi-criteria Queries?

Apr 8, 2008

Ive had a quick look at other threads to see if this question has been asked already, so hopefully Im not repeating a previous question!

I am completely new to Access but have been asked to perform multi-criteria queries across multiple tables. I realise it would be easier to just put all the information into one table, but if anyone could explain how to do the above I would be very grateful.

Thanks

View 6 Replies View Related

Criteria In Append Queries

Nov 11, 2004

I have a table that I want to append to another table, but I do not want to duplicate records if the check number is already in the table I am appending to. I have tried
<>[deposit].[check_number]
or
not like [deposit].[check_number]
or
not [deposit].[check_number]
but I always get prompted to enter a check number... therefore the criteria statement not working.
do not know what I am doing wrong.

View 5 Replies View Related

Same Criteria - Multiple Queries

Nov 23, 2005

I have multiple queries pulling data from multiple data tables but are all common by a date that I have to enter in the criteria field for each query each time I need to change the date. Is there a way for the criteria field to read data (the date I want) from somewhere so I only have to change the date in one place and is read into each query where specified? I'm having to go into many queries to change the date criteria which I change to the same date for all queries - I'd like to do it once and have the queries read that date.

View 1 Replies View Related

Queries :: IIF Expression With Three Criteria

May 14, 2015

I'm a little new to Access but I'm trying to create an IIf Expression:

iif ( condition, value_if_true, value_if_false )

Mine is: Au_ppb: IIf([Element]="Au" And [Units]="ppb",[Result],Null)

I need to have three criteria's (each looking into a different column). I can get two criterias but not three:

[Element]="Au" And [Units]="ppb"

When I add in the third (And [Priority]="12") it doesn't work .

Also, I need to have about 6 columns with similar IIF statements in the one query in my query and if a row has Null for all then I don't want it visible however if I make the Criteria "Is not Null" on one column or on all it comes up with no results.

View 2 Replies View Related

Queries :: Dlookup With A Between Criteria

Aug 6, 2013

I have two tables

Criteria_Ratings:
FFMin
FFMax
Rating

BaseData:
FF/M

What I am trying to do with a Dlookup is return the Rating when FF/M is between FFMin and FFMax. All fields are Numbers.

FFRating: DLookUp("Rating","Criteria_Ratings",[FFmin]<[FF/M] And [FFmax]>=[FF/M])

View 4 Replies View Related

Queries :: DCount Against Two Criteria

Nov 5, 2013

I am looking to create a summery on a dataset. I have

Employee ID Duration
1234..............10
1234..............20
1234..............8
1234..............9
1236..............40
1236..............2
1236..............20
1236..............10

I want to summerise the data to calculate the number of times an employee has an entry under 20 and the calculation. e.g.

Employee ID....Count.....Sum
1234...............3...........27
1236...............2...........12

I used a DCount however this does not separate the employees. Currently using
Count Of Visits: DCount("[Duration]","Test Query","[Duration]<20 AND [Employee ID] = [Employee ID]")

View 1 Replies View Related

Queries :: Possible To Set A Criteria On A Field?

Aug 23, 2013

In an Access 2010 query is it possible to set a criteria on a field (Results), that If it is "Negative" then another field (Variants) has the text "None Detected". Or is it easier to do that in the Variants field?

View 1 Replies View Related

Queries :: IF Statement With Criteria

Apr 21, 2013

I am working on one access database wherein in query i need to use iif statement, i have one field called Age, in field criteria i need to use,

IIF(weekday(DATE())="2",>"3",>"1")

this if staement is not working i believe that we cant use ">" ... this type of criteria with iif, so what should i do?

my intention is if today is Monday than age filed >3 else it should be >1

View 2 Replies View Related

Queries :: Use Three Criteria In IF Statement

Sep 23, 2013

Here are the criteria I am using:

Current Week: DatePart("ww", [Funded_Date]) = DatePart("ww", Date()) and Year([Funded_Date]) = Year(Date())

Current Month: Year([Funded_Date]) = Year(Now()) And Month([Funded_Date]) = Month(Now())

Previous Month: Year([Funded_Date])* 12 + DatePart("m", [Funded_Date]) = Year(Date())* 12 + DatePart("m", Date()) - 1

I am able to successfully set one of the above as a criteria just fine.I am stalled on trying to combine two or more of the above in an iif statement. Here is what I 'think it should look like:

Funded_Date_Period: IIF([funded_date] = "Year([Funded_Date])* 12 + DatePart("m", [Funded_Date]) = Year(Date())* 12 + DatePart("m", Date()) - 1)", "PrevMonth", IIF([funded_Date]= "Year([Funded_Date]) = Year(Now()) And Month([Funded_Date]) = Month(Now())", "CurrentMonth", IIF([funded_date]="DatePart("ww", [Funded_Date]) = DatePart("ww", Date()) and Year([Funded_Date]) = Year(Date())", "CurrentWeek")

In other words, in a separate field, I would like to print a period name such as above.I have not been able to find any reference to connect these long criteria together in one query expression.

My workaround is to use a union query. That works, but it is a little clumsy due to some outer joins which require a separate query. If that is my only solution, I will run with it.

View 4 Replies View Related

Queries :: Using IIF And OR In Query Criteria

Apr 24, 2015

Basically, I have a Combo Box [Forms]![Sales]![Site] which a user can use to select either an exact location or a market area for multiple locations.

I also have a query for sales with a SellingBranch field on.

I want the query to display sales for only specific selling branches as chosen using the above Combo box. However, I'm struggling to get it to display multiple selling locations for one selection. For example I want to tell it that if [Forms]![Sales]![Site] is "Bolton MA" then the query needs to show results for when the SellingBranch is either "Bolton" or "Chorley".

At present I can get it to work to show just Bolton but don't know how to adjust it to show Bolton and Chorley when "Bolton MA" is selected.

IIf([Forms]![DatesSalesLeague]![Site]="BOLTON MA","BOLTON","MANCHESTER")

View 1 Replies View Related

Queries :: Using A Function To Set SQL Criteria

Feb 5, 2014

I am using a function to set criteria in a an query. It reads the selected values from a combobox on a form and passes the appropriate value into the sql criteria.

E.g., the sql criteria is set to : like fnCountry()

And the function fnCountry() is something like;

If SelectedCountry = "All" Then
fnCountry = "*"
else fnCountry = SelectedCountry
end if

This works fine for a single selection (SelectedCountry = Africa) but doesn't work if I try to combine multiple selections into the criteria string.

E.g. SelectedCountry = Africa Or Italy

So the criteria would need to be
Like "Africa" Or like "Italy"

How else can i build this criteria with multiple values?

View 3 Replies View Related

Queries :: Using IIF For Sort Criteria

Jul 23, 2013

I am creating a query to select and sort events whose payments are overdue, prioritizing those that are most overdue at the top. The catch is that the due dates for payment varies based on several conditions and criteria.

Here's my end goal of the sort order (it's not in SQL, I'm just describing it):

1st step- If [Program_Code]="BD" Or "GT" Or "SG" Or "SC" Or "PR", then the sort criteria is [Date_of_Event] < Date()

2nd step- If [Program_Code]="WE" Or "KD", then the sort criteria is [Date_of_Invoice] < Date()

3rd step- If [Program_Code]="ZM" And [Cost_Category]="Full Price" Or "Discount", then the sort criteria is [Invoice_Date]+30 Days is < Date()

4th step- If [Program_Code]="BD" Or "PR" And [Deposit_Paid] Is Null, then the sort criteria is [Invoice_Date] < Date()

I'm not sure how to go about this but I have a feeling it involves an iif statement in the SQL and/or breaking it into smaller queries that are then consolidated into a "master" query.

View 14 Replies View Related

Search For Queries That Use Specific Criteria

Jun 6, 2005

Hi,

Is there a way to search for queries that use specific criteria?
Let's say I have 60 queries in total, but only 35 of them use the "Province" field as criteria. The criteria is set to retrieve all records that are in Province AB, SK, ON.

Suddenly we need to also include Province MB to all of these 35 queries.
Is there a way to identify these 35 queries (all the queries use criteria in the "Province" field). These are the queries that would need to be modified to include "MB" as part of the criteria.

I hope my explanation is clear.
Thanks upfront for any suggestions!

BJS

View 4 Replies View Related

Problems In Setting Criteria In Queries

Jul 4, 2005

Hi,

I have a problem in setting the criteria of queries.

I have two inspection methods: ABC and XYZ. Every two years, ABC will be carried out, and all other years, XYZ will be performed. However, information to which factory XYZ is performed is required. If ABC is carried out, then the information can be simply "N.A".

But when I do the query, I therefore need to add an extra field which will show "N.A" if ABC is performed, or the factory name if XYZ is performed (factory name can be retrieved from another table).

May I know how do I set this criteria in Query?

Thanks in advance.

View 1 Replies View Related

Query With Criteria On Two Existing Queries.

Aug 9, 2005

Hi,
I am in need of help to sort out some records. I have tow existing queries I would like to combine and get one final set of records out of. They go like this:

Query 1. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 x x (check boxes)
Y ACD 2 x x

Query 2. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 8/8/05 9/8/05 10/8/05 (query performs
Y ACD 2 8/8/05 9/8/05 10/8/05 calculations)

What I am looking to retrive through the third query is this:

Query 3. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 8/8/05 9/8/05
Y ACD 2 8/8/05 10/8/05

Where the third query only shows the calculated dates when the check box is true. I have tried to go through the expression builder, but to no avail. I either get all records, like query 2 or I get nothing reported. I am not sure how to limit the records based on the check boxes.

Thanks,
CB

View 1 Replies View Related

Running Queries With Multiple Criteria

Dec 20, 2005

I need to run a query using a list of unique values. I open a new query in design view, pick my table that I want to use, pick the field, but then in Criteria, I need to use a list of values. The list is 62 values long. Any help here would be greatly appreciated. I hope I am explaining myself thoroughly.
Thanks,
a_brooks

View 5 Replies View Related







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