Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2011
    Posts
    56

    Unanswered: Make a Query that Will show you info for a date?

    ok..

    so i'm creating a simple little job booking database.

    you book a job in:
    DateTaken
    CustomerDetails
    DateOfJob
    JobDetails
    Comments

    Basically, say i have 1500 jobs booked in for this year, but i only want to see what jobs are booked for tomorrow? how would i achieve this?

    i was thinking of writing a code but i was told some sort of query would be best for this, any reccomendations?

    thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try something like:
    Code:
    SELECT * FROM <JobTable> WHERE DateOfJob = DateAdd('d', 1, Date());
    Have a nice day!

  3. #3
    Join Date
    Oct 2011
    Posts
    56

    hey sindho :-) thanks for reply and good to see you

    hey sindy! :-) thanks for reply and good to see you

    just tried that in the "Criteria" part of a query, and it tells me the Syntax is not valid?

    Code:
    SELECT * FROM JobBookingTbl WHERE DateOfJob = DateAdd('d', 1, Date());
    have i not filled it right?

    It says, check subqueries syntax and enclose in parenthese.. but where are the () sposed to go?
    Last edited by jackjsmith88; 05-06-12 at 12:07.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Is [DateOfJob] defined as a Date/Time column in the table [JobBookingTbl]?
    Have a nice day!

  5. #5
    Join Date
    Oct 2011
    Posts
    56
    Yes it is? ill just double check but yes im sure that it is,

    would that matter?

  6. #6
    Join Date
    Oct 2011
    Posts
    56

    Whats wrong with this Syntax?

    Trying to specify this as Criteria for a simple query.

    What i want to acheive is to only show records that start tomorrow.

    What am i doing wrong? it says the syntax is incorrect, check for parenthese, and highlights the "Select" part as the error

    Code:
    SELECT * FROM JobBookingTbl WHERE DateOfJob = DateAdd('d', 1, Date());
    ty

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I think the problem is just that. Try this:
    Code:
    SELECT *
    FROM JobBookingTbl
    WHERE (((DateOfJob)=DateAdd('d',1,Date())));
    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Oct 2011
    Posts
    56

    hello

    Thank you for your very speedy reply,

    i'm a little stumped. :-D

    this syntax was put in the "criteria" field of a query, this may sound silly, but it only has space for one line of code. so how would i implement your 3 lines of code? could i simplify it down to one line?

    i'm just wondering if you 3 lined to to make it easier to understand for me other whether it goes somewhere else like in SQL View?

    thanks

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Missinglinq View Post
    I think the problem is just that.
    adding a few extra pairs of parentheses makes an error go away?

    never seen that before
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by jackjsmith88 View Post

    ...this syntax was put in the "criteria" field of a query
    What you posted was a SQL Statement, it is not something that belongs in the Criteria Box of a Field in Query Design View! In the Criteria Box you'd simply put


    DateAdd('d',1,Date())

    Quote Originally Posted by jackjsmith88 View Post

    ...it goes somewhere else like in SQL View?
    Yes, alternatively, you could Copy & Paste the SQL Statement I gave you over the current SQL Statement in SQL View, and that would work, too.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What can you see when you switch from SQL View to Design View in the query designer?
    Have a nice day!

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    threads merged
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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