Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88

    Dates, Forms, and Queries

    Hi Everyone,

    I've had some success building my multi-criteria selecting interface for my Access users.



    I've been repeating the same process over and over, but I've finally gotten to my criteria that are a little bit tricky.

    Check out my process and my challenges.

    Process
    I have an "inputTable" linked to a form where the user selects limited information from the "knowledgeTable"
    I then have a command button calling macros to run a premade query linking the "inputTable" with the "knowledgeTable"
    This creates a one to many relationship where I can choose to only have information selected where the "inputTable" value is equal to its corresponding column in the "knowledgeTable"

    Challenges:
    1) Interestingly, with the command button, I can no longer enter multiple information into my inputTables and eventually I will need to be able to.
    I'm a bit stuck on that.

    2) Also, I need to allow the users to input a date qualification. Meaning select no data with dates older than xx/mm/yyyy
    I'm not sure how to do this either.

    Thanks for any help with this

    Let me know if it's not clear or if you have any questions.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,549
    You will need a txtDate box to enter the date, but then 2 queries...1 w a date, 1 without.

    Code:
    if isnull(txtDate) then
      docmd.openquery "qsGetDataNoDate"
    else
      docmd.openquery "qsGetData1Date"
    end if
    Re: Challenge #1: is the table keyed so you cant enter >1 record / key?

  3. #3
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Date:
    So can I open a query and type that in the SQL section? Updating the names (txtDate) would equal the column in my table.
    The txtDate box can that be on a form or is it supposed to be a message box? I don't know how to do the pop-up input message boxes yet, although that could be really cool.

    Multiple input values:
    I don't believe so. I mean before I added the button that runs my select query based off the new criteria inputted by a combo box, forcing the users to select what they want, into the table the form is linked to, I could add multiple criteria.

    I have 2 rows in my table, but the first one changes over and over again.

  4. #4
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    It is not clear to me how you use your input table. It is also not clear how your form was build and why can't you no longer enter multiple information into my inputTables.

    Can you provide example how you use the form and input table? and what you want the form and input tables to do?

    It also would be easier if you provide the Access database for further analysis (like what the form looks like and what the design of your input tables looks like, etc).

  5. #5
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    I can send you my database if you want I'm still constructing it, so there's no confidential information, nor is it that heavy. If you could just inbox me with an email address you'd trust me to have.

    My knowledge of VBA is rather limited, so what I'm doing might not be the best way of doing it.

    Basically, I have a knowledge database that has different levels of segmentation.
    I need the users to be able to narrow the scope of the data in an easy way that will also prevent them from ruining the database.

    So, I've been using forms
    Form_Select_Level1 asks for the highest level of segmentation
    (The form was created by having the Select_Level1_tbl selected and creating a form. I changed the type box to a combo/list box that will only let the users select criteria at Level1 from the knowledge database.)
    The user selects there information (before I added the command button, they'd press "return" to get their information inputted into the Select_Level1_tbl.
    Then I have a query connecting the Select_Level1_tbl with the knowledge query (qry1_PartPricingAggregation)
    My button on the form, hopefully reruns the query, closes the form, and then opens the next level of information that needs to be selected...you guessed it...Level 2 lol

    I want only the Level 2s linked to Level 1 to be displayed, so I have to connect this form to the query I just ran.
    I repeat those steps for Level 2 and Level 3.

    Then I need the users to be able to select the oldest date they will accept, different brands, and different markets.
    *The way I've been doing, I'm not sure how to ask for "select dates > than x"
    **There can be multiple brands and I need to have them inserted into my table, as well as with the markets.

  6. #6
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    You can also use this method to attach to this post or I will send you a private IM for you to send to.

    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window

  7. #7
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    I think I'd just prefer to email it to you. If that would be ok

  8. #8
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Quote Originally Posted by ranman256 View Post
    You will need a txtDate box to enter the date, but then 2 queries...1 w a date, 1 without.
    Code:
    if isnull(txtDate) then
      docmd.openquery "qsGetDataNoDate"
    else
      docmd.openquery "qsGetData1Date"
    end if
    I was looking at this a little bit more. Does that mean that if there is no date it will return all the data? and then if there is a date, it will return all the data with dates greater than the inputted date?

  9. #9
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,549
    Right, no date = a separate query. You cant ask date between null and null.
    1 qry with dates too.

  10. #10
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    You can check the control value for being null as an or condition next to the between condition and you will need only one query.

  11. #11
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Well, I won't have any null dates.
    But I don't understand how to write the querry that would return databaseDates > inputMessageBoxDate
    When Run, query gives dates greater than inputted date

  12. #12
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    Quote Originally Posted by Ace2014 View Post
    Well, I won't have any null dates.
    So the user will always give a date as search criteria?

  13. #13
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,549
    under the date field,in the query criteria, put in brackets [Enter Date]

  14. #14
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Quote Originally Posted by hapm View Post
    So the user will always give a date as search criteria?
    Oh, no, the user can leave the date filter blank. Ok, I see what you mean.

    But you wrote commands, I still have to construct those queries right?
    I don't know how to construct a query adding the inputted dated from the form into the query
    I can create a query saying if isNull, return all, but then I have to assign a variable to my input box. I'm not very good at coding yet.
    So for example, if my combo box's name is "inputDate" the code might look something like: ? ? ? help?

    Function queryDate ()
    Dim Date string (format:mmm/YYYY)
    If inputDate isNull;
    Query = return all values;
    false
    Query = dates > Date
    End Sub

    Then there would be a command attached to the inputDateBox and it would run this macro

    CommandX_Update ()
    Call queryDate
    End Sub

  15. #15
    Ace2014 is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    88
    Quote Originally Posted by ranman256 View Post
    under the date field,in the query criteria, put in brackets [Enter Date]
    I don't have a query attached to it yet.

    I have like 5 filters and the date I need the user to choose a date and then it filters the attached query on values greater than inputted date.
    It's not a combo box, so no query is attached.
    Do I attach a query to Event: After Update, but then I'd need to code to create the query.

    Will the form continue to update the other filters as well if I make a special query for the inputDate
    Click image for larger version. 

Name:	selectscope.JPG 
Views:	7 
Size:	41.6 KB 
ID:	16839

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

Similar Threads

  1. Replies: 4
    Last Post: 03-15-2013, 04:00 AM
  2. Subtracting Dates in Queries
    By seanpbent in forum Access
    Replies: 1
    Last Post: 02-01-2013, 04:28 PM
  3. Dates and Queries (its hard to describe!)
    By Lauren1989 in forum Queries
    Replies: 6
    Last Post: 03-31-2011, 01:45 PM
  4. Replies: 4
    Last Post: 04-01-2009, 08:49 PM
  5. Handling dates in queries
    By mrk68 in forum Access
    Replies: 4
    Last Post: 03-23-2009, 06:35 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