RE: Adding info to query

Sorry, the RecordSet should be

Set MyRec = MyDb.OpenRecordSet("Select DateFieldName From TableName
Where [IdFieldName] < " & IdFieldName & " And DateFieldName Is Not Null
Order By IdFieldName Desc")

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


"Ofer" wrote:

> 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
    ... >> 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)
  • 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. ... Function LookForLastDate(IdFieldName As Double, DateFieldName As Variant) ... >>> Mike J ...
    (microsoft.public.access.queries)
  • Re: Create a Date Query
    ... SELECT * FROM TableName ... WHERE DateFieldName Between DateAnd DateAdd); ... <MS ACCESS MVP> ... Prev by Date: ...
    (microsoft.public.access.queries)
  • Re: date combo box
    ... FROM TableName ... GROUP BY DateFieldName ... > i've already done a parameter query on that. ... to select these 2 dates and having the related photos to appear?? ...
    (microsoft.public.access.macros)
Loading