Need Month Name based on Date in a Table

[pcride]

I need to populate the Month Name based on a date field in my table.

Thanks!!

 

[ajetrumpet]

I need to populate the Month Name based on a date field in my table.

Thanks!!

use this code:

format(DateFieldHere, "mmm")

 

[pcride]

Do I put this in the table or the form? I need to have this data logged into a table based on a date field that is entered by a user, it wouldn't be a form field on the Form. Just for reporting etc...

Does that go into the Format under the General Tab in the Table Design ?

 

[ajetrumpet]

Do I put this in the table or the form? I need to have this data logged into a table based on a date field that is entered by a user, it wouldn't be a form field on the Form. Just for reporting etc...

Does that go into the Format under the General Tab in the Table Design ?

no...update your table when you need. use the docmd.runsql command in VBA to do it.


you might be able to set the default value in the table's default value option. i have no idea, never done it. you might as well try it though. try this:

=format([FieldToFormat, "mmm")

format the field as a string, not a date

 

[pcride]

Ok, so I entered that formula on a form field and it worked. But I need this data logged into the table as well. I don't need it on the form really.

 

[pcride]

that didn't work.

This should be simple like excel , take a date and turn it into a month. Sometimes I hate access...

Not sure even how to start coding this up in VB. I am sure its simple but not knowing much makes it hard.

 

[ajetrumpet]

that didn't work.

This should be simple like excel , take a date and turn it into a month. Sometimes I hate access...

Not sure even how to start coding this up in VB. I am sure its simple but not knowing much makes it hard.

upload your db and i'll do it real quick

 

[pcride]

I just made a simple form. Date field and a Month field.

Thanks a bunch!!

 

[ajetrumpet]

I just made a simple form. Date field and a Month field.

Thanks a bunch!!

I dont have 07 on my desktop, although i do have 8gb of RAM.

please convert it and upload an MDB

i'm working on a pretty big PHP web project right now...whenever you get it up here, i'll take a look for ya

 

[pcride]

Here it is. Thanks again.

 

[ajetrumpet]

Here it is. Thanks again.

try the button now...

 

[boblarson]

If you have the date field in the table, you don't need to update the table, just use a query with the format displayed how you want it.

 

[ajetrumpet]

If you have the date field in the table, you don't need to update the table, just use a query with the format displayed how you want it.

.............

 

[pcride]

Ok thanks much, I see that it returns the month number and that should work, although name would be a bit better. But I see how it updates the table and thats what I needed.

Thanks AGAIN!!

 

[pcride]

If I want the button to update without adding in the Table Name, since it will be the same table each time, how can I just make it run without entering in the table name?

mytable = InputBox("enter the table name")

Thanks,

 

[ajetrumpet]

If I want the button to update without adding in the Table Name, since it will be the same table each time, how can I just make it run without entering in the table name?

mytable = InputBox("enter the table name")

Thanks,

replace this:

Private Sub Command7_Click()

Dim mytable As String
mytable = InputBox("enter the table name")

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE [" & mytable & "] SET [monthmain] = month([datemain])"
DoCmd.SetWarnings True

End Sub

with this:

Private Sub Command7_Click()

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE [B][U]TABLENAMEHERE[/U][/B] SET [monthmain] = month([datemain])"
DoCmd.SetWarnings True

End Sub

 

[Rich]

replace this:

Private Sub Command7_Click()
 
Dim mytable As String
mytable = InputBox("enter the table name")
 
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE [" & mytable & "] SET [monthmain] = month([datemain])"
DoCmd.SetWarnings True
 
End Sub

with this:

Private Sub Command7_Click()
 
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE [B][U]TABLENAMEHERE[/U][/B] SET [monthmain] = month([datemain])"
DoCmd.SetWarnings True
 
End Sub

Storing redundant data is madness and shows a lack of understanding of database principles

 

[Rabbie]

.............

Just look at the Date format options. It's really very simple. No need to store redundant data as already has been pointed out. Just extract the month name when you need it.

 

[gemma-the-husky]

if you are getting the month number, you have it set wrong

adam gave you

=format([FieldToFormat, "mmm")


"mm" will give you a month number
"mmm" will give you abbreviated month
"mmmm" will give you the month in full

---------
note that days of week work the same also

 

[Rich]

And of course you don't even need to use the Format function for this