Results 1 to 4 of 4
  1. #1
    marshallracer is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2018
    Posts
    2

    Issue with <= in query based on form field

    I am a bit of a neophyte in access programming so be gentle please. I am adding a simple form to my QC database to pick records fulfilling several criteria. It is basically a query for those who cannot make a query, populating an unbound listbox based on values entered in the form from a query referencing the form values (I know there are likely quicker and easier ways to accomplish this but they are beyond my skills.)

    One example criteria field in the query is "<=[Forms]![RCForm]![VOLMAXBOX]" The idea is to change the railcars in the listbox depending on the values entered in the form to ship to customer specifications.



    The numerical field [VOL] contains mostly 0's with a few values under 100 here and there in multiples of 4. The strange thing that is happening is when I have the field set to 4 I get 80 records returned. 3 and 2 return 80 records. When I reduce the field to 1 or 0 the list box is empty (again, all values are multiples of 4 so 80 should be returned.) I can design a query and return the 80 records using <=1 or 0 in the criteria field.

    Why would a listbox occasionally fail to return values that a direct query returns?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    So VOLMAXBOX is BOUND to [Vol] field?

    The expression you posted is incomplete - WHAT is <=[Forms]![RCForm]![VOLMAXBOX]? The [VOL] field?

    Post the full SQL statement of the listbox RowSource query. What table is the form bound to?

    If 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.

  3. #3
    marshallracer is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2018
    Posts
    2
    Thanks for the reply. [VOL] is a field in the Table: DATATABLE which contains our railcar ID's and quality control data as fields. [VOLMAXBOX] is a field in the Form: RCFORM that chooses railcars in DATATABLE meeting customer criteria stored in Table: CUSTSPECS

    Here is the SQL statement for the listbox:
    SELECT [RCQuery].[RCNumber], [RCQuery].[LotID], [RCQuery].[VOL], FROM [RCQuery];

    The query SQL:
    SELECT [DATATABLE].[RCNumber], [DATATABLE].[LotID], [DATATABLE].[VOL]
    FROM [DATATABLE]
    WHERE ((([DATATABLE].Vol)<=[Forms]![RCForm]![VOLMAXBOX]));


    There are many other quality parameters in the query but I deleted them from this for clarity. I have data in the DATATABLE's [VOL] field of 0-100 with most entries being zero. We have some customers that require the value to be under 4, others can take up to 50. I am designing a form that allows me to pick a customer from a combobox populated from my CUSTSPECS table, populate the form with the customer specifications (via the OnChange property of the combobox), and return the cars in the DATATABLE that meet the specifications to a listbox.

    The issue I am having is when I enter 2, 3, or 4 in [VOLMAXBOX] everything works as expected: only cars with [VOL] field in [DATATABLE] of (2, 3, 4) or less are returned in the listbox. If I enter 0 or 1 in [VOLMAXBOX] no cars are returned to the listbox even though I expect around 80 results. Why would 0 and 1 behave differently?

    As I mentioned there are other fields as well and using 0 and 1 behaves just fine for these fields. I have checked that my DATATABLE.VOL and RCFORM.VOLMAXBOX are number fields.

    Thanks again for the reply and help!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    However, really not enough info. At this point I would be making wild guesses and don't have even one because I don't see anything wrong with what you have provided. Would have to examine data and form design.
    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.

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

Similar Threads

  1. Replies: 6
    Last Post: 12-03-2017, 02:23 PM
  2. How to have a form field based on a query?
    By rtcary in forum Access
    Replies: 8
    Last Post: 04-29-2015, 03:34 PM
  3. Query to look up value based on form field
    By c.stewart28 in forum Access
    Replies: 1
    Last Post: 04-24-2015, 09:19 AM
  4. Replies: 22
    Last Post: 05-21-2013, 07:54 PM
  5. Replies: 3
    Last Post: 03-14-2012, 10:31 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