Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2010
    Posts
    10

    Unanswered: Lookup a value based on a month in a date field

    Hi,

    I think this is an old nut, but i have a form whereby a user selects a date from a picker.

    what i need is for the month of that date to be looked up on a table which then can return the amount in that table.

    here is the fields and tables etc.

    Field name (for the price to be returned): venuehire
    Datefield on form (which user selects): dateofevent (#dd/mm/yyyy#)
    Table to be looked up: tblvenuehire
    fields in tblvenuehire: Month and Amount

    I have tried dlookup to acertain this figure but i can not get it to work.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If Month is stored as a number into the table tblvenuehire (1 = January, 2 = February, etc.), it should be:
    Code:
    Me.venuehire.Value = DLookup("Amount", "tblvenuehire", "Month = " & Month(Me.dateofevent.Value))
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or set the criteria in your dlookup to be
    month(mydatecolumn) = month(dateofevent) AND year(mydatecolumn) = year(dateofevent)

    personally I think you should use a date picker or some other form of constraint to make certain the user puts in a valid date, but maybe you are already doing that elsewhere
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2010
    Posts
    10

    lookup a value etc

    Yeah i sorted it.
    I converted the datepicker that the user enters, to a number and performed a dlookup on the number and price.

    Me.eventhire = DLookup("[amount]", "tblvenuehire", "[month] = " & Format([dateofevent], "m"))
    Form_frmeventinfo.Refresh

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    check your design requirement to see if you need to add the year element as well, as next year you will get results from Jan2011 and 2012 and so on......
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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