Return last date in a table 1

[Roblin]

Hello. I have a database that contains a form users input data into. The table that is the record source for this form is dependent on on function that grabs the user's network ID. This table's primary key is "WorkDate". I would like to have the entry form open so that the date after the last value for "WorkDate" is populated in field "cboStartDate". If no date exists in the table then it defaults to 01/03/05. I've tried using MAX function to get the date, but then I have trouble assigning the table in SQL because it depends on the user that is in the database. Any ideas?

 

[Remou]

Hi Roblin
I haven't quite caught what you want:

date after the last value for "WorkDate" is populated in field "cboStartDate".

Bit hazy on this. You seem to have a defualt date set up already ("If no date exists in the table then it defaults to 01/03/05"), so do you mean that you want to find the last date entered in Workdate and put it in a combobox "cboStartDate"?

 

[Remou]

Hi
Sorry about that last post. Not quite awake! You have the name of the table as tblUserName as the record source, right? So:

"SELECT Max([WorkDate]) AS Blah FROM " & Me.RecordSource & ""

I think that is it - haven't tested.

 

[Roblin]

Hi Remou,

Yeah, while writing that I confused myself. One of the fields that is in the tables on the backend for each user is called "WorkDate". It is the primary key. I have a combo box that triggered a pop up calendar that allowed the user to choose a date. The combo box is cboStartDate. I disabled the calendar an no longer want to use it. I'll change the combo box to a text box later. After the user enters data for the first time and closes the database, I would it so when they come back 2 weeks later to enter data again, the day after the last date they entered is populated in cboStartDate. This will help prevent them from missing a date. This database is tracking employees activities by the function they perform on audits by increments of half days. I have it now so that when they hit a cmd button to advance to the next record (date) weekends are skipped. I'd like to have that happen also when they re-open the database. Instead of the day after the last date they entered, the non-weekend date is populated in cboStartDate. I hope this explains it better. Thanks.

 

[Remou]

Hi
If your RecordSource is a table, you could try:
[tt]Me!txtStartDate = DMax("WorkDate", "" & Me.RecordSource & "")[/tt]

You will have to fiddle around a bit to match up with your workday selection.

 

[Roblin]

I have the code looking like this:

Dim LDate as Date
DoCmd.RunSQL ("SELECT Max([WorkDate]) AS LDate FROM " & Me.RecordSource & "")
MsgBox LDate
If LDate = Null Then
cboStartDate = "01/03/05"
Else
cboStartDate = LDate
End If

It's returning a date of Saturday, December 31, 1899. The msgbox returns 12:00AM. I've tried pound signs around LDate in the SQL statement, & "Dim LDate" - by itself. If I put msgbox ME.Recordsource in place of msgbox LDate it does return the correct table. What am I doing wrong?

 

[Roblin]

Wow, that one line of code did what I needed. It works perfectly. I just set the default to 01/03/05 and added that single line of code and it works like a charm. Thanks again.

 

[Remou]

Hi
I don't think you can use RunSQL quite like that. Either try the second option I suggested (above) or try this:

Dim rs As Recordset
Dim LDate as Date
Set rs = CurrentDB.OpenRecordset("SELECT Max([WorkDate]) AS LDate FROM " & Me.RecordSource)
If rs!LDate = Null Then 'Bit unsure about this line
  cboStartDate = "01/03/05" 'Hope this is a text field
Else
  cboStartDate = rs!LDate
End If

But I think
[tt]Me!cboStartDate = DMax("WorkDate", "" & Me.RecordSource & "")[/tt]
Or
[tt]Me!cboStartDate = Nz(DMax("WorkDate", "" & Me.RecordSource & ""),"01/03/05")[/tt]

Is prettier!

 

[Roblin]

Hey Remou,

I've been using this:

Me!cboStartDate = DMax("WorkDate", "" & Me.RecordSource & "") + 1

and it works good. Is there a way to get it to skip weekends? If the last date is a friday, it adds one to make the new date Saturday. I could add 3 and that works, but if the last date is a wednesday, the new date is a Friday. Any ideas?

 

[Remou]

Hi
This is a Microsoft Article:

http://support.microsoft.com/kb/210064/EN-US/

It has a function for determining weekdays and holidays. You might find it useful. It uses the built-in Weekday function.

 

[Roblin]

Thanks for the pointer Remou. I got it to work with this:

Dim BeginDate As Date
BeginDate = DMax("WorkDate", "" & Me.RecordSource & "")

If Weekday(BeginDate) = 7 Then
Me!cboStartDate = DMax("WorkDate", "" & Me.RecordSource & "") + 2

ElseIf Weekday(BeginDate) = 6 Then
Me!cboStartDate = DMax("WorkDate", "" & Me.RecordSource & "") + 3

Else
Me!cboStartDate = DMax("WorkDate", "" & Me.RecordSource & "") + 1

End If

Thanks again for all your help.

 

[PHV]

Some optimisation:
If Weekday(BeginDate) = 7 Then
Me!cboStartDate = BeginDate + 2
ElseIf Weekday(BeginDate) = 6 Then
Me!cboStartDate = BeginDate + 3
Else
Me!cboStartDate = BeginDate + 1
End If


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244