Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Unanswered: Retrieving records??

    I have a form which stores records.. Now I need to create another form which pulls specific data in..

    ie. say I need to pull in all records that were dated between.. 7/01/03 thru 7/30/03 AND where inspected by supervisor 1. Something of that nature..

    I'm new to access.. so please bare with me..

    thanks

  2. #2
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    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 thats relates to you database

    the normal method of doing that is with a query.
    The first thing Id 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";
    Last edited by m.timoney; 10-01-03 at 10:52.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  3. #3
    Join Date
    Jul 2003
    Posts
    292
    thanks for the quick response...

    Ok Heres what I have done.. I created a form where the user can enter in the dates of the records the want to see..

    also, the leader..etc..

    I created a query.. in design view.. added my tables that had the data I wanted to pull out of..

    how would i set the criteria to pull specific data in ?

    the date fields.. are seperate.. one for open date and the other close date..

    thanks for the help

  4. #4
    Join Date
    Jul 2003
    Posts
    292
    sorry bout that..

    i have one date field.. for all opened records
    and another one for all closed records..

    so this is what i did.. under my criteria..
    Code:
    between Forms![frm_reportQualityStatus]![txtStartDate] And between Forms![frm_reportQualityStatus]![txtEndDate]
    and i get an syntax error.. ?

    any ideas.. im using access 97

  5. #5
    Join Date
    Jul 2003
    Posts
    292
    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..

    thanks

  6. #6
    Join Date
    Jul 2003
    Posts
    292
    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..

    area B - 01
    area B - 02
    area B - 03
    area B - 04
    area B - 05
    area C - 01
    area C - 02
    area C - 03
    area C - 04

    etc...

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

    seems like alot to ask.. thanks alot for helping..

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    that depends on how your trying to link the crits
    you say the 2 are
    Code:
    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..

    thanks
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    Jul 2003
    Posts
    292
    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..

    did i miss something ??

    thanks

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    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..

    area B - 01
    area B - 02
    area B - 03
    area B - 04
    area B - 05
    area C - 01
    area C - 02
    area C - 03
    area C - 04

    etc...

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

    seems like alot to ask.. thanks alot for helping..
    Last edited by m.timoney; 10-02-03 at 12:37.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  10. #10
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    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..

    did i miss something ??

    thanks
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  11. #11
    Join Date
    Jul 2003
    Posts
    292
    Ok I dont know if this helps or not but..

    everytime i add a different criteria.. in any of the other fields.. they have to be met before any record is displayed ??

    What I if I wanted to search for records between 2 dates.. then left my other fields blank.. why wont it just search for those recrods with those dates.. can't it be left blank?

    I'm attaching my mdb.. if you wanted to take a look..

    the info pulling out of is.. "tblInpections" "qryQualityStatus" "frm_reportQualityStatus"

    When I refer to "Area B" Im actaully referring to "tblInpectCodes" you'll see what i mean.. I'm tryin to make a count of all records - that the user wants to see with codes of AB or CO or DR..

    thanks
    Attached Files Attached Files

  12. #12
    Join Date
    Jul 2003
    Posts
    292
    ok looks like I got the dates to work...

    now as I add another field for user to pick out of.. it the dates dont work..

    heres my SQL:

    I can clear out openrecord and fill in close record fields.. and get the date for all closed records.. with:
    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 (((tblInspections.strDate) Between [Forms]![frm_reportQualityStatus]![txtStartOpen] And [Forms]![frm_reportQualityStatus]![txtStartEnd])) OR (((tblInspections.DATECLOSE) Between [Forms]![frm_reportQualityStatus]![txtCloseOpen] And [Forms]![frm_reportQualityStatus]![txtCloseEnd]));
    but if I add another criteria to filter out.... and just get record rejects "Y" or "N"... then i get nothing.. and heres that 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.strReference = tblQR.strReference) AND (tblInspections.strArea = tblQR.strArea) AND (tblInspections.strProject = tblQR.strProject)
    WHERE (((tblInspections.strDate) Between [Forms]![frm_reportQualityStatus]![txtStartOpen] And [Forms]![frm_reportQualityStatus]![txtStartEnd]) AND ((tblInspections.REJ)=[forms]![frm_reportQualityStatus]![txtREJ])) OR (((tblInspections.DATECLOSE) Between [Forms]![frm_reportQualityStatus]![txtCloseOpen] And [Forms]![frm_reportQualityStatus]![txtCloseEnd]));
    thanks for the help..

  13. #13
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    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 ...

    X= startdate
    Y= closedate
    A,B,C,D = txt boxes with date ranges
    Z = REJ
    E = txt box with reject result

    happy hunting
    Last edited by m.timoney; 10-03-03 at 14:31.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  14. #14
    Join Date
    Jul 2003
    Posts
    292
    thanks a bunch... I will give this a try...

  15. #15
    Join Date
    Jul 2003
    Posts
    292
    ok this is what I've done...
    ((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 ...

    X= startdate (strDate)
    Y= closedate (DATECLOSE)
    A,B,C,D = txt boxes with date ranges txtStartOpen, txtStartEnd, txtCloseOpen, txtCloseEnd
    Z = REJ (REJ)
    E = txt box with reject result (txtREJ)
    and came up with..

    Code:
    ((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"


    did i do something wrong ??

    thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •