Results 1 to 6 of 6
  1. #1
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167

    lookup function

    HI i have a continuous form that has a field [amt] that lists a bunch of invoice values


    I"m want the user to pick a value from a combo box [combo49] and have the form find the record with the matching [amt] value.

    I've created an OPEN FORM macro and set the AFTERUPDATE property of the combo box to trigger the macro

    the where condition is set as follows: Forms![fOutstandingInvoices]![amt] Like "*" & [Forms]![fOutstandingInvoices]![combo49] & "*"

    this doesn't find the records.

    i've adjusted it to : [amt] Like "*" & [Forms]![fOutstandingInvoices]![combo49] & "*"

    and this doesn't produce the results either.

    I have the [amt] and [combo49] properties both set to currency with 2 decimal places thinking this may be the problem.

    Can anyone suggest what i'm doing wrong?

    thanks,
    Steve

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Lots of things to consider here.
    The form is continuous and the lookup combo part of the form records (i.e. you see it more than once?) or it is only in the header?
    What is the row source for the combo?
    AFAIK, LIKE is for text and does not play well with numbers. You should be using other comparison operators such as >=, =, etc. You cannot get 45.00 because it looks "like" 45.25
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    thanks Micron,

    The form is continuous and the lookup combo part of the form records (i.e. you see it more than once?) or it is only in the header? = ONLY IN THE HEADER

    What is the row source for the combo? =
    SELECT [qInvAmt].[amt&gst] FROM qInvAmt ORDER BY [amt&gst];
    [qInvAmt] is a query i created that has a filed that is combining the "charge" and the "gst" in a field called [amt&gst]

    AFAIK, LIKE is for text and does not play well with numbers. You should be using other comparison operators such as >=, =, etc. You cannot get 45.00 because it looks "like" 45.25 = Can you suggest how i would do this?

    thanks,
    Steve


  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm not sure of the relationship between your query and the form records, but I see that the combo has no related record ID field. Regardless of whether or not the same query populates as the combo, you're trying to find one or more records based on an amount, which seems very odd to me. Which record do you want if 2 or more match your expression? It is possible that you will only retrieve 1 and there could be more.

    Let's assume for a sec that there can only be 1 instance of a particular amount. Notwithstanding the peculiarity of searching on amounts, with numbers, you can only find either:
    - exact =
    - greater than >
    - less than <
    or between >= And <= (inclusive, no = for exclusive)
    That leaves you with needing the record ID that corresponds to your combo amounts, and you would search on the ID, not by the amount. In such a case, you'd add the ID field to the combo query. It still leaves me thinking that you're going about this the wrong way, but even if you were not, it would be simpler to apply a filter to the form records by designing that in your macro.

    Not sure if any of that will help.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    i've adjusted it to : [amt] Like "*" & [Forms]![fOutstandingInvoices]![combo49] & "*"
    if this was in vba 3 things, a) you need to create a string b) if using like you need to apply quotation marks and c) you need to apply it to the form filter - something like


    me.filter="[amt] Like '*" & [combo49] & "*'"
    me.filteron=true

    however you say an openform macro (which i suspect is wrong because the form is already open), which if that is the case may be completely different - I don't use them because they have limited functionality and difficult to debug.

    other comments - what is the rowsource to your combo? you normally have a specific value selected which by implication of what you are filtering on is numeric so as micron says, you would use = and drop the * and quotes


    me.filter="[amt] =" & [combo49]

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Ajax View Post
    other comments - what is the rowsource to your combo?
    was answered:
    What is the row source for the combo? = SELECT [qInvAmt].[amt&gst] FROM qInvAmt ORDER BY [amt&gst];
    I forgot to add that amt&gst is a bad name for anything. Best not to use special characters (save for perhaps underscore) See
    http://access.mvps.org/access/general/gen0012.htm

    https://www.access-programmers.co.uk...ntions.225837/

    and don't use reserved words (not that I notice any, but which you likely never would if using one of the above conventions)
    http://allenbrowne.com/AppIssueBadWord.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 2
    Last Post: 03-08-2019, 05:25 PM
  2. VBA Lookup Function to Access Returning #value
    By troygeri in forum Queries
    Replies: 2
    Last Post: 05-26-2013, 08:50 PM
  3. Lookup function????
    By phd4212 in forum Forms
    Replies: 1
    Last Post: 05-10-2012, 11:34 AM
  4. Replace Function with Table Lookup
    By smurof in forum Access
    Replies: 1
    Last Post: 07-29-2011, 07:52 PM
  5. Lookup function
    By allykid in forum Programming
    Replies: 2
    Last Post: 11-09-2010, 09:48 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