Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2013
    Posts
    4

    Unanswered: Access query help

    Hello! Very new to the access scene so this may be a novice quesion to many. My query is returning all specified fields from a table. My conundrum is I;d like to only return the fields that are dated back 14 days. I understand to do this looks like 'date()-14' in the criteria but then I want to make a second criteria that if the status (one of the fields) is completed or rejected AND is older than 14 days, i'd still like it to show up. Is there an UNLESS operator? =)

    Any help is greatly appreciated!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry I tend not to use the query designer (in my books it comes between me and the SQL engine. it is very useful for relqtively simple queries.

    what you are trying to do is filter the data returned, in SQL speak thats a WHERE clause. WHERE clauses are effectively boolean statements (if rows match the boolean tests they are returned otherwise they are rejected). boolena logic can use equals, not equals, less than greater than aswell as compiund clauses eg OR or where Acolumn = avlalue and bcolumn is less than a value

    what I'd suggest you do is switch to the SQL pane (IIRC its the left most iicon set under FILE).
    you should see something similar to SELECT * FROM mytable

    so your whereclause will become somethign like

    WHERE datediff("d", myDateColumn, date())<=14 OR (myStatus = "Rejected" AND datediff("D", mydatecolumn, date()) >14)

    carefull use of brackets keeps sub clauses together
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2013
    Posts
    4

    Smile Mind Blown

    Wow. thanks. this is great. One more thing...If i want to add multiple statuses to include rejected, warehouse processing, etc...what is the correct syntax? Also, so I don't continue to blow up feeds with syntax questions, do you have any great references for me?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its all down to boolean logic
    your where clause evaluates as true or false

    your brackets are punctuation

    say ferinstance you had a column called MyStatus

    OR (myStatus = "Rejected" AND datediff("D", mydatecolumn, date()) >14)

    if you wanted rejected and warehouse processing
    OR ((myStatus = "Rejected" OR myStatus = "Warehouse processing") AND datediff("D", mydatecolumn, date()) >14)
    note the extra brackets
    OR

    OR (myStatus in ("Rejected", "Warehouse processing") AND datediff("D", mydatecolumn, date()) >14)

    OR
    if the 14 day rule only applied to every other status
    OR (myStatus NOT in ("Rejected", "Warehouse processing") AND datediff("D", mydatecolumn, date()) >14)


    as to a good book / reference there's lots out there. Rudy R937 wrote a good book 'Simply SQL' its nowhere near as comprehensive as some but it does give a good grounding in SQL. SQL is a psuedo English language based method to manipualte data and databases.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2013
    Posts
    4

    Red face Screwed up

    So, I added those lines where I "thought" they should go in an already created query but the results are not correct. When I created a subquery it worked fine but then I had issues when running from a form because it wanted the input which I didn't feel like fiddling with.

    Anyway, can you please let me know if my syntax is off? I checked over it several times (I have C++ and Java coding experience).

  6. #6
    Join Date
    Jan 2013
    Posts
    4
    Already fixed two issues. PLease consult this WHERE (((DateDiff("d",[DateRequested],Date()))<=14) AND ((Tbl_Equipment.[VE0#])=[Forms]![Frm_Welcome]![VE0Search])) AND ((Tbl_Equipment.[VE0#])=[Forms]![Frm_Welcome]![VE0Search]) AND (([LAN Admins].[User Name]) Like NZ([Forms]![Frm_Welcome]![LANAdminSearch],"*")) AND ((Tbl_Buildings.BuildingName) Like NZ([Forms]![Frm_Welcome]![BuildingSearch],"*")) AND ((Tbl_Orders.RequestedBy) Like "*" & [Forms]![Frm_Welcome]![ClientSearch] & "*") AND (([Forms]![Frm_Welcome]![VE0Search]) Is Null) AND (((DateDiff("d",[DateRequested],Date()))>14 OR ((Tbl_Equipment.Status)="Completed" AND DateDiff("d",[DateRequested],Date())>14)))

Tags for this Thread

Posting Permissions

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