My Head Hurts!! Parameter Date Query

[Guest]

HI all

I have a query that was working fine but like some of us we just cant stop
playing and then mess it all up.... yeah thats what I did...
Anyway... really simple one this... so simple I cant do it again...
I have a query with a orderdate, and in the criteria it has the code:

Year([OrderDate])=Year(Now()) And Month([OrderDate])=Month(Now())

This when re-open the query in design creates two new fields, Year & Month
and in their criteria it has for Year Year(Now()) and for month
Month(Now())

I put something else in there for a parameter query in both fields so that
when the user opens the query they entered the year and then the month and
the query brought back orders for that month and year.

But silly me began to change it because I wanted to have some validation for
when they entered the year and month... this is where it all went wrong...
dont know how to do the validation bit, BUT now I have lost the simple thing
I did to get the query to run with the two parameter boxes... to bring the
orders for that month and year... I know how to put in parameter boxes but
dont know how I got it to do the date checking thingy... HELPPPP!!!!

I now have a headache so going to get a drink and hope someone will have
answered me when I get back...

Thanks to all you wise ones out there...

Saj

 

[fredg]

HI all

I have a query that was working fine but like some of us we just cant stop
playing and then mess it all up.... yeah thats what I did...
Anyway... really simple one this... so simple I cant do it again...
I have a query with a orderdate, and in the criteria it has the code:

Year([OrderDate])=Year(Now()) And Month([OrderDate])=Month(Now())

This when re-open the query in design creates two new fields, Year & Month
and in their criteria it has for Year Year(Now()) and for month
Month(Now())

I put something else in there for a parameter query in both fields so that
when the user opens the query they entered the year and then the month and
the query brought back orders for that month and year.

But silly me began to change it because I wanted to have some validation for
when they entered the year and month... this is where it all went wrong...
dont know how to do the validation bit, BUT now I have lost the simple thing
I did to get the query to run with the two parameter boxes... to bring the
orders for that month and year... I know how to put in parameter boxes but
dont know how I got it to do the date checking thingy... HELPPPP!!!!

I now have a headache so going to get a drink and hope someone will have
answered me when I get back...

Thanks to all you wise ones out there...

Saj

I have no idea what you did but...
Year and Month are reserved Access/VBA/Jet words and should not be
used as a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

Perhaps you should retry the criteria. I would suggest you use Date()
in place of Now() when ever you do not need the time of day. Add 2 new
columns. Do not name the columns Year or Month. Uncheck the display
box.
The query SQL will look like this:

Where Year([TableName]![OrderDate])=Year(Date()) And
Month([TableName]![OrderDate])=Month(Date())

as query criteria.

 

[John Vinson]

I have a query that was working fine but like some of us we just cant stop
playing and then mess it all up.... yeah thats what I did...
Anyway... really simple one this... so simple I cant do it again...
I have a query with a orderdate, and in the criteria it has the code:

Year([OrderDate])=Year(Now()) And Month([OrderDate])=Month(Now())

Please open the Query in SQL view and post it here.

I'd actually suggest a different criterion to filter a recordset to
the current month; put a criterion on OrderDate of

= DateSerial(Year(Date()), Month(Data()), 1) AND < DateSerial(Year(Date()), Month(Date()) + 1, 1)

John W. Vinson[MVP]

 

[Guest]

Thanks for getting back to me...

I tried the code you gave and created two new columns called OrdYear and
OrdMonth put the code in the sql query changed the tablenames to Order and
ran it, but it didnt work?

But whilst doing this I then remembered what I originally did and put that
back in, and then entered the parameter for each column Enter Year and Enter
Month, realised after a while the reason it didnt work was because it didnt
like it when I entered 01 or Jan etc for months wanted 1, 2, 3 etc only for
the months.

This then reminded me why I was looking at ways for validation on entering
data into the parameter boxes... THIS is where I stopped... didnt want what
happened before to happen again ;o)

Will stick to this method, but do you know how I can validate the month or
year being entered into the parameter boxes... is there a format that can be
entered somewhere etc... the code I used is the same code in original msg.

I know you said not to use the Year Month but I didnt, I just used some
sample code given in help within access and it created those columns for me..

Any further help or advice on this is much appreciated... oh and head not
hurting as much now.. ;o)

Thanks again in advance

HI all

I have a query that was working fine but like some of us we just cant stop
playing and then mess it all up.... yeah thats what I did...
Anyway... really simple one this... so simple I cant do it again...
I have a query with a orderdate, and in the criteria it has the code:

Year([OrderDate])=Year(Now()) And Month([OrderDate])=Month(Now())

This when re-open the query in design creates two new fields, Year & Month
and in their criteria it has for Year Year(Now()) and for month
Month(Now())

I put something else in there for a parameter query in both fields so that
when the user opens the query they entered the year and then the month and
the query brought back orders for that month and year.

But silly me began to change it because I wanted to have some validation for
when they entered the year and month... this is where it all went wrong...
dont know how to do the validation bit, BUT now I have lost the simple thing
I did to get the query to run with the two parameter boxes... to bring the
orders for that month and year... I know how to put in parameter boxes but
dont know how I got it to do the date checking thingy... HELPPPP!!!!

I now have a headache so going to get a drink and hope someone will have
answered me when I get back...

Thanks to all you wise ones out there...

Saj

I have no idea what you did but...
Year and Month are reserved Access/VBA/Jet words and should not be
used as a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

Perhaps you should retry the criteria. I would suggest you use Date()
in place of Now() when ever you do not need the time of day. Add 2 new
columns. Do not name the columns Year or Month. Uncheck the display
box.
The query SQL will look like this:

Where Year([TableName]![OrderDate])=Year(Date()) And
Month([TableName]![OrderDate])=Month(Date())

as query criteria.

 

[Guest]

Hi John

The code in my sql is as follows:

SELECT [Order].[customerNo], [Order].[orderNo], [Order].[orderDate],
Sum([quantity]*[sellingPrice]) AS TotalOrder, [Customer].[title],
[Customer].[firstname], [Customer].[lastname]
FROM Stock INNER JOIN ((Customer INNER JOIN [Order] ON
[Customer].[customerNo]=[Order].[customerNo]) INNER JOIN Orderline ON
[Order].[orderNo]=[Orderline].[orderNo]) ON
[Stock].[stockNo]=[Orderline].[stockNo]
WHERE (((Year([OrderDate]))=[Enter Year]) AND ((Month([OrderDate]))=[Enter
Month])
GROUP BY [Order].[customerNo], [Order].[orderNo], [Order].[orderDate],
[Customer].[title], [Customer].[firstname], [Customer].[lastname]
HAVING (((Sum([quantity]*[sellingPrice]))>All (SELECT AVG([sellingPrice] *
[Quantity]) FROM [Orderline],[Stock])));

Thanks for getting back to me, will also try your code, would appreciate it
if you could let me know if there is a way of checking validation for this
too.

Thanks again

I have a query that was working fine but like some of us we just cant stop
playing and then mess it all up.... yeah thats what I did...
Anyway... really simple one this... so simple I cant do it again...
I have a query with a orderdate, and in the criteria it has the code:

Year([OrderDate])=Year(Now()) And Month([OrderDate])=Month(Now())

Please open the Query in SQL view and post it here.

I'd actually suggest a different criterion to filter a recordset to
the current month; put a criterion on OrderDate of

= DateSerial(Year(Date()), Month(Data()), 1) AND < DateSerial(Year(Date()), Month(Date()) + 1, 1)

John W. Vinson[MVP]