Date Range Syntax

[Guest]

I'm trying to get the count of the [Recieved] and the [Shipped] fields that
occured within a range of dates. My SQL syntax is a bit off, I'm afraid.
Can anyone help? Here's the SQL:

SELECT [Order Entry].[Order Number], [Order Entry].Received, Count([Order
Entry].Received) AS CountOfReceived, [Order Entry].Shipped, Count([Order
Entry].Shipped) AS CountOfShipped
FROM [Order Entry]
GROUP BY [Order Entry].[Order Number], [Order Entry].Received, [Order
Entry].Shipped
HAVING ((([Order Entry].Received) Between "#" & [Start] & "#" And "#" &
[Stop] & "#"))
ORDER BY [Order Entry].Received DESC;


I don't get any results, just a message saying that my calculation is too
complicated or I typed it in wrong.

 

[Guest]

I assume that [Start] and [Stop] are for data entry prompts and not fields.
Try this and at the prompts enter dates in a format that Access recognizes.
HAVING ((([Order Entry].Received) Between [Start] And [Stop] ))
ORDER BY [Order Entry].Received DESC;

 

[Guest]

Hi,

What are [Start] and [Stop]? If they a parameters that prompt you for data
input, you don't need the #'s and other stuff. Instead you need to define the
parameters as Date/Time.

PARAMETERS [Start] DateTime, [Stop] DateTime;
SELECT [Order Entry].[Order Number],
[Order Entry].[Received] ,
Count([Order Entry].[Received]) AS CountOfReceived,
[Order Entry].[Shipped],
Count([Order Entry].[Shipped]) AS CountOfShipped
FROM [Order Entry]
GROUP BY [Order Entry].[Order Number],
[Order Entry].[Received],
[Order Entry].[Shipped]
HAVING [Order Entry].Received Between [Start] And [Stop]
ORDER BY [Order Entry].Received DESC;

 

[Guest]

Argh! You are right, of course... A case of me trying to make things too
complicated!
--
Why are you asking me? I dont know what Im doing!

Jaybird

I assume that [Start] and [Stop] are for data entry prompts and not fields.
Try this and at the prompts enter dates in a format that Access recognizes.
HAVING ((([Order Entry].Received) Between [Start] And [Stop] ))
ORDER BY [Order Entry].Received DESC;

--
KARL DEWEY
Build a little - Test a little

I'm trying to get the count of the [Recieved] and the [Shipped] fields that
occured within a range of dates. My SQL syntax is a bit off, I'm afraid.
Can anyone help? Here's the SQL:

SELECT [Order Entry].[Order Number], [Order Entry].Received, Count([Order
Entry].Received) AS CountOfReceived, [Order Entry].Shipped, Count([Order
Entry].Shipped) AS CountOfShipped
FROM [Order Entry]
GROUP BY [Order Entry].[Order Number], [Order Entry].Received, [Order
Entry].Shipped
HAVING ((([Order Entry].Received) Between "#" & [Start] & "#" And "#" &
[Stop] & "#"))
ORDER BY [Order Entry].Received DESC;


I don't get any results, just a message saying that my calculation is too
complicated or I typed it in wrong.

 

[Guest]

Man, you guys are fast! Thanks so much for clearing this thing up!
--
Why are you asking me? I dont know what Im doing!

Jaybird

Hi,

What are [Start] and [Stop]? If they a parameters that prompt you for data
input, you don't need the #'s and other stuff. Instead you need to define the
parameters as Date/Time.

PARAMETERS [Start] DateTime, [Stop] DateTime;
SELECT [Order Entry].[Order Number],
[Order Entry].[Received] ,
Count([Order Entry].[Received]) AS CountOfReceived,
[Order Entry].[Shipped],
Count([Order Entry].[Shipped]) AS CountOfShipped
FROM [Order Entry]
GROUP BY [Order Entry].[Order Number],
[Order Entry].[Received],
[Order Entry].[Shipped]
HAVING [Order Entry].Received Between [Start] And [Stop]
ORDER BY [Order Entry].Received DESC;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm trying to get the count of the [Recieved] and the [Shipped] fields that
occured within a range of dates. My SQL syntax is a bit off, I'm afraid.
Can anyone help? Here's the SQL:

SELECT [Order Entry].[Order Number], [Order Entry].Received, Count([Order
Entry].Received) AS CountOfReceived, [Order Entry].Shipped, Count([Order
Entry].Shipped) AS CountOfShipped
FROM [Order Entry]
GROUP BY [Order Entry].[Order Number], [Order Entry].Received, [Order
Entry].Shipped
HAVING ((([Order Entry].Received) Between "#" & [Start] & "#" And "#" &
[Stop] & "#"))
ORDER BY [Order Entry].Received DESC;

I don't get any results, just a message saying that my calculation is too
complicated or I typed it in wrong.