Results 1 to 8 of 8
  1. #1
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127

    User Defined Criteria Not Working.

    Hello,

    I am having a strange error. I have a calculated field and when you run the query the user inputs the threshold to return select results from the calculated field. I have pasted the SQL statement below. When I select a threshold of "30" everything works. When I change that threshold to "100" it does not work and appears to return everything. But if I just put 100 in the criteria and not have it [user defined] then it works as expected. Both of the fields used in the calculation are stored as numbers. I am assuming it's a type error in the calculation but I have no idea.



    SELECT Header.Hole_NUM, [Geochemistry - Samples].SAMP_Length_m, [Geochemistry - Samples].SAMP_Lith_Class, [Geochemistry - Samples].SAMP_Class, [Pb206-p_ppm]/[Pb204-p_ppm] AS [Pb-p206/204]
    FROM Header INNER JOIN ([Geochemistry - Samples] INNER JOIN [Geochemistry - Results] ON ([Geochemistry - Samples].SAMP_Group_Num = [Geochemistry - Results].RESU_Group_Num) AND ([Geochemistry - Samples].SAMP_Num = [Geochemistry - Results].RESU_Sample_Num)) ON Header.Hole_NUM = [Geochemistry - Samples].SAMP_Hole_NUM
    WHERE ((([Geochemistry - Samples].SAMP_Lith_Class)="SDST") AND (([Geochemistry - Samples].SAMP_Class)="COMP") AND (([Pb206-p_ppm]/[Pb204-p_ppm])>[Enter Threshold]))
    ORDER BY Header.Hole_NUM;

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Shot in the dark but try making sure the value is seen as numeric:

    CInt([Enter Threshold])

    or whatever the appropriate data type is.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    This works. Thank you.

    Why would access assume it's a text value?

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Why would access assume it's a text value?
    Two more shots in the dark: you alias a field that applies to the prompt but your prompt is applied to the field name rather than the alias.
    I bet that's a miss.

    Maybe you have defined parameters in the query design and they're conflicting.
    AFAIK, only if that were the case and the declared type was text would it fail without error. Defining a parameter type as text will suppress data type errors - or so I've read.
    Another miss?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by JRodko View Post
    This works. Thank you.
    No problem. I'll get out of the way on the follow up question.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    I have no idea. It's very strange. Thank you for the suggestions, though.

    As long as it works predictably I can live with a little uncertainty.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    As long as it works predictably I can live with a little uncertainty.
    I don't know how to take that.
    At first glance, it's a fantastic oxymoron and I'd love to steal it.

  8. #8
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    It's all yours, Micron.

    It was supposed to be a joke for the record

    I am relieved I at least got a band-aid on this issue.

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

Similar Threads

  1. Replies: 4
    Last Post: 10-16-2017, 09:09 AM
  2. Replies: 19
    Last Post: 06-08-2016, 07:55 AM
  3. mydb - User-defined type not defined
    By adams77 in forum Forms
    Replies: 4
    Last Post: 07-22-2015, 08:43 AM
  4. Replies: 3
    Last Post: 11-12-2013, 04:13 PM
  5. Replies: 6
    Last Post: 09-09-2011, 10:26 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