Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23
  1. #16
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    Whether you use a prompt or reference to textbox, the same parameter has to be under each date field on separate criteria rows as I originally described.

    Why does the unbound textbox require a date value? Does it have formatting that requires a date and not just a number?



    If you want the query results to show on form make the query the RecordSource for the form. Review this tutorial to see one way to build filter into a form: http://datapigtechnologies.com/flash...tomfilter.html

    At this point, I don't think I will be able to help unless you provide the project so I can see what you have done. Make copy, remove confidential data, run Compact & Repair, zip if large, attach to post or upload to fileshare site such as box.com and post link.
    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.

  2. #17
    Lmartinrn is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    17
    http://www.box.com/s/4c58a03a71e5ed80fdc0

    I have uploaded to Box.com

    Any help you can provide will be greatly appreciated!

  3. #18
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    Which objects are involved - which query, form, report?
    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.

  4. #19
    Lmartinrn is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    17
    qryEmplCredExpEnterParameter

  5. #20
    Lmartinrn is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    17
    Once I get the query working, I will make a report from this query the user will use.

  6. #21
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    The query includes employee name in the query filter parameters. The employee name is unique in the source table. Only one record will return. Might as well just search only on the employee name criteria and eliminate all the others. If you want this query to return all employees with expiring dates, eliminate the employee name parameter. When I remove the employee name criteria, 43 out of 107 are returned.

    SELECT tblEmplInfo.Location, tblEmplInfo.[BLS Expiration Date], tblEmplInfo.[ACLS Expiration Date], tblEmplInfo.[TNCC Expiration Date], tblEmplInfo.[PALS Expiration Date], tblEmplInfo.[ENPC Expiration Date], tblEmplInfo.[HAZMAT Awareness], tblEmplInfo.[CEN Expiration Date], tblEmplInfo.[TB Spot Test Due], tblEmplInfo.Active
    FROM tblEmplInfo
    WHERE tblEmplInfo.Location Like [Enter Location] & "*" And tblEmplInfo.[Employee Last Name] Like [Enter Last Name] & "*" And tblEmplInfo.Active=True
    And (tblEmplInfo.[BLS Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
    Or tblEmplInfo.[ACLS Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
    Or tblEmplInfo.[TNCC Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
    Or tblEmplInfo.[PALS Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
    Or tblEmplInfo.[ENPC Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
    Or tblEmplInfo.[HAZMAT Awareness] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
    Or tblEmplInfo.[CEN Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
    Or tblEmplInfo.[TB Spot Test Due] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ()))
    ORDER BY tblEmplInfo.[Employee Last Name];
    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.

  7. #22
    Lmartinrn is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    17
    The user needs to have the option to either pull up one person with expiring credentials or everyone at a specific location with upcoming expiring credentials which is where I'm running into problems

  8. #23
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    Paste this into the SQL View editor:

    SELECT * FROM tblEmplInfo
    WHERE tblEmplInfo.Location Like [Enter Location] & "*" And [ID] Like [Enter ID] & "*" And Active=True
    And ([BLS Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
    Or [ACLS Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
    Or [TNCC Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
    Or [PALS Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
    Or [ENPC Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
    Or [HAZMAT Awareness] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
    Or [CEN Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
    Or [TB Spot Test Due] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ()))
    ORDER BY [Employee Last Name], [Employee First Name];

    Then I suggest instead of the query input parameter prompts (I never use them), have unbound controls on the form as input for the Location and ID, right next to the date criteria box. The employee ID could be multi-column combobox. Users would see and pick name but the search would be on the ID.
    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.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. DCount Items in query field
    By rdr910 in forum Queries
    Replies: 7
    Last Post: 03-02-2012, 05:31 PM
  2. Replies: 1
    Last Post: 12-15-2011, 08:47 AM
  3. query returning extra items
    By cbrsix in forum Queries
    Replies: 6
    Last Post: 07-05-2011, 02:22 PM
  4. Counting multiple items in a query
    By slaterino in forum Access
    Replies: 2
    Last Post: 10-14-2010, 08:21 AM
  5. Query Not Listing All Items
    By Rawb in forum Queries
    Replies: 7
    Last Post: 05-14-2010, 08:00 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