Results 1 to 12 of 12
  1. #1
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108

    queries by date... 2 questions

    Hello,


    I have two questions on creating queries...

    1. I am trying to make a query with a user prompt that will pull all records with the month given. I have tried Datepart('m',[DateOf1032])=[Enter month] and I have tried month([DateOf1032])=[Enter month] in the criteria field of my query. Am I just setting up the syntax wrong or am I completely wrong?

    2. I need a query that will look at 3 different date fields and see if any of them match the month entered into the user prompt. I am not even sure how to begin this one.

    Thanks,
    Will

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    1. I am trying to make a query with a user prompt that will pull all records with the month given. I have tried Datepart('m',[DateOf1032])=[Enter month] and I have tried month([DateOf1032])=[Enter month] in the criteria field of my query. Am I just setting up the syntax wrong or am I completely wrong?
    why do you think the syntax is wrong? do you get an error message? if so - what?

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    What happens when you try running the query those expressions in the criteria? Do you get an error, or just the wrong data?
    The month has to be entered as a number in these cases.

    For the second question, that should be possible - you just need to have the same prompt in each criteria expression. Since you are looking to see if ANY of the dates meet the criteria, you need to have each criteria expression on a separate criteria line in query design view.

  4. #4
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    for the Datepart('m',[DateOf1032])=[Enter month] and month([DateOf1032])=[Enter month] when I run the query I just get a blank query, for the prompt I am entering 7 for July ect.. I do not get any errors its just a blank pull. I have also checked that there are records with month date I am looking for.

    for my second question I need it to pull by the month I entered for all 3 fields, so if I enter 7 for July if any of the 3 date fields match I want it to pull those records. I am assuming I would use an if statement something like if(COPExp, DateRTW, DateOfMed)=datepart('m',[DateSearchedFor]), but I am not to comfortable with if statements yet.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I presume DateOf1032 is a date field and not a text field which you would get if you use the format function

  6. #6
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    Yes, all the fields I am searching in are set as date fields.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    in that case suggest you remove the criteria for now and show month([DateOf1032]) then run the query and see what value it produces for July - it should be 7, but perhaps not

  8. #8
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    ok, I figured out my first issue, it was user error I have removed my head from my butt and things seem to be working better...

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Good
    for my second question I need it to pull by the month I entered for all 3 fields, so if I enter 7 for July if any of the 3 date fields match I want it to pull those records. I am assuming I would use an if statement something like if(COPExp, DateRTW, DateOfMed)=datepart('m',[DateSearchedFor]), but I am not to comfortable with if statements yet.

    turn it round to avoid lots of OR's and have your criteria something like this


    Month([DateSearchedFor]) IN ( Month([COPExp]), Month([DateRTW]), Month([DateOfMed]))

  10. #10
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    Ajax,
    Would I put that in the criteria field in the query or would I need to do that in VBA?

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    in the query
    in the criteria row for the Month([DateSearchedFor]) column put IN ( Month([COPExp]), Month([DateRTW]), Month([DateOfMed]))

  12. #12
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    Ajax,
    Thanks that seemed to do the trick. Thanks everyone for the help!!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Database Questions - Forms & Queries
    By chaddresnick in forum Access
    Replies: 1
    Last Post: 03-29-2015, 11:38 AM
  2. Replies: 26
    Last Post: 05-28-2014, 12:23 PM
  3. Queries Questions
    By data808 in forum Queries
    Replies: 4
    Last Post: 10-21-2013, 08:21 AM
  4. Replies: 7
    Last Post: 10-07-2013, 01:57 PM
  5. Replies: 7
    Last Post: 04-24-2012, 11:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums