date range selection and verify

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to change the date on selected records.

I wish to have a query that selects a date range (input by user) and then
verifies with that user that the range is correct, then input a single date
to update all records within that range to.

I understand the BETWEEN function and how to update the records, but I am
unable to figure how to validate date range before updating.

Any and all help is appreciated.
 
The safest way to do this would be a form with 3 unbound text boxes and a
command button.

The first 2 boxes are the From and To dates.
The 3rd is for the date to change the value to.
The command button performs the validation, and executes the update.

Set the Format property of the 3 unbound text boxes to Short Date or
similar. Access then accepts only valid dates.

The command button will build a string such as:
Dim strSql As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"
If IsDate(Me.txtFrom) And IsDate(Me.txtTo), and IsDate(Me.txtNew) Then
'Whatever validation code you want here.
strSql = "UPDATE Table1 SET MyDate = " & _
Format(Me.txtNew, strcJetDate) & " WHERE MyDate Between " & _
Format(Me.txtFrom, strcJetDate) & " And " & _
Format(Me.txtTo, strcJetDate) & ";"
dbEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "All 3 dates required."
End If

For an explanation of why to use the Execute method rather than RunSQL, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html
 
Thanks. I will give that a go as soon as possible.

Fair warning: I may be posting a followup.

--
Thanks for your help,
Chris


Allen Browne said:
The safest way to do this would be a form with 3 unbound text boxes and a
command button.

The first 2 boxes are the From and To dates.
The 3rd is for the date to change the value to.
The command button performs the validation, and executes the update.

Set the Format property of the 3 unbound text boxes to Short Date or
similar. Access then accepts only valid dates.

The command button will build a string such as:
Dim strSql As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"
If IsDate(Me.txtFrom) And IsDate(Me.txtTo), and IsDate(Me.txtNew) Then
'Whatever validation code you want here.
strSql = "UPDATE Table1 SET MyDate = " & _
Format(Me.txtNew, strcJetDate) & " WHERE MyDate Between " & _
Format(Me.txtFrom, strcJetDate) & " And " & _
Format(Me.txtTo, strcJetDate) & ";"
dbEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "All 3 dates required."
End If

For an explanation of why to use the Execute method rather than RunSQL, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html
Click to expand...
 
Back
Top