FindFirst not recognizing dates

[Chris 952]

I am an Access novice and any help is greatly appreciated.

In the form I'm creating, I want certain fields to be entered into a table
when a Save button is clicked. If the data has not been saved already, I
want it to create a new record in the table. If the record already exists,
I'd like the record edited with the changes made in the form.

The two fields which identify a record are the Date and the Forecast Group
combination. I've tried to use FindFirst to determine whether a record is
new, but for some reason it doesn't 'find' the date (it has no problem with
the Forecast Group). The formatting in both the form and the table are set
on 'Short Date'. Here is the code:

Set rs = db.OpenRecordset("tbl_Remarks", dbOpenDynaset)
' Determine whether this Date-Forecast Group combination is in the table
rs.FindFirst "[Forecast_GroupID] = " & Me.Forecast_Group & "AND
[Date] = " & Me.Date

If rs.NoMatch Then ' this item has not been added
rs.AddNew
rs!Date = Me.Date
rs!Forecast_GroupID = Me.Forecast_Group
rs!Remarks = Me.Intraday_Event1
rs.Update
Else
rs.Edit
rs!Date = Me.Date
rs!Forecast_GroupID = Me.Forecast_Group
rs!Remarks = Me.Intraday_Event1
rs.Update

End If

If anybody has any ideas as to why the Access isn't reading the Date as
being a match, please let me know.

Thanks.

 

[Allen Browne]

Potential Issues:
1. Date is a reserved word. Rename the field to (say) ForecastDate.

2. The literal date value needs to be delimited with # in the FindFirst
string.

3. The format of the date in the string matters if your regional settings
are not US.

4. You need spaces between your words.

5. The string will be mal-formed if the controls are null.

6. It's easier to see what's wrong if you use a string variable.

Try this approach:

Dim strWhere As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If IsNull(Me.Forecast_Group) Or IsNull(Me.ForecastDate) Then
MsgBox "Both required."
Else
strWhere = "([Forecast_GroupID] = " & Me.Forecast_Group & _
") AND ([ForecastDate] = " & Format(Me.ForecastDate, strcJetDate) & ")"

'Debug.Print strWhere
rs.FindFirst strWhere

 

[Douglas J Steele]

Dates need to be delimited with # characters, and should be in mm/dd/yyyy
format, regardless of what your Regional Settings may have the short date
format as. The format string I use below accomplishes both of these
requirements.

rs.FindFirst "[Forecast_GroupID] = " & Me.Forecast_Group & _
"AND [Date] = " & Format(Me.Date, "\#mm\/dd\/yyyy\#")

(FWIW, you should consider renaming your table field from Date. That's a
reserved word)

 

[Chris 952]

Thanks, Douglas. The code work perfectly. I will rename the table field.

Dates need to be delimited with # characters, and should be in mm/dd/yyyy
format, regardless of what your Regional Settings may have the short date
format as. The format string I use below accomplishes both of these
requirements.

rs.FindFirst "[Forecast_GroupID] = " & Me.Forecast_Group & _
"AND [Date] = " & Format(Me.Date, "\#mm\/dd\/yyyy\#")

(FWIW, you should consider renaming your table field from Date. That's a
reserved word)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am an Access novice and any help is greatly appreciated.

In the form I'm creating, I want certain fields to be entered into a table
when a Save button is clicked. If the data has not been saved already, I
want it to create a new record in the table. If the record already exists,
I'd like the record edited with the changes made in the form.

The two fields which identify a record are the Date and the Forecast Group
combination. I've tried to use FindFirst to determine whether a record is
new, but for some reason it doesn't 'find' the date (it has no problem with
the Forecast Group). The formatting in both the form and the table are set
on 'Short Date'. Here is the code:

Set rs = db.OpenRecordset("tbl_Remarks", dbOpenDynaset)
' Determine whether this Date-Forecast Group combination is in the table
rs.FindFirst "[Forecast_GroupID] = " & Me.Forecast_Group & "AND
[Date] = " & Me.Date

If rs.NoMatch Then ' this item has not been added
rs.AddNew
rs!Date = Me.Date
rs!Forecast_GroupID = Me.Forecast_Group
rs!Remarks = Me.Intraday_Event1
rs.Update
Else
rs.Edit
rs!Date = Me.Date
rs!Forecast_GroupID = Me.Forecast_Group
rs!Remarks = Me.Intraday_Event1
rs.Update

End If

If anybody has any ideas as to why the Access isn't reading the Date as
being a match, please let me know.

Thanks.