DLookup issue for previous row only data

[carnealse]

Simply put, I am trying to pull the data from the previous record / row only in a continuous form for a dive log to keep tabs on total bottom times. All I am doing is pulling the "new total bottom time" from the previous record in to the "old total bottom time" of the current record in order to add the "old total bottom time" to the "bottom time this dive" for the current record to have a number to calculate the "new total bottom time."
This is what I put in the control source;
=DLookUp([NEW TOTAL BOTTOM TIME],[divelog],[ID]=[Forms]![DIVE LOG]![ID]-1)

This is what is shown on the form; #name

What do you see here that I am doing wrong?

 

[CJ_London]

simple answer is you are missing quotation marks

Try

=DLookUp("[NEW TOTAL BOTTOM TIME]","[divelog]","[ID]=" & [Forms]![DIVE LOG]![ID]-1)

However if you control for this lookup is in the DiveLog form then instead of using

[Forms]![DIVE LOG]![ID]

us

me.ID

The more complex answer is that you are doing it wrong.

a) You should not be saving calculated values in your table but simply adding up the bottom times from all the previous records. Why? Well what if you change a previous record bottom time? you'll have a massive task to correct the subsequent records

b) you appear to be relying on there being a previous ID (presumably based on a sequential autonumber) - what if a record is entered and then deleted? the ID number is not reused.

What you should be doing is is adding up the total bottom times for all records which precede the dive in question - I suggest this would be based on date and time - so use something like

=dsum("[Bottom Time]","DiveLog","DiveDateTime<" & [Forms]![DIVE LOG]![DiveDateTime])

 

[carnealse]

I get what you are saying, I do.
The nice thing is that bottom time is pulled from the dive computer at the time of dive long entry. Using rental gear, its not much of a reality that a diver will go back, track down the dive computer they had two weeks or two months ago and search for a specific dive to verify the bottom time. Those dive computers get cleared out anyway for new customers. Slim to none a bottom time will ever change.
I get the summing of all dives prior to that entry date. I think I can get that to work the way I want assuming I can take the sum up to that record and add that to the current dive's bottom time entry to display the new total bottom time. All the records would pull from that new total bottom time for the overall sum to display in each record for all the preceding dates.
Thanks.

 

[CJ_London]

I'm a diver myself and know what you mean!
Just come back from Sharm...

 

[carnealse]

Sharm...as in Sharm el Sheikh, red sea type diving?!
I dove yesterday in the Gulf Of Mexico off of Pensacola Florida but that is nothing like diving over there. I'd give a lot to dive a place like that!
So yeah, I will end up trying out what you suggested later today. If I run into troubles I will seek further guidance. Right now, Battlefield 4 is calling my name haha.

 

[CJ_London]

Twas Sharm el Sheikh indeed.

Plenty of interesting fish and have dived on a WW2 wreck (The Thistlegorm, sunk by German aircraft in 1941) which still has ambulances and other vehicles and ordinance etc aboard.

Deepest part of wreck is around 30m rising to 15m. A really fascinating dive!

Beats Battlefield4

 

[carnealse]

Very nice. A round trip ticket from here would cost me $1,250 about. Not including hotel, charter, and gear.
Yet I am still falling short of making this sum work for me.
I think I am over thinking it. There has to be a way to start from zero and automate the sum of the last dive's bottom time with the over all total for a new total. The reference is becoming the issue. I might have to do it on the back end a different way and then only show certain data on the form. Gah...yes I am over thinking this to the point I cannot see the easy solution.:banghead:
I need to go dive.

 

[CJ_London]

Explain what you have - I would expect a table with something like the following fields?

ID autonumber
DiverID long
ComputerID text? Long?
DiveStartDateTime date
DiveTime long? integer? double?
MaxDiveDepth long
BottomTime long? integer? double?

 

[carnealse]

There are 39 fields but the ones specifically to this question are;

ID autonumber
OLD BOTTOM TIME number
BOTTOM TIME THIS DIVE number
NEW TOTAL BOTTOM TIME number

I attached the rough work in progress so you can see what I am working with first hand.

View attachment Dive Log.accdb

 

[CJ_London]

As I have said before, you shouldn't have calculated fields.

I'm getting a bit confused - this form is your dive log but this

The nice thing is that bottom time is pulled from the dive computer at the time of dive long entry. Using rental gear, its not much of a reality that a diver will go back, track down the dive computer they had two weeks or two months ago and search for a specific dive to verify the bottom time. Those dive computers get cleared out anyway for new customers. Slim to none a bottom time will ever change.

Implies you are a dive shop!

Which is it? at the moment there is nothing in the table to identify a diver.

Sorry to complain, but you really have a messy design making it difficult to read and understand

• table field names are all in capitals with spaces and non alphanumeric characters
• form names not consistent with field names e.g. DIVE DATE/divedate, BOTTOM TIME THIS DIVE/numbottomtimethisdive

General comments:

• your dive date should be date/time (i.e. 05/17/2014 10:35:00) - otherwise there is no way to differentiate two or more dives on the same day

But to answer your question, remove the OLD BOTTOM TIME and NEW TOTAL BOTTOM TIME field from your table and in the

numoldbottomtime control put the following in the control source

=DSum("[BOTTOM TIME THIS DIVE]","divelog","[DIVE DATE]<#" & [divedate] & "#")

and in the numnewbottomtime control put the following in the control source

=[numoldbottomtime]+[numbottomtimethisdive]

 

[carnealse]

Yeah I don't abide by all the etiquette of access because it makes sense to me how it is laid out and that is all I am concerned with. I know others have their ways and mine doesn't work for them, that's cool.
I am not a dive shop, I just know how it works because my buddy works in one his Dad owns. This is for me and for any of my friends who want it. Nothing implied, that was just your spin on the info
The date time, well they are entered in the order they are dove which is on the computer. That is why I didn't worry about the time. Not saying it isn't a good idea, just that is why I didn't do that initially. I will her a try.

 

[vbaInet]

May I just mention a couple of things:

1. In order for you to get the previous record your on your form it should be bound to an ordered query (not a table).
2. Create a query based on the one in step 1 for use in the DLookup function and sort in DESC order. You only need the relevant fields in here, perhaps ID and [NEW TOTAL BOTTOM TIME] fields in this query.
3. The new query from step 2 should return only 1 record (by using SELECT TOP 1 ...). This just helps your DLookup run faster.
4. For the DLookup criteria look for an ID less than the one in the query.

 

[CJ_London]

I don't abide by all the etiquette of access

Up to you

look for an ID less than the one in the query

Providing everything is entered in the right order