Query Based On Multiple Conditions

Jan 11, 2005

For some reason, this one is driving me nuts so i would really appreciate some help.
This is kind of complicated to explain but here goes.
I am trying to create a query so that, based on certain conditions certain rows from the Form/Table are either visible or not visible on the report.
I have four fields involved:
Antic Dep$ (Anticipated Deposit Amount)
Antic Loan$ (Anticipated Loan Amount)
Date Dep Booked
Date Loan Booked

Basically i am trying to hide any records that in which the Date Dep Booked and/or Date Loan Booked are older than the current month...

I can think it through logically but cannot get my head around the query.

If Date Dep Booked or Date Loan Booked is not older than current month then show the row;
If Date Dep Booked is older than current month and Date Loan Booked is older than current month then hide the row; unless

If Date Dep Booked is older than current month but Date Loan Booked is not older than current month then show the row; except
If Date Dep Booked is older than current month and Antic Loan$ is zero then hide the row; but
If Date Dep Booked is older than current month and Antic Loan$ is not zero then show the row;

Then i also need to do the reverse for the Date Loan Booked, i.e.

If Date Loan Booked is older than current month but Date Dep Booked is not older than current month then show the row; except
If Date Loan Booked is older than current month and Antic Dep$ is zero then hide the row; but
If Date Loan Booked is older than current month and Antic Dep$ is not zero then show the row;

Phew! That probably makes no sense but i had to try.

Once again, i appreciate any help so that i can keep the remainder of my hair.

Switters

View Replies


ADVERTISEMENT

Queries :: Possible To Set Multiple Conditions Across Multiple Tables

Apr 10, 2013

Is it possible to set multiple conditions across multiple tables using OR in a where clause? For instance, can you run where......

(table1 = 'test') or (table2 = pass) or (table2 = "fail) AND (class = 'SQL');

View 1 Replies View Related

Copying Records Based Off Of Conditions

Jul 8, 2006

Hello. Can this be done?

I am creating a mini scheduling program, and I have one table that handles all requests (tblRequests), then a second table is the final schedule (tblFinalSchedule). On the table for the requests, I have a field called "locked" that is of type yes/no. The purpose of this field is for a meeting time that is of uptmost importance and that I don't want the program to manipulate it in any way.

Is there a way to program in VBA so that when a button on a separate form is clicked, those records that when the yes/no field is checked (set to true), are copied into the appropriate fields from tblRequests into tblFinalSchedule?

View 1 Replies View Related

Multiple Conditions For Form

Apr 11, 2006

G'Day:

I have a form that I created that allows the user to input an "ExhibitNo" and then a "PlacingNo" to allow each entry to receive a set monetary amount (based on values in a table).

The "ExhibitNo" has two references linked to it. They are "ClassNo" and "ExhibitorID".

My supervisors want me to make it so that when the "ExhibitNo" is entered on the form that it will not allow a monetary amount.

This is where I started, but it does not work. Does anyone have a better idea?

Private Sub Combo14_AfterUpdate()
Me.Refresh
If [ClassNo] >= 0 >= 5000 Then
If [ExhibitorNo] <= 0 >= 499 Then
If [Placinggrade] = "Blue" Then
[Premium] = [Blue]
End If
If [Placinggrade] = "Red" Then
[Premium] = [Red]
End If
If [Placinggrade] = "White" Then
[Premium] = [White]
End If
End If
End If

If [ClassNo] >= 5000 Then
If [ExhibitorNo] <= 500 Then
If [Placinggrade] = "Blue" Then
[Premium] = [Blue]
End If
If [Placinggrade] = "Red" Then
[Premium] = [Red]
End If
If [Placinggrade] = "White" Then
[Premium] = [White]
End If
End If
End If

If [ClassNo] >= 5000 Then
If [ExhibitorNo] >= 500 Then
[Premium] = 0
End If
End If

End Sub

Thanks

View 1 Replies View Related

Modules & VBA :: How To Do Multiple Conditions

Nov 6, 2013

how to do multiple conditions, Nested....I have 6 conditions that i like to check,

Code:
if (condition-1) And
(condition-2) And
(condition-3) And

'[code]....

View 8 Replies View Related

Modules & VBA :: IF / Then With Multiple Conditions

Sep 10, 2014

I'm trying to create an If/Then statement that needs to match to multiple values. My initial thoughts were to create a list but that doesn't seem to work the way I thought it would.

For example:
If Me.Field = 1,2,5, or 8 Then
Do 'X'
Else IF Me.Field = 3,4,6 or 7 THen
Do 'Y'
Else Do 'Z'

I haven't been able to get the syntax to work right. I'd rather not have to do 'Me.Field = 1 or Me.Field = 2, etc.' but if that's the only way to have it work right, then I guess it'll have to do.

View 6 Replies View Related

Multiple Conditions In Access

Apr 3, 2013

I am trying to calculate [current status] based on multiple conditions. for example

Current status = A , if (w>0 and x=0 and y=0 and z=0)

Current Status =B , if(w>0 and x>0 , y=0 and z=0)
Current Status =C , if(w>0 and x>0 and y>0 , z=0)
Current status =D , if(w>0 and x>0 and y>0 and z>0)

Where A,B,C,D are text values and w,x,y,z are dates

View 1 Replies View Related

Modules & VBA :: Multiple Conditions In A DoCmd

Oct 11, 2013

I have a Customer Issue form that writes the following into one table named Table1: Date, Customer Name, Ticket number, Agent, Issue and Comments. This is very simple.

All of this is filled in from a form that has links to 3 other tables for drop downs; Customer Name, Agents, Issues. This is working perfectly.

What I want to be able to do is generate a report based on a date range for a particular agent. Say 9/1/2013 - 9/30/2013 for John Doe.

For the report portion, on the form I have to combo boxes, one for start date, one for end date. I also have a drop down for selecting the agent. When I click a button on the form named Report, It will generate a preview of the report.

Here is the problem. I can get this to generate a report based either on the date range, which gives me all of the agents, or by agent, which gives me all of the dates. I can't get it to do both.

Here is some code that I have on the Report button:

DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent
This is the code that will let me choose the agent, but gives me all dates.

If I change this code to this:
DoCmd.OpenReport "AIReport3", acViewPreview, , "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
It will display all issues in the date range, but gives me all agents.

I was thinking I should be able to combine them with an AND or an & to get it to use both the agent and date fields, but I can't get this to work.

Something like: DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent And "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"

or

DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent & "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"

View 3 Replies View Related

Modules & VBA :: Check If Record Already Exists In Recordset Based On 2 Conditions

May 27, 2014

I have a table in Access that I have a form saving new records to. Before this save occurs, I would like Access to check if the account number already exists and if the account does exist if it is outstanding. If both of those conditions are met I would like a message box to display and cancel the save as it is a duplicate. I can't seem to get it to work though.

I was thinking to use a filtered recordset based on one of the conditions and then perform a find on that recordset to see if it is null.

Code:

dim acct as long
dim rstfiltered as DAO.Recordset
Set rstfiltered = CurrentDb.OpenRecordset("SELECT * FROM tblclstrack WHERE [Request Status] <> 'Completed'")
acct = Me.cd_number.Value

[code]...

View 9 Replies View Related

Compare Data In Two Tables, With Multiple Conditions…..

Aug 25, 2005

Been trying to crack this one for a while hoping someone on here might be able to help me. ;)

I have a table with a list of required software and a table with a list of computers and the software installed software. I made a query that displays the machines with the required installed. My problem is if a machine has more than required then it does not display the record. If it needs Office, Photoshop but has office, Photoshop and quark installed then it does not display that machine.

This should be quite a simple thing, I have played with Like, NOT, Where and others but with no luck...

Any help would be greatly appreciated.

Graeme

View 2 Replies View Related

Multiple Match Conditions In A Unrelated Table

Jan 31, 2007

Hi All,

My database has two tables:
1. Complete Address Data
2. Address conditions.

Table 1. has verbose customer [address] data like 11 smith st, 14 Kent Rd etc.
Table 2. has [std] address conditions, ST, RD, Street, Road etc.

How can I query ALL records by field [address] in table 1 by using ALL [std] address conditions in table 2?

The address conditions can occur anywhere in the [address] string, so I was thinking about maybe a CONTAINS condition, but I'm not sure

View 2 Replies View Related

Forms :: Filter Form With Multiple Conditions?

Mar 12, 2014

I want to open a filter form with mulitple conditions.one of them is a date condition.

I'm using access 2013 and the code below:

Code:
DoCmd.OpenForm "Edit_Mission", acNormal, , "[Report_Date]= " & Me.Date & " And [Supporter_Name]='" & Me.Supporter & "'"

it's opens the form but with no data. I also tried the # and it's still didn't work.

View 1 Replies View Related

Tables :: Multiple Conditions In Calculated Fields

Jan 9, 2013

is there any way to put into the calculated field (in expression builder) conditions? What I need is something like

Sum If (Table1.Field1="Y" And CurrentTable.Field2=Table1.Field3)

I means sum how many times there is "S" value in the field1 Table1, but only for records where the field3 in Table1 is equal to the value in the actual table in Field2 (in the actual row).

View 13 Replies View Related

Forms :: Validation Rule With Multiple Conditions

Apr 14, 2014

I have a main form which has couple of subforms bound to one main table. One of the subfomrs is about employment info. It has 3 TextBoxes: (Job Start Date), (Employer Name), and (Wage).

It has also 3 ComboBoxes: (Job Type [Full Time or Part Time]), (Hire Status [Permanent or Temporary]), (Quarter [1st, 2nd, or 3rd]).

I want to force users to fill all these 6 fields if they put any value in any one of them. I tried to set a Validation Rule in the TextBox/ComboBox’s property including IIF statement condition, but I did not succeed, and these rules did not work.I put the following code in the subform’s (After Update), (Before Update), and (On Current) events which works very well when I keep the (Wage) value 0 and move to a new record or to another subform:

Private Sub Form_AfterUpdate()
If Me.[Start Date] > 0 And Me.Wage.Value = 0 Then
MsgBox "You did not put how much is the wage."
Cancel = True
End If
End Sub

[code]....

how to make a similar validation rule for the other TextBoxes and ComboBoxes.

View 14 Replies View Related

Queries :: How To Insert Multiple Conditions / Criteria For A Field

Jul 24, 2013

I am trying to make a query that outputs the minimum "Need Year" AND ALSO if the need year was equal to 9999 it shows "NO DATA".

This is what I have so far for checking the minimum value:

field: Need Year: MinofList(PMS_output!pqi_ny,PMS_output!iri_ny,PMS_ output!sdi_ny,pms_output!sai_ny)

I am not sure if I should be putting it in the criteria to check whether this minimum value (need year) equals to 9999 or not and if it does, it says "NO DATA" instead of 9999.

View 3 Replies View Related

Modules & VBA :: Trying To Get Case Statement To Recognize Multiple Conditions

Sep 8, 2014

I am trying to get a Case Statement to evaluate multiple conditions. Example: below when I get diagnosis code 20400 and the age_at_diagnosis is 40 the code is basically ignoring the second condition of the Case "And rs![Age_At_Diag] < 18". How do I get the code to recognize both conditions?

Code:

Private Sub cmd_Update_Conditional_Codes_Click()
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset

[code]...

View 1 Replies View Related

Query Based On Multiple LIKE

Feb 14, 2006

Hi.
I have a situation where I have 2 tables A (20K records) and B (2K records).
Each table has a field 'Name'.

I'm trying to figure out a query that will return all the records in table A that have a LIKE match in table B, i.e a the name in table B is somewhere in the record in table A (pattern matching).

So, it looks as tough I would need to step through the records in table B, take the data in the 'Name' field, compare it with a Like '*<whatever>*' match on table A to return any records that contain that data from table B.

Then move onto the next record in table B and compare it again to all of table A and so on.

The result set would then display just the unique rows from table A that had a pattern match with one of the rows in table B.

Hope I'm making myself clear, apologies if not.

Any ideas?

View 3 Replies View Related

Query Based On Multiple Forms

Jul 22, 2005

Hi there,

I have a query to update data to a table which takes the account ID from the form and updates. This works fine as is.

The problem is that the update can be made on several different forms and was wondering whether there was a way to conditionally select the ID in the query based on what form is open. I have tried some combinations of IIF statements but every time I get a parameter prompt for the Forms that are not open.

Is there any way around this or do I just need a separate query for each form. Any advice would be appreciated.

JC

View 7 Replies View Related

Select Query With Conditions

Aug 9, 2005

Hi friends,

I had two tables parent(f1) , child(f1,f2,f3).
I had to display f3 value for parent(f1) when f1 has only one f2,
if f2 values are more than one for single f1 and if all f3 values are 0 then
i should display f3 as 0, else if some values for f3 are nonZeros and some are Zeros then
is should display f3 value as '-'.

How to do this?
please any suggestions?

Thanks.

View 1 Replies View Related

Query With Conditions By Date

Aug 9, 2007

I am so frustrated right now that I could scream. I have a database which tracks attendance and referrals for a networking organization - it meets weekly on Thursdays. It has a report which displays totals of both attendance and referrals for each weekly meeting for an entire month. The problem I am having is related to the fact that some months have 4 Thursdays and some have 5. If I get my queries to display the meeting dates correctly for the 4-Thursday months, then it displays incorrectly for the 5-Thursday months, and vice versa.

Is there any way to put a criteria on a missing record? If I could have it just populate the 5th week with spaces or null when it doesn't exist for that month, it would make my life a lot easier.

I can send a copy of the database to anyone who thinks they can help. It's too big to post.

View 8 Replies View Related

How To Check More Than 3 Conditions In A Query?

Mar 23, 2005

I have a filed and I want to find out its length then in the next column i have to add the data like if filed 1's length is 1 then it should be 000+field1.value, if it is 2 then 00+field1.value, if it is 3 then 0+field1.value and so on...any help is appreciated , thanks

View 2 Replies View Related

Query Based On Multiple Criteria With Check Box's

Nov 18, 2004

I have a table(Product Change) with these fields:
Tracking Number
Approved (a check box)
Engineering (text box that represents department)
Purchasing (text box that represents department)
Quality (text box that represents department)
Production (text box that represents department)
Customer Service (text box that represents department)

I want my query results to show all records that have the "Approved" check box...checked, and then only the records that have one of the Department fields with a null value.
So I'm looking to see only records that are "Approved" and out of those....only the records with at least one department field empty(Null).

Any help doing this is SQL view would be great...or even design view.

View 2 Replies View Related

Query With 2 &lt;&gt; Conditions On Different Fields Not Working

Jan 23, 2008

I'm trying to get this query to return all records except those with category: 4 AND issue: 12

Instead I'm always getting ZERO category: 4 and ZERO issue: 12

Should not the parenthesis force this to be taken as a single "WHERE"?


SELECT ASSIGNED_TO, COMPLAINT_NUM, DATE_RESOLVED, CATEGORY, ISSUE
FROM tmptbl_tm_YTD
WHERE (CATEGORY<>4 AND ISSUE<>12);

View 3 Replies View Related

Doing A Date Query With Conditions In Excel?

Aug 8, 2015

I am trying to do a date query with conditions in excel but it don't seem to work.

I am trying to do a query on a table on the birthdate column and I need the records of all athletes that were born on or after 1/1/2008. Below is what I did but it doesn't seem to work.

Created a design view
chose the birth date field
and the criteria row I typed
>=#1/1/2008#

View 2 Replies View Related

Excluding Multiple Records From Query Based On Value In ONE Record

Sep 27, 2005

Table1 gives Case Nu.

Table2 gives Case Nu. and EventType.

Table3 gives EventType and Cleared (yes/no field)

I want to run a query that gives Case Nu and Event where if there is even one event marked Cleared for a Case, then that case number and its events (even those not marked cleared) don't show up at all.

Any ideas? I know there must be an obvious solution, but my mind is stuck right now!

View 2 Replies View Related

Please Help-Conditions On Query Results (Booking Problem)

Nov 23, 2004

Ok i'm new so don't be too hard.

I'm making the most simple DB where there are bookings consisting of a StartDate and EndDate. The Scenario is a car hire firm. Obviously i don't want to be able to double book a car and want to be able to list all cars available within the two dates (input).

I have set the defult input to 12/07/04(StartDate) and 12/17/04(EndDate) for the purpose of testing.

I have created a query to find all cars which are not available in relation to the input dates, but cannot get it to list the available cars!

this is the query i have:

-----------------------------------------------------------

SELECT CarDetails.CarReg
FROM CarDetails INNER JOIN BookingDetails ON CarDetails.CarReg = BookingDetails.CarReg
WHERE (((((([BookingDetails].[StartDate])<#12/7/2004#) And (([BookingDetails].[ReturnDate])<#12/7/2004#)) Or ((([BookingDetails].[StartDate])>#12/17/2004#) And (([BookingDetails].[ReturnDate])>#12/17/2004#)))=False));


-----------------------------------------------------------

All i want to do is take all of the cars and deduct the one's which are produced by this query.

Thanks for any help!

View 3 Replies View Related







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