Results 1 to 9 of 9
  1. #1
    jaash2000 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    11

    Question Criteria not working

    I have a query that when I run and manually put in the criteria (example <14423), it outputs the correct #. When I tell it to ASK me for the # and I put in 14423 on a pop up screen, it's the completely wrong output (and includes negative numbers). I have tried to tell it the field is a number or currency but it doesn't seem to matter. I am subtract this year's # from prior year's # and have the difference here. Any help is greatly appreciated.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    jaash2000 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    11
    sorry i'm obviously new to this. can you see these pictures?Click image for larger version. 

Name:	gdcnotworking.jpg 
Views:	14 
Size:	172.6 KB 
ID:	25456Click image for larger version. 

Name:	gdcworking.jpg 
Views:	14 
Size:	172.4 KB 
ID:	25457Click image for larger version. 

Name:	gdcworking.jpg 
Views:	14 
Size:	172.4 KB 
ID:	25457

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You might consider the DateDiff function in Access.
    Other Date related functions are along the right side of the page.

    It is easier for readers if you tell/describe WHAT you are trying to do in plain, simple English. Rather than showing some code that isn't working as you intended.

    Good luck.

  5. #5
    jaash2000 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    11
    it's not a date. it's the difference between their sales goals from last year and this year. I want it to filter out anyone who is below a minimum dollar difference of $14,423.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm surprised Access accepts >250 as a field name.
    You might try [>250].

    If you remove the calculation, do you get the expect values in fields

    YTD GDC and prior YTD GDC??

    I mocked up a sample:
    My Table and values (currency)

    Id >250 YTD GDC Prior YTD GDC
    1 257 $35,456.00 $23,244.00
    2 300 $36,500.00 $25,899.00
    3 432 $38,755.00 $29,578.00

    Click image for larger version. 

Name:	myQuery.jpg 
Views:	12 
Size:	32.0 KB 
ID:	25458

    The result of query
    Id YTD GDC Prior YTD GDC Diff >250
    1 $35,456.00 $23,244.00 $12,212.00 257
    2 $36,500.00 $25,899.00 $10,601.00 300

  7. #7
    jaash2000 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    11
    some one else made this database. the >250 is actually field that he uses to filter and has nothing to do with this issue.
    The issues is he 5th field labeled "YTD GDC Growth: [ytd gdc]-[Prior ytd gdc]" is the one i am looking at. It calculates correctly and subtracts the # and displays the correct answer. However, I want values that are > a certain amount. When I put in that amount, under criteria manually [i.e., .14423], it filters out the correct records. when I try to make it request an amount <[enter min value], it does NOT.

  8. #8
    jaash2000 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    11
    so in looking at your example, instead of making criteria <10000 for diff, I want it to ask me, I input the # and it gives me the correct values

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    My criteria is >10000 {greater than 10,000}

    You have < and > confused???

    I modified the query as per jpg. The value I entered was 10120

    Click image for larger version. 

Name:	sttDesign.jpg 
Views:	5 
Size:	35.7 KB 
ID:	25518

    Click image for larger version. 

Name:	sttResult.jpg 
Views:	5 
Size:	12.8 KB 
ID:	25519

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

Similar Threads

  1. Iif not working as a part of criteria
    By crobaseball in forum Access
    Replies: 8
    Last Post: 05-26-2015, 07:32 PM
  2. Criteria not working
    By MFS in forum Queries
    Replies: 7
    Last Post: 03-27-2014, 07:36 AM
  3. Criteria in query is not working
    By sshel55 in forum Queries
    Replies: 7
    Last Post: 10-17-2013, 04:50 PM
  4. query criteria not working
    By George in forum Access
    Replies: 3
    Last Post: 10-30-2012, 12:14 PM
  5. date criteria is not working
    By kwooten in forum Queries
    Replies: 6
    Last Post: 08-12-2011, 12:31 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