Null values being returned when use movelast

[ssalem]

ok don't know what I would do without this forum.....Being a newbie I seem to get stuck and research and still don't understand sometimes what is going on. It probably would help if I knew some debugging tools and took a VB course. But here is my problem. I have a table and I am adding a spoilage/variance record for ending Physical Inventory. The person entering the ending Physical Inventory needs to enter the physical inventory with the last working day of the end of the month it was taken in. So I am trying to do some validity checks when they are entering the date. To do this I need to break apart using Datepart the day, month, year for comparison. But I also need to break apart the date of the last day that was entered into the table. So I set my recordset to my table and order it by the used_date. I then try to move to the last record and break apart the last used_date. I keep getting a Null value for my date field. I threw in a moveprevious to see if I was hitting the EOF which I realize I still need to error trap that....but first I can't figure out why movelast is null. This is being entered through a form...so i figured I maybe at a new record that has not been added yet...thus throwing in the moveprevious to go one record back. But it is still a null value. Here is what I have coded. Not sure if I need to build a loop and go through the loop to hit the EOF. Help please.

Dim m1, y1, m2, y2 As String
Dim t1 As String
Dim db As Database
Dim rst As Recordset
Dim LResponse As Integer

Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * From [Stamp Inventory] Order by Used_Date")


rst.MoveLast
rst.MovePrevious

d1 = DatePart("d", Me.Used_date)
m1 = DatePart("m", Me.Used_date)
y1 = DatePart("yyyy", Me.Used_date)

d2 = DatePart("d", Date)
m2 = DatePart("m", Date)
y2 = DatePart("yyyy", Date)

[Missinglinq]

I'm having no luck, whatsoever, in following your scenario here; probably my fault! But if you're trying to set the variables d1, m1 and y1 to the components of Used_date, from the Recordset you've opened, you don't use

Me.Used_date

which refers to a Control on the Form, but rather to the Field in the Recordset

rst.Used_date

Linq ;0)>

[dr91075]

Since you're ordering by Used_Date, have you checked that field for nulls? Also... what is d1 and d2? I don't see those variables dimensioned.

[ssalem]

ok I redefined my code........but now I get "Method or Data Member not found" for my rst.Used_date....stamp inventory is my table and Used_date is a date field in the table. I need dates to do the comparison...I need to check the date they are entering on the form is the same month and year as the last entry on the database. How can I order a database by the field I am trying to break apart yet it says it can't be found?

Dim d1, m1, y1, d2, m2, y2 As String
Dim t1 As String
Dim db As Database
Dim rst As Recordset
Dim LResponse As Integer

Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * From [Stamp Inventory]")


rst.MoveLast



d1 = DatePart("d", Me.TxtUsed_date)
m1 = DatePart("m", Me.TxtUsed_date)
y1 = DatePart("yyyy", Me.TxtUsed_date)


d2 = DatePart("d", rst.Used_date)
m2 = DatePart("m", rst.Used_date)
y2 = DatePart("yyyy", rst.Used_date)

[ssalem]

took the order off but it worked with it on....the database is already ordered when the form is loaded.

[dr91075]

rst!Used_date

[ssalem]

Thank you.......not sure when I should use an exclamation point and when I should use a period. It works now....and I have values. I just need to set up my comparisons which I am sure I can handle.


Thanks again.