I have 2 tables. One for customer details. With customer ID,Company Name, Customer Name and Address.The other for sales details with Order ID, Customer ID, salesprice and sales date.I would like to have a list of customer ID, Company name,total sales last year(sum of salesprice for 2013), Total sales till date(Jan 2014 til now). All should include customers details who have not made any sale as well.
View RepliesBelow is the SQL I have on a Union Query. Each Query is
based on a date range. The first Query date is Planned
Immplementation Date. The second Query date is Revised
Planned Implementation Date. If the Date in the Revised
Planned Implementation Date is higher than the EndDateTxt
Date Range I do not want the results to appear for that
ECN. Any suggestions on how to accomplish this?
SELECT ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number], ECNDetailtbl.[ECN Description], ECNDetailtbl.[Planned Implementation Date], ECNDetailtbl.[Revised Planned Implementation Date], ECNBCNVIPtbl.[Serial Number Break Required?], ECNBCNVIPtbl.[Implementation Reporting Required?], ECNBCNVIPtbl.[Do Not Process]
FROM ECNBCNVIPtbl INNER JOIN ECNDetailtbl ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID]
WHERE (((ECNBCNVIPtbl.[ECN Number])<>"sample") AND ((ECNDetailtbl.[Planned Implementation Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) AND ((ECNBCNVIPtbl.[Do Not Process])="yes"))
ORDER BY ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number]
UNION SELECT ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number], ECNDetailtbl.[ECN Description], ECNDetailtbl.[Planned Implementation Date], ECNDetailtbl.[Revised Planned Implementation Date], ECNBCNVIPtbl.[Serial Number Break Required?], ECNBCNVIPtbl.[Implementation Reporting Required?], ECNBCNVIPtbl.[Do Not Process]
FROM ECNBCNVIPtbl INNER JOIN ECNDetailtbl ON ECNBCNVIPtbl.[ECNBCNVIP ID] = ECNDetailtbl.[ECNBCNVIP ID]
WHERE (((ECNBCNVIPtbl.[ECN Number])<>"sample") AND ((ECNDetailtbl.[Revised Planned Implementation Date]) Between [Forms]![EcnVisualStatusFRM]![StartDateTxt] And [Forms]![EcnVisualStatusFRM]![EndDateTxt]) AND ((ECNBCNVIPtbl.[Do Not Process])="yes"))
ORDER BY ECNBCNVIPtbl.[ECN Analyst], ECNBCNVIPtbl.[ECN Number];
I have query in MS access which does date filter based on value given by user.
SELECT *
FROM Table1
WHERE ((DateValue([Table1].[Date_col])
Between "#"&[Forms]![Frm1]![Date1]&"#"
and "#"&[Forms]![Frm1]![Date2]&"#"))
But this gives empty output
I have been building a database for use in a charity shop and am struggling with an issue regarding one of the forms.
I currently have a form which displays all expenses from the shop. I have added a combo box to the top of this form which allows users to filter records based on an expense ID Code. It all works fine but I would like to be able to add a start date and end date box to the form also so that records can be shown between two dates.
I have tried adding parameter boxes to the query which runs the form (which works) but the issue I am having is that when a new id is selected from the combo box the parameter boxes pop up again asking me for start date and end date again. This happens every time a new combo box id is selected.
I think the way resolve this issue may be to add a start date and end date box to the form but I don't know how to implement this.
Please see the attached files for images of what I currently have. The forum won't let me post images directly here until I have 10 posts so I have had to attach the files instead.:
Query running the form - Attachment 1
The Form itself - Attachment 2
Combo Box - Attachment 3
Bound Column on combo box - Attachment 4
Code in Combo Boxes after update event - Attachment 5
So I have a search form that I created that has also a subform (a continous form, that has a query as its source) where my records are listed. I started on making a Between dates filter where i would be able to pick dates and get the results of records that are in that range.
I put two text boxes in which I set format to Short Date so that I get the button to select the date from a calendar for each text box. Then I made a button that is only for this and added a Clickon code so that it filters my subform over the recordsource of the form. The problem is is that i get an error and it doesn't filter.
Code:
Private Sub btnDatumUpis_Click()
Dim SQL As String
SQL = "SELECT qrySearchV.VID, qrySearchV.MarkVoz, qrySearchV.ModelVoz, " _
& "qrySearchV.TipMot, qrySearchV.Regist, qrySearchV.VlaVoz, " _
& "qrySearchV.KorVoz, qrySearchV.KatV, qrySearchV.DatumUVoz, " _
[Code] ....
I have a form that is filter based on a combo box. I would like to add another filter for date. but the code I'm using for the first combo box doesn't work for date.
the code is:
Sub SetFilter()
Dim LSQL As String
LSQL = "select * from Preventive_Q_View"
LSQL = LSQL & " where Item_Name = '" & Combo206 & "'"
Form_Preventive_View.RecordSource = LSQL
End Sub
How do I modify this code to work with the date combo box? Also, is there a way to get both filters to work together, as in filter based on the first combo OR the second combo, OR both?
I've two fields to work with:
[Date of Device]
[DischDate]
If i was explaining it, it would be as follows:
If [DischDate] Is in the next month after [Date of Device] then Y else N.
to add for example if the [Date of Device] is April 2015, and the [DischDate] is also April then i'd expect a N answer
to add for example if the [Date of Device] is April 2015, and the [DischDate] is May then i'd expect a Y answer
I am working in MS access 2007.
What I am trying to do is fairly simple i just dont have the ability to correctly code what i want to do.
I want to filter my query based on some criteria in multiple columns. But i only want the query to filter based on the specific criteria if a checkbox has been selected.
Basically i want the criteria for one of the columns criteria to read
IF a check box "Check0" is selected THEN filter the column to only records that = 1 and if "Check2" then filter all records that = 2
how to return all values in a query when a form critieria is left blank. I have made some progress, the combo box criteria queries were fairly simple, but i'm getting stuck with my date criteria. My query doesn't return null values when I want it to.
I want it to return all records (including null values) if the form OpenFrom and OpenTo dates are blank, and just the values between the selected dates (excluding null values) if the form is completed.
Code:
SELECT qryReportSelector2_Authority.*, qryReportSelector2_Authority.ApplicationDate AS ApplicationDateFilter
FROM qryReportSelector2_Authority
WHERE (((qryReportSelector2_Authority.ApplicationDate) Between Nz([Forms]![frmReportSelector]![OpenFromDate],DMin("[ApplicationDate]","[qryAllCases]")) And Nz([Forms]![frmReportSelector]![OpenToDate],DMax("[ApplicationDate]","[qryAllCases]"))));
I am using the following expression to calculate a completion date for a project which is based on the due date. My problem that I am running into is when I get to a project that is due on a Tuesday, it returns a date for Sunday when it should be Friday.
IIf(Weekday([Due Date])=2,DateAdd("d",-4,[Due Date]),DateAdd("d",-2,[Due Date]))
I have form that user can filter the records and generate a report but I have difficult trying filter null date.
If I have check box called filter null if it has a tick in I would like it only show records that have no value (is null) in field "date start" but if unticked I would like it to only show records with a date in field "date start" ...
I have a query that pulls scores for this month only for each class member. Problem is, I only need a count of these scores (per person) and because the date is in the query, it doesn't group the scores together and count them as one. i.e. it sees score 1 and score 2 as separate because they have different dates so they won't count together.
The only purpose of date in this query is to filter out only this month's dates. Is there some advanced query expression something or other that will tell it to leave date out of the count and only use it to filter?
I have a table, tblDailyCalls, that contains agent_name, date, calls_ answered, and talk_time. Ideally on a form, the user will select an agent, enter the date range in txtStartDate and txtEndDate and a report opens to show what the total amount of calls and talk time is for that date range.
All I've managed so far is doing a simple expression on the report itself to sum the fields I want. But my method returns every date in the range. I would like to only display the total.
I've been trying with Totals in the query and crosstab queries but am not familiar with them.
I m trying to make form which filters my records and generates a report..here's where I am
Code:
Like "*" Or Between [Forms]![Form1]![Text6] And [Forms]![Form1]![Text8] & "*"
but this doesn't work I would like to show all records if textbox 6 is null and textbox8 is null this part of code works perfect but below but I'm struggling to get the between in with the code
Code:
Like "*" & [Forms]![Form1]![Text6] & "*"
the code is in report record source
created a query (in Access 2010) that joins several linked tables (to an Oracle database). The query runs in about 20 seconds when I filter with a hard coded date (e.g., #12/31/2014#). The Oracle table column Im filtering on is defined as date/time.
When I attempt to change the hard coded value to a soft coded value (e.g., Forms![Form1]![Latest_Extract_Date]), the query runs over 5 minutes. In this case, the form field has the exact same value (12/31/2014).
Ive encountered similar issues using Access 2000, 2003, etc. This is quite frustrating. Does Access interpret #date value# is a special way? Is there a way to trick Access into the thinking a soft coded date is a hard coded date?
In the fields of the table, among others, there is a date field which represents the starting date of the flat availability. I'd like to create a query which displays the available nights per flat for each month in the current year. For example, Flat1 is available from 11.08.2014, then I'd like to see in the query result 20 nights for August and 30 for September, 31 for October and so on.I created a query which displays only the dates for the current year. Then I joined this query with the Flats table, the join condition is query.date >= flat.valid_from_date and displayed 1 in each row in the result as night. This way, I have the available flats for each day with 1 as available night. Then I tried to summarize the number of nights by month and flat.
View 5 Replies View RelatedI have a date of service [DOS] and a date of birth [Birth Date]
I'm trying to calculate the age at the last day of the date of service year not the current year.
Example:
[Birth Date] = 6/25/1993
[DOS] = 10/18/2013
Age at the last day of the date of service year (12/31/2013) = 20
How to do this. I tried
Code:
DateDiff("yyyy",[BIRTH DATE],[DOS])+Int(Format([DOS],"mmdd")<Format([BIRTH DATE],"mmdd"))
but this does not work correctly.
Hiya All
Hope you can help me here
I need help with an update query that will change records for me based on the date
In the record I have a TEXT record called [GI1] that can have a multitude of data in it.
The data can be “EVA”, “CON 10/12/06”, “REQ 10/12/06”, “PAS 10/12/06”, or “B 10/12/06”, and finally “A 10/12/06”
What I need is a query will look at the Data that starts with “B” and change the letter to “A” if the date in the record has passed. {The Date can be any date in any year }
Many Thanks for Looking
Paul
I have a form where someone enters a 'Job' to be done which in turn creates a record in a table with all this information
On the form I have a field called 'Repeat Frequency' where the user can choose how often they would like this 'Job' to repeat. e.g. Monthly
Then, on another form someone else can check what 'Jobs' should be done on that day
I have managed to get this to work fine using the DatePart function and it displays all jobs to be done that fall on the current day (e.g. if they choose to repeat it weekly, it will look for all entries where the datePart "d" [DateToBeDone] = Date()-7, -14, -21 and -28)
The problem is that i have been limited to the options of 'repeatability'
What i would like to do is change the options from 'Weekly', 'monthly' etc and instead have a field makrked "Repeat every so many days"
Is there a criteria i can use in a query where i can show records every so many days? i.e. if there is a job in the system that should repeat every 7 days and the [DateToBeDone] is set to 31/10/13, it would return this record on 07/11/13, 14/11/13, 21/11/13 and so on?
I Have tried DatePart ("d", [DateToBeDone]=DatePart ("d", (Date()-[RepeatDays] but what happens is it only shows it if the day is that many number of days in the past and not multiples as i said above.
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code:
'Purpose: This module illustrates how to create a search form, _
where the user can enter as many or few criteria as they wish, _
and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
I have a query that I want to "filter" based on a date derived from a Build statement (Expression). The Build statement works I can even sort by "Ascending".. But when I try to add a "Between [Startdate] And [Enddate] statement in the Criteria box, returns nothing".
Build statement : ReInvestDate: IIf([Eligibility]="SCI",DateAdd("d",+1825,[PrevInvestDate]),IIf([Eligibility]="TS",DateAdd("d",+1825,[PrevInvestDate]),IIf([Eligibility]="S",DateAdd("d",+3650,[PrevInvestDate]))))
I am trying to run query on a months worth of dates, have it count based on each day and then display the date and the number?
Table:
field1 - field2 - field 3 - Date
I can run a query one day at a time but would like to run it for the month and get this
12/01/2014 - 15
12/02/2014 - 32
12/03/2014 - 0
12/04/2014 - 12
I have a file of transaction history from the accounting system. All of the payroll cash payments are coded as ZG. Payroll accruals are coded as ZC. I need a sum of payroll accruals by department that have the same date or later than the last payroll cash payment. How do I write that query?
View 1 Replies View RelatedI have a query which is displaying a date/time field a record was input. in my query I'd like to only display the records where the date/time based on what today is i.e. Monday it will display <Friday or yesterday for Tue to Fri.
View 7 Replies View RelatedWithin a query, I'd like to reference another query field based on a date specified as a parameter.
In my query, there are fields for each month: [January],[February], etc.
I have a field titled [Current Month], based on the parameter [As Of Date]. So if when running the query, the parameter pops up and I type 5/6/2013, it knows that the month is May. I know how to return May in the current month field (format([As Of Date],"MMMM"). But how to I return the value that is in the May column?
I have multiple buildings that I own. Each building earns a monetary amount each day. Some days they earn $0, some days they earn $1,000. This is all kept track in a data base in which someone manually enters the information each day.The three fields are:
Building
Date
Profit
I have narrowed a query down to one building, and I am interested in two things. How do I write a query that shows a 30 day peak (the most profitable 30 days). In other words. I want to see the following
Building: GNB Tower Date: 02/14/13 - 03/15/13 Profit: $25,162
The next thing I am interested in is a 7 day peak from within these 30 days. This shouldn't be too difficult if I can figure out how to query these 30 days, I can query 7 days out of those 30 days. So this bigger mind stumper is the first one.
out of a date range of months and months (Let's say 10/12/13 - 6-14-13).Once I figure this part out, I am sure I can figure out how to compile all buildings into one large report with their 7 day peak and 30 day peak.