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
|
|||
|
|||
Use Date as Query Criteria
Hi,
I hope someone can help me. I have a query that I want to execute using a date range in the criteria. It works fine if I prompt the user to enter the dates into the query - by using "Between [Enter 1st date] And [Enter 2nd Date]" in the criteria section. However, I want to use a form for the user to enter the date range into and whilst using "Between [Forms]![Frmxx]![StartDate] And [Forms]![Frmxx]![EndDate]" in the criteria, where StartDate and EndDate are fields on the form, the query doesn't seem to recognise the values in the form fields and returns no records. The fields on the form are formatted as Medium Date as are the respective fields on the query. Any suggestions/solutions would be very much appreciated. |
#2
|
|||
|
|||
Use Date as Query Criteria
That approach should work, Dave.
Suggestions to help you debug this: 1. Make sure the focus is not still in the StartDate or EndDate boxes on the form. If it is the Value of the control may not be up to date yet. 2. Declare the 2 parameters in the query, to ensure Access understands their data type correctly. In query design view, choose Parameters on the Query menu. Access opens a dialog. Enter 2 rows: [Forms]![Frmxx]![StartDate] Date/Time [Forms]![Frmxx]![EndDate] Date/Time 3. Try Short Date instead of Medium Date in the text boxes on the form. (Medium Date should work, but Short Date is a system setting.) 4. Presumably the field you are applying this to is a Date/Time type field if you open your table in design view? (not a Text field?) 5. If the field might have a time as well as a date component, change the criteria in your query to: = [Forms]![Frmxx]![StartDate] And ([Forms]![Frmxx]![EndDate]+1) 6. If you're still stuck, open the Immediate Window (Ctrl+G) while the form is open and has the dates in its text boxes, and ask Access how it understands the values, e.g.: ? [Forms]![Frmxx]![StartDate] ? TypeName([Forms]![Frmxx]![StartDate]) -- 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. "Dave C" wrote in message ... I have a query that I want to execute using a date range in the criteria. It works fine if I prompt the user to enter the dates into the query - by using "Between [Enter 1st date] And [Enter 2nd Date]" in the criteria section. However, I want to use a form for the user to enter the date range into and whilst using "Between [Forms]![Frmxx]![StartDate] And [Forms]![Frmxx]![EndDate]" in the criteria, where StartDate and EndDate are fields on the form, the query doesn't seem to recognise the values in the form fields and returns no records. The fields on the form are formatted as Medium Date as are the respective fields on the query. |
#3
|
|||
|
|||
Use Date as Query Criteria
Thanks for your rapid response. I will work through your debug suggestions
and see if can get it working. Dave "Allen Browne" wrote: That approach should work, Dave. Suggestions to help you debug this: 1. Make sure the focus is not still in the StartDate or EndDate boxes on the form. If it is the Value of the control may not be up to date yet. 2. Declare the 2 parameters in the query, to ensure Access understands their data type correctly. In query design view, choose Parameters on the Query menu. Access opens a dialog. Enter 2 rows: [Forms]![Frmxx]![StartDate] Date/Time [Forms]![Frmxx]![EndDate] Date/Time 3. Try Short Date instead of Medium Date in the text boxes on the form. (Medium Date should work, but Short Date is a system setting.) 4. Presumably the field you are applying this to is a Date/Time type field if you open your table in design view? (not a Text field?) 5. If the field might have a time as well as a date component, change the criteria in your query to: = [Forms]![Frmxx]![StartDate] And ([Forms]![Frmxx]![EndDate]+1) 6. If you're still stuck, open the Immediate Window (Ctrl+G) while the form is open and has the dates in its text boxes, and ask Access how it understands the values, e.g.: ? [Forms]![Frmxx]![StartDate] ? TypeName([Forms]![Frmxx]![StartDate]) -- 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. "Dave C" wrote in message ... I have a query that I want to execute using a date range in the criteria. It works fine if I prompt the user to enter the dates into the query - by using "Between [Enter 1st date] And [Enter 2nd Date]" in the criteria section. However, I want to use a form for the user to enter the date range into and whilst using "Between [Forms]![Frmxx]![StartDate] And [Forms]![Frmxx]![EndDate]" in the criteria, where StartDate and EndDate are fields on the form, the query doesn't seem to recognise the values in the form fields and returns no records. The fields on the form are formatted as Medium Date as are the respective fields on the query. |
#4
|
|||
|
|||
Use Date as Query Criteria
Hi again Allen,
Problem sorted! I feel such an idiot but I will own up to the cause - the form field names specified in the query criteria did not match those on the actual form. "StartDate"/"EndDate" vs. "dteStartDate"/"dteEndDate". Next time I promise I will check the basics a little more closely. Thanks for your time; I will file your reply incase I run into something similar in the future. Regards (a little humiliated) Dave "Allen Browne" wrote: That approach should work, Dave. Suggestions to help you debug this: 1. Make sure the focus is not still in the StartDate or EndDate boxes on the form. If it is the Value of the control may not be up to date yet. 2. Declare the 2 parameters in the query, to ensure Access understands their data type correctly. In query design view, choose Parameters on the Query menu. Access opens a dialog. Enter 2 rows: [Forms]![Frmxx]![StartDate] Date/Time [Forms]![Frmxx]![EndDate] Date/Time 3. Try Short Date instead of Medium Date in the text boxes on the form. (Medium Date should work, but Short Date is a system setting.) 4. Presumably the field you are applying this to is a Date/Time type field if you open your table in design view? (not a Text field?) 5. If the field might have a time as well as a date component, change the criteria in your query to: = [Forms]![Frmxx]![StartDate] And ([Forms]![Frmxx]![EndDate]+1) 6. If you're still stuck, open the Immediate Window (Ctrl+G) while the form is open and has the dates in its text boxes, and ask Access how it understands the values, e.g.: ? [Forms]![Frmxx]![StartDate] ? TypeName([Forms]![Frmxx]![StartDate]) -- 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. "Dave C" wrote in message ... I have a query that I want to execute using a date range in the criteria. It works fine if I prompt the user to enter the dates into the query - by using "Between [Enter 1st date] And [Enter 2nd Date]" in the criteria section. However, I want to use a form for the user to enter the date range into and whilst using "Between [Forms]![Frmxx]![StartDate] And [Forms]![Frmxx]![EndDate]" in the criteria, where StartDate and EndDate are fields on the form, the query doesn't seem to recognise the values in the form fields and returns no records. The fields on the form are formatted as Medium Date as are the respective fields on the query. |
#5
|
|||
|
|||
Use Date as Query Criteria
Glad you got it sorted out, Dave.
And thanks for posting the solution. :-) -- 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. "Dave C" wrote in message news Hi again Allen, Problem sorted! I feel such an idiot but I will own up to the cause - the form field names specified in the query criteria did not match those on the actual form. "StartDate"/"EndDate" vs. "dteStartDate"/"dteEndDate". Next time I promise I will check the basics a little more closely. Thanks for your time; I will file your reply incase I run into something similar in the future. Regards (a little humiliated) Dave "Allen Browne" wrote: That approach should work, Dave. Suggestions to help you debug this: 1. Make sure the focus is not still in the StartDate or EndDate boxes on the form. If it is the Value of the control may not be up to date yet. 2. Declare the 2 parameters in the query, to ensure Access understands their data type correctly. In query design view, choose Parameters on the Query menu. Access opens a dialog. Enter 2 rows: [Forms]![Frmxx]![StartDate] Date/Time [Forms]![Frmxx]![EndDate] Date/Time 3. Try Short Date instead of Medium Date in the text boxes on the form. (Medium Date should work, but Short Date is a system setting.) 4. Presumably the field you are applying this to is a Date/Time type field if you open your table in design view? (not a Text field?) 5. If the field might have a time as well as a date component, change the criteria in your query to: = [Forms]![Frmxx]![StartDate] And ([Forms]![Frmxx]![EndDate]+1) 6. If you're still stuck, open the Immediate Window (Ctrl+G) while the form is open and has the dates in its text boxes, and ask Access how it understands the values, e.g.: ? [Forms]![Frmxx]![StartDate] ? TypeName([Forms]![Frmxx]![StartDate]) "Dave C" wrote in message ... I have a query that I want to execute using a date range in the criteria. It works fine if I prompt the user to enter the dates into the query - by using "Between [Enter 1st date] And [Enter 2nd Date]" in the criteria section. However, I want to use a form for the user to enter the date range into and whilst using "Between [Forms]![Frmxx]![StartDate] And [Forms]![Frmxx]![EndDate]" in the criteria, where StartDate and EndDate are fields on the form, the query doesn't seem to recognise the values in the form fields and returns no records. The fields on the form are formatted as Medium Date as are the respective fields on the query. |
Thread Tools | |
Display Modes | |
|
|