Results 1 to 3 of 3
  1. #1
    rjscoates is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Florida
    Posts
    6

    User input for Date then put into [art of a query

    Hi,




    I have two tables, one with attendance data recorded by day (mm/dd/yyyy) and another with certificates with a starting and an ending date (also mm/dd/yyyy).


    i.e.


    Attendance Data Table:


    UserID -------- Date -------- Data


    1 -------- 1/1/2020 -------- 1
    1 -------- 1/2/2020 -------- 2
    1 -------- 1/3/2020 -------- 1
    1 -------- 1/4/2020 -------- 1
    2 -------- 1/1/2020 -------- 1
    2 -------- 1/2/2020 -------- 2
    2 -------- 1/3/2020 -------- 2
    2 -------- 1/4/2020 -------- 1


    Certificate Table:


    UserID --- Start -------------- End -------------- Cost
    1 -------- 12/1/2019 -------- 1/31/2020 -------- $1.00
    1 -------- 2/1/2020 -------- 5/31/2020 -------- $2.00
    2 -------- 11/1/2019 -------- 4/15/2020 -------- $1.50


    I am trying to build a query that uses both tables to search for results between the 1st and last day of a selected month. My thoughts are to have the user enter a month and year, and then build a 'between dates' criteria, but how do I build the criteria using the user entered data?


    My existing crosstab query pulls the year from the daily attendance data using 'Year: Format([AttndDate],"yyyy")', monthly using 'Month: Format([AttndDate],"mm")' which I use as rows and 'Format([AttndDate],"d")' which I use as columns with the Data in the columns. This part is working well for me!


    My end result that I think I should have is along the lines of ask the user the search parameters of [Year?] [Month?] which will pull the correct data from my crosstab table and also build a query that is 'Between [Month?]/01/[Year?] and [Month?]/31/[Year?]' for my certificates table, but I am so lost in how to build this query correctly!


    My questions are:
    How do I take user entered data of month, year (and convert that into mm/dd/yyyy) to build a dated criteria using it? How can I make sure the first and last day is correct as not every month ends on the 31st?


    Many thanks,

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Have user enter parameters into textboxes on form. Query references textboxes as criteria. Since CROSSTAB is involved, will likely need to use PARAMETERS clause. Review: http://allenbrowne.com/ser-67.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    rjscoates is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Florida
    Posts
    6
    Thank you for your prompt response.

    I learnt something I had not thought of from your comments and this lead me down a new rabbit hole!! I managed to achieve so much more with you pointing me in the right direction.

    Thank you.

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

Similar Threads

  1. Replies: 1
    Last Post: 02-16-2018, 03:38 AM
  2. Replies: 1
    Last Post: 10-12-2017, 06:12 PM
  3. Replies: 1
    Last Post: 10-30-2014, 10:11 AM
  4. MS Access Date filter basedon User Input
    By shanmugamgsn in forum Access
    Replies: 1
    Last Post: 12-12-2011, 04:15 PM
  5. Replies: 9
    Last Post: 10-01-2010, 05:50 PM

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