Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Unanswered: query and criterias ?!!??

    I'm having problems with my query...

    I'm trying to pull certain records that meet certain criterias..

    Ok i have three fields on my form...

    one is a date field..
    --------
    here the user enters in two dates.. a starting date and an ending date.. ( this finds all records in between those dates)

    2nd field is the area
    --------------
    Here the users enters in the area of the record..
    (this find all records between those dates in field 1(date) then pulls out only the records between those dates and with that area...

    3rd field.. is the code
    ----------
    Here the user enters in the code of a record he wants to find.. ( this finds all records between those dates in field 1 and within that area and with that code)

    so the finally output would be records btween the dates specified,that area, and with that code..

    I have a couple of problems..

    when i have more than one criteria.. in my query nothing comes up when i do my search for records.. I tested each criteria by itself.. and they work.. its just when i add more than one criteria.. no records pull up...

    the second problem I'm having is..

    say that the user just wants to find records in between the dates specifide.. this is the only field he enters into.. hits enter.. nothing comes up!.. . but if i take all criterias off my query except for the date.. then yes it comes up...

    in other words.. I have to fill in the fields of all criteria for it to bring records in.


    hope this made sense..
    if you need to see my .mdb let me know.. thanks

    I'm soo lost...

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Post Can you show us your SQL

    Can you show us your SQL

  3. #3
    Join Date
    Jul 2003
    Posts
    292
    yes.. here it is..

    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

  4. #4
    Join Date
    Apr 2003
    Posts
    280
    Use the DatePart function to pull it out the data.


    Say that you have a field column for DateStart

    Date_Starting: DatePart("m",[DateStart]) and in the criteria, I'm assuming you would have an form for this, put Forms!FormName!NameofObject

    Do the same for the DateEnd column.

    For the other column in the query, do the same thing as mention above for the criteria.

  5. #5
    Join Date
    Jul 2003
    Posts
    292
    with some help... heres the new SQL statment..

    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 (((([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)))));

    The code here is suppose to pull back records that meet requirements..

    ok first off - I created a form which lets the user enter in records.. each time the user creates a new record.. an open date (which is today's date) is assigned to this record...

    users can go back and update these records and put a close date on them.. soo now I have two dates for one record.. an open date and a close date..

    Now I want to create another form which pulls back certain records.. I have a form which lets the user enter dates in...

    ie..

    open records : [field 1 - start date] thru [field 2-end date]

    closed records: [field 3 - start date] thru [field 4-end date]

    and i have a [cmd button]

    ok here the user can enter in the first set of dates.. say from 9-01-03 to 9-30-03.. and this would pull all open records within the month of sept.

    ok now the user wants to find all closed records between the month of sept. now the user clears out the date the the open records field and puts dates in the closed field.. the user hits the cmd button and this would pull all closed records in sept.

    when i started my form with just the open records. .. and made a query with the criteria just to pull those records.. it pulled out all open records no problem.. now when i added the second set of fields for all closed records.. and inserted some criteria for all closed records.. its like both criterias have to meet to bring back those set of records.. but i just want what is asked for..

    say i ask for all open records.. i would add in dates of which record i want to see and hit the cmd button and bam.. records. same goes for closed records..

    but adter entering both closed and open record fields to my form and inserting two criteria in my query.. it doesnt seem to work..

    any ideas why ??

    note: the "REJ" [REJection = yes or no] in the code above is another field that I want met .. say all closed records with a REJ of no.. would pull all closed records with a REJ of no...


    thanks so much



    lansing,

    Could you explain what you mean.. sorry im kinda new.. thanks

  6. #6
    Join Date
    Jul 2003
    Posts
    292
    lansing,

    This is what I did with the code you gave me..

    DatePart("m",[Forms]![frm_reportQualityStatus]![strDate])

    I inserted that in my "strDate" thats the dates of all open records. I cleared out all other criterias in my query. Inserted some dates in my form.. but no records were pulled up ..

    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
  •