Date Value from string

[flect]

I'm trying to set up my continuous form so that the default date for a new record is 1 week after the last one:

so far my approach has been:

Private Sub Form_Current()
Dim strSql As String

strSql = "SELECT TOP 1 DateAdd('d',7,[wagedate]) AS NewDate " & _
"FROM tblWagebook " & _
"WHERE (((tblWagebook.StaffID)=[forms]![frmwagebook]![cmbname])) " & _
"ORDER BY tblWagebook.WageDate DESC;"

If Me.NewRecord = True Then
Me.WageDate.Value = strSql

End If

The SQL works fine on it's own and returns the correct value - however I am not having much success applying the value to the proper txt field.

I'm getting error 2113 - The Value you entered is not valid for this field.


I'm guessing that this might be a syntax error - or I may have just totally gone about it the wrong way.

Thanks in advance!

 

[Galaxiom]

Can't use SQL to enter a value in a control.
Use a Lookup:
=DMax("WageDate", "tblWageBook", "tblWagebook.StaffID=[forms]![frmwagebook]![cmbname]")+7

 

[flect]

Thanks Galaxiom!!! - Perfect solution!