Table 1: contains sales summed by salesrep by week_date for 5 years + current
Table 2: contains sales rep, start_date and end_date
Query: Sum sales by rep where (start_date >= week_date and end_date <=week_date)
Each rep has different start and end dates.
Basically, I have a table showing our clients and when their next service dates are:
I need the query to show NEXT SERVICE DUE dates in January, AND/OR if NEXT LOLER has any January dates.
These should show as either or both fields have January dates:
Next Service Due: February
Next LOLER: January
Next Service Due: January
Next LOLER: December
Next Service Due: January
Next LOLER: January
I managed to do the NEXT SERVICE DUE with January dates, but not both
Hello all
I am using Access as a front end (GUI) with a SQL 2005 database and have several linked tables. An user encountered an odd error that I cannot figure out.
He created a query that had criteria on a date. When he tries to run it the query will not do anything. If he closes out of the query and does any other operation he gets the "This Action will reset the current code in break mode." error and will have to close out access before he can do anything else.
I'm found some strange work-arounds but want to fix the problem the correct way. Attached are two examples - the first one causes the error the second one is the work-around that returns the desired results.
Any ideas?
I am fairly new to Access and I would like to create a form to allow users to create their own query. I would like to allow users to select multiple fields (perhaps with checkboxes?) from all possible fields in a table to return either all data from that field or narrow their search by inputting certain criteria or choosing from a drop down into a text box. Is this possible in Access and any detailed specifics on how to achieve this?
View 3 Replies View RelatedI 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] .....
With a form with two fields one is a date field formatted as short date and the other is a lookup combo box that I use to lookup predefined times and is formatted as short time. The date and time are stored in the same table as date/time in separate fields. I attempted to use Dcount, and get syntax error missing operator. Below is one of a hundred that i have tried after scouring this forum and the net. I ended up converting the Appt_Time field to a text field in the table to eliminate one date field but still got no where.
Code:
Private Sub Appt_Time_BeforeUpdate(Cancel As Integer)
'Check table for duplicate
If DCount("[Appt_Time]", "tblexams", "Appt_Time= & Appt_Time & And [Sch_Date] = #" & Sch_Date & "#") > 0 Then
'Message box warning of duplication
[Code] ....
I am creating a query with criteria for a dates column. The column cotaining dates in my Table has many empty fields. I want to limit the criteria for my dates to >#1/1/2004#, but I don't want Access to exclude all the empty fields because I want all the data displayed for the purposes of my report.
What do I do? I'm not terribly familiar with Access, so please explain as completely as possible. Thanks in advance!
How can i update fields of one table with update query where creteria are more than one 1
Example:
DoCmd.RunSQL "UPDATE Salary2009toUpdate SET " & Me.Combo2 & "='" & Me.Text4 & "'
Where Empid='" & Me.Combo0 & "'" and where bank='" & Bankname & "'"
What is the syntax error ...
Hi all,
I am endeavouring to filter a form based on the position of an individual.
I would like the users to be able to select their position and then any record where there is a match in the "Primary Responsibility" field or the "Secondary Responsibility " field will be displayed.
I have an underlying query that is populated by a combo box on an unbound form.
This has worked when generating reports for individuals but I cannot get the required result when using the form.
Thanking anyone in advance who can assist me with this.
Regards,
Rod
Hi All
I want to create a query which is based on two fields.
One field is a date query i.e >=Date()-7 OR >=Date+7
The Other is a Combo box where the criteria is No
My problem is that access is currently running this as two individual queries.
Is it possible to make this query return data only when both fields are matching?
Or do I have to run this as a parameter query?
Thanks!!
I'm trying to search for a variable (varCod) in a table(SerialNumberCustomer) but I want to only store the returned variable in an array if its shipDate is between 9/30/2001 and 10/1/2012
this is what my code looks like so far:
Set rst = CurrentDb.OpenRecordset( _
"Select * from SerialNumberCustomer WHERE SerialCardId = " & varCod & " AND (ShipDate BETWEEN #09/30/2001# AND #10/01/2012#) ")
The line of code works and it returns all of the values in SerialCardId where it's = to varCode but it doesn't go through with the shipDate requirement. It's instead returning all of the dates with the found value.
Hello buddies :D, do you have any idea how to make this work?
To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.
This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only.
([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)
How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif
I have a table [maintable] that holds print records, with the following fields :
id,userlogon,printername,pagecount,color(yes/no),duplex(yes/no),timestamp and papersize.
I want to create a query that will show the following:
Sum of pagecount where papersize like 'a4' and between 2 dates but FOR EACH USERLOGON.The dates will take them from 2 fields in a form so i will use this
"WHERE (((maintable.Timestamp)>=[forms]![reportsform]![frmdate] And (maintable.Timestamp)<DateAdd("d",1,[Forms]![reportsform]![todate])))" and some more..
So i want the query to show,first the userlogon then a field to show sum of a4 then a4 color prints etc..(remember,i want the between 2 dates criteria to be global,to the whole query)
here are all of the criteria fields i want:
1) - papersize=A4
2) - papersize=A4 and Color=True
3) - papersize=A4 and Color=False
4) - papersize=A4 and Duplex=True
5) - papersize=A4 and Duplex=False
6) - papersize=A4 and Color=True and Duplex=True
7) - papersize=A4 and Color=True and Duplex=False
8) - papersize=A4 and Color=False and Duplex=True
9) - papersize=A4 and Color=False and Duplex=False
Hi,
I have a date field in a query and was wondering if anyone knows how i can
have a prompt to choose between two dates.
Thanks
Alex
Wonder if anyone can help with the syntax in this query? It always returns no results and I can't for the life of me understand why.
SELECT tblExpectedShipments.ShipmentDate
FROM tblExpectedShipments
WHERE (((tblExpectedShipments.ShipmentDate)=IIf([Forms]![frmSelectShipperReport]![chkOverdue]=0,(tblExpectedShipments.ShipmentDate)>Date(),(tblExpectedShipments.ShipmentDate)<Date())));
Any pointers would be greatly appreciated. Thanks.
I have a query criteria where the data in the query field are numbers.
The following works as long as I have a value in the textbox otherwise I get an error message stating, this expression is typed incorrectly.
Code:
Like [FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null
Also tried
Code:
[FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null
Yet I use the following script for another column which has identical data and it works fine either way and I can use it in the other column and it works with or without data in the textbox
Code:
Like [Forms]![FONMain]![txtFact1] Or [Forms]![FONMain]![txtFact2]
Or [Forms]![FONMain]![txtFact3] Or [Forms]![FONMain]![txtFact4] Or [Forms]![FONMain]![txtFact5]
Or [Forms]![FONMain]![txtFact6] Or [Forms]![FONMain]![txtFact7] Or [Forms]![FONMain]![txtFact8]
[Code] .....
Hi,
I am using a Form to input the criteria required for a query to run. All fields seem to work fine, except for the date field...(i hate dates!!)...i am using a Where clause and in the criteria field i have the following:
[Forms]![Bookings_Diary].[DateFrom]
However, when I run the form and input all the criteria required including the DateFrom textbox and click on the Load Query button, the system will still ask me to input the date field...
To make things clear, the DateFrom text box is an unbound text box having the format property set to 'Short Date'. In the query, the date field relates to a table Date/Time field..
Any help?! Tnx :)
What I want to do is from several records find the Max() date and update that one record with my stuff. However, I get an error message cannot use aggregate in update query or something to do that effect. Is there a way to get around this?
View 1 Replies View RelatedHi.
I want to set up a simple query criteria where it searches based upon a column called Date.
So I want a criteria that says when you run the query ...
Between which date range? And which date?
Now I know I need to use Between and And within the Citeria, however I would be grateful for someone to show me the exact wording for the criteria.
Thanks and kind regards.
Phil Drury.
Hey guys, I really need some help on this problem.
I have a database for a tutorial center, and we have invoices recording the dates of the lessons that students pay for in this month.
We have a reminder form that display query results when today is the last lesson for the student so we can give them a new invoice. This has been working perfectly well untill now, it suddenly no longer displays the student and invoice for their last lesson.
What happens is, if there are 4 last lessons on Sept. 17, it displays reminders for 4 students in a datasheet subform. However, Sept. 17 may only be the second last or third lesson for some of the students. It just randomly shows a student that has a lesson on Sept.17, not their last lesson.
Thanks ahead to anyone who can offer me any advice.
SELECT tblStudents.StudentID, tblStudents.StudentName, Last(tblDate.dtDate) AS DateOfLast, tblInvoice.InvoiceID, tblInvoice.Paid, tblCourse.ShortDescription
FROM tblStudents INNER JOIN (tblInvoice INNER JOIN ((tblCourse INNER JOIN tblInvoiceDetail ON tblCourse.CourseCode = tblInvoiceDetail.CourseCode) INNER JOIN (tblDate INNER JOIN tblInvoiceDetail2 ON tblDate.DateID = tblInvoiceDetail2.DateID) ON tblInvoiceDetail.InvoiceDetailID = tblInvoiceDetail2.InvoiceDetailFK) ON tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID) ON tblStudents.StudentID = tblInvoice.StudentFK
GROUP BY tblStudents.StudentID, tblStudents.StudentName, tblInvoice.InvoiceID, tblInvoice.Paid, tblCourse.ShortDescription
HAVING (((Last(tblDate.dtDate)) Like "*" & [Forms]![frmReminder]![txtDate] & "*"));
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?
I have a database with the following information:
FTA # Date Attended Last name First name assigned person......
Each week I need to generate a report that shows the people that attended for that week, grouped by the assigned person. I only want to show the data for a specific "date attended" (i.e. that days date). I figured I first need to generate a query that only returns the data for that specific date in the "date attended" column. I did that but it is manuel by using the criteria field.
Is there an easy way to type in the date that I want the query to use as the criteria?
Thanks
:o
Below 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'm trying to setup a query to pull only the records that have the latest date in Time Scanned for each different serial number.
I have a table with 4 fields: Serial Number, Model Number, Location and Time Scanned. Some records will have the same Serial Number repeated with different Model numbers,locations and times scanned.
I can't figure out how to query only the Last Time Scanned for each different Serial Number. I've tried using "Last" for critria on the time scanned field, but I need that for each different serial number.
It's probably easy...but I'm not able to get it. Thanks!
I have 2tables,table1 id,name, table2 id,name,date.
How can i write a delete query that will delete everything in table2,if the data date is today?
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;