help in retriving data till current month

[eddii]

iam using in a query which has field [month] containing jan,feb,mar.... till dec
for this [month] field the " data type " in table is " text"


because when i open the query name " till this month " i want it to show the month names till current month that is till "oct" and should not show nov & dec
for this i tried to give this in criteria but iam not getting because [month] field data type is text

<=Now()

what should i do to see the months till this current month

can you help pls

 

[vbaInet]

[month] field data type is text

That doesn't sound like a very good design.

But you need <= Month(Date())

 

[eddii]

thanks for the reply VBAINET as you say design is poor

how can i change my table to store only month like ( jan) not as day month & year (01/jan/2011) o

 

[vbaInet]

You store it as day/month/year (Date field) but you can retrieve the month from it. That's a much better design.

 

[Brianwarnock]

A couple of points
Don't use words like month,date for field names as they are reserved words as you saw in vb.'s first post
Always store the complete date in a date time field as it is easy to parse it into any require part.

Thus your criteria would become something like
Mymonth: month(mydate) in the field row

<=month(date()). In the criteria

Brian

 

[eddii]

thanks for your help vbaInet and brain warnock
i actualy want only month names in the field dont want day and year if i give date/time in table datatype field it is asking for longdate, generaldate etc

so iam not able to put to data type date/time field

or iam not able to understand your answer

thanks for the help thank you very much
let me post it in table forum

 

[CBrighton]

You can create custom formats.

"mmm" should give you the short month name (but I may be wrong, it may be the full month name).

I don't seem to be able to find a site which lists all the possibilities, but here's a link to the format function: http://www.webcheatsheet.com/SQL/access_functions/format.php

Note that the results of the 1st & 2nd example are the same, one using the pre-defined "Long Date" and the other using the custom "mm/dd/yyyy".