Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2016
    Posts
    3

    Unanswered: Getting DLookUp to work in Access

    Help! I'm trying to set up a database for my running club and need to be able to do the equivalent of an Excel VLookUp.

    On one table I have two fields - time and points, for the points you score running races in specific time ranges.

    eg

    Table "5k" with fields "Time" and "Points"

    Time Points

    23:00 62
    23:06 61
    23:12 60
    etc down to
    29:25 0

    Table "Race_Score" includes field "Race_Time"

    What I want to include in an extract is a range, so if you finish the race in 23:10 your score is shown as 60 points (the next Time in the list greater than 23:10). I've tried a gazillion ways using DLookUp but cannot determine what I'm doing wrong.

    The criteria I've given the extract is DLookUp("Points","5k","[Race_Score]![Race_Time]>=[5k]![Time]")

    I've tried with and without square brackets and with and without quotes but nothing seems to work. VLookUp is easy to use in Excel and I have a spreadsheet which does something similar no problem.

    If I don't include any criteria and type in every time possible then the lookup works (I've tried it with a handful of entries for times/points), but that would mean typing in thousands of entries as there are 8 tables for races for distances from 5k to marathon and times from 23:00 to 29:25 for 5k and 3:14:01 to 4:37:55 for marathon. This would make it a much bigger database, and mean a lot of hours at the keyboard for me. Although if that's the way to go I'll have to do it.

    Thanks in advance :-)

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Dlookup has 3 patameters
    The first us the column whose value you want to return
    Next the table or query
    Lastly the filter,essentially its a where clause without using the word where.
    = dlookup ("points", "race_score", "race_time ........")
    The race_time needs defining
    Personally i think youd be better off using dmax or a sql statement
    The dmax would be something like
    = dmax ("points", "race_score", "race_time <= " & cdate (5k!time))
    Assuming race_time is a datetime column
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2016
    Posts
    3

    Thanks

    Thanks, I tried that, and several other suggestions I found in google, but no luck so I've just typed in all the entries I need. Or at least, I imported them from Excel.

    Now comes the next bit of fun - working out what I need the database to do

  4. #4
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    139
    "Time" is a reserved word in MS Access -- using it will be trouble. Try changing the field name.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what was the dmax expression you tried
    what value did it return
    did you handle the (possible) null value correctly, have a look at the NZ function

    is 5k!time a datetime value...

    you are making life possibly harder for yourself by using a reserved word (time) that can cause strange errors

    rereading your original post you mention that you have
    8 tables for races for distances from 5k to marathon
    I do hope that you arent' creating a new points table for each type of race
    you can design out that by having two tables
    one racetype eg:
    Type text 5 (PK)
    Description
    eg
    5K | 5,000m
    m | marathon
    etc...

    and the points table
    racetype text(5)
    TimeCutoff datetime
    NoPoints integer
    use a composite PK of ractettype and timecutoff as your PK, racetype is a FK to Race.Type

    the advantage of this post is that irrespective of how many race types you need to track in the future there ar eno more table design changes, all it needs i s to add the data and the application keeps working.
    the reason for using a text/string instead of say an autonumber is it makes the data easy to read if you need to look at the tables. good selection of the values is self documenting and may not require the description fromt he race table table to identify
    you can also play tricks by settign a sort sequence in the racetype table. whether thats based on distance or some other form of grouping. but don't get over clever if tyhere are rexistign tersm (eg short, middle , distance usde 'em in another table.

    your dmax then becomes something like

    = dmax ("points", "race_score", "raceType ='5k' and race_time <= " & cdate (5k!time))
    note string/text literals are delimited with ' or ", IE '5k' NOT 5k
    date literal should be in ISO or US format an d delimted with #

    the SQL becomes something like
    Code:
    select top 1 point from points where racetype = '5k' and point.cutoff <= race_time
    order by racetype DESC, race_time DESC
    you will need to tweak this as Im not too certain where you are getting your race time from

    quoting
    DLookUp("Points","5k","[Race_Score]![Race_Time]>=[5k]![Time]")
    without providing the DDL (table definition) and actual values for your parameters doesn't help matters for anyone so minded to help
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jul 2016
    Posts
    3
    In the 5k table I have fields "Race_Time" and "Points" containing times and numbers..

    Race_Time Points
    00:14:30 144
    00:14:36 143
    00:14:42 142
    00:14:49 141 (yes, it isn't a completely linear progression)
    .
    .
    .
    . (all the way down to)
    00:29:19 1
    00:29:25 0

    So if you complete a 5k race in 14:46 you will score 141 points (the next time after 14:46 being 14:49) Similar tables exist for other distances 5m, 10k, 10m etc.

    I have another table Race_Score with fields "Athlete", "Race" and "Time"

    Athlete Race Time
    John Smith Pendle Hill 00:14:46
    John Smith Catforth Canter 00:17:50

    All other lookups in my query are working fine, but what I want to do is lookup the points scored using "Time".

    Using Access I'm in Design view for a query and I'm typing into the Criteria field. When I use DLookUp("Points","5k","[Race_Score]![Time]>=[5k]!Race_Time]") it errors saying I have a Syntax error, even though that's what you say above, that's what it says in Access help and that's what I've found in google and elsewhere on this forum.

    If I start off by typing DLookUp("Points","5k", everything is fine. If I don't type the double quotes or square brackets, as soon as I type Ra I get an option to choose from a list, and one of those is Race_Score. When I do [Race_Score] appears in the field with no double quotes. I then type the exclamation mark. Then as soon as I type Ti I'm given several options, including Time. I choose this and [Time] is entered. I type in >= and then 5k and choose from the option, ! and then Ra and choose from the options, leaving me with DLookUp("Points","5k",[Race_Score]![Time]>=[5k]![Race_Time]), which is the same as above without the double quotes, but when I run it nothing is extracted. If I remove the DLookUp I only get results where the time matches a time entry exactly, hence I've entered all the times between, and this is working ok.

    I tried the above and then went back and inserted the double quotes and the same result.

    I did try changing "Time" to Race_Time and Race_Time in the other table to 5k_Time but the results were identical - syntax error or nothing extracted.

    I see your point about having a table with all distances in it, eg

    Race_Distance Race_Time Race_Points
    5k 00:14:30 144
    5k 00:14:36 143
    5k 00:14:42 142
    .
    .
    .
    .
    5m 00:23:20 148
    5m 00:23:30 147
    5m 00:23:40 146
    .
    .
    .
    .
    10k 00:29:00 150
    10k 00:29:12 149

    and so on, and if I can work out how to get DLookUp to work that would be easier, but unless and until I can get it to work like VLookUp in Excel....

    Thanks

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so if you are using this in a query of using a domain function use a join. The problem with dLookup is it returns the first row that matches the criteria and thats not waht you want, you want the highest value, hence why if you must use a domain function use dmax NOT dlookup

    however this is only tentative becuase you still haven't said what data types you are suing to define the columns, and it makes a huge difference. if you have sued a datetime column then its all OK, if you have used string/text then you have a problem

    I don't use the Query designer so can't really help you on that.

    in the absence of someoneelse steeping up to the plate I'd suggest posting your db here
    delete anything sensitive or obfuscate it
    if there are lots of forms, reports and other stuff delete them as well
    compact and repair the db
    compress it into a zip file and post it as an attachment
    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
  •