RE: Adding info to query

I'll try and give you an example to what I mean, it could be that the prev
record is also null, so you need to look for the previous and not empty date,
also, what if the first record is empty, there will be no previous record.
So, try this example
==========================================
Query:
Select IdFieldName , DatFieldName, DateDiff("d",LookForLastDate(IdFieldName,
DateFieldName) ,Date()) As DifferentInDays From TableName
==========================================
Function:
Function LookForLastDate(IdFieldName As Double, DateFieldName As Variant)
As Date
On Error Goto LookForLastDate_Err
Dim MyDb as Dao.DataBase, MyRec As Dao.RecordSet
' If there is date, it will return
If Not IsNull(DateFieldName) Then
LookForLastDate = DateFieldName
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select DateFieldName From TableName
Where [IdFieldName] < " & IdFieldName & " Order By IdFieldName Desc")
If MyRec.Eof then
LookForLastDate = Date()
Else
LookForLastDate = MyRec!DateFieldName
End If
End If
Exit Function
LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date()
End Function

--
\\// Live Long and Prosper \\//


"mjj4golf" wrote:

> Yes, all the id's are unique to identify the person. But, I just want to go
> to the previous record where the date is and enter that date in the record
> with the empty date.. I would then go down till I come to the next empty
> date field and go back one record, get the date, and plug that date in the
> empty date..etc.
>
> Mike J
>
> "Ofer" wrote:
>
> > You can by using a function to return the prev date, do you have a unique id
> > for each record, so you can find all the prev records?
> >
> > --
> > \\// Live Long and Prosper \\//
> >
> >
> > "mjj4golf" wrote:
> >
> > > I have a table with a date field that the user left blank sometimes. Now I
> > > need to use it to find out how many monts between todays date and the date in
> > > the table. Is there a way to use the date in the previous record in the
> > > query and put it into the next record with the blank date?
> > >
> > > Mike J
> > >
> > >
.

Relevant Pages

  • RE: Adding info to query
    ... it sort of makes sense now. ... > compile error. ... >>> also, what if the first record is empty, there will be no previous record. ... Prev by Date: ...
    (microsoft.public.access.queries)
  • RE: Adding info to query
    ... >> I'll try and give you an example to what I mean, it could be that the prev ... >> also, what if the first record is empty, there will be no previous record. ... >> DateFieldName),Date) As DifferentInDays From TableName ... >> LookForLastDate = DateFieldName ...
    (microsoft.public.access.queries)
  • delete rows from datagrid
    ... My problem is that whatever rows I delete, dgDeleted is always empty ... What should I do to store the deleted rows in a dataset so i can ... Thx ... Prev by Date: ...
    (microsoft.public.dotnet.languages.vb)
  • List Box
    ... View table in datasheet view...the field bound to the list box is empty. ... Prev by Date: ...
    (microsoft.public.access.forms)
  • Re: open a file and go to last place you worked on it
    ... > Microsoft Word MVP ... >> I seem to remember a keystroke combination that you ... >> section but came up empty. ... Prev by Date: ...
    (microsoft.public.word.newusers)