FindFirst not recognizing dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris 952 - 30 Nov 2005 14:43 GMT

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.

Reply to this Message

Allen Browne - 30 Nov 2005 14:58 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I am an Access novice and any help is greatly appreciated.
>
[quoted text clipped - 37 lines]
>
> Thanks.

Reply to this Message

Douglas J Steele - 30 Nov 2005 15:01 GMT

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)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> I am an Access novice and any help is greatly appreciated.
>
[quoted text clipped - 33 lines]
>
> Thanks.

Reply to this Message

Chris 952 - 30 Nov 2005 15:21 GMT

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
[quoted text clipped - 49 lines]
> >
> > Thanks.

Reply to this Message