Help using Dlookup with Dates

[Guest]

I am trying to use Dlookup to get the date from the previous record and make
the date of the new record that date + 1 day...here is my code. Clearly, I
don't understand the dlookup procedure

Private Sub Form_Current()
Dim TempDate As Date
If Me.NewRecord And IsNull(Me.DOS) Then
TempDate = DLookup("[DOS]", "InputData", "[DOS] = " & Me.DOS)
Me.DOS.Value = TempDate + 1
End If
End Sub

 

[tina]

if the DOS field, in the table InputData, is a Date/Time data type, then try
this

DLookup("[DOS]", "InputData", "[DOS] = #" & Me.DOS & "#")

hth

 

[Marshall Barton]

I am trying to use Dlookup to get the date from the previous record and make
the date of the new record that date + 1 day...here is my code. Clearly, I
don't understand the dlookup procedure

Private Sub Form_Current()
Dim TempDate As Date
If Me.NewRecord And IsNull(Me.DOS) Then
TempDate = DLookup("[DOS]", "InputData", "[DOS] = " & Me.DOS)
Me.DOS.Value = TempDate + 1
End If
End Sub

When you concatenate a date into a string, Access uses your
system settings to determine how to format the date. A
common system date format is mm/dd/yyyy. Using that will
result in DLookup seeing the argument:
[DOS]=10/31/2005
which may look like a date to you, but Access sees an
expression with two divisions. The systax for date literals
is to enclose the formatted date in # signs. Because your
system setting may vary from machine to machine, the safe
way to deal with all this is:

...., "[DOS] = " & Format(Me.DOS, "\#m\/d\/yyyy\#"))

 

[Pat Hartman\(MVP\)]

You are checking DOS for null and only if it is true are you doing the
lookup but the criteria of the lookup is using DOS which is always going to
be null. You aren't going to find anything that way.

You are attempting to make one record dependent on another record. This is
not appropriate in a relational database. Each row in a table should be
independent. Also "previous" doesn't really have any meaning in a
recordset. Each time a recordset is opened, the rows could be in a
different order unless they are sorted by a UNIQUE identifier. If your
table contains an autonumber, the DMax() function might do what you need.
It will return the DOS value from the row with the highest (most recently
entered) autonumber. If you don't have a unique identifier, there is no way
to get the "previous" record.

 

[Guest]

There is one other issue here. The destination variable for the DLookup is a
date/time data type. All Domain Aggrate functions can return Null. The only
save way to use a Domain Aggrate is to either enclose it in an Nz function or
make the destination variable a variant data type. Also, a DMax would be
better in this case than a DLookup.

You are checking DOS for null and only if it is true are you doing the
lookup but the criteria of the lookup is using DOS which is always going to
be null. You aren't going to find anything that way.

You are attempting to make one record dependent on another record. This is
not appropriate in a relational database. Each row in a table should be
independent. Also "previous" doesn't really have any meaning in a
recordset. Each time a recordset is opened, the rows could be in a
different order unless they are sorted by a UNIQUE identifier. If your
table contains an autonumber, the DMax() function might do what you need.
It will return the DOS value from the row with the highest (most recently
entered) autonumber. If you don't have a unique identifier, there is no way
to get the "previous" record.

I am trying to use Dlookup to get the date from the previous record and
make
the date of the new record that date + 1 day...here is my code. Clearly,
I
don't understand the dlookup procedure

Private Sub Form_Current()
Dim TempDate As Date
If Me.NewRecord And IsNull(Me.DOS) Then
TempDate = DLookup("[DOS]", "InputData", "[DOS] = " & Me.DOS)
Me.DOS.Value = TempDate + 1
End If
End Sub

 

[Guest]

Thanks for all the replies. The code by tina and Marshall did not work. I
cut and paste so I don't think I made a typo.

As for Klatuu's comments...the idea of checking to see is DOS is null is to
be sure it is a new record. Otherwise, I would be changing already entered
dates. As for looking at the previous record, it should be feasible. See
here: http://support.microsoft.com/?kbid=210504

Meanwhile, any other suggestions on how to make this work. If I can't, no
big deal, it was really just for user convenience.

 

[Guest]

I read the article you posted the reference to.
Notice the DLookup method has this disclaimer:

This technique assumes that you have a table with an ID field of a Number
data type, and that the ID values are not missing any number (or numbers) in
sequential order.

You are using a data type and cannot be sure there are not missing dates.

The code version is assuming you are selecting an existing record and moving
to the previous record. Previous record means in the current sort order.
Since you are adding a new record, this will not work for you. Try this:

Private Sub Form_Current()
Dim varTempDate As Variant

If Me.NewRecord And IsNull(Me.DOS) Then
varTempDate = DMax("[DOS]", "InputData")
If IsNull(varTempDate) Then
Me.DOS = Date()
Else
Me.DOS = DateAdd("d", 1, varTempDate)
End If
End If
End Sub

Now, the issue I see is there will only ever be one record in this table for
any date. Is that what you want?

varLastDate = DMax

 

[Guest]

And this???

Dim rs As Recordset
Set rs = Me.RecordsetClone
With rs
.MoveLast
If Me.NewRecord And IsNull(Me.DOS) Then
DOS = DateAdd("d", 1, !DOS)
End If
End With

 

[Guest]

The table with DOS does have a numeric index, unfortunately due to deletes,
it will not be continuous. Furthermore, the form this is on is a subform
which complicates this issue as I need to look at the last date for the
subform not in all the data. The new code still doesn't do quite what I
wanted. Oh well. Not a big deal.

Thanks to all for trying.

Mark

I read the article you posted the reference to.
Notice the DLookup method has this disclaimer:

This technique assumes that you have a table with an ID field of a Number
data type, and that the ID values are not missing any number (or numbers) in
sequential order.

You are using a data type and cannot be sure there are not missing dates.

The code version is assuming you are selecting an existing record and moving
to the previous record. Previous record means in the current sort order.
Since you are adding a new record, this will not work for you. Try this:

Private Sub Form_Current()
Dim varTempDate As Variant

If Me.NewRecord And IsNull(Me.DOS) Then
varTempDate = DMax("[DOS]", "InputData")
If IsNull(varTempDate) Then
Me.DOS = Date()
Else
Me.DOS = DateAdd("d", 1, varTempDate)
End If
End If
End Sub

Now, the issue I see is there will only ever be one record in this table for
any date. Is that what you want?

varLastDate = DMax

Thanks for all the replies. The code by tina and Marshall did not work. I
cut and paste so I don't think I made a typo.

As for Klatuu's comments...the idea of checking to see is DOS is null is to
be sure it is a new record. Otherwise, I would be changing already entered
dates. As for looking at the previous record, it should be feasible. See
here: http://support.microsoft.com/?kbid=210504

Meanwhile, any other suggestions on how to make this work. If I can't, no
big deal, it was really just for user convenience.

 

[tina]

probably there is a way to do whatever it is you're trying to do - there
usually is, in Access. but you need to be specific about what your goal is,
and what entities you're working with, so that we can help you figure it
out.

for instance, what is "InputData"? in order for a domain function to work,
it must be a table or a query. if you want to work only with the records in
a subform, then you need to use the name of the table or query that the
subform is bound to AND (assuming that the subform is linked to the main
form via Parent/Child fields) you need to set criteria in the domain
function to return that same subset of records. it's possible you'd be
better off using a RecordsetClone in this situation, but we can't really
tell you until we understand the situation better.

when you say "the previous record", do you mean the last record that was
entered in the table? or the last record that was entered in the subform
during the current session? is only one record entered in a particular
subset of records on any given day?

suggest you post back with a clear explanation of your goal and the setup
you're working with.

hth

The table with DOS does have a numeric index, unfortunately due to deletes,
it will not be continuous. Furthermore, the form this is on is a subform
which complicates this issue as I need to look at the last date for the
subform not in all the data. The new code still doesn't do quite what I
wanted. Oh well. Not a big deal.

Thanks to all for trying.

Mark

I read the article you posted the reference to.
Notice the DLookup method has this disclaimer:

This technique assumes that you have a table with an ID field of a Number
data type, and that the ID values are not missing any number (or numbers) in
sequential order.

You are using a data type and cannot be sure there are not missing dates.

The code version is assuming you are selecting an existing record and moving
to the previous record. Previous record means in the current sort order.
Since you are adding a new record, this will not work for you. Try this:

Private Sub Form_Current()
Dim varTempDate As Variant

If Me.NewRecord And IsNull(Me.DOS) Then
varTempDate = DMax("[DOS]", "InputData")
If IsNull(varTempDate) Then
Me.DOS = Date()
Else
Me.DOS = DateAdd("d", 1, varTempDate)
End If
End If
End Sub

Now, the issue I see is there will only ever be one record in this table for
any date. Is that what you want?

varLastDate = DMax

Thanks for all the replies. The code by tina and Marshall did not work. I
cut and paste so I don't think I made a typo.

As for Klatuu's comments...the idea of checking to see is DOS is null is to
be sure it is a new record. Otherwise, I would be changing already entered
dates. As for looking at the previous record, it should be feasible. See
here: http://support.microsoft.com/?kbid=210504

Meanwhile, any other suggestions on how to make this work. If I can't, no
big deal, it was really just for user convenience.

 

[Marshall Barton]

I like it! It avoids the DMax altogether.

But a check for EOF will guard against the situation where
there's no "previous" record?

Also, if the form is at the new record, won't the DOS value
always be Null?

 

[Guest]

Marsh,

I agree with you...didn't see the need of IsNull(Me.DOS) either, however
sneagle seemed to post that he needed it so I included it.

For error trapping, no I didn't include any error handling for EOF and
should be included if he uses this code.

I am not really sure what the objective of sneagle was, and so the code I
posted was a general code to give him an alternate idea rather than using the
DLookup or DMax functions.

Personally, I would have just used a default value for DOS and eliminated
any code.

lwells

I like it! It avoids the DMax altogether.

But a check for EOF will guard against the situation where
there's no "previous" record?

Also, if the form is at the new record, won't the DOS value
always be Null?
--
Marsh
MVP [MS Access]

And this???

Dim rs As Recordset
Set rs = Me.RecordsetClone
With rs
.MoveLast
If Me.NewRecord And IsNull(Me.DOS) Then
DOS = DateAdd("d", 1, !DOS)
End If
End With

 

[Marshall Barton]

Instead of error handling, I was thinking of something like:
If rs.RecordCount > 0 Then
. . .
Else
DOS = Date
End

I don't think a DefaultValue can work in the case where the
value is dependent on the foreign key field (the subform's
LinkChild field).

Regardless of this nitpicking, using the subform's recordset
clone is a good idea.

 

[Guest]

Wow, lots more replies...thanks to all. Before I try any of the new
suggestions, let me explain my db better. As you know my understanding is
limited.

Two tables with data (and several 'lookup' tables)
1. InputPeople - has people and demographic data
2. InputData - events for above people

Main Form: InputPeopleA - from InputPeople
SubForm: InputDataA - from InputData, linked to main via name

So, I want to be able to view the events listed and add new ones.
Generally, one event per day. There may be more, but by default I want a new
record in the subform to start with the day after the previous event.

Does that make more sense?

 

[Marshall Barton]

Wow, lots more replies...thanks to all. Before I try any of the new
suggestions, let me explain my db better. As you know my understanding is
limited.

Two tables with data (and several 'lookup' tables)
1. InputPeople - has people and demographic data
2. InputData - events for above people

Main Form: InputPeopleA - from InputPeople
SubForm: InputDataA - from InputData, linked to main via name

So, I want to be able to view the events listed and add new ones.
Generally, one event per day. There may be more, but by default I want a new
record in the subform to start with the day after the previous event.

I don't see how this relates to your original question, but
maybe a comment is in order.

The relationship between people and events sounds like a
many to many relationship. Each person can participate in
many events and more than one person can be involved in each
event. This is best modeled by using a third table for
personevent. This "junction" table would have fields for
person ID, event ID, the date, and maybe other fields
related to an individual's involvement with an event. The
table's primary key would be compound index of the fields,
person ID, event ID and maybe the date.

Your main form would be bound to the person table and the
subform would be bound to the junction table. The event ID
field would be a combo box with its row source set to the
events table.

 

[Guest]

Thanks for the suggestion. I don't fully understand it, but...I did not
explain my tables well. The InputPeople table actually has start and end
dates as well as demographic data. In other words the events for that person
must occur between those dates. The events are unique to that person. In
other words, no event can be happen to two folks together.

One idea I had for my problem is to add a field to my InputPeople table
called 'LastEnteredDate'. Then after each entry to the subform, the
LastEnteredDate will become the date of the most recently entered record for
that person. Since this wil be unique to each person, it will carry over if
you change the person you are viewing on the main form. Also, if you close
Access and come back later, it will carry over.

Note that in the date field on the subform, I capture keypresses for '+',
'-' and 't' or 'T' to add/subtract from the date or make it the current date.

So, does anyone like the idea?

 

[Marshall Barton]

Thanks for the suggestion. I don't fully understand it, but...I did not
explain my tables well. The InputPeople table actually has start and end
dates as well as demographic data. In other words the events for that person
must occur between those dates. The events are unique to that person. In
other words, no event can be happen to two folks together.

One idea I had for my problem is to add a field to my InputPeople table
called 'LastEnteredDate'. Then after each entry to the subform, the
LastEnteredDate will become the date of the most recently entered record for
that person. Since this wil be unique to each person, it will carry over if
you change the person you are viewing on the main form. Also, if you close
Access and come back later, it will carry over.

Note that in the date field on the subform, I capture keypresses for '+',
'-' and 't' or 'T' to add/subtract from the date or make it the current date.

Sounds reasonable, with the caveat that I can't evaluate it
in the context of your entire application.