If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

if i seem a bit patronising here i apologise but i don't know how much you know
note: anything enclosed by me in triangular brackets ie <> need to be replaced by the specific data that’s relates to you database

the normal method of doing that is with a query.
The first thing I’d advise is to stay well clear of the query wizards, as they're truly awful.

now once you into the query you have two option uses the query builder which is fairly easy to use but a bit inflexible or to use SQL by selecting the SQL option in the drop down button in the top corner.

even if you decide not to use SQL to build queries it's worth knowing about as most people on this forum use SQL to transfer queries around

the structure for the query you've given would be

Code:

SELECT *
FROM [<the name of your table>]
WHERE ([<the name of the date field>] Between #7/01/2003# And #7/30/2003#) And [<name of field with the inspecting supervisor in it>] = "Supervisor 1";

ok I have a form which adds records in with fields such as.. the date the record was opened, the date the record was closed, project #, system, area, etc...

I have a seperate form to create a report of my findings.. say that i was to search for all records from august 1 through august the 30th.. and in area B and are labeled project 1..

I want a form which the user enters in what they want out of the database.. (ie.. the date, area, project etc.. ) all criterias have to be met then it displays only those records... and in those records I want a count of how many are "area B"

In my table "AREA" would be my field name and under AREA there should be something like..

In the beginning the user adds records.. and chooses through a combo box these fields.. area B - 03 or area C - 03 etc.. so each record has its only area.

again,

I want to be able to have a form which searches through dates, area, project .. first.. then with those records pick out all areas of what the user asked for and make a count and display of how many those records there are..

Between [Forms]![frm_reportQualityStatus]![txtStartOpen] And [Forms]![frm_reportQualityStatus]![txtStartEnd]

and

Code:

Between [Forms]![frm_reportQualityStatus]![txtCloseOpen] And [Forms]![frm_reportQualityStatus]![txtCloseEnd]

if both of these work seperatly than you should be able to combine them in the following manor

Code:

(<field> Between [Forms]![frm_reportQualityStatus]![txtStartOpen] And [Forms]![frm_reportQualityStatus]![txtStartEnd]) Or
(<field> Between [Forms]![frm_reportQualityStatus]![txtCloseOpen] And [Forms]![frm_reportQualityStatus]![txtCloseEnd])

Originally posted by TonyT
Ok I think I got it.. I just have a couple of question..

I'm trying to get 2 or more criterias to work together..

ie..

my first criteria was.. to find all records that were still open by dates..

my second criteria .. I was to find all records that are closed..

would i have to setup a different query just for that criteria..

In my first criteria:

Code:

Between [Forms]![frm_reportQualityStatus]![txtStartOpen] And [Forms]![frm_reportQualityStatus]![txtStartEnd]

and my second:

Code:

Between [Forms]![frm_reportQualityStatus]![txtCloseOpen] And [Forms]![frm_reportQualityStatus]![txtCloseEnd]

I put them together in the same query and i get nothing.. but if i list them seperately .. i get the fields i ask for..

code:--------------------------------------------------------------------------------
(<field> Between [Forms]![frm_reportQualityStatus]![txtStartOpen] And [Forms]![frm_reportQualityStatus]![txtStartEnd]) Or
(<field> Between [Forms]![frm_reportQualityStatus]![txtCloseOpen] And [Forms]![frm_reportQualityStatus]![txtCloseEnd])
--------------------------------------------------------------------------------

in my criteria... "OpenDate"

would i have to add this criteria to

"CloseDate" - My other date field

also,

<field> - here do i enter in my field name?

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

I tired combing the code.. and inserted in my criteria field under "Opendate" but I get a syntax error..

I'm not quite sure what your asking here the way i've interpreted your question is how do you make a query that counts the number of records that start with the the first 6 charactors "area B" if this is what you ment then the answer is easily done in 2 ways

method 1

Code:

qry1
select *, left(<field with area in>,6) as sixChar
from <table or query name that has the base data>;
qry2
select count([sixChar])
from qry1
where [sixChar] like "area B";

method 2

Code:

qry1
select count(<field with area in>)
from <table or query name that has the base data>
where <field with area in> like "area B*";

i'm sure there are more way of doing this but these are the two that come to mind

Originally posted by TonyT
I also have another question... sorry

ok I have a form which adds records in with fields such as.. the date the record was opened, the date the record was closed, project #, system, area, etc...

I have a seperate form to create a report of my findings.. say that i was to search for all records from august 1 through august the 30th.. and in area B and are labeled project 1..

I want a form which the user enters in what they want out of the database.. (ie.. the date, area, project etc.. ) all criterias have to be met then it displays only those records... and in those records I want a count of how many are "area B"

In my table "AREA" would be my field name and under AREA there should be something like..

In the beginning the user adds records.. and chooses through a combo box these fields.. area B - 03 or area C - 03 etc.. so each record has its only area.

again,

I want to be able to have a form which searches through dates, area, project .. first.. then with those records pick out all areas of what the user asked for and make a count and display of how many those records there are..

to take your last question first
yes <field> needs replacing with the field name

so it should read something like this using the field name you've given:
where ([OpenDate] Between [Forms]![frm_reportQualityStatus]![txtStartOpen] And [Forms]![frm_reportQualityStatus]![txtStartEnd]) Or ([CloseDate] Between [Forms]![frm_reportQualityStatus]![txtCloseOpen] And [Forms]![frm_reportQualityStatus]![txtCloseEnd]);

to translate that into engish:
where the OpenDate is between txtStartOpen and txtStartClose or the CloseDate is between txtCloseOpen and txt

notice the "or" ie records with OpenDates outside the specified range will show if they have a valid CloseDate

Originally posted by TonyT
So if I combine this code:

code:--------------------------------------------------------------------------------
(<field> Between [Forms]![frm_reportQualityStatus]![txtStartOpen] And [Forms]![frm_reportQualityStatus]![txtStartEnd]) Or
(<field> Between [Forms]![frm_reportQualityStatus]![txtCloseOpen] And [Forms]![frm_reportQualityStatus]![txtCloseEnd])
--------------------------------------------------------------------------------

in my criteria... "OpenDate"

would i have to add this criteria to

"CloseDate" - My other date field

also,

<field> - here do i enter in my field name?

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

I tired combing the code.. and inserted in my criteria field under "Opendate" but I get a syntax error..

i think the problem is your logic
the way you've made the query it reads
show all records with a start date betweeen the specified dates and that matches the data in the txtrej box or has the correct close date

ie if the close date is right show the record else if the close date is wrong but both the start date and rej are right then show the record

niw i think i'm starting to understand what your after and i think that you need to use a few logic tricks

A OR True = true
A OR False = A
A AND True = A
A And False = False

so if you do
(X between A and B) or not(A is Null or B is Null)

this logicly says If neither A or B is blank then do then return the results of between otherwise return true

i think this is what you need to do:
((X between A and B) OR (not(A is Null OR B is Null))) AND ((Y between C and D) OR (Not(C is Null OR D is Null))) AND ((Z Like E) OR (Not(E is Null))) AND ...

((strDate between txtStartOpen and txtStartEnd) OR (not(txtStartOpen is Null OR txtStartEnd is Null))) AND ((DATECLOSE between txtCloseOpen and txtCloseEnd) OR (Not(txtCloseOpen is Null OR txtCloseEnd is Null))) AND ((REJ Like txtREJ) OR (Not(txtREJ is Null)))

I stuck this under my strDate which was.. "X" in your example

and came up with this SQL

Code:

SELECT tblInspections.strProject, tblInspections.strDate, tblInspections.DATECLOSE, tblQR.EPO, tblInspections.REJ, tblQR.System, tblInspections.CRT, tblInspections.INSP, tblInspections.FLT, tblInspections.WF, tblInspections.DISP, tblInspections.strArea, tblInspections.strReference, tblInspections.INSPECTOR
FROM tblInspections INNER JOIN tblQR ON (tblInspections.strProject = tblQR.strProject) AND (tblInspections.strArea = tblQR.strArea) AND (tblInspections.strReference = tblQR.strReference)
WHERE (((("strDate" Between "txtStartOpen" And "txtStartEnd") Or (Not ("txtStartOpen" Is Null Or "txtStartEnd" Is Null))) And (("DATECLOSE" Between "txtCloseOpen" And "txtCloseEnd") Or (Not ("txtCloseOpen" Is Null Or "txtCloseEnd" Is Null))) And (("REJ" Like "txtREJ") Or (Not ("txtREJ" Is Null)))));

I saved it.. went to my form.. punched in the A and B from your example.. with dates.. and it pulled up all records between those dates.. i go back to the form.. leave the same dates there.. now I type in a Y for Yes in the REJ field.. click my get record button.. and it displays all record.. including the N = No ... in my db there are only 2 Yes records from the date i asked for..

tested it again.. same thing goes for my CLOSEDATE... it just pulls up all records from the date I specify in the first set of dates.. "strDate"