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
|
|||
|
|||
Dates between
On an input form the user can query an item between 2 dates that they infill
in two unbound text boxes. At the moment in the date field of the query criteria field I use Between [forms].[main menu].[text18] And [forms].[main menu].[text20] As the name suggests it returns items "between" the dates in question how can I set it so that it includes BOTH dates listed. I have tried several permutations, I either get everything in the database returned or nothing at all. with thanks Bob |
#2
|
|||
|
|||
Dates between
=format([text20],"short date") and =format([Text18],"short date")
Rick B "Box 666" wrote in message ... On an input form the user can query an item between 2 dates that they infill in two unbound text boxes. At the moment in the date field of the query criteria field I use Between [forms].[main menu].[text18] And [forms].[main menu].[text20] As the name suggests it returns items "between" the dates in question how can I set it so that it includes BOTH dates listed. I have tried several permutations, I either get everything in the database returned or nothing at all. with thanks Bob |
#3
|
|||
|
|||
Sorry still does not work
Sorry Rick but your solution still does not work, if I put in both dates as
04/05/2004 then I get no results back, If I put in 04/05/2004 and 05/05/2004 then I get back results for just the 04/05/2004. (and yes there are items on 05/05/2004) Any other suggestions. Bob "Rick B" wrote in message ... =format([text20],"short date") and =format([Text18],"short date") Rick B "Box 666" wrote in message ... On an input form the user can query an item between 2 dates that they infill in two unbound text boxes. At the moment in the date field of the query criteria field I use Between [forms].[main menu].[text18] And [forms].[main menu].[text20] As the name suggests it returns items "between" the dates in question how can I set it so that it includes BOTH dates listed. I have tried several permutations, I either get everything in the database returned or nothing at all. with thanks Bob |
#4
|
|||
|
|||
Dates between
On Tue, 08 Jun 2004 19:34:31 GMT, Box 666 wrote:
On an input form the user can query an item between 2 dates that they infill in two unbound text boxes. At the moment in the date field of the query criteria field I use Between [forms].[main menu].[text18] And [forms].[main menu].[text20] As the name suggests it returns items "between" the dates in question how can I set it so that it includes BOTH dates listed. I have tried several permutations, I either get everything in the database returned or nothing at all. with thanks Bob Bob, You are mistaken. Between 1/1/2004 and 1/31/2004 will return records of the first and last dates, inclusive. If, however, your date field includes a time value, i.e. 1/31/2004 10:15 AM, it will not return the last date's records as 1/31/2004 midnight is earlier than 1/31/2004 10:15 AM. The above will occur if the date field is filled using Now() which includes a time of day value, instead of Date(), which time value is always midnight. You can either change the parameters to Between [forms].[main menu].[text18] And ([forms].[main menu].[text20] )+1 (Note the addition of parenthesis in the 2nd parameter) in which case you must also set the Parameters dialog to include both parameters as Date/Time .... Query + Parameters .... or.... use your current parameters but enter a date 1 day after you wish, i.e. enter 2/1/2004 instead of 1/31/2004. -- Fred Please only reply to this newsgroup. I do not reply to personal email. |
#5
|
|||
|
|||
Dates between
Fred,
Thank you for your thoughts, You are correct the original date field is populated from a Now(). I have tried as suggested and added the parenthesis in the 2nd parameter plus +1, but I now get the answer of "Too complex to run". The input text boxes were set as short date I have changed that to general date, in the actual query itself I have also clicked on the criterias and set that to general date as well. Is that what you meant by "in which case you must also set the Parameters dialog to include both parameters as Date/Time " As it still does not work I feel I have miss understood the above paragraph. Bob "fredg" wrote in message ... On Tue, 08 Jun 2004 19:34:31 GMT, Box 666 wrote: On an input form the user can query an item between 2 dates that they infill in two unbound text boxes. At the moment in the date field of the query criteria field I use Between [forms].[main menu].[text18] And [forms].[main menu].[text20] As the name suggests it returns items "between" the dates in question how can I set it so that it includes BOTH dates listed. I have tried several permutations, I either get everything in the database returned or nothing at all. with thanks Bob Bob, You are mistaken. Between 1/1/2004 and 1/31/2004 will return records of the first and last dates, inclusive. If, however, your date field includes a time value, i.e. 1/31/2004 10:15 AM, it will not return the last date's records as 1/31/2004 midnight is earlier than 1/31/2004 10:15 AM. The above will occur if the date field is filled using Now() which includes a time of day value, instead of Date(), which time value is always midnight. You can either change the parameters to Between [forms].[main menu].[text18] And ([forms].[main menu].[text20] )+1 (Note the addition of parenthesis in the 2nd parameter) in which case you must also set the Parameters dialog to include both parameters as Date/Time .... Query + Parameters .... or.... use your current parameters but enter a date 1 day after you wish, i.e. enter 2/1/2004 instead of 1/31/2004. -- Fred Please only reply to this newsgroup. I do not reply to personal email. |
#6
|
|||
|
|||
Dates between
On Tue, 08 Jun 2004 22:23:52 GMT, Box 666 wrote:
See comments interspersed below. Fred, Thank you for your thoughts, You are correct the original date field is populated from a Now(). Give me 2 points. :-) I have tried as suggested and added the parenthesis in the 2nd parameter plus +1, but I now get the answer of "Too complex to run". You missed the point of this part of my reply. :-( in which case you must also set the Parameters dialog to include both parameters as Date/Time .... Query + Parameters .... This is done in the query, not on the form. Open the Query in Design View. Click on the Query menu button. Select Parameters from the drop-down. Write [forms].[main menu].[text18] (with the brackets, as shown) on the left side of the dialog. Write Date/Time on the right side. Do the same for the other part of the parameter. Exit the dialog. Open the Query SQL window. The first line should read PARAMETERS [forms]![main menu]![Text18] DateTime, [forms]![main menu]![text20] DateTime; The input text boxes were set as short date I have changed that to general date, The date format in the form control is irrelevant. Set it to whatever your normal date data entry is. in the actual query itself I have also clicked on the criterias and set that to general date as well. Also not relevant. Is that what you meant by "in which case you must also set the Parameters dialog to include both parameters as Date/Time " Nope. Explained in the top section. As it still does not work I feel I have miss understood the above paragraph. Bob Keep going. Your almost there. Fred "fredg" wrote in message ... On Tue, 08 Jun 2004 19:34:31 GMT, Box 666 wrote: On an input form the user can query an item between 2 dates that they infill in two unbound text boxes. At the moment in the date field of the query criteria field I use Between [forms].[main menu].[text18] And [forms].[main menu].[text20] As the name suggests it returns items "between" the dates in question how can I set it so that it includes BOTH dates listed. I have tried several permutations, I either get everything in the database returned or nothing at all. with thanks Bob Bob, You are mistaken. Between 1/1/2004 and 1/31/2004 will return records of the first and last dates, inclusive. If, however, your date field includes a time value, i.e. 1/31/2004 10:15 AM, it will not return the last date's records as 1/31/2004 midnight is earlier than 1/31/2004 10:15 AM. The above will occur if the date field is filled using Now() which includes a time of day value, instead of Date(), which time value is always midnight. You can either change the parameters to Between [forms].[main menu].[text18] And ([forms].[main menu].[text20] )+1 (Note the addition of parenthesis in the 2nd parameter) in which case you must also set the Parameters dialog to include both parameters as Date/Time .... Query + Parameters .... or.... use your current parameters but enter a date 1 day after you wish, i.e. enter 2/1/2004 instead of 1/31/2004. -- Fred Please only reply to this newsgroup. I do not reply to personal email. -- Fred Please only reply to this newsgroup. I do not reply to personal email. |
#7
|
|||
|
|||
Sorry still does not work
On Tue, 08 Jun 2004 21:24:36 GMT, "Box 666"
wrote: Sorry Rick but your solution still does not work, if I put in both dates as 04/05/2004 then I get no results back, If I put in 04/05/2004 and 05/05/2004 then I get back results for just the 04/05/2004. (and yes there are items on 05/05/2004) Any other suggestions. Since Access was written mostly in Redford, Washington, USA, by Americans, it is OBLIGATORY to use either American-style mm/dd/yyyy dates or an unambiguous format such as 04-May-2004. Try =Format([Forms]![Main Menu]![txtStartdate], "mm\/dd\/yyyy") AND Format([Forms]![Main Menu]![txtEndDate], "mm\/dd\/yyyy") + 1 John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#8
|
|||
|
|||
Thank you
Fred,
Thank you finally got there, I had no idea that "such a simple" change was going to be so confusing. Next time I will tell the input clerk to add a day on to the closing date ;-)) Just joking, a good learning point thank you. Bob "fredg" wrote in message .. . On Tue, 08 Jun 2004 22:23:52 GMT, Box 666 wrote: See comments interspersed below. Fred, Thank you for your thoughts, You are correct the original date field is populated from a Now(). Give me 2 points. :-) I have tried as suggested and added the parenthesis in the 2nd parameter plus +1, but I now get the answer of "Too complex to run". You missed the point of this part of my reply. :-( in which case you must also set the Parameters dialog to include both parameters as Date/Time .... Query + Parameters .... This is done in the query, not on the form. Open the Query in Design View. Click on the Query menu button. Select Parameters from the drop-down. Write [forms].[main menu].[text18] (with the brackets, as shown) on the left side of the dialog. Write Date/Time on the right side. Do the same for the other part of the parameter. Exit the dialog. Open the Query SQL window. The first line should read PARAMETERS [forms]![main menu]![Text18] DateTime, [forms]![main menu]![text20] DateTime; The input text boxes were set as short date I have changed that to general date, The date format in the form control is irrelevant. Set it to whatever your normal date data entry is. in the actual query itself I have also clicked on the criterias and set that to general date as well. Also not relevant. Is that what you meant by "in which case you must also set the Parameters dialog to include both parameters as Date/Time " Nope. Explained in the top section. As it still does not work I feel I have miss understood the above paragraph. Bob Keep going. Your almost there. Fred "fredg" wrote in message ... On Tue, 08 Jun 2004 19:34:31 GMT, Box 666 wrote: On an input form the user can query an item between 2 dates that they infill in two unbound text boxes. At the moment in the date field of the query criteria field I use Between [forms].[main menu].[text18] And [forms].[main menu].[text20] As the name suggests it returns items "between" the dates in question how can I set it so that it includes BOTH dates listed. I have tried several permutations, I either get everything in the database returned or nothing at all. with thanks Bob Bob, You are mistaken. Between 1/1/2004 and 1/31/2004 will return records of the first and last dates, inclusive. If, however, your date field includes a time value, i.e. 1/31/2004 10:15 AM, it will not return the last date's records as 1/31/2004 midnight is earlier than 1/31/2004 10:15 AM. The above will occur if the date field is filled using Now() which includes a time of day value, instead of Date(), which time value is always midnight. You can either change the parameters to Between [forms].[main menu].[text18] And ([forms].[main menu].[text20] )+1 (Note the addition of parenthesis in the 2nd parameter) in which case you must also set the Parameters dialog to include both parameters as Date/Time .... Query + Parameters .... or.... use your current parameters but enter a date 1 day after you wish, i.e. enter 2/1/2004 instead of 1/31/2004. -- Fred Please only reply to this newsgroup. I do not reply to personal email. -- Fred Please only reply to this newsgroup. I do not reply to personal email. |
#9
|
|||
|
|||
Sorry still does not work
"John Vinson" wrote in message
Since Access was written mostly in Redford, Washington, USA Is that a suburb of Redmond of which I'm not aware, John? g -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) |
Thread Tools | |
Display Modes | |
|
|