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
|
|||
|
|||
"Between" operation on dates
I'm trying to return all the records between two dates that are entered in
text boxes. here's what I have in there.... Between [Forms]![Licence Search Menu]![SearchDateFrom] And [Forms]![Licence Search Menu]![SearchDateTo] The form exists and is named correctly, and the two date fields are there as well. However the query draws a blank. Is there something else I have to do? |
#2
|
|||
|
|||
There is a good chance that Access is misinterpreting the dates.
To help it get it right: 1. Format the text boxes. Open the Licence Search menu in design view. Right-click SearchDateFrom, and choose Properties. On the Format tab, set the Format property to: Short Date Access then knows that the control is a date. Repeat for SearchDateTo. 2. Declare your parameters in the query. Open your query in design view. Choose Parameters from the Query menu. In the dialog box, enter two rows: [Forms]![Licence Search Menu]![SearchDateFrom] Date/Time [Forms]![Licence Search Menu]![SearchDateTo] Date/Time In the context of the query, Access knows these values are dates. More information: International Date Formats in Access at: http://members.iinet.net.au/~allenbrowne/ser-36.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Nick in Tokyo" wrote in message ... I'm trying to return all the records between two dates that are entered in text boxes. here's what I have in there.... Between [Forms]![Licence Search Menu]![SearchDateFrom] And [Forms]![Licence Search Menu]![SearchDateTo] The form exists and is named correctly, and the two date fields are there as well. However the query draws a blank. Is there something else I have to do? |
#3
|
|||
|
|||
That's already done - still no result. Japan uses the same date formatting as
the US so that's OK too. I have another query that returns all the records before a specific date and it works great. The query is pointing at the textboxes and the output form is pointing at the query. This is very strange. "Allen Browne" wrote: There is a good chance that Access is misinterpreting the dates. To help it get it right: 1. Format the text boxes. Open the Licence Search menu in design view. Right-click SearchDateFrom, and choose Properties. On the Format tab, set the Format property to: Short Date Access then knows that the control is a date. Repeat for SearchDateTo. 2. Declare your parameters in the query. Open your query in design view. Choose Parameters from the Query menu. In the dialog box, enter two rows: [Forms]![Licence Search Menu]![SearchDateFrom] Date/Time [Forms]![Licence Search Menu]![SearchDateTo] Date/Time In the context of the query, Access knows these values are dates. More information: International Date Formats in Access at: http://members.iinet.net.au/~allenbrowne/ser-36.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Nick in Tokyo" wrote in message ... I'm trying to return all the records between two dates that are entered in text boxes. here's what I have in there.... Between [Forms]![Licence Search Menu]![SearchDateFrom] And [Forms]![Licence Search Menu]![SearchDateTo] The form exists and is named correctly, and the two date fields are there as well. However the query draws a blank. Is there something else I have to do? |
#4
|
|||
|
|||
Any chance that the field has a time component which is preventing the
recors from being included? For example, if you ask for records between Between 8/15/04 And 8/16/04 then the record that contains 8/16/04 1am is not matched because it is after 8/16/04 -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Nick in Tokyo" wrote in message ... That's already done - still no result. Japan uses the same date formatting as the US so that's OK too. I have another query that returns all the records before a specific date and it works great. The query is pointing at the textboxes and the output form is pointing at the query. This is very strange. "Allen Browne" wrote: There is a good chance that Access is misinterpreting the dates. To help it get it right: 1. Format the text boxes. Open the Licence Search menu in design view. Right-click SearchDateFrom, and choose Properties. On the Format tab, set the Format property to: Short Date Access then knows that the control is a date. Repeat for SearchDateTo. 2. Declare your parameters in the query. Open your query in design view. Choose Parameters from the Query menu. In the dialog box, enter two rows: [Forms]![Licence Search Menu]![SearchDateFrom] Date/Time [Forms]![Licence Search Menu]![SearchDateTo] Date/Time In the context of the query, Access knows these values are dates. More information: International Date Formats in Access at: http://members.iinet.net.au/~allenbrowne/ser-36.html "Nick in Tokyo" wrote in message ... I'm trying to return all the records between two dates that are entered in text boxes. here's what I have in there.... Between [Forms]![Licence Search Menu]![SearchDateFrom] And [Forms]![Licence Search Menu]![SearchDateTo] The form exists and is named correctly, and the two date fields are there as well. However the query draws a blank. Is there something else I have to do? |
#5
|
|||
|
|||
nope, it's all dates
"Allen Browne" wrote: Any chance that the field has a time component which is preventing the recors from being included? For example, if you ask for records between Between 8/15/04 And 8/16/04 then the record that contains 8/16/04 1am is not matched because it is after 8/16/04 -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Nick in Tokyo" wrote in message ... That's already done - still no result. Japan uses the same date formatting as the US so that's OK too. I have another query that returns all the records before a specific date and it works great. The query is pointing at the textboxes and the output form is pointing at the query. This is very strange. "Allen Browne" wrote: There is a good chance that Access is misinterpreting the dates. To help it get it right: 1. Format the text boxes. Open the Licence Search menu in design view. Right-click SearchDateFrom, and choose Properties. On the Format tab, set the Format property to: Short Date Access then knows that the control is a date. Repeat for SearchDateTo. 2. Declare your parameters in the query. Open your query in design view. Choose Parameters from the Query menu. In the dialog box, enter two rows: [Forms]![Licence Search Menu]![SearchDateFrom] Date/Time [Forms]![Licence Search Menu]![SearchDateTo] Date/Time In the context of the query, Access knows these values are dates. More information: International Date Formats in Access at: http://members.iinet.net.au/~allenbrowne/ser-36.html "Nick in Tokyo" wrote in message ... I'm trying to return all the records between two dates that are entered in text boxes. here's what I have in there.... Between [Forms]![Licence Search Menu]![SearchDateFrom] And [Forms]![Licence Search Menu]![SearchDateTo] The form exists and is named correctly, and the two date fields are there as well. However the query draws a blank. Is there something else I have to do? |
#6
|
|||
|
|||
Cluching at straws, but is the date field indexed?
If so, you could try temporarily dropping the index, and compacting, just in case the index is corrupt. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Nick in Tokyo" wrote in message ... nope, it's all dates "Allen Browne" wrote: Any chance that the field has a time component which is preventing the recors from being included? For example, if you ask for records between Between 8/15/04 And 8/16/04 then the record that contains 8/16/04 1am is not matched because it is after 8/16/04 -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Nick in Tokyo" wrote in message ... That's already done - still no result. Japan uses the same date formatting as the US so that's OK too. I have another query that returns all the records before a specific date and it works great. The query is pointing at the textboxes and the output form is pointing at the query. This is very strange. "Allen Browne" wrote: There is a good chance that Access is misinterpreting the dates. To help it get it right: 1. Format the text boxes. Open the Licence Search menu in design view. Right-click SearchDateFrom, and choose Properties. On the Format tab, set the Format property to: Short Date Access then knows that the control is a date. Repeat for SearchDateTo. 2. Declare your parameters in the query. Open your query in design view. Choose Parameters from the Query menu. In the dialog box, enter two rows: [Forms]![Licence Search Menu]![SearchDateFrom] Date/Time [Forms]![Licence Search Menu]![SearchDateTo] Date/Time In the context of the query, Access knows these values are dates. More information: International Date Formats in Access at: http://members.iinet.net.au/~allenbrowne/ser-36.html "Nick in Tokyo" wrote in message ... I'm trying to return all the records between two dates that are entered in text boxes. here's what I have in there.... Between [Forms]![Licence Search Menu]![SearchDateFrom] And [Forms]![Licence Search Menu]![SearchDateTo] The form exists and is named correctly, and the two date fields are there as well. However the query draws a blank. Is there something else I have to do? |
#7
|
|||
|
|||
Hi Nick,
PMFBI Not to doubt you, but just to be sure, would you mind trying: = [Forms]![Licence Search Menu]![SearchDateFrom] And = [Forms]![Licence Search Menu]![SearchDateTo] Also...is this "purely" Access? I have seen posts talking about "BETWEEN" needing "values" reversed to work correctly in non-Access context. Apologies again for butting in. Good luck, Gary Walter "Nick in Tokyo" wrote nope, it's all dates "Allen Browne" wrote: Any chance that the field has a time component which is preventing the recors from being included? For example, if you ask for records between Between 8/15/04 And 8/16/04 then the record that contains 8/16/04 1am is not matched because it is after 8/16/04 -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Nick in Tokyo" wrote in message ... That's already done - still no result. Japan uses the same date formatting as the US so that's OK too. I have another query that returns all the records before a specific date and it works great. The query is pointing at the textboxes and the output form is pointing at the query. This is very strange. "Allen Browne" wrote: There is a good chance that Access is misinterpreting the dates. To help it get it right: 1. Format the text boxes. Open the Licence Search menu in design view. Right-click SearchDateFrom, and choose Properties. On the Format tab, set the Format property to: Short Date Access then knows that the control is a date. Repeat for SearchDateTo. 2. Declare your parameters in the query. Open your query in design view. Choose Parameters from the Query menu. In the dialog box, enter two rows: [Forms]![Licence Search Menu]![SearchDateFrom] Date/Time [Forms]![Licence Search Menu]![SearchDateTo] Date/Time In the context of the query, Access knows these values are dates. More information: International Date Formats in Access at: http://members.iinet.net.au/~allenbrowne/ser-36.html "Nick in Tokyo" wrote in message ... I'm trying to return all the records between two dates that are entered in text boxes. here's what I have in there.... Between [Forms]![Licence Search Menu]![SearchDateFrom] And [Forms]![Licence Search Menu]![SearchDateTo] The form exists and is named correctly, and the two date fields are there as well. However the query draws a blank. Is there something else I have to do? |
#8
|
|||
|
|||
When you run the query without the form open, you'll get parameter prompts.
If you fill these in, do you get the records you want? -- Joan Wild Microsoft Access MVP Nick in Tokyo wrote: nope, it's all dates "Allen Browne" wrote: Any chance that the field has a time component which is preventing the recors from being included? For example, if you ask for records between Between 8/15/04 And 8/16/04 then the record that contains 8/16/04 1am is not matched because it is after 8/16/04 -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Nick in Tokyo" wrote in message ... That's already done - still no result. Japan uses the same date formatting as the US so that's OK too. I have another query that returns all the records before a specific date and it works great. The query is pointing at the textboxes and the output form is pointing at the query. This is very strange. "Allen Browne" wrote: There is a good chance that Access is misinterpreting the dates. To help it get it right: 1. Format the text boxes. Open the Licence Search menu in design view. Right-click SearchDateFrom, and choose Properties. On the Format tab, set the Format property to: Short Date Access then knows that the control is a date. Repeat for SearchDateTo. 2. Declare your parameters in the query. Open your query in design view. Choose Parameters from the Query menu. In the dialog box, enter two rows: [Forms]![Licence Search Menu]![SearchDateFrom] Date/Time [Forms]![Licence Search Menu]![SearchDateTo] Date/Time In the context of the query, Access knows these values are dates. More information: International Date Formats in Access at: http://members.iinet.net.au/~allenbrowne/ser-36.html "Nick in Tokyo" wrote in message ... I'm trying to return all the records between two dates that are entered in text boxes. here's what I have in there.... Between [Forms]![Licence Search Menu]![SearchDateFrom] And [Forms]![Licence Search Menu]![SearchDateTo] The form exists and is named correctly, and the two date fields are there as well. However the query draws a blank. Is there something else I have to do? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Default values to load up automatically in a form based on value entered in another form | Anthony Dowd | Using Forms | 8 | August 12th, 2004 08:53 AM |
Projecting dates from dates and times! | Suddes | General Discussion | 5 | July 19th, 2004 01:57 PM |
Sumproduct search between Dates | Robert Christie | Worksheet Functions | 5 | May 22nd, 2004 03:09 PM |
Charting woes with hidden dates | dustin pockets | Charts and Charting | 4 | April 15th, 2004 04:20 AM |
Cummaltive/Summarised Item total on a different product vlookup, depending on the description on the operation | John Colling | Worksheet Functions | 2 | February 21st, 2004 03:44 PM |