If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
and for Date Search
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 *Whe True and the same for the end date However it dose not work... What am I doing wrong? THanks for the help |
#2
|
|||
|
|||
and for Date Search
GregB wrote:
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 *Whe 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 -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
and for Date Search
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 "GregB" wrote: 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 *Whe True and the same for the end date However it dose not work... What am I doing wrong? THanks for the help |
#4
|
|||
|
|||
and for Date Search
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?? "Marshall Barton" wrote: GregB wrote: 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 *Whe 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 -- Marsh MVP [MS Access] |
#5
|
|||
|
|||
and for Date Search
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 "KARL DEWEY" wrote: 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 "GregB" wrote: 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 *Whe True and the same for the end date However it dose not work... What am I doing wrong? THanks for the help |
#6
|
|||
|
|||
and for Date Search
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 GregB wrote: 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 *Whe True and the same for the end date However it dose not work... What am I doing wrong? THanks for the help |
#7
|
|||
|
|||
and for Date Search
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])) -- KARL DEWEY Build a little - Test a little |
#8
|
|||
|
|||
and for Date Search
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] GregB wrote: 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?? "Marshall Barton" wrote: GregB wrote: 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 *Whe 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 |
#9
|
|||
|
|||
and for Date Search
hThank you all for helping, I followed what KARL WROTE and everything works
greatly! Awsome, THanks a bunch! "KARL DEWEY" wrote: 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])) -- KARL DEWEY Build a little - Test a little |
Thread Tools | |
Display Modes | |
|
|