Query Input

  • Thread starter Thread starter order800
  • Start date Start date
O

order800

This is the complete DB.

Table1
ID Date
1 1/1/2000
2 2/1/2000
3 3/1/2000
4 4/1/2000
5 5/1/2000
6 6/1/2000
7 7/1/2000
8 8/1/2000
9 9/1/2000
10 10/1/2000
11 11/1/2000
12 12/1/2000

When I input 1 and 6 on the following query it returns outside of range….

SELECT Table1.ID, Table1.Date, DatePart("m",[Date]) AS Expr1
FROM Table1
WHERE (((DatePart("m",[Date])) Between [Start] And [End]));

Query1
ID Date Expr1
1 1/1/2000 1
2 2/1/2000 2
3 3/1/2000 3
4 4/1/2000 4
5 5/1/2000 5
6 6/1/2000 6
10 10/1/2000 10
11 11/1/2000 11
12 12/1/2000 12

But without input it works:

SELECT Table1.ID, Table1.Date, DatePart("m",[Date]) AS Expr1
FROM Table1
WHERE (((DatePart("m",[Date])) Between 1 And 6));

Query1
ID Date Expr1
1 1/1/2000 1
2 2/1/2000 2
3 3/1/2000 3
4 4/1/2000 4
5 5/1/2000 5
6 6/1/2000 6


Why will it not accept input?
 
Part of the problem here is that the names you used for fields and
parameters are ambiguous. Date is a VBA function/statement, as well as a
reserved word in queries. End is also reserved, and Start is future
reserved.

To address this:
1. Make sure Name AutoCorrect is off, so Access doesn't remember the bad
names. Details and explanation of why:
http://allenbrowne.com/bug-03.html

2. Rename to field to something else, such as SaleDate.

3. In the Criteria row in query design, try:
Between [StartMonth] And [EndMonth]

4. Declare the parameters.
Choose Parameters from the Query menu (in query design.)
Enter 2 rows in the dialog:
[StartMonth] Long Integer
[EndMonth] Long Integer

There are lots of names to avoid. You might want to refer to this list when
designing tables and queries:
http://allenbrowne.com/AppIssueBadWord.html
 
Back
Top