Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Khao is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2012
    Posts
    13

    General query filter question!

    Hey hey,

    I've been asked to create my company's database and I'm a little out of my depth when it comes to queries & reporting.

    My query (well... table) has the following fields: Date Invoiced, Product, Quantity, Subtotal Price, JobCode, State

    I've been able to filter the report with dates by simply using the date criteria " Between [Date from] And [Date to] "... essentially two boxes pop up asking me which dates to choose between.

    However, I would also like to be able to filter by state and job code. The job code contains two entries (DM / SW) and I'd like to have the option to choose either one or both.

    Furthermore, I need to be able to filter depending on which state the job was in (QLD, NSW, VIC, SA, WA, NT, ACT, TAS) or all of them. It appears that all basic query criteria is restricted to finding specific values.

    The only option I know of is to create dozens of queries depending on which specific filter I would like... But that seems highly impractical and I'm sure that someone that is far more knowledgable than I could be of assistance. Ideally, a form with drop down boxes would be preferred but if I'm limited to boxes popping up asking for parameters then that is fine



    My skills in access are rather rudimentary so if you could break it down that would be amazing!

    Current SQL:

    Code:
    SELECT [Mock Jobs].[Date Invoiced], [Mock Jobs].Product, [Mock Jobs].State, [Mock Jobs].Quantity, [Mock Jobs].[Subtotal Price], [Mock Jobs].[Job Code]
    FROM [Mock Jobs]
    WHERE ((([Mock Jobs].[Date Invoiced]) Between [Date from] And [Date to]) AND (([Mock Jobs].[Job Code])="deck-max"));
    Regards,

    Harry.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Review
    http://datapigtechnologies.com/flash...mtoreport.html
    http://datapigtechnologies.com/flash...tomfilter.html

    The examples don't include date criteria. Try this for your date range:

    Between Nz([Date from],#1/1/1900#) And Nz([Date to],#12/31/2900#)
    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
    Khao is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2012
    Posts
    13
    Quote Originally Posted by June7 View Post
    Review
    http://datapigtechnologies.com/flash...mtoreport.html
    http://datapigtechnologies.com/flash...tomfilter.html

    The examples don't include date criteria. Try this for your date range:

    Between Nz([Date from],#1/1/1900#) And Nz([Date to],#12/31/2900#)
    I am tearing my hear out with this simple task and I am at loss with words as to why it won't work.

    I create the button on a form. Exit out of the wizard and go to event properties. Click the 3 dots and go to code builder. Enter "DoCmd.OpenReport "MainReport", acViewPreview" in between the code. Close it and return to the form. BUT THE REPORT WON'T OPEN! The button does absolutely nothing. Can anyone explain why?

    I've done everything to the tee that the first flash video explains and I have no idea why this damn report won't open... doesn't make sense.

    =[

  4. #4
    Khao is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2012
    Posts
    13
    Might I also add that I can get the report to open if I use the wizard but not if I enter the code manually.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Do you want to provide db for analysis? Follow instructions at bottom of my post.
    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.

  6. #6
    Khao is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2012
    Posts
    13
    Quote Originally Posted by June7 View Post
    Do you want to provide db for analysis? Follow instructions at bottom of my post.
    I'll do it first thing Monday morning, its on the work server. Thanks a lot mate

  7. #7
    Khao is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2012
    Posts
    13
    Should be attached.
    Attached Files Attached Files

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I have your db. Now tell me which report is the issue. What button opens the 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.

  9. #9
    Khao is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2012
    Posts
    13
    Ah I deleted the button and report thinking it would have been easier to start from scratch. The button was located on the form 'Main' and the report pulled information from 'Mock Jobs' - information being Date Invoiced, State, Product, Job Code. The filter needed is in the original post.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Can't analyze your issue if there is nothing to look at.

    Me creating a working button and report does nothing to help you.
    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.

  11. #11
    Khao is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2012
    Posts
    13
    Quote Originally Posted by June7 View Post
    Can't analyze your issue if there is nothing to look at.

    Me creating a working button and report does nothing to help you.

    I've reuploaded it with the issue I originally had. Button just won't work and I haven't been able to see if the report works because the button doesn't.
    Attached Files Attached Files

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    The code is associated with the combobox Click event. There is no code associated with the button.
    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.

  13. #13
    Khao is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2012
    Posts
    13
    Quote Originally Posted by June7 View Post
    The code is associated with the combobox Click event. There is no code associated with the button.
    Excuse my lack of knowledge, but what's the difference? I'm pretty sure I followed exactly what the video did and he does "event -> on click" and his works and mine doesn't.

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Every control has its own events. You click the combobox and its Click event runs. You click the button and nothing happens because there is no code.

    Beginning of the 'Filter Report by Form' tutorial shows setting the command button Click event, not combobox. The combobox isn't even created at that point.
    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.

  15. #15
    Khao is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2012
    Posts
    13
    Quote Originally Posted by June7 View Post
    Every control has its own events. You click the combobox and its Click event runs. You click the button and nothing happens because there is no code.

    Beginning of the 'Filter Report by Form' tutorial shows setting the command button Click event, not combobox. The combobox isn't even created at that point.
    Oh yeah my mistake - I just changed the buttons on click to the code below but i'm still getting nothing when I click it.
    Code:
     
    Private Sub Command67_Click()
    DoCmd.OpenReport "MainReport", acViewReport
    End Sub

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

Similar Threads

  1. General totals question
    By brownk in forum Database Design
    Replies: 8
    Last Post: 05-18-2012, 09:16 AM
  2. General Question
    By notadbadmin in forum Access
    Replies: 3
    Last Post: 08-03-2011, 08:03 PM
  3. General Database Question
    By Lorlai in forum Access
    Replies: 1
    Last Post: 06-23-2011, 05:00 PM
  4. General question about access
    By TEE in forum Access
    Replies: 2
    Last Post: 05-30-2011, 07:50 AM
  5. General question
    By dollygg in forum Access
    Replies: 7
    Last Post: 12-11-2009, 05:13 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