General :: MS Query Criteria With Two Fields If Either Contain A January Date

Jun 16, 2015

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

View Replies


ADVERTISEMENT

Date To Default To January 1

Jun 11, 2006

I have a little application that includes information about insurance deductables.

Deductables revert to their maximum on a date set by the insurance company, but that is USUALLY January 1.

So, for the control on the form where a new patient is set up, I have a date field where that "revert" date is to be entered.

Since it's usually January 1, I want a default to appear of January 1, of whatever the current year is. I've been looking through the Access help screens as well as this and other forums and have not found anything to suggest how to go about this.

Not being trained as a programmer, I'm coming here asking for some help.

Can anyone give me the appropriate code to generate the default date I want?

View 2 Replies View Related

General :: Set Up Conditional Format To Highlight Any Dates Within January Red

Jun 17, 2015

I have a report and i'm trying to set up the conditional format to highlight any dates within January red.

View 1 Replies View Related

How To Sum Fields In A Query With Different Date Criteria From Other Table

Aug 31, 2015

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.

View 7 Replies View Related

General :: Query Criteria - Data In Fields Are Numbers

Nov 7, 2014

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] .....

View 5 Replies View Related

General :: Delete Query With Date Criteria

Nov 23, 2014

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?

View 2 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

Dcount For 2 Criteria Date Fields?

Apr 19, 2014

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] ....

View 4 Replies View Related

Criteria For Empty Fields (date/time)

Aug 10, 2005

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!

View 3 Replies View Related

General :: DLookup With Date Criteria

Jun 29, 2012

I'm having trouble with DLookup() using dates as part of a multiple criteria. I'm using the following;

Code:
If IsNull(DLookup("ExchRate", "TBL_DDPExchRates", "CurID = " & Me.Combo4 & " AND ExchDate = #" & Me.ShipOBDate & "#")) And Me.Combo4 <> 2 Then

It seems that if ShipOBDate is any date between the first and tenth of the month the DLookup fails to locate the appropriate record. I can't understand why as ShipOBDate and ExchDate are both formatted as Short Date and ExchDate is being populated via an OpenArgs which is derived form the field ShipOBDate..Is the fact the the date is getting converted from a date to string and back to a date some how upsetting things

View 14 Replies View Related

General :: Connect Date Range With Other Criteria In SQL

Nov 2, 2012

I have the following function which works well except when I want to run a search using date range and any other criteria on another text/listbox or combo box.

This is work i get in the immediate window (Where Status, TypeID, PurposeID are the other criterias used with the date range.):

Code:
WHERE ([Incident_Date] Between #02/06/2012# AND #02/06/2012#)[Status] LIKE '*Active*' AND [TypeID] Like '*1*' AND [PurposeID] Like '*2*'

And the Build Function is as follows:

Code:
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim strField As String 'for the date field
varWhere = Null ' Main filter

[Code] .....

View 3 Replies View Related

General :: Find Records That Match Criteria Of Multiple Fields

Mar 12, 2013

Using sql or access query I would like to create an expression that aggregates the first field and I would like to see all records grouped by the relationship with another field. Let me show an example.

My query shows:

field1 field2
apple a
apple b
banana a
carrot a
carrot b
dog b
elephant b

I would like my query to now display a third field and group field :

field1 field3
apple both
banana a
carrot both
dog b
elephant b

View 1 Replies View Related

Value Reset On January 1

Nov 8, 2005

I'm building a form for a clinic.

Insured patients have deductables and copays that usually reset on January 1st.

To be clear, a deductable of $500 is reduced each time the patient pays, but jumps back to $500 at the beginning of the next year.

The table containing patient records has a deductable field, where the form gets the initial value. I'll be making a running calculation of how much remains after each visit, but that calculation has to start over again each year.

Can anyone suggest code to do this or point me at something similar?

View 7 Replies View Related

Queries :: General Date Field Criteria - Limit Results To Only Specific Month

Apr 16, 2015

I basically have General Date field (e.g. 10/1/2014 6:34:11 PM) and I want to limit the results to only a specific month and only to show reuslts after 6PM. I tried everything and still stuck.

View 2 Replies View Related

Query Two Fields Based On One Criteria

Aug 21, 2005

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

View 2 Replies View Related

Query Based On Criteria In Two Fields?

Sep 7, 2007

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!!

View 2 Replies View Related

Hard Code TIME To Selected Date On Form (to Make It Date&time) For My Query Criteria

Aug 17, 2006

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

View 10 Replies View Related

Queries :: Sum Of Same Fields Many Times With Different Criteria In Same Query

Mar 20, 2015

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

View 5 Replies View Related

Query Date Criteria

Oct 3, 2005

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

View 11 Replies View Related

Date Criteria In A Query

Jun 26, 2006

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.

View 3 Replies View Related

Problem With Date Criteria In Query

Dec 19, 2005

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 :)

View 4 Replies View Related

Date Criteria In Update Query

Mar 31, 2006

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 Related

Query Criteria - Date Range

Apr 18, 2007

Hi.

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.

View 2 Replies View Related

Query - Date Criteria Problem

Sep 19, 2007

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] & "*"));

View 4 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

General :: Query Criteria From List Box

Nov 12, 2014

I have a parameter query where user enter the department number to get their info. I want instead of entering the department number, a list box pops up and ask to select department as parameter and run the same query.

View 2 Replies View Related







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