Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2

    Unhappy Answered: Where am I going wrong?

    Hello members.

    I have been struggling with this and can't seem to get it right. It's something I've done in the past in another project and it works fine there.

    I have a club membership DB. In this I have a form where I can view the individual club members details. On this form I would like to display the date on which their membership fee was last paid.

    There are 3 tables involved in this;
    Members Table (records member details)
    Payments Table (records payments received from members)
    Payment Catagory table (list of payments types e.g membership fee)

    When a Payment is received from a member using a Payments Received form, the payment catagory is chosen via a combo box whose data source is in the Payments Catagory table.

    I am using this code on a text box that I want to display the last date on which a membership fee payment made.

    Code:
    =DMax("[Payment Date]","Payments","Member ID='" & [Member ID] & "' AND Payment Catagory = 'Membership Fee'")
    I feel that this should work however my text box displays #Error

    I'm thinking that the problem lies in the way I am querying the Payments table and that this is actually looking back to the Payment Catagory table.

    Any assistance would be greatly appreciated.

  2. Best Answer
    Posted by weejas

    "DMax takes two mandatory parameters and one optional parameter. It can only interrogate one table or query at a time, so trying to restrict the values returned from table [Payments] by referring to values in table [Payment Category] will not work.

    From here you have two ways to move forwards - either create a query that links [Payments] to [Payment Category] and use this in the DMax call, or just plug the ID value for membership fees into your existing one:
    Code:
    =DMax("[Payment Date]", "PaymentDetails", "[Member ID] = " & [Member ID] & " AND [Payment Category Name]* = 'Membership Fee - senior'")
    Code:
    =DMax("[Payment Date]", "Payments", "[Member ID] = " & [Member ID] & " AND [Payment Category] = 1*)
    *Substitute these with the actual column name/ID value."


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is the member id a string/text value OR a numeric value.
    if you thing of a domain function as a means of wrapping a SQL query you need to follow the rules for supplying a value
    in Access / JET
    if its string delimit with ' or "
    if its a date use the ISO (yyyy/mm/dd) OR US (mm/dd/yyyy) format delimited by #
    if its a number do no delimit, but IIRC its advised you use the correct form. ie if the column is on of the integer types use integer, if its one of the decimal types use a decimal value NOT an integer

    however the most likely reason for your problem is down to using reserved symbols in your table/column names. Don't use spaces or other symbols or reserved words in your column / table names. do a google search for reserved words for your version of Access.

    Code:
    =DMax("[Payment Date]","Payments","Member ID='" & [Member ID] & "' AND [Payment Catagory] = 'Membership Fee'")
    the #error# comes about because the SQL parser couldn't resolve the a table or column name..... that could mean you have a typo (spelling mistake), eg catagory as opposed to category, but in this case its almost certainlyh becuase you have a space in a column name.

    Access / JET allows a workaround which is to delimit table and column names with square brackets. EG:-
    Code:
    [my badly chosen table name].[my equally badly chose column name]
    but going forward get to grips with what are reserved words / symbols in Access / JET AND SQL itself

    if you need the prettified spacing and full words for columns for use say in reports then set a value in the columns 'caption' property, but you need to do that before you first create the form or report. I don't think the form/report designers pickup changes to the caption property after the initial layout.

    when naming columns don't use reserved words or symbols, bear in mind the limitiations of column/table names (soem SQL engines place an arbitary limit on length. Use a naming convention there's plenty out there including recommendations from SQL standards themselves, use a consistent approach to abbreviating words.
    ferinstance use tel or telno for telephone,
    use camel case (where words are separated by capital letters [CamelCaseUsage]) OR the preferred SQL standard of underscores [all_lower_case_words])
    call tables by the plural of the items they refer to, eg orders
    don't refer to to the table table name inside that table (IE don't have an order_no inside orders, but do refer to the table where its a foreign key

    A debugging tip for these sort of problems
    break the proboem down into constituent parts, then gradually build up form know working parts.

    eg instead of trying:-
    Code:
    =DMax("[Payment Date]","Payments","Member ID='" & [Member ID] & "' AND Payment Catagory = 'Membership Fee'")
    take out the variability
    Code:
    =DMax("[Payment Date]","Payments")
    ..would prove if the problem lies with the first 2 parameters
    ...then add back the start fo the 3rd parameter
    Code:
    =DMax("[Payment Date]","Payments","Member ID='" & [Member ID] & "'")
    ..would prove if it this part or not
    Code:
    =DMax("[Payment Date]","Payments","Payment Catagory = 'Membership Fee'")
    ..would prove if it this part or not
    ..then once you have proven the components glue it all together
    Code:
    =DMax("[Payment Date]","Payments","Member ID=" & [Member ID] & " AND [Payment Category] = 'Membership Fee'")
    Last edited by healdem; 02-26-16 at 09:33.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    another observation
    by using text to store data you leave your application open to problems with mispellings, typos, possibly capitalisation problems
    so instead to storing 'Membership Fee' in your payment s use a code that is a FK to a payment type.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #4
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Hi Healdem

    Thanks for the in-depth reply. I hear what you are saying about reserved words etc. My beef with this is that you do twice as much typing if you have to fill in every 'caption' property as well.

    In answer to your first question the Member ID is a numeric field (AutoNumber)

    I'm not using text to store the data. The Payment Catagory table contains the different types of catagories. When a payment is received, the catagory is chosen from a combo box. So Membership fee is actually CatagoryID (AutoNumber) 4 in the Payment Catagory table. I hope that makes sense.

    Even though I've just started this project, I really don't want to go through and change all my field names & table nams now. I'm aiming to make it so that when I pass it on the next person has some chance of understanding what is going on.

    So basically I'm still a bit confused.

  6. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Can you post some sample data from table Payments (column names and a few records)?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #6
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Quote Originally Posted by weejas View Post
    Can you post some sample data from table Payments (column names and a few records)?
    Hello Weejas

    This is the Payments Table
    Click image for larger version. 

Name:	Capture1.PNG 
Views:	7 
Size:	4.9 KB 
ID:	16780

    And a couple of records.
    Click image for larger version. 

Name:	Capture2.PNG 
Views:	5 
Size:	7.7 KB 
ID:	16781

  8. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    going forward don't use reserved words and symbols...
    you are where you are.
    Im pretty certain its because you have not used square brackets to delimit the column name [Payment Catagory]

    My beef with this is that you do twice as much typing if you have to fill in every 'caption' property as well.
    its Access rules, either play to their rules or don't play at all.

    I'm not using text to store the data. The Payment Catagory table contains the different types of catagories. When a payment is received, the catagory is chosen from a combo box. So Membership fee is actually CatagoryID (AutoNumber) 4 in the Payment Catagory table.
    ..ok so remove the delimiter from the membership
    ..place a watch or breakpoint on the code (if you dont' know how to read up on Access Debugging).
    prove that what you are writing is valid
    that there is:-
    1) a column called Payment Date in the table or query called Payments
    2) a column called Member ID in the table or query called Payments
    3) a column called Product Catagory in the table or query called Payments

    in the current form/report (whereever you are using this code prove that
    1) there is value for (presumably a control) called Member ID and its a valid value
    2) there is a value in Payment Category that is of the type you have specified ie String/Text

    I hope that makes sense.
    not really

    I fear you are going to say that you have used the lookup wizard or something similar

    lastly if you have used this construct before why not go back to that usage and see what you are doing differently?

    right now my suspicisions on clearing this fault are
    1) spaces in column / control names and those not being delimited.
    2) not getting values of the expected type. I'm highly suspicious of your seeming confusion between the text 'Membership Fee' AND elsewhere you state its a number.
    3) missspellt or wrong control names supplying values in the third parameter (I hardly ever use a default control name in Access, I nearly always rename a control (often with a reverse Hungarian style prefix eg lb_paytypes, tb_amnt_pd [lb:listbox,tb:textbox]) the main reason for this is that it eliminates potential confusion with between the recordset and the control. the prefix helps others immediately understand what the control is without having to switch between the form designer and the code module

    In answer to your first question the Member ID is a numeric field (AutoNumber)
    however on your sample data the member ID is a name. so there is a total lack of consistency here

    have you explicitly defined relationships between tables
    Last edited by healdem; 02-26-16 at 10:10.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yup, as feared/expected you've used a lookup wizard
    bad news
    go back to fundamentals of table design
    that table to identify payment types, use the PK of that table (could be numeric or text/string,. but NOT varchar) as the FK in the payments table
    design a table and do the spadework yourself


    bear in mind that even if your code worked
    'Membership Fees' in your current will never ever match to 'Membership Fees Seniors'

    and I fervently hipe you haven't used the lookup wizard to defien the member id.... that really, really will be a frightmare
    I'd rather be riding on the Tiger 800 or the Norton

  10. #9
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Hi Healdem

    Taking the step-by-step approach I get as far as

    Code:
    =DMax("[Payment Date]","Payments","Member ID='" & [Member ID] & "'")
    Whereupon I get the error. So the problem is in the syntax of the Member ID. This is a numeric (AutoNumber) field. I have tried various iterations of syntax on that and the best I can get is a blank text box

  11. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    NO based on your supplied data in post #6 member id IN payments is string/text using the lookup wizard its easy to screw this up. don't use the lookup wizard.

    put a watch/breakpoint in the code and find out what the actual values are when the code runs.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #11
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Hi Healdem

    Your replies are coming faster than I can absorb them.

    Looks like I'll go back to square 1 at least where this part of the DB is concerned. By the way if PK is Primary Key, what is FK?

    "bear in mind that even if your code worked
    'Membership Fees' in your current will never ever match to 'Membership Fees Seniors'" I was lazy with my typing.

  13. #12
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Try:
    Code:
    =DMax("[Payment Date]", "Payments", "[Member ID] = " & [Member ID])
    You will also save yourself a lot of trouble if you take healdem's advice and avoid spaces and reserved words in table and column names.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  14. #13
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Hi Weejas

    That works well. Now I need to match the criteria so that Payment Catagory from Payments = "Membership Fee"


    I've tried this but end up with a #Name? error


    Code:
    =DMax("[Payment Date]","Payments","[Member ID] = " & [Member ID]) And ([Payment Catagory],"Payments"="Membership Fee - senior")

  15. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    look at the two images you posted
    one has the design of table payments, one has sample data
    the two don't match
    the only time ive seen this is if you are using the lookup wizard which is fine and dandy but it fails spectacularly. the lookup wizard is a lazy tool for a developer.
    don't use a lookup wizard, use properly designed tables and relationships
    I'd rather be riding on the Tiger 800 or the Norton

  16. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by sheusz View Post
    Hi Weejas

    That works well. Now I need to match the criteria so that Payment Catagory from Payments = "Membership Fee"


    I've tried this but end up with a #Name? error


    Code:
    =DMax("[Payment Date]","Payments","[Member ID] = " & [Member ID]) And ([Payment Catagory],"Payments"="Membership Fee - senior")
    are you surprised at that. I am surprised you only get error


    how many parameters can dmax take
    how many have you supplied
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •