Accesing a record via a date primary key

  • Thread starter Thread starter Carlos Saez
  • Start date Start date
C

Carlos Saez

I'm new to Access and have dificulties trying to check whether a record
exists or not, based on its primary key that is a date field.

The user enters a date in a date formatted field in a form, and the code
tries to check whether a record exists with that key.

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] = " & DateEntered)

I allways get DateExists as Null.Getting into debugging, I know that
DataEntered has a value that exists into the table and formatted properly
(dd/mm/yyyy in my case).

The sentence:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] =
dateserial(2005,1,6)")

gets a proper (not null) result. That is a value that exists into the table.

Please, some help.

Regards,

Carlos.
 
Delimit date strings with # character:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] = #" & DateEntered &
"#")
 
I've also tried that, even with a constant:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] = #06/01/2005#")

No way.
Any idea?
Carlos.


Ken Snell said:
Delimit date strings with # character:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] = #" & DateEntered &
"#")

--

Ken Snell
<MS ACCESS MVP>



Carlos Saez said:
I'm new to Access and have dificulties trying to check whether a record
exists or not, based on its primary key that is a date field.

The user enters a date in a date formatted field in a form, and the code
tries to check whether a record exists with that key.

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] = " & DateEntered)

I allways get DateExists as Null.Getting into debugging, I know that
DataEntered has a value that exists into the table and formatted properly
(dd/mm/yyyy in my case).

The sentence:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] =
dateserial(2005,1,6)")

gets a proper (not null) result. That is a value that exists into the
table.

Please, some help.

Regards,

Carlos.
Click to expand...
Click to expand...
 
What is your regional setting for dates? I'm betting that it's dd/mm/yyyy.

Use the Format function to convert the date string to US format, which is
the format that Jet expects:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] = #" &
Format([DateEntered], "mm/dd/yyyy") & "#")

--

Ken Snell
<MS ACCESS MVP>


Carlos Saez said:
I've also tried that, even with a constant:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] = #06/01/2005#")

No way.
Any idea?
Carlos.


Ken Snell said:
Delimit date strings with # character:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] = #" & DateEntered
& "#")

--

Ken Snell
<MS ACCESS MVP>



Carlos Saez said:
I'm new to Access and have dificulties trying to check whether a record
exists or not, based on its primary key that is a date field.

The user enters a date in a date formatted field in a form, and the code
tries to check whether a record exists with that key.

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] = " & DateEntered)

I allways get DateExists as Null.Getting into debugging, I know that
DataEntered has a value that exists into the table and formatted
properly (dd/mm/yyyy in my case).

The sentence:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] =
dateserial(2005,1,6)")

gets a proper (not null) result. That is a value that exists into the
table.

Please, some help.

Regards,

Carlos.
Click to expand...
Click to expand...
Click to expand...
 
How is the field [MyDate] being populated? If it's with the Now() function,
then it also includes Time, and so will not equal just a date.

You can use either:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] Between #" &
DateEntered & "# And #" & DateAdd("d", 1, DateEntered) & "#")

or

DateExists = DLookup("[MyDate]", "MyTable", "DateValue([MyDate]) = #" &
DateEntered & "#")

Note, though, that this will only work if they've entered the date in
mm/dd/yyyy format. If that's not what they're providing, you'll need to
ensure that you've translated the date to that format.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Carlos Saez said:
I've also tried that, even with a constant:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] = #06/01/2005#")

No way.
Any idea?
Carlos.


Ken Snell said:
Delimit date strings with # character:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] = #" & DateEntered
& "#")

--

Ken Snell
<MS ACCESS MVP>



Carlos Saez said:
I'm new to Access and have dificulties trying to check whether a record
exists or not, based on its primary key that is a date field.

The user enters a date in a date formatted field in a form, and the code
tries to check whether a record exists with that key.

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] = " & DateEntered)

I allways get DateExists as Null.Getting into debugging, I know that
DataEntered has a value that exists into the table and formatted
properly (dd/mm/yyyy in my case).

The sentence:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] =
dateserial(2005,1,6)")

gets a proper (not null) result. That is a value that exists into the
table.

Please, some help.

Regards,

Carlos.
Click to expand...
Click to expand...
Click to expand...
 
You are right. That solved the problem.
Thank you very much.
Carlos.


Ken Snell said:
What is your regional setting for dates? I'm betting that it's dd/mm/yyyy.

Use the Format function to convert the date string to US format, which is
the format that Jet expects:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] = #" &
Format([DateEntered], "mm/dd/yyyy") & "#")

--

Ken Snell
<MS ACCESS MVP>


Carlos Saez said:
I've also tried that, even with a constant:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] = #06/01/2005#")

No way.
Any idea?
Carlos.


Ken Snell said:
Delimit date strings with # character:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] = #" & DateEntered
& "#")

--

Ken Snell
<MS ACCESS MVP>



I'm new to Access and have dificulties trying to check whether a record
exists or not, based on its primary key that is a date field.

The user enters a date in a date formatted field in a form, and the
code tries to check whether a record exists with that key.

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] = " &
DateEntered)

I allways get DateExists as Null.Getting into debugging, I know that
DataEntered has a value that exists into the table and formatted
properly (dd/mm/yyyy in my case).

The sentence:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] =
dateserial(2005,1,6)")

gets a proper (not null) result. That is a value that exists into the
table.

Please, some help.

Regards,

Carlos.
Click to expand...
Click to expand...
Click to expand...
 
Thank you very much for your advice.
Carlos.



Douglas J. Steele said:
How is the field [MyDate] being populated? If it's with the Now()
function, then it also includes Time, and so will not equal just a date.

You can use either:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] Between #" &
DateEntered & "# And #" & DateAdd("d", 1, DateEntered) & "#")

or

DateExists = DLookup("[MyDate]", "MyTable", "DateValue([MyDate]) = #" &
DateEntered & "#")

Note, though, that this will only work if they've entered the date in
mm/dd/yyyy format. If that's not what they're providing, you'll need to
ensure that you've translated the date to that format.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Carlos Saez said:
I've also tried that, even with a constant:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] = #06/01/2005#")

No way.
Any idea?
Carlos.


Ken Snell said:
Delimit date strings with # character:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] = #" & DateEntered
& "#")

--

Ken Snell
<MS ACCESS MVP>



I'm new to Access and have dificulties trying to check whether a record
exists or not, based on its primary key that is a date field.

The user enters a date in a date formatted field in a form, and the
code tries to check whether a record exists with that key.

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] = " &
DateEntered)

I allways get DateExists as Null.Getting into debugging, I know that
DataEntered has a value that exists into the table and formatted
properly (dd/mm/yyyy in my case).

The sentence:

DateExists = DLookup("[MyDate]", "MyTable", "[MyDate] =
dateserial(2005,1,6)")

gets a proper (not null) result. That is a value that exists into the
table.

Please, some help.

Regards,

Carlos.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top