About Date/time type

[Yue Zhao]

I have a Date/Time type field, which is called "Date", in a table called
"tblHomePrac"
Then, in a form, I enter a date (it looks like "12/1/2006" because I had
the format of the textbox to be "shortdate"), and look up the table to
see whether the date I entered is already existing in the table.

The codes are attached at the bottom. If the date is existing,
intfound1=1. But I have never got intfound1=1 even whenI am sure the
date I entered is existing.

I guess that's something about the Date/Time type and the shortdate
format - but have no idea how to convert them to be comparable.

I am wondering anybody could help me out with that.

Thanks a lot!

P.S. my codes are below:

Dim strDateCode As Date
strDateCode = Me![Date]

Dim strSQL1 As String
strSQL1 = "SELECT tblHomePrac.Date FROM tblHomePrac WHERE
(date(tblHomePrac.Date) = " & strDateCode & ")"

Dim intFound1 As Integer
intFound1 = 0

Dim db1 As DAO.Database
Dim rs1 As DAO.Recordset

Set db1 = CurrentDb()
Set rs1 = db1.OpenRecordset(strSQL1)

Do While ((Not rs1.EOF) And intFound1 = 0)

If (rs1![Date] = strDateCode) Then
intFound1 = 1
End If
rs1.MoveNext

Loop
rs1.Close

If intFound1 = 1 Then
...
Else
...
End If

 

[John Nurick]

Hi,

Your code has got a bit confused. For example you start by trying to
open a recordset with a WHERE condition, and then you iterate through
the recordset looking for records that meet the condition.

One problem is in the SQL statement:

SELECT tblHomePrac.Date FROM tblHomePrac WHERE
(date(tblHomePrac.Date) = " & strDateCode & ")"

I'd expect this to raise an error, because the Date() function returns
the current date and doesn't take any arguments.

But you can do it more simply, with something like this:

strDateCode = "#" & Me.[Date] & "#" 'delimit date string
If IsNull (DLookup("[Date]", "tblHomePrac", _
"[Date] = " & CDate(strDateCode)) Then
intFound1 = False
Else
intFound2 = True
End If

By the way, it's usually a good idea to avoid using field and control
names that are also the names of common functions, such as "Date",
"Month", "Year". I'd change "Date" to something like "EntryDate" or
"DateSent", whatever's relevant.

I have a Date/Time type field, which is called "Date", in a table called
"tblHomePrac"
Then, in a form, I enter a date (it looks like "12/1/2006" because I had
the format of the textbox to be "shortdate"), and look up the table to
see whether the date I entered is already existing in the table.

The codes are attached at the bottom. If the date is existing,
intfound1=1. But I have never got intfound1=1 even whenI am sure the
date I entered is existing.

I guess that's something about the Date/Time type and the shortdate
format - but have no idea how to convert them to be comparable.

I am wondering anybody could help me out with that.

Thanks a lot!

P.S. my codes are below:

Dim strDateCode As Date
strDateCode = Me![Date]

Dim strSQL1 As String
strSQL1 = "SELECT tblHomePrac.Date FROM tblHomePrac WHERE
(date(tblHomePrac.Date) = " & strDateCode & ")"

Dim intFound1 As Integer
intFound1 = 0

Dim db1 As DAO.Database
Dim rs1 As DAO.Recordset

Set db1 = CurrentDb()
Set rs1 = db1.OpenRecordset(strSQL1)

Do While ((Not rs1.EOF) And intFound1 = 0)

If (rs1![Date] = strDateCode) Then
intFound1 = 1
End If
rs1.MoveNext

Loop
rs1.Close

If intFound1 = 1 Then
...
Else
...
End If