How can I find the last date in subform?? 1

[Fekri]

Hi,

I have a subform which is include "Date Paid",

I want to create some event to select the last date in record and save in one text box.

Is there any body knows???


your help will be appreciated...

Ali

 

[roppetech]

try this:

Dim cn As New adodb.Connection
Dim rs As New adodb.Recordset

Set cn = CurrentProject.Connection

strsql = "SELECT TOP 1 * FROM tbl1 " & _
         "ORDER BY [Date Paid] DESC"

rs.Open strsql, cn
rs.MoveFirst

txt1 = rs.Fields.Item("Date Paid")

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

just be sure to change tbl1 to your table name, and txt1 to your textbox

Hope it helps!


- RoppeTech

 

[Fekri]

Hi

Thanks for your help

what is tb11 in srtsql???

because I run this code but it issued error on this line:
rs.Open strsql, cn

so, I think because of tb11

what should I replace in criteria words???

Thamks again
Ali

 

[roppetech]

You'll have to replace tbl1 with the name of your table which contains "date paid".
You'll also have to replace txt1 with the name of the textbox you want to display the output


- RoppeTech

 

[Fekri]

Hi,

I'm so sorry because on your first reply I didn't read your last sentence.

But any way, thanks a lot
I used this and it works great.
but I need more help,

Because i'm using this code in subform which is linked to form by Invoice number.
this code will get the last date on all the table records but I need the last date on my subform which is normally filtered by Form invoice number.

Your help are appreciate

Thank you
and regards
Ali

 

[roppetech]

Great! I'm glad it worked out.

To filter by invoice number, use code similar to this:

Dim cn As New adodb.Connection
Dim rs As New adodb.Recordset

Set cn = CurrentProject.Connection

[b]strsql = "SELECT TOP 1 * FROM tbl1 " & _
         "ORDER BY [Date Paid] DESC " & _
         "WHERE [Invoice Number] = " & txtInvoiceNumber
[/b]
rs.Open strsql, cn
rs.MoveFirst

txt1 = rs.Fields.Item("Date Paid")

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

you may have to change:
[Invoice Number] to the name of the invoice number field
txtInvoiceNumber to a textbox or variable that contains the desired invoice number

- RoppeTech

 

[Fekri]

Hi,

Thanks
I made this event but it makes an syntax error on :

rs.Open strsql, cn

I don't know why???
This event which I put on event of subform are as follow:

Dim cn As New adodb.Connection
Dim rs As New adodb.Recordset

Set cn = CurrentProject.Connection

strsql = "SELECT TOP 1 * FROM Payments " & _
"ORDER BY [Date Paid] DESC" & _
"WHERE [Invoice Number] = " & Invoice_Number

rs.Open strsql, cn
rs.MoveFirst

Text10 = rs.Fields.Item("Date Paid")

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

Ali

 

[roppetech]

try putting a space after DESC in this line:

strsql = "SELECT TOP 1 * FROM Payments " & _
"ORDER BY [Date Paid] DESC" & _
"WHERE [Invoice Number] = " & Invoice_Number

so it reads like this:

strsql = "SELECT TOP 1 * FROM Payments " & _
"ORDER BY [Date Paid] DESC " & _
"WHERE [Invoice Number] = " & Invoice_Number



- RoppeTech

 

[Fekri]

Hi ,


I tried by still it makes error on same line

the error message is : "Syntax error on Order By clause"

I posted exactly your written but no avail.

It's come to big headache for me....
Appraciate your try & Help

Ali

 

[roppetech]

oh shoot! my fault. ORDER BY must be after the WHERE clause.

try this one:

strsql = "SELECT TOP 1 * FROM tbl1" & _
         " WHERE [Invoice Number] = " & txtInvoiceNumber & _
         " ORDER BY [Date Paid] DESC"

- RoppeTech