Results 1 to 5 of 5
  1. #1
    Peace is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    3

    Form Textbox based on a Query Criteria

    Hi everyone.. Can someone help me please? In Access form I have two textboxes (Months From) and (Months To). These two textboxes returns records based on the following query criteria code:

    Between [Forms]![frm_JobPlacementInfo]![MonthFrom TextBox] And [Forms]![frm_JobPlacementInfo]![MonthTo TextBox]



    So, if for instance I put 3 in the first textbox and 9 in the second textbox, then I will get all records containing the numbers between 3 and 9. This works perfect and great.
    My problem is: when I want to keep the two textboxes blank to get all records, the query fails to give all available records because I did not put any number in the textboxes, which means no record matches my criteria.
    I adjusted my code to the following:

    Between [Forms]![frm_JobPlacementInfo]![MonthFrom TextBox] And [Forms]![frm_JobPlacementInfo]![MonthTo TextBox] Or Like [Forms]![frm_JobPlacementInfo]![MonthFrom TextBox] & "*"

    This code solves the previous problem, but I got a new problem. When I put the numbers 3 and 9 in the textboxes, I am not getting only records have numbers between 3 and 9 only but also records have numbers like 13, 30, 33, 19, 90, or 99.
    I got confused and don’t know how to solve this new problem. Is there any suggestion please?
    Thank you so m much.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,620
    Try:

    Between Nz([Forms]![frm_JobPlacementInfo]![MonthFrom TextBox],0) And Nz([Forms]![frm_JobPlacementInfo]![MonthTo TextBox],99999)
    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
    Peace is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    3
    Thank you so much June7. This worked perfect. I have similar textboxes in the same form but to get (Start Date) and (End Date). I have the following code which works well:

    Between [Forms]![frm_JobPlacementInfo]![StartDateTextBox] And [Forms]![frm_JobPlacementInfo]![EndDate TextBox] Or Like [Forms]![frm_JobPlacementInfo]![StartDateTextBox] & "*"

    Do you think it is better to use your code in this situation to avoid any mistakes. For example, should I replace the above code with the follwoing one:

    Between NZ([Forms]![frm_JobPlacementInfo]![StartDateTextBox],#1/1/1900#) And NZ([Forms]![frm_JobPlacementInfo]![EndDate TextBox],#12/31/2020#)

    Do you have any alternative codes/suggestions.. Thank you so much June7, with my best wishes.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,620
    With LIKE and wildcard data is treated as text, even if only numbers are involved.

    Which is why the match on full date works but matching on month only was not satisfactory.

    Use whichever makes you more comfortable.
    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.

  5. #5
    Peace is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    3
    Got it. Thank you June7. I hightly appreciate your answers and feedback.

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

Similar Threads

  1. Query criteria based on Form TextBox value
    By stildawn in forum Queries
    Replies: 8
    Last Post: 11-10-2013, 04:53 PM
  2. Replies: 5
    Last Post: 11-15-2012, 03:33 PM
  3. Replies: 1
    Last Post: 10-20-2012, 12:53 PM
  4. Replies: 1
    Last Post: 12-04-2011, 06:33 PM
  5. Replies: 1
    Last Post: 10-28-2011, 02:46 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