Queries :: Add Date Selection Criteria To Query?

Sep 11, 2014

Below is the SQL for a query I have, which returns events that are scheduled for today, I need to change this so that The user can specify events to be displayed in a date range and If possible only show those events for the users windows ID

Code:

SELECT tblEvent.EventStart, tblEvent.EventOwner, qryCompany.Company, ltDescriptionType.Description
FROM (tblEvent INNER JOIN qryCompany ON tblEvent.Company = qryCompany.ContactID) INNER JOIN ltDescriptionType ON tblEvent.EventDescrip = ltDescriptionType.[DescriptionType ID]
WHERE (((tblEvent.EventStart)=Date()))
ORDER BY tblEvent.EventStart, tblEvent.EventOwner;

View Replies


ADVERTISEMENT

Queries :: Expression Asking For Parameter - Date Selection Criteria

Jul 31, 2013

I'm trying to create an query that has two separate expression in it and a date selection criteria. The first expression is,

Expr1: DateDiff("s", [Arrival Time 1],[Departure Time 1])/3600

and the second is,

Expr2: IIf([Expr1]>[Hours Per Day],[Expr1]-[Hours Per Day]

When I run this query it asks me for the date (which is fine) but then it also pops up a box asking for "Expr1". How can I get "Expr2" to use the value returned from "Expr1"?

View 3 Replies View Related

Queries :: Alter Selection Criteria Of A Query From A Form

Nov 25, 2013

I have a form that runs off a query that displays further details of a record in a datasheet when you double click on a row.

The query itself has criteria that looks at the open form and selects the correct record.

My question is can you change the results of the query either using vba or a built in feature of access without having to use a separate query.

The selection criteria in the query is:

Forms]![Main]![Ordering-Supplier]![Ordering-Order List].[Form]![suppOrderID]

Basically I want to override the resulting data with another record when I run an event on the form.

View 3 Replies View Related

Date Selection Criteria - Show Last Day Of The Every Month

Apr 16, 2015

I have a big list of data, with a row of data for every day for each client.

I need a selection criteria that will provide show the last day of the every month (historic data) for each client.

I've had a go but I'm not making much progress.

View 4 Replies View Related

Reports :: Selection Criteria - Date Range To Be Queried

Sep 18, 2014

I have a report based on a query that returns all info from the query which is fine, I now need to amend this so that individual users can specify the date range to be queried and the person for whom the results are required (one of the query strings)...

View 1 Replies View Related

Queries :: Put Date Criteria In Query

Dec 10, 2014

I have a table with a field TDate (dd/mm/yyyy format). A query with calculated fields is lying on this table. I want to put a date criteria in this query, by a combobox in an unbound form, where the date format has to be mmmm/yyyy and has to be updated as new TDates come in.

Trying to pick up values for the combobox from the TDates field (and formatting them), I get a list with several same values. This is of course expected as there are many records on the same month, even on the same day of the month.Is there a way to have this list with unique values for each TDates month/year?

View 3 Replies View Related

Queries :: Using Part Of Date As Query Criteria From Combo Box?

Apr 4, 2013

I have a query with a date field that is formatted mm/dd/yyyy. I have a combo box that is formatted as mm/yyyy. I need the choice from the combo box to be the criteria for the date field in the query. the combo box has to contain the month and year only (which it does now) and the query must return mm/dd/yyyy. I have tried a few statements and the closest I got was a between statement that added 30 days to the combo box selection but that's not really accurate.

View 2 Replies View Related

Queries :: IIF Statements Within Query Criteria (Actual Date)

Jul 9, 2013

I am working with Access 2007. I have a little problem building the iif statement within a query criteria in [Actual Date] which is the following:

iif([Actual Date] Is Not Null And [Document]=0;>#01.01.2012# Or Is Null;999)

When I enter this I get no records out of about 8000. The table fields behind the query are in the right format (date/time and number).

View 1 Replies View Related

Queries :: Date Prompt Criteria In Crosstab Query

Apr 11, 2014

I have a table of data going back to 2007 that needs to be looked at on a monthly/quarterly/annual basis. I am able to filter the data when running a normal query by using

Between [Start Date] and [End Date]

in the Criteria section of the Date field. I now need to apply this same idea to a crosstab query. My current set up is:

[Gender]- Group By / Row Heading
[Plan Type]- Group By / Column Heading
[Pmt Amt]- Sum / Value
[Date]- Where / Criteria = Between [Start Date] and [End Date]

I am getting an error message that says:"The Microsoft Office Access database engine does not recognize '[Start Date]' as a valid field name or expression."Am I setting something up incorrectly or is it impossible to use input prompts in a crosstab query like this?

View 1 Replies View Related

Queries :: Query To Select The Date From Two Tables On The Criteria Basis?

May 21, 2013

get the data from two tables on the basis of criteria...

I want to select the whole table1 which has 6 fields including Emp_id...

I want to select the single field from table2. field name is "Username" from second table2. will select the username on the basis of Emp_ID becaue both tables has same emp_ID.

It should be in order like. Emp id, Username, Startdate, Enddate, Hours, trainingNames.....

View 1 Replies View Related

Queries :: Date Formatting Criteria - Query Returns No Data

May 7, 2015

I have a query in my database called "Open Date" which is a date formatted field when imported as DD/MM/YYYY. The criteria is set to:

>=#06/04/2014# And <=#05/04/2015#

The query works just fine. My customer has come back and asked for the way the date is displayed to be in the YYYY-MM-DD format (i know it's not ideal but thats what they want). When i've done that using the following

Format([Table].[Open Date],"yyyy-mm-dd")

The query then returns no data.

View 6 Replies View Related

Queries :: Mailing List For Specific Month - Date Criteria Query

Jun 11, 2013

I am trying to create a mailing list of patients. Let's say I am creating a mailing list for February. I need the mailing list to consist of people who have had surgery in February from the beginning of the database, and people who have had surgery three months ago, so anyone who had surgery in November. I have created a form that has a button which is connected to a query, the form has a unbound textbox where I can enter the month in (2 for February). Then the query uses the datepart function to search for this month in their date of surgery. But this only gives me people for surgeries with february, how would I get people who have had surgery three months ago in the same query.

View 2 Replies View Related

Queries :: Access Database - Query Based On Multiple Day / Date Criteria

Jul 26, 2015

I am currently working on a project to develop an access database to manage a roster of calls to clients on a daily basis based on two general criteria:

1. Pre-determined days selected by the client. (e.g. Call Mon, Wed, Fri only. This can change as client requirements change.)

2. Ad-hoc changes based on the client’s circumstances. (e.g. No call from 27/7/2015 to 29/7/ 2015)

I have managed to successfully deal with the second of these with the following expression in a query:

CallToday?: IIf((Date()>=[NoCallFrom] And Date()<=[NoCallTo]),"No","Yes")

However dealing with the first is a little more difficult to work out. I have tried a multivalue lookup field with multiple days selectable, but constructing an IIF query to deal with these multiple values is proving quite a challenge.

I am thinking of using a table with days of the week and a junction table to allow the multi-selection, but I may need constructing the relationships and the query here.

View 8 Replies View Related

Queries :: Date / Time Field - Query Criteria To X Quarter Of Y Year

Jul 24, 2014

I have a form (frmMetrics) with 2 Combo Boxes: "Year" and "Quarter" These are just number fields (which might be my problem?)

I have a date/time field in a query, and I want the criteria to be based off the year and quarter selected in frmMetrics.

View 2 Replies View Related

Using A Combo Box Selection For Query Criteria

Nov 1, 2006

I have a report that is based on a query. In the query, I have a field called "Mode". In the criteria section for the Mode field, I am calling the selection from a combo box on a form called "frm_main". So the criteria for the Mode field is "[Forms]![frm_Main]![Mode_ComboBox]". This combo box has selections for 1,2,3,4,5. I want a selecton on the combo box that will work with the query to show all modes. Sometimes something weird gets entered into the database like "NoMode" for example. I have tried adding a combo box selection of "*" , "Is Not Null" and "". If I manually type these into the criteria section of the query, it works fine. But when I use these in the combo box and call the combo box selection from the query, it does not work.

Does anyone have any idea what I can use in my combo box selection that when selected, the query (and hense report) will show all data?

Thanks,
Jim

View 5 Replies View Related

Queries :: Max Date Selection - Subquery?

May 23, 2013

I need to select a record with the latest (max) date along with associated values that go along with that record. However, some records have the same date. Initial query sorts by Asset_ID and Date (Desc)...A second query against the initial query then groups by Asset_ID, taking the Max Date and First Switch and Port...Results show the latest date but with a different Switch and Port

Asset_ID | Date | Switch | Port
123 23/05/2013 WAR01 GI01
123 23/05/2013 SAM01 GI02
123 20/05/2013 ROC01 GI03
123 21/05/2013 CHR01 GI04

e.g I get 23/05/2013 with ROC01 | GI03...I have tried doing this as a nested query but get similar results.

View 2 Replies View Related

Problem With ComboBox Selection To Drive Query Criteria.

Jun 8, 2007

Hi, I have been reading through the forum and found alot of really interesting stuff but cannot seem to find the answer to my specific problem. I'm sure it's easy but i'm sick of trying to figure it out now.

I am trying to build a form to display a shift rota. My tables contain daily shift patterns for 6 staff members for 2007. I want to build a form that has a subform displaying the rota for a particular analyst and a control in the master form to select which of the 6 staff members you wish to view. I have built a master form with a combo box. The combo box holds the names of the six staff and gets this data with a select statement in the Control Source field from the employee table. I constructed this with the wizard. I have constructed a query to drive the sub form. The query builds the rota, getting data from my tables but gives all shifts for all dates for all staff . I want to restict this in my subform to all shifts for all dates but for 1 member of staff. The member of staff should be defined by the selection made in the combo box in the master form. I then have a statement in the criteria of the query driving the subform to restrict the results by name to the name selected in the master form combo box. I'm sure this should work as i have used similar methods in the past. The statement in the query criteria reads like this:

[forms]![frmRota].[cmbAnalystSelect]

this is in the field of analystName, so the criteria should find all instances where the value in the analystName field matches the value in the cmbAnalystSelect control. However, the query always returns no results.

When i run the quey i have already opened the form (frmRota) and selected a value for cmbAnalystSelect, so the quey should collect the value from the combo box and filter the result according to the criteria but it seems to filter out all the results. If i run the query without first opening the frmRota form then i am prompted by a dialog box to enter a value for [forms]![frmRota].[cmbAnalystSelect]. If i enter a corresponding correct value (a valid name for a member of staff) the query returns a correct set of results, i.e. all shift patterns for all dates for the selected member of staff. When i have the form already opened i am not prompted for a value by the dialog box which would suggest it is at least recognising the form is open and that the control is there.

The last thing is that my ComboBox control is not bound to anything, when i view the control in design view it says it is unbound. I have nothing in the control source for the ComboBox. In the row source i have a select statement to pick out the employee names from the employee table, this then poulates the list in the ComboBox.

I hope this is enough info for someone to understand my problem and offer some advice, i can elaborate, attach screen shots etc. if needed.

Thanks in advance for any advice you can offer.

View 4 Replies View Related

Forms :: Running A Query Based On User Selection Criteria On Form

Jan 9, 2014

I have a form which lets the user enter a contract ID and then a combo box that has a list of reports which run against whichever ID is entered.

What I want to know if I put the ID's in a table if theres a way to allow the user to select multiple ID's in one go and run the report against them the range rather than 1 at time?

I'm thinking about utilising the check/tick boxes would this be possible? or another way?

I'm using 2003 and have some VBA ability.

View 5 Replies View Related

Queries :: Date Criteria - Query Previous 12 Months Including Months With No Data

Mar 22, 2013

I currently have a form where users can enter an "End Date", click a button, and it queries the data from 12 months prior to "End Date" entered.

Is there a way to force the query to show all 12 months, even if there are no records for a particular month? For example: if the user enters February 2013 in the date field, I would like the query to return:

March 2012
April 2012
May 2012
June 2012
July 2012
August 2012
September 2012
October 2012
November 2012
December 2012
January 2013
February 2013

...so even if June 2012 has no records, it is included in the query with a value of zero.

This is what I have so far:

WHERE (((Qry_Tbl_Assets.Dte) Between DateAdd("m",-12,[Forms]![Main_screen]![End_Date]) And [Forms]![Main_screen]![End_Date]))

View 2 Replies View Related

Queries :: DSum Using Date Criteria

Jan 16, 2014

I have a query which includes a deposit field and a Transaction_Date field. I would like a cumulative deposit on each record (arranged according to date). The following is the expression I am using:

=DSum("[Deposit]","[myQuery]","[Transaction_Date]<=#" & [Transaction_Date] &"#")

I am getting completely nonsense values from the expression - some of the fields are blank (the first few fields) and then the values bear no relation to the deposits preceding them. I have tried all sorts of formats including using Format([Date], 'dd/mm/yyyy') on either side and then on both sides but with no effect.

View 4 Replies View Related

Queries :: 2 Different Date Fields With Same Criteria

Apr 1, 2014

I am trying to figure out how to build my query that returns equipment reservations where "date out" OR "date In" are between 2 dates that are specified on my form using [Forms]![Myformname]![StartDate] and [Forms]![Myformname]![EndDate].

The purpose of the query is to find the amount of companies that have had a piece of equipment reserved between 2 dates, which could be a calendar year, or a fiscal year for example.

This is my current SQL statement that returns records where only DateIn falls between the criteria. My objective is to also return records where DateOut falls between 2 dates.

Code:
SELECT tblReservations.ReservationNumber, tblCompanies.CompanyName,
tblLookup_Contact_types.[Contact Type], tblReservation_details.Dateout, tblReservation_details.DateIn
FROM tblLookup_Reservation_Status INNER JOIN (tblCompanies INNER JOIN ((tblLookup_Contact_types INNER JOIN tblReservations ON tblLookup_Contact_types.[Contact Code] = tblReservations.CategoryID)

[Code] .....

View 4 Replies View Related

Queries :: Reports Database By Date And Criteria?

Jun 24, 2014

I have a report database that provides my company with clients that took our training modules and notify us of which clients completed our trainings.The clients can complete training in 3 States and "Passed" means they are good to go.

I download an excel report daily and import it to Access on a daily basis. Problem is the Report is over 8,000 rows long and basically I just need the clients that completed training within past 48 hours. The excel report provides a date of completion.

code that only pulls those clients that "Passed" within the last 48 Hours. Here is my SQL Statement I use on the RecordSource.

SELECT report.SPS, report.FirstName, report.region, report.id, report.AZ_Cert, report.AZStatus, report.CA_CERT, report.CAStatus, report.OR_CERT, report.ORStatus, report.Completed FROM report WHERE (((report.Completed)=False));

The completed checkbox removes the record from the cert queue. How can I do this more efficiently? I think I have it right.

Private Sub Completed_Click()
Const cstrPrompt As String = _
"Are you sure you want to complete this record? Yes/No"
If MsgBox(cstrPrompt, vbQuestion + vbYesNo) = vbYes Then
If Me.Dirty Then
Me.Dirty = False ' save the record
Forms!frmRecertView.subfrmRecert.Requery
End If
End If
End Sub

View 6 Replies View Related

Queries :: DSum With Dynamic Date Criteria

Jul 10, 2013

I need to do a DSum with criteria that picks up a previous month based on todays date, I have got the following but I don't know how to format the last part of the criteria.

If I take out the Month(DateAdd("m",-13,(Date()))) and replace it with 6 it works fine.

Code:
DSum("[invquan]","[qrySalesByStockCode]","[stcode] = '" & [stockcode] & "' and [Month] = Month(DateAdd("m",-13,(Date())))'")

View 1 Replies View Related

Queries :: DLookup With 2 Criteria - One Number And One Date

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

Queries :: Month To Date When Tied To A Criteria

Feb 5, 2014

How do you calculate or find Month To Date in a query as it relates to a hand keyed criteria.

For example I have a field called Operating Day and right now my criteria in my query is Between DateSerial(Year(Date()),Month(Date()),1) And Date(). Works perfectly. I'm using this data in a sub report.

But now my requirements have changed and its possible that I may need to report on something from January (or December and so forth). Well this criteria will show data from February. But the Month To Date data should show totals for the Month I'm reporting on.

Is this even possible?

View 4 Replies View Related

Queries :: Group Results By Criteria Date Range

Sep 23, 2014

Below is a simplified example of what I'm trying to achieve with a parameter query.

The source table for the query contains two fields:

Reading_Date (short date) and Use_value (integer)

The parameter query sums Use_value between two dates for various date ranges specified as 'or' criteria. SQL as follows:

SELECT Sum(Table1.Use_Value) AS SumOfUse_Value
FROM Table1
WHERE (((Table1.Reading_Date) Between #1/1/2013# And #1/5/2013#)) OR (((Table1.Reading_Date) Between #1/1/2014# And #1/5/2014#));

This produces a single sum total, but I'd like the query to give a total per criteria date range. In other words to group results by criteria. As date ranges may span year change, grouping by year is not possible.

View 4 Replies View Related







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