Expiration date!

[alleybye]

i want to view the number of my expired and non expired stocks, and i cant figure it out.
Heres my code. can some please help me?

Private Sub Form_Load()
If rsInventory.RecordCount > 0 Then
        Do Until rsInventory.EOF
            If Val(rsInventory("Expiration_Date")) < Date Then
                expired = expired + 1
                    rsInventory.MoveNext
                txtExp = expired
            Else
                nonexpired = nonexpired + 1
                txtNonExp = nonex
            End If
        Loop
End If
end sub

i use dtpicker for the Expiration date and save it to the db.

thanks!

[AndreRet]

You went the long way around.:)

Just change your select statement and show the recordcount.

rsInventory.Open "SELECT Expiration_Date FROM YourTable WHERE Expiration_Date < DateValue(Date)", conn, adOpenStatic, adLockOptimistic

txtExp.Text = rsInventory.Recordcount

[alleybye]

What should i put on dateValue(date)? it gives me an error. i think its about that datevalue, sorry dude.

[AndreRet]

Replace "Date" with the date you want to search from i.e. "2011/03/01". Also, did you change the "YourTable" to your own database table name?

The dat also has to be between apostrophes, '2011/03/01'

[alleybye]

well my dateformat is mm/dd/yy and also that is on my database.. sorry dude.. i do really having hard time dealing with this.. sux..

[AndreRet]

Paste the code you currently have available, let me have a look. Also your table name and the field names.

[alleybye]

rsInventory.Open "SELECT Expiration_Date FROM YourTable WHERE Expiration_Date < 'Date'", conn, adOpenStatic, adLockOptimistic

thats your code sir i just edit it.

it display the recordset but not those who are < than the current date. :(

[alleybye]

rsInventory.Open "SELECT Expiration FROM tblInventory WHERE Expiration < 'Date'", con, adOpenStatic, adLockOptimistic

hats your code sir i just edit it.

it display the recordset but not those who are < than the current date.

[AndreRet]

rsInventory.Open "SELECT Expiration FROM tblInventory WHERE Expiration < DateValue('04/01/2011')", con, adOpenStatic, adLockOptimistic

Also, what connection string are you using. I need that as well because "con" refers to the connection.

[alleybye]

Thanks dude! i owe you one! i think i got it! :) really big help! godbless :)

[alleybye]

how to use the current date on that syntax? :|

[AndreRet]

Only a pleasure. Please mark this as solved, found at the bottom of this page. Happy coding.:)

[reygcalantaol]

To compare dates on SQL statement date should be enclose with # sign.

[AndreRet]

Seems I missed a post.

how to use the current date on that syntax?

DateValue('Now')