Date Range By Month Number

DanG · Mar 7, 2006

I am trying to make a query that finds all records by the "Month Number" and have problems.

What I did:
Month(DateField)
I got month numbers like I wanted but when I typed in the criteria below, I got February thru December Records, but when I search on anything "up to" 9 it works fine.

I want:
Between{month1] and [month2]
Month1= 9 (example)
Month2= 12 (example)
Need results to be from the requested months but any year

I have to imagine it's how the date is converted to an integer, but not sure.

Any help would be great!

Jon K · Mar 7, 2006

You need to declare the data type of the parameters.

Open the query in Design view. Choose menu Query, Parameters...
Type the parameter [Month1] and select the data type.
Do the same for [Month2].
Click OK.
.

raskew · Mar 8, 2006

Hi -

Try this against Northwind's Orders table:

Code:

SELECT
    Orders.OrderID
  , Month([OrderDate]) AS MyMonth
  , Orders.OrderDate
FROM
   Orders
WHERE
   (((Month([OrderDate]))>=Val([enter low]) 
AND
   (Month([OrderDate]))<=Val([enter high])))
ORDER BY
   Month([OrderDate])
  , Orders.OrderDate;

...when prompted for [enter low] respond with 4
...when promoted for [enter high] respond with 12

HTH - Bob

EMP · Mar 8, 2006

Both suggestions should work. There are other ways too, such as using the CInt() function to tell Access the parameters are numbers. Even the expression Between [month1]+0 And [month2]+0 works.

Declaring data type of parameters is the direct way. Using functions or adding +0 are indirect ways.

Without being told that the parameters are numbers, Access would erroneously treat them as text.

^

DanG · Mar 8, 2006

Thank you all very much.
Got it working.
Is there anything you can't do with Access?

Date Range By Month Number

DanG

Registered User.

Jon K

Registered User.

raskew

AWF VIP

EMP

Registered User.

DanG

Registered User.

Similar threads

Users who are viewing this thread