I need to write a query that has a criteria of two separate fields (one
compared to another, then a seperate one) but I can't figure out how
to do it. Let me first explain the table
id (primary ID)
FirstName (text)
LastName (text)
BadgeIn (text)
BadgeOut (text)
Damaged (yes/no)
What I what to do is check if BadgeIn is equal to Badge Out, if not I
want to retrieve the record. I also want to retrieve the record if
BadgeIn DOES match Badge Out, but only if Damage is true (checked).
So
BadgeIn = BadgeOut , Damaged = False - Skip Record
Badge <BadgeOut , Damaged = False - Retrieve Record
Badge <BadgeOut , Damage = True - Retrieve Record
Badge = BadgeOut, Damage = True - Retrieve record.
Im not sure how to go about this. Im guessing an If Statement but I
have never seend one in a query window. I googled for some examples
but counldnt find any like this.
Thanks,
Dave
3  1833  st********@hotm ail.com wrote:
I need to write a query that has a criteria of two separate fields (one
compared to another, then a seperate one) but I can't figure out how
to do it. Let me first explain the table
id (primary ID)
FirstName (text)
LastName (text)
BadgeIn (text)
BadgeOut (text)
Damaged (yes/no)
What I what to do is check if BadgeIn is equal to Badge Out, if not I
want to retrieve the record. I also want to retrieve the record if
BadgeIn DOES match Badge Out, but only if Damage is true (checked).
So
BadgeIn = BadgeOut , Damaged = False - Skip Record
Badge <BadgeOut , Damaged = False - Retrieve Record
Badge <BadgeOut , Damage = True - Retrieve Record
Badge = BadgeOut, Damage = True - Retrieve record.
Im not sure how to go about this. Im guessing an If Statement but I
have never seend one in a query window. I googled for some examples
but counldnt find any like this.
Thanks,
Dave
so what's the problem? just put your criteria on multiple lines. And
leave out the false criteria. They won't help you.
So
BadgeIn<>BadgeO ut AND Damaged=False OR
Badge<>BadgeOut AND Damaged=True OR
Badge=BadgeOut AND Damaged=True
actually, since Damaged can only be T/F, you don't need them in the
first two statements.
BadgeIn<>BadgeO ut OR
Badge<>BadgeOut OR
Badge=BadgeOut AND Damaged=True
Do I create an expression in a new column then put this in the
creiteria? I guess that is where I am confused. Im new(er) to access. pi********@hotm ail.com wrote:
st********@hotm ail.com wrote:
I need to write a query that has a criteria of two separate fields (one
compared to another, then a seperate one) but I can't figure out how
to do it. Let me first explain the table
id (primary ID)
FirstName (text)
LastName (text)
BadgeIn (text)
BadgeOut (text)
Damaged (yes/no)
What I what to do is check if BadgeIn is equal to Badge Out, if not I
want to retrieve the record. I also want to retrieve the record if
BadgeIn DOES match Badge Out, but only if Damage is true (checked).
So
BadgeIn = BadgeOut , Damaged = False - Skip Record
Badge <BadgeOut , Damaged = False - Retrieve Record
Badge <BadgeOut , Damage = True - Retrieve Record
Badge = BadgeOut, Damage = True - Retrieve record.
Im not sure how to go about this. Im guessing an If Statement but I
have never seend one in a query window. I googled for some examples
but counldnt find any like this.
Thanks,
Dave
so what's the problem? just put your criteria on multiple lines. And
leave out the false criteria. They won't help you.
So
BadgeIn<>BadgeO ut AND Damaged=False OR
Badge<>BadgeOut AND Damaged=True OR
Badge=BadgeOut AND Damaged=True
actually, since Damaged can only be T/F, you don't need them in the
first two statements.
BadgeIn<>BadgeO ut OR
Badge<>BadgeOut OR
Badge=BadgeOut AND Damaged=True
You got me on track!..
The way I got it to work was create an expression (new column ) in the
query
Match: [BadgeIn]<>[BadgeOut]
cretiera 0 = match and -1 = no match
then put the numbers in the "or" section of the critera to meet the
examples listed.
Is this the correct way of doing it? st********@hotm ail.com wrote:
Do I create an expression in a new column then put this in the
creiteria? I guess that is where I am confused. Im new(er) to access.
pi********@hotm ail.com wrote:
st********@hotm ail.com wrote:
I need to write a query that has a criteria of two separate fields (one
compared to another, then a seperate one) but I can't figure out how
to do it. Let me first explain the table
>
id (primary ID)
FirstName (text)
LastName (text)
BadgeIn (text)
BadgeOut (text)
Damaged (yes/no)
>
>
What I what to do is check if BadgeIn is equal to Badge Out, if not I
want to retrieve the record. I also want to retrieve the record if
BadgeIn DOES match Badge Out, but only if Damage is true (checked).
>
So
BadgeIn = BadgeOut , Damaged = False - Skip Record
Badge <BadgeOut , Damaged = False - Retrieve Record
Badge <BadgeOut , Damage = True - Retrieve Record
Badge = BadgeOut, Damage = True - Retrieve record.
>
Im not sure how to go about this. Im guessing an If Statement but I
have never seend one in a query window. I googled for some examples
but counldnt find any like this.
>
Thanks,
>
Dave
so what's the problem? just put your criteria on multiple lines. And
leave out the false criteria. They won't help you.
So
BadgeIn<>BadgeO ut AND Damaged=False OR
Badge<>BadgeOut AND Damaged=True OR
Badge=BadgeOut AND Damaged=True
actually, since Damaged can only be T/F, you don't need them in the
first two statements.
BadgeIn<>BadgeO ut OR
Badge<>BadgeOut OR
Badge=BadgeOut AND Damaged=True
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: John young |
last post by:
I have been looking for an answer to a problem and have found this
group and hope you can assist .
I have been re doing a data base I have made for a car club I am with
and have been trying to make a query that selects from a table as
desribed below ..
I have a table (Volunteer) that has a member field (memnumber)
and a number of fields that are headed in various categories and are
yes/no formated
|
by: Enterprise |
last post by:
I'm stuck. I use Access 2000. I have a date field in a query.
What I need done is if a Limit_Date function returns a true, I want
the date field to be Between 1/1/02 and 1/1/03, otherwise I want it
left alone(all records shown).
I have an if statement in the criteria:
iif(Limit_Date()=true,Between 1/1/02 and 1/1/03,"*")
This does not work since the if statement returns strings. So if
Limit_Date is true, then the criteria is "Between...
|
by: Andy |
last post by:
Hello,
I am having many problems with setting up a parameter query that
searches by the criteria entered or returns all records if nothing is
entered.
I have designed an unbound form with 3 fields on it: Date (DateSpan1
and DateSpan2), Originator, and GroupName. I have added a button that
triggers a query and uses those fields as its parameter criteria to
populate a form. The user must be allowed to either enter all of the
|
by: Matthew |
last post by:
Hey ,
I have built a query which takes values from unbounded fields on a
form, and it all works except for one thing. I have a few fields in
my query that are dates. I also have a start and end date and time on
my form. So, a user will specify the start and end date, and also,
the user has the option of checking which of the time fields from the
query will have these dates as criteria.
So, one of the date fields in my query is...
|
by: Robert |
last post by:
I am trying to create a db for service providers by county. I'm relatively
new to db programming, but I have done quite a bit of programming ranging
from the old basic days up to doing some programming in the HotDocs
software. I've kind of accomplished my goal in access, but I'm not quite
there yet and figure I've really screwed something up.
The db consists of 5 tables I believe. T1 is the main contact info, with an
autonumber ID...
| | |
by: Judy |
last post by:
I'm using Access 2003 and was wondering if it is possible to have a
paramater selection within a crosstab query so that I wouldn't need to build
a new table.
I have a select query that I'm using to build the crosstab query from. In
the select query I'm prompting for a specific quarter but when I go to run
the crosstab it doesn't seem to like the parameter that has been put on the
select query and returns a message of "The Microsoft Jet...
|
by: MLH |
last post by:
I have an A97 query (qryVehiclesNowners2) that has a table
field in it named . Depending on the selections made
in a number of criteria choices on a form, a field on the form
will have string values in it like...
Between #12/1/2004# And #12/31/2004# - or -
>= #6/10/2005# - or -
Between #6/1/2005# And #6/30/2005#
I want to use them as criteria for the query.
|
by: MLH |
last post by:
Am repeating question with different subject heading, perhaps
stating more clearly my problem...
I have an A97 query (qryVehiclesNowners2) that has a table
field in it named . Depending on the selections made
in a number of criteria choices on a form, a field on the form
will have string values in it like...
Between #12/1/2004# And #12/31/2004# - or -
>= #6/10/2005# - or -
|
by: Stan |
last post by:
I am working with Access 2003 on a computer running XP. I am new at
using Access. I have a Db with a date field stored as mm/dd/yyyy. I
need a Query that will prompt for the month, ie. 6 for June, and will
return all records in that month.
|
by: Doris |
last post by:
It does not look like my message is posting....if this is a 2nd or 3rd
message, please forgive me as I really don't know how this site works.
I want to apologize ahead of time for being a novice with MS Access
and VBA. I desperately need help with 2 queries that I am trying to
put together. I want to thank anyone that can help me out with this
situation.
I want to put a select query(Query1) that uses one table and the
criteria would...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| | |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
