Public function from next record

[sargon]

I never used VBA before, but I need to create a public function (fc_P) that read all the record from one field (x_date) and after that to create a new field that has in the previous record x_date - 1 (last record should be Now())

x_date
01/01/2022
03/05/2022
08/07/2022

and to generate

fc_P
03/04/2022
08/06/2022
Now()

 

[arnelgp]

you can use query:

select x_date, Nz(DMin("x_date", "YourTableName", "x_date > #" & Format$(x_date, "mm/dd/yyyy") & "#")-1, Date()) As fc_p from YourTableName;

 

[sargon]

And If I want to create a Public Function?


Public Function fc_p()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("YourTableName")

Dim strSQL As String


strSQL = "Select x_date, Nz(DMin("x_date", "YourTableName", "x_date > #" & Format$(x_date, "mm/dd/yyyy") & "#")-1, Date()) As fc_p from YourTableName;"

Set fc_p = db.OpenRecordset(strSQL)

End Function

 

[arnelgp]

the function is somewhat the same:

Public Function fnx(ByVal dte) As Date
    fnx = Nz(DMin("x_date", "YourTableName", "x_date > #" & Format$(dte, "mm/dd/yyyy") & "#")-1, Date())
End Function

you need to pass a Date to the function.
but the query itself is sufficient.

 

[sargon]

Something is not working...

 

[cheekybuddha]

Try changing the signature of the function slightly to specifically type your parameter:

Public Function fnx(ByVal dte As Date) As Date
' ...

What datatype is field x_date?

 

[arnelgp]

see query 2 in design view.
and see the query 3

 

[sargon]

It is perfect! Thank you!