RE: Adding info to query

Hey Thanks, it sort of makes sense now.
One more ?? though. In the function statement itself. My date field in the
table is called "Date Signed up" I tried to enclose it in [] but got a
compile error. Is there another way to declare it w/ou changing the name w/o
spaces?

"Ofer" wrote:

> 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
    ... > 'If the date is empty, it will return the prev one that is not empty ... > LookForLastDate = Date ... >> Dim MyDb As DAO.Database, ... >> 'If the date is empty, it will return the prev one that is not empty ...
    (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: Adding info to query
    ... Dim MyDb As DAO.Database, MyRec As DAO.Recordset ... 'If the date is empty, it will return the prev one that is not empty ... LookForLastDate = Date ... Prev by Date: ...
    (microsoft.public.access.queries)
  • RE: Adding info to query
    ... Set MyRec = MyDb.OpenRecordSet("Select DateFieldName From TableName ... > also, what if the first record is empty, there will be no previous record. ... > Set MyRec = MyDb.OpenRecordSet("Select DateFieldName From TableName ...
    (microsoft.public.access.queries)
  • 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)
Loading