Date range

[toppscrt]

Trying to do a query with only records showing between certain dates in
descending order. Not sure where I'm going wrong. Have tried several codes
but for some reason, I can't get it to work. Either I get an error code or I
get a pop-box asking for dates. Can someone tell me what part of the last
line is incorrect? Thanks.

SELECT Firm.[Firm#], Payment.AmtPaid, Payment.[Payment#], Payment.DatePaid
FROM Firm INNER JOIN Payment ON Firm.[Firm#]=Payment.[Firm#]
WHERE (((Firm.[Firm#]) Like "1115"))
ORDER BY Payment.DatePaid.Between [06/01/2001] And [06/10/2001] DESC;

 

[George Nicholson]

WHERE (((Firm.[Firm#]) Like "1115") AND (Payment.DatePaid.Between
#06/01/2001# And #06/10/2001#))
ORDER BY Payment.DatePaid DESC;

1) WHERE clause determines which data is included in the results (i.e., acts
as a filter). That's where you want your Between statement.
2) Brackets usually identify something as a table or field, so you don't
want to use them around your dates. Use # for a date delimiter instead.
3) ORDER BY determines the sort order of the data. Name "Payment.DatePaid
DESC" is all you need here.

HTH,

 

[Marshall Barton]

Trying to do a query with only records showing between certain dates in
descending order. Not sure where I'm going wrong. Have tried several codes
but for some reason, I can't get it to work. Either I get an error code or I
get a pop-box asking for dates. Can someone tell me what part of the last
line is incorrect? Thanks.

SELECT Firm.[Firm#], Payment.AmtPaid, Payment.[Payment#], Payment.DatePaid
FROM Firm INNER JOIN Payment ON Firm.[Firm#]=Payment.[Firm#]
WHERE (((Firm.[Firm#]) Like "1115"))
ORDER BY Payment.DatePaid.Between [06/01/2001] And [06/10/2001] DESC;

You've mixed the Where and Order By clauses:
. . .
WHERE (Firm.[Firm#] = "1115")
AND (Payment.DatePaid Between [06/01/2001]
And [06/10/2001])
ORDER BY Payment.DatePaid DESC

 

[toppscrt]

Thanks guys but still having one problem. When I go to run it, I get a popup
that tells me to "Enter parameter value". I'm trying to get it to go
directly to the query itself. Any suggestions?

Trying to do a query with only records showing between certain dates in
descending order. Not sure where I'm going wrong. Have tried several codes
but for some reason, I can't get it to work. Either I get an error code or I
get a pop-box asking for dates. Can someone tell me what part of the last
line is incorrect? Thanks.

SELECT Firm.[Firm#], Payment.AmtPaid, Payment.[Payment#], Payment.DatePaid
FROM Firm INNER JOIN Payment ON Firm.[Firm#]=Payment.[Firm#]
WHERE (((Firm.[Firm#]) Like "1115"))
ORDER BY Payment.DatePaid.Between [06/01/2001] And [06/10/2001] DESC;

You've mixed the Where and Order By clauses:
. . .
WHERE (Firm.[Firm#] = "1115")
AND (Payment.DatePaid Between [06/01/2001]
And [06/10/2001])
ORDER BY Payment.DatePaid DESC

 

[Hafeez Esmail]

In the design view; go to query --> parameters
remove any parameter you see there (if you're not using them). If that
doesn't help, then post the exact SQL code you have right now

Thanks guys but still having one problem. When I go to run it, I get a popup
that tells me to "Enter parameter value". I'm trying to get it to go
directly to the query itself. Any suggestions?

Trying to do a query with only records showing between certain dates in
descending order. Not sure where I'm going wrong. Have tried several codes
but for some reason, I can't get it to work. Either I get an error code or I
get a pop-box asking for dates. Can someone tell me what part of the last
line is incorrect? Thanks.

SELECT Firm.[Firm#], Payment.AmtPaid, Payment.[Payment#], Payment.DatePaid
FROM Firm INNER JOIN Payment ON Firm.[Firm#]=Payment.[Firm#]
WHERE (((Firm.[Firm#]) Like "1115"))
ORDER BY Payment.DatePaid.Between [06/01/2001] And [06/10/2001] DESC;

You've mixed the Where and Order By clauses:
. . .
WHERE (Firm.[Firm#] = "1115")
AND (Payment.DatePaid Between [06/01/2001]
And [06/10/2001])
ORDER BY Payment.DatePaid DESC

 

[Marshall Barton]

If that was the actual query you're using, then change the
dates to use the # literal date/time delimiter:
. . .
WHERE (Firm.[Firm#] = "1115")
AND (Payment.DatePaid Between #06/01/2001#
And #06/10/2001#)
. . .
--
Marsh
MVP [MS Access]

Thanks guys but still having one problem. When I go to run it, I get a popup
that tells me to "Enter parameter value". I'm trying to get it to go
directly to the query itself. Any suggestions?

Trying to do a query with only records showing between certain dates in
descending order. Not sure where I'm going wrong. Have tried several codes
but for some reason, I can't get it to work. Either I get an error code or I
get a pop-box asking for dates. Can someone tell me what part of the last
line is incorrect? Thanks.

SELECT Firm.[Firm#], Payment.AmtPaid, Payment.[Payment#], Payment.DatePaid
FROM Firm INNER JOIN Payment ON Firm.[Firm#]=Payment.[Firm#]
WHERE (((Firm.[Firm#]) Like "1115"))
ORDER BY Payment.DatePaid.Between [06/01/2001] And [06/10/2001] DESC;

You've mixed the Where and Order By clauses:
. . .
WHERE (Firm.[Firm#] = "1115")
AND (Payment.DatePaid Between [06/01/2001]
And [06/10/2001])
ORDER BY Payment.DatePaid DESC

 

[toppscrt]

Thanks chief. That did the trick. You guys ae the best

If that was the actual query you're using, then change the
dates to use the # literal date/time delimiter:
. . .
WHERE (Firm.[Firm#] = "1115")
AND (Payment.DatePaid Between #06/01/2001#
And #06/10/2001#)
. . .
--
Marsh
MVP [MS Access]

Thanks guys but still having one problem. When I go to run it, I get a popup
that tells me to "Enter parameter value". I'm trying to get it to go
directly to the query itself. Any suggestions?

toppscrt wrote:
Trying to do a query with only records showing between certain dates in
descending order. Not sure where I'm going wrong. Have tried several codes
but for some reason, I can't get it to work. Either I get an error code or I
get a pop-box asking for dates. Can someone tell me what part of the last
line is incorrect? Thanks.

SELECT Firm.[Firm#], Payment.AmtPaid, Payment.[Payment#], Payment.DatePaid
FROM Firm INNER JOIN Payment ON Firm.[Firm#]=Payment.[Firm#]
WHERE (((Firm.[Firm#]) Like "1115"))
ORDER BY Payment.DatePaid.Between [06/01/2001] And [06/10/2001] DESC;

You've mixed the Where and Order By clauses:
. . .
WHERE (Firm.[Firm#] = "1115")
AND (Payment.DatePaid Between [06/01/2001]
And [06/10/2001])
ORDER BY Payment.DatePaid DESC