< and > for Date Search

[GregB]

I have 2 unbound text box son my form that I want users to be able to input
a date and my subform to pupulate with dates that or after the first text box
(start date) and before the second textbox (end date)

I want users to have the ability to enter one, both or niether. There is
also other search criter on the form that can be selected.

In my query buiulder I tried putting this for the dates

Format([Invoice.Date of Order],"Short
Date")<=Format([Forms]![AdvanceSearch]![Date1],"Short Date") Or
[Forms]![AdvanceSearch]![Date1] Is Null
*Unchecked
*Where: True

and the same for the end date

However it dose not work... What am I doing wrong?

THanks for the help

 

[Marshall Barton]

I have 2 unbound text box son my form that I want users to be able to input
a date and my subform to pupulate with dates that or after the first text box
(start date) and before the second textbox (end date)

I want users to have the ability to enter one, both or niether. There is
also other search criter on the form that can be selected.

In my query buiulder I tried putting this for the dates

Format([Invoice.Date of Order],"Short
Date")<=Format([Forms]![AdvanceSearch]![Date1],"Short Date") Or
[Forms]![AdvanceSearch]![Date1] Is Null
*Unchecked
*Where: True

and the same for the end date

However it dose not work... What am I doing wrong?

The Format function always returns a text value, which would
be useless for what you want to do. Just compare the date
values.

It also looks like you have incorrect [ ] around the order
date field.

[Invoice].[Date of Order] <= [Forms]![AdvanceSearch]![Date1]
Or [Forms]![AdvanceSearch]![Date1] Is Null

 

[GregB]

I tried your solution Marshall but it did not work, The textbox controls do
not afffect the query data.

I don't understand why this won't work? I have compared dates before using
the diff date function.....

What do you think is going on??

I have 2 unbound text box son my form that I want users to be able to input
a date and my subform to pupulate with dates that or after the first text box
(start date) and before the second textbox (end date)

I want users to have the ability to enter one, both or niether. There is
also other search criter on the form that can be selected.

In my query buiulder I tried putting this for the dates

Format([Invoice.Date of Order],"Short
Date")<=Format([Forms]![AdvanceSearch]![Date1],"Short Date") Or
[Forms]![AdvanceSearch]![Date1] Is Null
*Unchecked
*Where: True

and the same for the end date

However it dose not work... What am I doing wrong?

The Format function always returns a text value, which would
be useless for what you want to do. Just compare the date
values.

It also looks like you have incorrect [ ] around the order
date field.

[Invoice].[Date of Order] <= [Forms]![AdvanceSearch]![Date1]
Or [Forms]![AdvanceSearch]![Date1] Is Null

 

[GregB]

Hey Karl, I tired your solution (I had to add an extra ) at the end) but it
does not work. No records are returned by with the controls empty and with
the controls populated
I don't understand why I can't use the <, >, = when comparing dates... I
have done t before in a query, but it does not seem to work when comparing to
a control..
I am really confused...

Thanks for the help

Try this --
[Invoice.Date of Order] Between CVDate([Forms]![AdvanceSearch]![Date1]) AND
IIF([Forms]![AdvanceSearch]![Date2] Is Null, #12/31/9999#,
CVDate([Forms]![AdvanceSearch]![Date2])

--
KARL DEWEY
Build a little - Test a little

I have 2 unbound text box son my form that I want users to be able to input
a date and my subform to pupulate with dates that or after the first text box
(start date) and before the second textbox (end date)

I want users to have the ability to enter one, both or niether. There is
also other search criter on the form that can be selected.

In my query buiulder I tried putting this for the dates

Format([Invoice.Date of Order],"Short
Date")<=Format([Forms]![AdvanceSearch]![Date1],"Short Date") Or
[Forms]![AdvanceSearch]![Date1] Is Null
*Unchecked
*Where: True

and the same for the end date

However it dose not work... What am I doing wrong?

THanks for the help

 

[John Spencer]

If Invoice.Date of Order always has a value (or if you don't want to show
records where the date is null) you can use

Try this one first and if it works for you - good.
Field: [Date of Order]
Table: Invoice
Criteria: >= CDate(Nz(Forms!AdvanceSearch!Date1,#100/1/1#)) and
<=CDate(Nz(Forms!AdvanceSearch!Date2,#3999/12/31#))

IF you also want to get records where {Date of Order] is null

Criteria: (>=CDate(Forms!AdvanceSearch!Date1) OR Forms!AdvanceSearch!Date1)
AND (<=CDate(Forms!AdvanceSearch!Date2) or Forms!AdvanceSearch!Date2 is null)

When you save the query and later reopen it, Access will rearrange the
criteria. The rearrangement can make the query to complex to run. If it does
but you still need null dates returned, you can try

Field: CDate(Nz([Date of Order],Date()))
Table: Invoice
Criteria: >= CDate(Nz(Forms!AdvanceSearch!Date1,#100/1/1#)) and
<=CDate(Nz(Forms!AdvanceSearch!Date2,#3999/12/31#))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

I don't understand why I can't use the <, >, = when comparing dates... I
You can, I like to use Between.

[Invoice.Date of Order] Between IIF([Forms]![AdvanceSearch]![Date1] Is Null,
#12/31/1900#, CVDate([Forms]![AdvanceSearch]![Date1])) AND
IIF([Forms]![AdvanceSearch]![Date2] Is Null, #12/31/9999#,
CVDate([Forms]![AdvanceSearch]![Date2]))

[Invoice.Date of Order] >= IIF([Forms]![AdvanceSearch]![Date1] Is Null,
#12/31/1900#, CVDate([Forms]![AdvanceSearch]![Date1])) AND
<= IIF([Forms]![AdvanceSearch]![Date2] Is Null, #12/31/9999#,
CVDate([Forms]![AdvanceSearch]![Date2]))

 

[Marshall Barton]

Since I can't see what you actually did nor any details of
what happened, I don't have a clue why "it did not work".

Double check the table, field, form and control names and
post a Copy/Paste of your query's SQL statement along with a
detailed description of what happened when you tried it.
--
Marsh
MVP [MS Access]

I tried your solution Marshall but it did not work, The textbox controls do
not afffect the query data.

I don't understand why this won't work? I have compared dates before using
the diff date function.....

What do you think is going on??

I have 2 unbound text box son my form that I want users to be able to input
a date and my subform to pupulate with dates that or after the first text box
(start date) and before the second textbox (end date)

I want users to have the ability to enter one, both or niether. There is
also other search criter on the form that can be selected.

In my query buiulder I tried putting this for the dates

Format([Invoice.Date of Order],"Short
Date")<=Format([Forms]![AdvanceSearch]![Date1],"Short Date") Or
[Forms]![AdvanceSearch]![Date1] Is Null
*Unchecked
*Where: True

and the same for the end date

However it dose not work... What am I doing wrong?

The Format function always returns a text value, which would
be useless for what you want to do. Just compare the date
values.

It also looks like you have incorrect [ ] around the order
date field.

[Invoice].[Date of Order] <= [Forms]![AdvanceSearch]![Date1]
Or [Forms]![AdvanceSearch]![Date1] Is Null

 

[GregB]

hThank you all for helping, I followed what KARL WROTE and everything works
greatly! Awsome, THanks a bunch!

You can, I like to use Between.

[Invoice.Date of Order] Between IIF([Forms]![AdvanceSearch]![Date1] Is Null,
#12/31/1900#, CVDate([Forms]![AdvanceSearch]![Date1])) AND
IIF([Forms]![AdvanceSearch]![Date2] Is Null, #12/31/9999#,
CVDate([Forms]![AdvanceSearch]![Date2]))

[Invoice.Date of Order] >= IIF([Forms]![AdvanceSearch]![Date1] Is Null,
#12/31/1900#, CVDate([Forms]![AdvanceSearch]![Date1])) AND
<= IIF([Forms]![AdvanceSearch]![Date2] Is Null, #12/31/9999#,
CVDate([Forms]![AdvanceSearch]![Date2]))