I suppose you could say that it's because of the fact that strCriteria is a
string.
While internally dates are numbers (eight byte floating points, where the
integer portion represents the date as the number of days relative to 30
Dec, 1899 and the decimal portion represents the time as a fraction of a
day), if you concatenate a date value to a string, you'll going to get the
date written out as a date, using whatever Short Date format has been
defined through Regional Settings. If that format happens to be
dd/mm/yyyy, for 01 May, 2009 you'd get
[DateField] = 01/05/2009
When dealing with date values in Where clauses, they need to be delimited
with octothorpes (#), and the date must be formatted in a manner that
Access will recognize. Generally, that means mm/dd/yyyy format or
yyyy-mm-dd format, although other unambiguous formats could be used (dd
mmm yyyy). Even if you use
strCriteria = "[DateField] = #" & datYourVariable & "#"
so that you have
[DateField] = #01/05/2009#
it won't work. Regardless of the fact that the Short Date format is set to
dd/mm/yyyy, Access will treat that as 05 January, 2009.
That's the reason for using an explicit format.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
BruceM said:
Is that because strCriteria is a string variable, so the date variable
needs to be converted to a string value? If so, why that particular
formatting? I looked back through the thread, but could not find anything
specific about the date variable other than that it was global.
Douglas J. Steele said:
Not quite, Bruce.
strCriteria = "[DateField] = " & Format(datYourVariable,
"\#yyyy\-mm\-dd\#")
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
"BruceM" <bamoob_at_yawhodotcalm.not> wrote in message
You can use the date variable as the criteria:
Private Sub cmdFindContactName_Click()
Dim rst As DAO.Recordset
Dim strCriteria As String
strCriteria = "[DateField] = " & datYourVariable
Set rst = Me.RecordsetClone
rst.FindFirst strCriteria
If rst.NoMatch Then
MsgBox "No entry found.", vbInformation
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End Sub
Are you trying to format a control on one form based on a value on
another form? Is there any connection between the data on one form and
the data on the other one? Please decribe in plain language, without
database code or terminology, exactly what you are trying to
accomplish.
Bruce,
the way in which i want to determine which record to find is by using
the
value stored in the global variable 'Dates2' this will be a date, i
then want
to pull up the record which has the matching date in the 'Date Of
Appointment' field and then apply the formatting to that field
I understand that your saying i should locate the record first and
then apply
the formatting code that i know works however as the record is on a
different
form im not sure how to go about this, i.e. i guess 'Me.recordset' etc
wont
work?
Lee
BruceM wrote:
As Douglas said, the code you are trying is not the way to use
Bookmark. My
suggestion was to find the record. If the formatting is working now,
it
should work when you find the record. Do not attempt to find the
record and
apply formatting at the same time.
The sample code provides the syntax using an input box:
********************
Private Sub cmdFindContactName_Click()
Dim rst As DAO.Recordset
Dim strCriteria As String
strCriteria = "[ContactName] Like '*" & InputBox("Enter the " _
& "first few letters of the name to find") & "*'"
Set rst = Me.RecordsetClone
rst.FindFirst strCriteria
If rst.NoMatch Then
MsgBox "No entry found.", vbInformation
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End Sub
********************
You can use another way to select the criteria by which you would find
the
record, but the only way I or anybody else can help is to know what
that is.
How are you determining which record to find? Text box? Combo box?
Bruce,
[quoted text clipped - 60 lines]
thanks
Lee
