Displaying Month on a Form

[cornishteeth]

I have a form that runs off of a query. I use the Month Function to ask user to enter a number (1-12) to pull data for that specific month.

Month([DAYS DATE])

Works fine. On my form though, I want the month to be displayed. Is there a way to have the actual month be displayed on the form. I tried using the MonthName Function, but kept getting errors. Any suggestions.

Frank

 

[mdmilner]

MonthName should work - were you passing it a numeric or text value instead of a date value?

DateSerial is a useful function to ensure that you've really got a 'date' variable.

 

[Abulafia]

I'm guessing that this database is relatively new. When you have two years worth of data then you are going to run into problems as typing in 6 for June will return June for EVERY year in your database which, no doubt, you won't want.

As has been said above, the best way is to use the DateSerial function to create a range in order to have perfect criteria:

Between DateSerial(Year, Month, 1) And DateSerial(Year, Month, Day)


With a date, you can easily get the month name by using the Format() function.


=Format(myDate, "mmmm")

 

[bat17]

I am guessing that you are having the query ask for the criteriaa, instead set up a control on the form for the user to pick their date from and link the criteria of the query to this.

HTH

Peter

 

[cornishteeth]

I have been reading up on the DateSerial function and don't see where it would fit in my situation. I understand that after 2 years, I will be obtaining erroneous data. Never thought of it that way. How would I go about implementing this function into my own database. I see I know nothing of VB, so posting code won't help unless you give me detailed instructions. Secondly, to reiterate my problem, I have a query that asks the user for a month by number. That in turn gives me all data from that month. I was attempting to display the month on my form. Is there a way to have the actual month be displayed on the form?

Thanks,

Frank

 

[cornishteeth]

Figured out the DateSerial Function. I do not need the Day in there. How would I arrange it to where user enters month/year vice month/day/year.

Thanks,

Frank

 

[Abulafia]

Figured out the DateSerial Function. I do not need the Day in there. How would I arrange it to where user enters month/year vice month/day/year.

Although you don't need a specific day, it's still advised to put the 1st of the month and the last of the month into the query to create the date range.


I don't have to explain any VBA as I haven't mentioned any VBA.

 

[Abulafia]

Query Criteria: Set [Enter Month] as a Parameter

Between DateSerial(Year(Date()),[Enter Month],1) And DateAdd("m",1,DateSerial(Year(Date()),[Enter Month],1))-1


This will return all records for the selected month within the current year. I'd recommend you request the year also from the user.

If you do this, the set [Enter Year] as a Parameter and change the criteria to:

Between DateSerial([Enter Year],[Enter Month],1) And DateAdd("m",1,DateSerial([Enter Year],[Enter Month],1))-1


Personally I'd put two textboxes on a form and let the user specify any range they so wished - why restrict them to a month all the time? By having two textboxes they can pick to look at info for one day, one week, one month, three months, six months, 23 days, one year, ten years, etc.

So much better.

It's even shorter for criteria purposes.

Between [Forms]![MyForm]![txtStartDate] And [Forms]![MyForm]![txtEndDate]


where MyForm is the name of your form and txtStartDate and txtEndDate are the names of the textboxes.