Simple WHERE statement using Month

[jd_boss_hogg]

Hi All - hoping one of you guys can help..

I'm running a query, with a simple WHERE statement...

WHERE ((([jb-2001].WORKTYPE)="Chrome Mask") AND (([jb-2001].GONE)="yes") AND (([jb-2001].Sage)<>"0") AND (([jb-2001].OUT_DATE)= Month(Now())));

I'm having problems with the Month part. I'm trying to show only items where the OUT_DATE is the current month. The above query gives me no results at all (if i omit the out_date field, then everything works). I obvioulsy havn't syntaxed it correctly, although i get no error messages.

Starngely, if i use (([jb-2001].OUT_DATE)> Month(8)) or (([jb-2001].OUT_DATE)> Month(#01/01/09#) or anything like that, i get every record from 6/may/08 ???

Can anyone see that i've done anything wrong with the Month(Now)) coding?

Thanks !

 

[MStef]

Try this:
......AND ((Month([jb-2001].Out_Date)) = Month(Date())));

 

[namliam]

What kind of field is your out_date? Make sure it is a date field.

Then if you are checking Month and Out_date is a date... dont you need to do something else??

Also if Out_date is a date, isnt there the same month in each year?? Thus will you not run into problems next year?

Assuming Out_date is a proper date, you can use:
Date() - Day(date()) + 1 to find the start of this month (and not last year's month)
Dateadd("M",1,Date() - Day(date()) + 1) - 1 will find you the last date of this month.

Then use between to fetch your data

Happy coding !

 

[jd_boss_hogg]

Hey Mstef,

Worked a treat ! But, as namliam pointed out, i hadn't thought about the year also ! So, curenttly i get year 08 and 09, which is what i don;t want.

Is there a simple way of getting this current month (year AND month) in this method?

 

[MStef]

Try this.
.... AND ((Month([jb-2001].Out_Date) & Year([jb-2001].Out_Date)) = Month(Date()) & Year(Date())));

 

[namliam]

ERM Solution posted above and quoted below!!!
Simply not use the month at all as that is the devil's way...

When working with dates (and/or times) where ever, when ever possible dont use the part(s) but rather the whole.

Assuming Out_date is a proper date, you can use:
Date() - Day(date()) + 1 to find the start of this month (and not last year's month)
Dateadd("M",1,Date() - Day(date()) + 1) - 1 will find you the last date of this month.

Then use between to fetch your data

Happy coding !

 

[namliam]

Try this.
.... AND ((Month([jb-2001].Out_Date) & Year([jb-2001].Out_Date)) = Month(Date()) & Year(Date())));

This is not "optimal" because of
1) My above statement re: Use the full date, not the part
The problem with parts is ... they dont work well with the whole...

2) The implicit conversion that is going on.
Both Month and Year return number values while & will append two strings, therefor both numbers/integers will be converted to strings causing problems

I.e. try and get everything beyond this month last year ...
If everything this year is:
((Month([jb-2001].Out_Date) & Year([jb-2001].Out_Date)) = Month(Date()) & Year(Date())));
Then everything this month last year is:
((Month([jb-2001].Out_Date) & Year([jb-2001].Out_Date))-1 = Month(Date()) & Year(Date())))-1;
Now everything beyond that is:
((Month([jb-2001].Out_Date) & Year([jb-2001].Out_Date))-1 >= Month(Date()) & Year(Date())))-1;
???? But that breaks ???? It doesnt work ????
This is because of 2 reasons... but the most important beeing the &, this makes it into a string. In a string a 1 < 9
ie. The order of number is...
1
10
100
2
20
3
30
9

Thus this month order is:
102008
102009
112008
112009
122008
12008
12009

See how things go bad??

Word to the wize: When working with dates work with dates...

 

[jd_boss_hogg]

Hi Mailman

thanks for the explanation - i 'think' i can see what you mean. For the time being, the other solutions also works, although i know that it isn't the 'correct' way to do it. I'll see if i can figure out how to implement your solution also once i've got the guts of this thing working.

Thanks both of you very much ! I really really appreciate your help. I'd also appreciate if you could give me some clues to the next stage of implementing this query..

SELECT [jb-2001].WORKTYPE, [jb-2001].NET, [jb-2001].GONE, [jb-2001].Sage
FROM [jb-2001]
WHERE ((([jb-2001].WORKTYPE)="Chrome Mask") AND (([jb-2001].GONE)="yes") AND (([jb-2001].Sage)<>"0") AND ((Month([jb-2001].[Out_Date]) & Year([jb-2001].[Out_Date]))=Month(Date()) & Year(Date())));

I have a text box on a report. I want the sum of the NET value from the above query to appear in this text box. I presume i need to syntax this correctly, and copy it into the properties/control source of the box? Or do i have to have an external query and then use that as the control source?

 

[jd_boss_hogg]

Sorry - just had a quick play around (i should have done that first). If i use the following SQL everything works for me...

SELECT Sum([jb-2001].NET) AS SumOfNET
FROM [jb-2001]
WHERE ((([jb-2001].WORKTYPE)="Chrome Mask") AND (([jb-2001].GONE)="yes") AND (([jb-2001].Sage)<>"0") AND ((Month([jb-2001].[Out_Date]) & Year([jb-2001].[Out_Date]))=Month(Date()) & Year(Date())));

So this is probably the easiest bit......how do i assign that coding to the properties/control source of a text box on a report? I've tried = Select Sum ....... and that doesn;t work, and also tried = Sum ........ and that doesn't work either.

I have 10 of these text boxes to add to the report - i don;t have to design 10 seperate queries, do i ? I was hoping i could do all of the work in the control source of the text box?

 

[namliam]

Search for "Recordset" (in DAO) in the access help and find out...

If you post code on the forum please
1) format it
2) use the code tags ("[ code ]" in front and "[ /code ]" at the end without the spaces)

Your sql would look something like:

SELECT Sum([jb-2001].NET) AS SumOfNET
FROM [jb-2001]
WHERE ((([jb-2001].WORKTYPE)="Chrome Mask") 
  AND (([jb-2001].GONE)="yes") 
  AND (([jb-2001].Sage)<>"0") 
  AND ((Month([jb-2001].[Out_Date]) & Year([jb-2001].[Out_Date]))=Month(Date()) & Year(Date())));

Preverably without all the extra () in there that are not needed... but I am to lazy for that...

You will agree this is much more readable

 

[jd_boss_hogg]

Hi mailman

Formatting posts - understood....... Recordsets, not understood ;-( Sorry, i'm very much part time at this and havn't learnt very much about acess yet (but am learning loads thanks to forums like this !)

I have an underlying query to a report.

SELECT Sum([jb-2001].NET) AS sumNETposted
FROM [jb-2001]
WHERE ...........
UNION 
SELECT Sum([jb-2001].NET) AS sumNETinv
FROM [jb-2001]
WHERE ......................;

If i run this query, in datasheet view i can see my 2 sum fields clearly and it works great. In the report i can select as a control source for a textbox the first "As" sumNETposted , but i can't select the second AS (sumNETinv) because it doesn't appear in the list for control source ?

I had a quick look at RecordSets, but it appears to be all VBA and i don;t understand that at all.

Hoping you can shed some light on the problem that i have !

 

[namliam]

Recordset = VBA period...

As for your report, offcourse it does that. Your UNION makes the query return 1 column = 1 control with 2 records.
Records dont get seperate controls.... columns do...
What you are doing is not the answer to your problem, the union is used sparingly and far inbetween.

What you are doing is a pivot/Crosstab query - like thing. Either as a full fledged crosstab query or one that does something very much simular using an IIF structure.

Moreover ...
Select * from table where xyz
union all
Select * from table where abc
union all
Select * from table where def

Is generaly a sign of something that is not quite right...

If you want more insight into what you need to do I would need more background/more detail on the SQL...
My guess though is that the where will determain different categories... then something like so

Select sum(IIF(this=that, [jb-2001].NET,0) as sumNETposted
,      sum(IIF(here=there, [jb-2001].NET,0) as sumNETinv
from yourtable

Where "This=That" and "Here=There" is what comes from your where.

This will only access the table once instead of 2 times, which will increase performance and the seperate columns will make for seperate controls.

 

[jd_boss_hogg]

Hey, thanks mailman! I've spent the last few hours googling around and it looks like i can't have more than one query per report (which u obviosuly knew). So, if i understand you correctly, i need to set up recordset in VBA...

In your "this=that" example, can we have multiple citeria? For example, on the report i need one box to show me the result of this SQL..

SELECT Sum([jb-2001].NET) AS GlassSumOfNETposted
FROM [jb-2001]
WHERE ([jb-2001].WORKTYPE ="Chrome Mask" Or [jb-2001].WORKTYPE ="emulsion") AND ([jb-2001].GONE="yes") AND ([jb-2001].Sage <>"0") AND (Month([jb-2001].[Out_Date]) & Year([jb-2001].[Out_Date])=Month(Date()) & Year(Date();

and the next box on the same report to show me the result of this..

SELECT Sum([jb-2001].NET) AS GlassSumOfNETinv
FROM [jb-2001]
WHERE ([jb-2001].WORKTYPE ="Film Mask" Or [jb-2001].WORKTYPE ="photoplot") AND ([jb-2001].GONE ="yes") AND ([jb-2001].Sage = 0") AND ((Month([jb-2001].[Out_Date]) & Year([jb-2001].[Out_Date]))=Month(Date()) & Year(Date());

There are about 10 different values that i want to represent, all of which come from the same table, just with varying 'where' criteria. In the above examples, i have used seperate AS fieldnames but the only things that change are [worktype] and [sage]...

 

[namliam]

So, if i understand you correctly, i need to set up recordset in VBA...

No no no... your totaly misunderstanding me

In your "this=that" example, can we have multiple citeria? For example, on the report i need one box to show me the result of this SQL..

Yes you can have multiple criteria

And yes only one query per report, but any query like
Select ... from ...
Union all
Select ... from ....

can have as many unions as you like and still be only 1 query...

You want 1 column / control per value and/or want a "row header" per value...

Try this:

 Select "a name" as Name, sum().... bla bla
Union all
Select "other name" as name, sum ... bla bla

Though NOT pretty, NOT perfect and NOT "the best way" this I think will do what you are looking for...
Repeat as many Union's as you see fit

The better solution (using the table only once) is what I posted already...

Select sum(IIF(this=that, [jb-2001].NET,0) as sumNETposted
,      sum(IIF(here=there, [jb-2001].NET,0) as sumNETinv
from yourtable

You can stack the IIFs and use AND or OR as however you see fit exactly like you are doing in your where...
So you would get something like

Select Sum (IIF(     (      [jb-2001].WORKTYPE ="Chrome Mask"
                         Or [jb-2001].WORKTYPE ="emulsion")
                 AND (      [jb-2001].GONE     ="yes"     )
                 AND (      [jb-2001].Sage     <>"0"      )
                 AND (Month([jb-2001].[Out_Date]) & Year([jb-2001].[Out_Date])=Month(Date()) & Year(Date())
                 , [jb-2001].NET
                 , 0) as GlassSumOfNETposted
,      sum (IIF( .... ) ) as GlassSumOfNETinv
, etc...
from YourTable

Get the picture?

 

[jd_boss_hogg]

Brilliant - Works a treat using the table-once method !

Thanks SOOOOOOOO much for the help - i'd buy you a pint if you were here !

 

[namliam]

Thanks SOOOOOOOO much for the help - i'd buy you a pint if you were here !

LOL Let me know anytime you hit amsterdam

Select Sum (IIF(     (      [jb-2001].WORKTYPE ="Chrome Mask"
                         Or [jb-2001].WORKTYPE ="emulsion")
                 AND (      [jb-2001].GONE     ="yes"     )
                 AND (      [jb-2001].Sage     <>"0"      )
                 AND (Month([jb-2001].[Out_Date]) & Year([jb-2001].[Out_Date])=Month(Date()) & Year(Date())
                 , [jb-2001].NET
                 , 0) as GlassSumOfNETposted
,      sum (IIF( .... ) ) as GlassSumOfNETinv
, etc...
from YourTable

Looking at that this morning I am thinking you only want the figures for this month, so .... something like:

Select Sum (IIF(     (      [jb-2001].WORKTYPE ="Chrome Mask"
                         Or [jb-2001].WORKTYPE ="emulsion")
                 AND (      [jb-2001].GONE     ="yes"     )
                 AND (      [jb-2001].Sage     <>"0"      )
                 , [jb-2001].NET
                 , 0) as GlassSumOfNETposted
,      sum (IIF( .... ) ) as GlassSumOfNETinv
, etc...
from YourTable
Where (Month([jb-2001].[Out_Date]) & Year([jb-2001].[Out_Date])=Month(Date()) & Year(Date())

Would probably be better. Also remember my statement about the "use the whole date not the parts", I neglected one thing to say... that is if you have any indexes on you date, then those cannot be used using anything like you have above.

While if you use Yourdate Between X and Y then your index can be used and will increase performance rather drasticaly.