Date in VB - Banging my Head on my desk!!!!

[Guest]

I am in need of some serious assistance!! I cannot figure out how to
indicate the date in VB. What I am trying to do is search based on a date.
The user inputs a date on a form and I'm trying to search for that date by
using VB code. It is searching an unbound text box (set for medium date).
The code does not return any error, it just indicates that there is no match.
I know it is because of the VB date issue. Here is a part of the code that
looks up the date:

ElseIf IsNull([Surname]) And IsNull([GivenName]) And (Not
IsNull([DateOfBirth]) Then
DoCmd.RunSQL "insert into tblTemNameSearch select tblContactInformation.*
from tblContactInformation where " _
& "dtDateOfBirth = " & [DateOfBirth]

I have absolutely no idea on how I can indicate the date etc....Begging for
help lol

Regards

 

[Paul Overway]

You need to enclose the date in # symbols, i.e.,

DoCmd.RunSQL "insert into tblTemNameSearch select tblContactInformation.*
from tblContactInformation where " _
& "dtDateOfBirth = #" & [DateOfBirth] & "#"

 

[Guest]

THANK YOU PAUL!!!! It worked...Can't believe it was something that
simple....Another question since I know I will run into problems...Any idea
on how you search between two dates??

Thank you in advance.

You need to enclose the date in # symbols, i.e.,

DoCmd.RunSQL "insert into tblTemNameSearch select tblContactInformation.*
from tblContactInformation where " _
& "dtDateOfBirth = #" & [DateOfBirth] & "#"


--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

I am in need of some serious assistance!! I cannot figure out how to
indicate the date in VB. What I am trying to do is search based on a
date.
The user inputs a date on a form and I'm trying to search for that date by
using VB code. It is searching an unbound text box (set for medium date).
The code does not return any error, it just indicates that there is no
match.
I know it is because of the VB date issue. Here is a part of the code
that
looks up the date:

ElseIf IsNull([Surname]) And IsNull([GivenName]) And (Not
IsNull([DateOfBirth]) Then
DoCmd.RunSQL "insert into tblTemNameSearch select tblContactInformation.*
from tblContactInformation where " _
& "dtDateOfBirth = " & [DateOfBirth]

I have absolutely no idea on how I can indicate the date etc....Begging
for
help lol

Regards

 

[Paul Overway]

The where clause would look like this:

[SomeDate] Between [SomeOtherDate] and [AnotherDate]

If any of the dates are being passed as part of a string, they need to be
wrapped in #s...if they are date fields in the underlying table, you don't
need the #s.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

THANK YOU PAUL!!!! It worked...Can't believe it was something that
simple....Another question since I know I will run into problems...Any
idea
on how you search between two dates??

Thank you in advance.

You need to enclose the date in # symbols, i.e.,

DoCmd.RunSQL "insert into tblTemNameSearch select tblContactInformation.*
from tblContactInformation where " _
& "dtDateOfBirth = #" & [DateOfBirth] & "#"


--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

I am in need of some serious assistance!! I cannot figure out how to
indicate the date in VB. What I am trying to do is search based on a
date.
The user inputs a date on a form and I'm trying to search for that date
by
using VB code. It is searching an unbound text box (set for medium
date).
The code does not return any error, it just indicates that there is no
match.
I know it is because of the VB date issue. Here is a part of the code
that
looks up the date:

ElseIf IsNull([Surname]) And IsNull([GivenName]) And (Not
IsNull([DateOfBirth]) Then
DoCmd.RunSQL "insert into tblTemNameSearch select
tblContactInformation.*
from tblContactInformation where " _
& "dtDateOfBirth = " & [DateOfBirth]

I have absolutely no idea on how I can indicate the date etc....Begging
for
help lol

Regards

 

[David C. Holley]

Add the isDate() function to test that the value in the box is a date.

Enclose the field value with # to indicate the value is a date

dtDateOfBirth = #" & [DateOfBirth] & "#"

And just to be certain the value is processed as a date CDate([DateofBirth])

Of course all of this is irrelevant since the Vogons arrive on the 29th
of this month, better grab your towel (http://hitchhikers.movies.go.com/)

David H

 

[Guest]

Thanks a lot Paul, It is working perfectly now...Thanks for your help...

The where clause would look like this:

[SomeDate] Between [SomeOtherDate] and [AnotherDate]

If any of the dates are being passed as part of a string, they need to be
wrapped in #s...if they are date fields in the underlying table, you don't
need the #s.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

THANK YOU PAUL!!!! It worked...Can't believe it was something that
simple....Another question since I know I will run into problems...Any
idea
on how you search between two dates??

Thank you in advance.

You need to enclose the date in # symbols, i.e.,

DoCmd.RunSQL "insert into tblTemNameSearch select tblContactInformation.*
from tblContactInformation where " _
& "dtDateOfBirth = #" & [DateOfBirth] & "#"


--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


I am in need of some serious assistance!! I cannot figure out how to
indicate the date in VB. What I am trying to do is search based on a
date.
The user inputs a date on a form and I'm trying to search for that date
by
using VB code. It is searching an unbound text box (set for medium
date).
The code does not return any error, it just indicates that there is no
match.
I know it is because of the VB date issue. Here is a part of the code
that
looks up the date:

ElseIf IsNull([Surname]) And IsNull([GivenName]) And (Not
IsNull([DateOfBirth]) Then
DoCmd.RunSQL "insert into tblTemNameSearch select
tblContactInformation.*
from tblContactInformation where " _
& "dtDateOfBirth = " & [DateOfBirth]

I have absolutely no idea on how I can indicate the date etc....Begging
for
help lol

Regards