Search records on form with similar dates

[Guest]

How do i enclose the date in my code e.g, if searching for something that is
of a number "[FieldID] =" & Str(Nz(Me![Controlsource], 0)) What about the
date?

 

[Larry Linson]

How do i enclose the date in my code e.g, if searching for something that
is
of a number "[FieldID] =" & Str(Nz(Me![Controlsource], 0)) What about the
date?

Except in the text of your message, I see nothing related to date. Could you
clarify whether this was just an example of something else, or did you
intend that the date be somehow related?

Larry Linson
Microsoft Access MVP

 

[Guest]

It was just an example, this what I would like to do, I have unbound combox
in my form, it has all the dates in my table, so i would like to search for
all the records on my form that have the date that I selected from the
combobox

How do i enclose the date in my code e.g, if searching for something that
is
of a number "[FieldID] =" & Str(Nz(Me![Controlsource], 0)) What about the
date?

Except in the text of your message, I see nothing related to date. Could you
clarify whether this was just an example of something else, or did you
intend that the date be somehow related?

Larry Linson
Microsoft Access MVP

 

[Larry Linson]

You can build the SQL string, something like:

strSQL = "SELECT * FROM mytable WHERE [thedatetoselect] = #" &
Me!txtDateToFind & """"

or put the following in the Criteria line under Field "thedatetoselect" in
the Query builder:

[txtDateToFind]

but you may have to fully qualify the Control name, as in
Forms![yourformname]![txtDateToFind].

You can use the BETWEEN operator in SQL to select dates that fall between a
start and stop date, too...

Larry Linson
Microsoft Access MVP

It was just an example, this what I would like to do, I have unbound
combox
in my form, it has all the dates in my table, so i would like to search
for
all the records on my form that have the date that I selected from the
combobox

How do i enclose the date in my code e.g, if searching for something
that
is
of a number "[FieldID] =" & Str(Nz(Me![Controlsource], 0)) What about
the
date?

Except in the text of your message, I see nothing related to date. Could
you
clarify whether this was just an example of something else, or did you
intend that the date be somehow related?

Larry Linson
Microsoft Access MVP

 

[Guest]

Thanks, I am trying to do what you have done and this is what I had, I used
the combobox wizard and put it on the form the line I would like incorporate
what you have done is this line

rs.FindFirst "[Date] = #" & Str(Nz(Me![Combo134], 0)) & "#"

it only woks if I select once, if try to select the 2nd time, it gives me an
error on that line Run-time error 13 type mismatch....

You can build the SQL string, something like:

strSQL = "SELECT * FROM mytable WHERE [thedatetoselect] = #" &
Me!txtDateToFind & """"

or put the following in the Criteria line under Field "thedatetoselect" in
the Query builder:

[txtDateToFind]

but you may have to fully qualify the Control name, as in
Forms![yourformname]![txtDateToFind].

You can use the BETWEEN operator in SQL to select dates that fall between a
start and stop date, too...

Larry Linson
Microsoft Access MVP

It was just an example, this what I would like to do, I have unbound
combox
in my form, it has all the dates in my table, so i would like to search
for
all the records on my form that have the date that I selected from the
combobox

How do i enclose the date in my code e.g, if searching for something
that
is
of a number "[FieldID] =" & Str(Nz(Me![Controlsource], 0)) What about
the
date?

Except in the text of your message, I see nothing related to date. Could
you
clarify whether this was just an example of something else, or did you
intend that the date be somehow related?

Larry Linson
Microsoft Access MVP

 

[John Vinson]

Thanks, I am trying to do what you have done and this is what I had, I used
the combobox wizard and put it on the form the line I would like incorporate
what you have done is this line

rs.FindFirst "[Date] = #" & Str(Nz(Me![Combo134], 0)) & "#"

it only woks if I select once, if try to select the 2nd time, it gives me an
error on that line Run-time error 13 type mismatch....

0 isn't an appropriate NZ value: #0# isn't a valid date! Whatever the
combo returns should be in a mm/dd/yy or mm/dd/yyyy date format. Try

rs.FindFirst "[Date] = #" & Str(Nz(Me![Combo134], #1/1/1800#)) & "#"

assuming that the contents of the combo are formatted correctly.

John W. Vinson[MVP]

 

[Guest]

Thanks for the reply, that worked perfect!

Thanks, I am trying to do what you have done and this is what I had, I used
the combobox wizard and put it on the form the line I would like incorporate
what you have done is this line

rs.FindFirst "[Date] = #" & Str(Nz(Me![Combo134], 0)) & "#"

it only woks if I select once, if try to select the 2nd time, it gives me an
error on that line Run-time error 13 type mismatch....

0 isn't an appropriate NZ value: #0# isn't a valid date! Whatever the
combo returns should be in a mm/dd/yy or mm/dd/yyyy date format. Try

rs.FindFirst "[Date] = #" & Str(Nz(Me![Combo134], #1/1/1800#)) & "#"

assuming that the contents of the combo are formatted correctly.

John W. Vinson[MVP]