Adding info to query

G

Guest

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
 
G

Guest

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?
 
G

Guest

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
 
G

Guest

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
 
G

Guest

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 said:
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 said:
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
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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

:

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 \\//


:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Can you post the SQL and the function you have created?

mjj4golf said:
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 said:
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 said:
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 \\//


:

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

:

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 \\//


:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Ofer said:
Can you post the SQL and the function you have created?

mjj4golf said:
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 said:
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 \\//


:

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 \\//


:

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

:

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 \\//


:

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

Function LookForLastDate(Id As Long, [Date Signed up] As Variant) As Date
Click to expand...
Click to expand...
Click to expand...
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 = [Date Signed up]
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date

End Function

I get a compile error on the variable [Date Signed up] in the function
stment and
"expected list seperator or )" on the Where.

Mike J
 
G

Guest

The parameter doesn't need to have the same name as the field
Try this

Function LookForLastDate(Id As Long, Date_Signed_up 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 = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date()
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date()

End Function



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


mjj4golf said:
Ofer said:
Can you post the SQL and the function you have created?

mjj4golf said:
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?

:

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 \\//


:

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 \\//


:

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

:

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 \\//


:

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

Function LookForLastDate(Id As Long, [Date Signed up] As Variant) As Date
Click to expand...
Click to expand...
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 = [Date Signed up]
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date

End Function

I get a compile error on the variable [Date Signed up] in the function
stment and
"expected list seperator or )" on the Where.

Mike J
Click to expand...
 
G

Guest

Thanks for your help. However, In the query you wrote I notice that you are
calling the function w/ three variables but the function is defined with two.
This gives an error of course. May I ask do you need the third?

Mike J


Ofer said:
The parameter doesn't need to have the same name as the field
Try this

Function LookForLastDate(Id As Long, Date_Signed_up 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 = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date()
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date()

End Function



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


mjj4golf said:
Ofer said:
Can you post the SQL and the function you have created?

:

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?

:

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 \\//


:

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 \\//


:

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

:

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 \\//


:

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

Function LookForLastDate(Id As Long, [Date Signed up] As Variant) As Date
Click to expand...
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 = [Date Signed up]
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date

End Function

I get a compile error on the variable [Date Signed up] in the function
stment and
"expected list seperator or )" on the Where.

Mike J
Click to expand...
Click to expand...
 
G

Guest

In the query there are two parameters defined, the Date() is a part of the
DateDiff.

Select IdFieldName , DatFieldName, DateDiff("d", - -
-LookForLastDate(IdFieldName, DateFieldName) - - - -,Date()) As
DifferentInDays From TableName

I added deshes before and after the function

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


mjj4golf said:
Thanks for your help. However, In the query you wrote I notice that you are
calling the function w/ three variables but the function is defined with two.
This gives an error of course. May I ask do you need the third?

Mike J


Ofer said:
The parameter doesn't need to have the same name as the field
Try this

Function LookForLastDate(Id As Long, Date_Signed_up 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 = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date()
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date()

End Function



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


mjj4golf said:
:

Can you post the SQL and the function you have created?

:

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?

:

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 \\//


:

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 \\//


:

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

:

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 \\//


:

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

Function LookForLastDate(Id As Long, [Date Signed up] 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 = [Date Signed up]
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date

End Function

I get a compile error on the variable [Date Signed up] in the function
stment and
"expected list seperator or )" on the Where.

Mike J
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Never Mind!
Thx

Ofer said:
The parameter doesn't need to have the same name as the field
Try this

Function LookForLastDate(Id As Long, Date_Signed_up 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 = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date()
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date()

End Function



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


mjj4golf said:
Ofer said:
Can you post the SQL and the function you have created?

:

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?

:

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 \\//


:

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 \\//


:

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

:

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 \\//


:

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

Function LookForLastDate(Id As Long, [Date Signed up] As Variant) As Date
Click to expand...
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 = [Date Signed up]
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date

End Function

I get a compile error on the variable [Date Signed up] in the function
stment and
"expected list seperator or )" on the Where.

Mike J
Click to expand...
Click to expand...
 
G

Guest

Ofer;
Sorry to bother you with this but the function doesn't seem to work. It is
not putting the previous date in the blank date and the query seems to hang
on the blank field until I scroll down. I put brek points in the function
and do see the date there but it's not passed back I guess. Can you explain
the Where [Id] < " & Id & " ... ?

mjj4golf said:
Never Mind!
Thx

Ofer said:
The parameter doesn't need to have the same name as the field
Try this

Function LookForLastDate(Id As Long, Date_Signed_up 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 = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date()
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date()

End Function



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


mjj4golf said:
:

Can you post the SQL and the function you have created?

:

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?

:

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 \\//


:

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 \\//


:

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

:

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 \\//


:

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

Function LookForLastDate(Id As Long, [Date Signed up] 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 = [Date Signed up]
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date

End Function

I get a compile error on the variable [Date Signed up] in the function
stment and
"expected list seperator or )" on the Where.

Mike J
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Also, can you explain the 'AS DifferentinMonths'
Mike J

mjj4golf said:
Never Mind!
Thx

Ofer said:
The parameter doesn't need to have the same name as the field
Try this

Function LookForLastDate(Id As Long, Date_Signed_up 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 = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date()
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date()

End Function



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


mjj4golf said:
:

Can you post the SQL and the function you have created?

:

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?

:

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 \\//


:

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 \\//


:

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

:

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 \\//


:

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

Function LookForLastDate(Id As Long, [Date Signed up] 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 = [Date Signed up]
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date

End Function

I get a compile error on the variable [Date Signed up] in the function
stment and
"expected list seperator or )" on the Where.

Mike J
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Can you post again the SQL and function?
==================================
Also, I just noticed in the function , that you didn't use square brackets
for the date field
And, Is the Id field name [Id Desc] Or [Id], it look like you used both


Function LookForLastDate(Id As Long, [Date Signed up] 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 = [Date Signed up]
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select [Date Signed up] From RC_Main "
& _
" Where [Id] < " & Id & " And [Date Signed up] Is Not Null " & _
" Order By [Id Desc]")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

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


mjj4golf said:
Also, can you explain the 'AS DifferentinMonths'
Mike J

mjj4golf said:
Never Mind!
Thx

Ofer said:
The parameter doesn't need to have the same name as the field
Try this

Function LookForLastDate(Id As Long, Date_Signed_up 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 = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date()
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date()

End Function



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


:



:

Can you post the SQL and the function you have created?

:

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?

:

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 \\//


:

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 \\//


:

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

:

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 \\//


:

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

Function LookForLastDate(Id As Long, [Date Signed up] 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 = [Date Signed up]
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date

End Function

I get a compile error on the variable [Date Signed up] in the function
stment and
"expected list seperator or )" on the Where.

Mike J
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hey;
I did some more playing around using breakpoints and found that the
variables MyDb and MyRec does not get set. That is probably the problem but
here is the
function and query:

Function LookForLastDate(ID As Long, Date_Signed_up As Variant) As Date
On Error GoTo LookForLastDate_Err
Dim MyDb As Database, MyRec As Recordset

' If there is date, it will return
If Not IsNull(Date_Signed_up) Then
LookForLastDate = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordset("Select DateSignedup From RC_Main" _
& "Where [Id] < " & ID & " And Date_Signed_up Is Not Null" _
& "Order By Id Asc")
End If

If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec!DateSignedup

End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date

End Function

Query:
SELECT Id, DateSignedup,
DateDiff("m",LookForLastDate(Id,DateSignedup),Date()) AS differentinmonths
FROM RC_Main;

Thx again
Mike J
Ofer said:
Can you post again the SQL and function?
==================================
Also, I just noticed in the function , that you didn't use square brackets
for the date field
And, Is the Id field name [Id Desc] Or [Id], it look like you used both


Function LookForLastDate(Id As Long, [Date Signed up] 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 = [Date Signed up]
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select [Date Signed up] From RC_Main "
& _
" Where [Id] < " & Id & " And [Date Signed up] Is Not Null " & _
" Order By [Id Desc]")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

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


mjj4golf said:
Also, can you explain the 'AS DifferentinMonths'
Mike J

mjj4golf said:
Never Mind!
Thx

:

The parameter doesn't need to have the same name as the field
Try this

Function LookForLastDate(Id As Long, Date_Signed_up 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 = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date()
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date()

End Function



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


:



:

Can you post the SQL and the function you have created?

:

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?

:

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 \\//


:

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 \\//


:

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

:

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 \\//


:

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

Function LookForLastDate(Id As Long, [Date Signed up] 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 = [Date Signed up]
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date

End Function

I get a compile error on the variable [Date Signed up] in the function
stment and
"expected list seperator or )" on the Where.

Mike J
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I am getting an invalid From clause and it also looks like MyDb and MyRec is
not getting set when I do some brkpts in the Function, Here is the func and
the query:

Function LookForLastDate(id As Long, Date_Signed_up 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(Date_Signed_up) Then
LookForLastDate = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordset("Select DateSignedup From RC_Main" _
& "Where [Id] < " & id & " And Date_Signed_up Is Not Null" _
& "OrderBy Id")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec!DateSignedup
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date

End Function

Query:
SELECT Id, DateSignedup,
DateDiff("m",LookForLastDate(Id,DateSignedup),Date()) AS differentinmonths
FROM RC_Main;

Thx

Ofer said:
Can you post again the SQL and function?
==================================
Also, I just noticed in the function , that you didn't use square brackets
for the date field
And, Is the Id field name [Id Desc] Or [Id], it look like you used both


Function LookForLastDate(Id As Long, [Date Signed up] 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 = [Date Signed up]
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select [Date Signed up] From RC_Main "
& _
" Where [Id] < " & Id & " And [Date Signed up] Is Not Null " & _
" Order By [Id Desc]")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

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


mjj4golf said:
Also, can you explain the 'AS DifferentinMonths'
Mike J

mjj4golf said:
Never Mind!
Thx

:

The parameter doesn't need to have the same name as the field
Try this

Function LookForLastDate(Id As Long, Date_Signed_up 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 = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date()
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date()

End Function



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


:



:

Can you post the SQL and the function you have created?

:

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?

:

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 \\//


:

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 \\//


:

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

:

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 \\//


:

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

Function LookForLastDate(Id As Long, [Date Signed up] 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 = [Date Signed up]
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date

End Function

I get a compile error on the variable [Date Signed up] in the function
stment and
"expected list seperator or )" on the Where.

Mike J
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear MJ:

What your code produces looks like this:

Select DateSignedup From RC_MainWhere [Id] < 12345 And Date_Signed_up Is Not
NullOrderBy Id

Notice there is no space where you have concatenated Where or OrderBy. Add
a space at those two places, and between "Order" and "By".

I strongly recommend you do this:

Function LookForLastDate(id As Long, Date_Signed_up As Variant) As Date
On Error GoTo LookForLastDate_Err
Dim MyDb As DAO.Database, MyRec As DAO.Recordset
Dim StrSQL As String

' If there is date, it will return
If Not IsNull(Date_Signed_up) Then
LookForLastDate = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
StrSQL = "Select DateSignedup From RC_Main" _
& "Where [Id] < " & id & " And Date_Signed_up Is Not Null" _
& "OrderBy Id"
Set MyRec = MyDb.OpenRecordset(StrSQL)
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec!DateSignedup
End If
End If
Exit Function

For testing, put a breakpoint on the OpenRecordset line. In the immediate
pane, look at StrSQL to see what you have generated. If you can see this
kind of error, you will probably easily avoid it.

Tom Ellison


mjj4golf said:
I am getting an invalid From clause and it also looks like MyDb and MyRec
is
not getting set when I do some brkpts in the Function, Here is the func
and
the query:

Function LookForLastDate(id As Long, Date_Signed_up 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(Date_Signed_up) Then
LookForLastDate = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordset("Select DateSignedup From RC_Main" _
& "Where [Id] < " & id & " And Date_Signed_up Is Not Null" _
& "OrderBy Id")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec!DateSignedup
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date

End Function

Query:
SELECT Id, DateSignedup,
DateDiff("m",LookForLastDate(Id,DateSignedup),Date()) AS differentinmonths
FROM RC_Main;

Thx

Ofer said:
Can you post again the SQL and function?
==================================
Also, I just noticed in the function , that you didn't use square
brackets
for the date field
And, Is the Id field name [Id Desc] Or [Id], it look like you used both


Function LookForLastDate(Id As Long, [Date Signed up] 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 = [Date Signed up]
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select [Date Signed up] From
RC_Main "
& _
" Where [Id] < " & Id & " And [Date Signed up] Is Not Null " & _
" Order By [Id Desc]")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

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


mjj4golf said:
Also, can you explain the 'AS DifferentinMonths'
Mike J

:

Never Mind!
Thx

:

The parameter doesn't need to have the same name as the field
Try this

Function LookForLastDate(Id As Long, Date_Signed_up 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 = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not
empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From
RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date()
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date()

End Function



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


:



:

Can you post the SQL and the function you have created?

:

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?

:

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 \\//


:

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 \\//


:

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

:

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 \\//


:

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

Function LookForLastDate(Id As Long, [Date Signed
up] 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 = [Date Signed up]
Else
'If the date is empty, it will return the prev one that is
not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From
RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null
_
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date

End Function

I get a compile error on the variable [Date Signed up] in the
function
stment and
"expected list seperator or )" on the Where.

Mike J
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thx Tom and I did what you said. I set the breakpoint at set myrec and when
I continue I get an error: Too few parameters:expected 1

Confused here but thx for your patience!

Mike J

Tom Ellison said:
Dear MJ:

What your code produces looks like this:

Select DateSignedup From RC_MainWhere [Id] < 12345 And Date_Signed_up Is Not
NullOrderBy Id

Notice there is no space where you have concatenated Where or OrderBy. Add
a space at those two places, and between "Order" and "By".

I strongly recommend you do this:

Function LookForLastDate(id As Long, Date_Signed_up As Variant) As Date
On Error GoTo LookForLastDate_Err
Dim MyDb As DAO.Database, MyRec As DAO.Recordset
Dim StrSQL As String

' If there is date, it will return
If Not IsNull(Date_Signed_up) Then
LookForLastDate = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
StrSQL = "Select DateSignedup From RC_Main" _
& "Where [Id] < " & id & " And Date_Signed_up Is Not Null" _
& "OrderBy Id"
Set MyRec = MyDb.OpenRecordset(StrSQL)
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec!DateSignedup
End If
End If
Exit Function

For testing, put a breakpoint on the OpenRecordset line. In the immediate
pane, look at StrSQL to see what you have generated. If you can see this
kind of error, you will probably easily avoid it.

Tom Ellison


mjj4golf said:
I am getting an invalid From clause and it also looks like MyDb and MyRec
is
not getting set when I do some brkpts in the Function, Here is the func
and
the query:

Function LookForLastDate(id As Long, Date_Signed_up 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(Date_Signed_up) Then
LookForLastDate = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordset("Select DateSignedup From RC_Main" _
& "Where [Id] < " & id & " And Date_Signed_up Is Not Null" _
& "OrderBy Id")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec!DateSignedup
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date

End Function

Query:
SELECT Id, DateSignedup,
DateDiff("m",LookForLastDate(Id,DateSignedup),Date()) AS differentinmonths
FROM RC_Main;

Thx

Ofer said:
Can you post again the SQL and function?
==================================
Also, I just noticed in the function , that you didn't use square
brackets
for the date field
And, Is the Id field name [Id Desc] Or [Id], it look like you used both


Function LookForLastDate(Id As Long, [Date Signed up] 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 = [Date Signed up]
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select [Date Signed up] From
RC_Main "
& _
" Where [Id] < " & Id & " And [Date Signed up] Is Not Null " & _
" Order By [Id Desc]")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

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


:

Also, can you explain the 'AS DifferentinMonths'
Mike J

:

Never Mind!
Thx

:

The parameter doesn't need to have the same name as the field
Try this

Function LookForLastDate(Id As Long, Date_Signed_up 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 = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not
empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From
RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date()
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date()

End Function



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


:



:

Can you post the SQL and the function you have created?

:

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?

:

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 \\//


:

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 \\//


:

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

:

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 \\//


:

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

Function LookForLastDate(Id As Long, [Date Signed
up] 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 = [Date Signed up]
Else
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

OK, Forget that. I found the problem but now it is not putting a date for
the blank date but it is putting a 2 in the diiferentinmths column beside the
blank date.
Progress!

Mike J

Tom Ellison said:
Dear MJ:

What your code produces looks like this:

Select DateSignedup From RC_MainWhere [Id] < 12345 And Date_Signed_up Is Not
NullOrderBy Id

Notice there is no space where you have concatenated Where or OrderBy. Add
a space at those two places, and between "Order" and "By".

I strongly recommend you do this:

Function LookForLastDate(id As Long, Date_Signed_up As Variant) As Date
On Error GoTo LookForLastDate_Err
Dim MyDb As DAO.Database, MyRec As DAO.Recordset
Dim StrSQL As String

' If there is date, it will return
If Not IsNull(Date_Signed_up) Then
LookForLastDate = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
StrSQL = "Select DateSignedup From RC_Main" _
& "Where [Id] < " & id & " And Date_Signed_up Is Not Null" _
& "OrderBy Id"
Set MyRec = MyDb.OpenRecordset(StrSQL)
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec!DateSignedup
End If
End If
Exit Function

For testing, put a breakpoint on the OpenRecordset line. In the immediate
pane, look at StrSQL to see what you have generated. If you can see this
kind of error, you will probably easily avoid it.

Tom Ellison


mjj4golf said:
I am getting an invalid From clause and it also looks like MyDb and MyRec
is
not getting set when I do some brkpts in the Function, Here is the func
and
the query:

Function LookForLastDate(id As Long, Date_Signed_up 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(Date_Signed_up) Then
LookForLastDate = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordset("Select DateSignedup From RC_Main" _
& "Where [Id] < " & id & " And Date_Signed_up Is Not Null" _
& "OrderBy Id")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec!DateSignedup
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date

End Function

Query:
SELECT Id, DateSignedup,
DateDiff("m",LookForLastDate(Id,DateSignedup),Date()) AS differentinmonths
FROM RC_Main;

Thx

Ofer said:
Can you post again the SQL and function?
==================================
Also, I just noticed in the function , that you didn't use square
brackets
for the date field
And, Is the Id field name [Id Desc] Or [Id], it look like you used both


Function LookForLastDate(Id As Long, [Date Signed up] 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 = [Date Signed up]
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select [Date Signed up] From
RC_Main "
& _
" Where [Id] < " & Id & " And [Date Signed up] Is Not Null " & _
" Order By [Id Desc]")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

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


:

Also, can you explain the 'AS DifferentinMonths'
Mike J

:

Never Mind!
Thx

:

The parameter doesn't need to have the same name as the field
Try this

Function LookForLastDate(Id As Long, Date_Signed_up 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 = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not
empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From
RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date()
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date()

End Function



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


:



:

Can you post the SQL and the function you have created?

:

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?

:

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 \\//


:

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 \\//


:

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

:

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 \\//


:

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

Function LookForLastDate(Id As Long, [Date Signed
up] 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 = [Date Signed up]
Else
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

#error - Blank 3
Dcount returning no results!!! 0
query to pull between two dates 4
Query for previous field value 3
Another most-recent date query 0
parameter query issue with single date 4
Parameter Query to filter dates as entered in and if not to show N 4
Query Question 1

Top