Date and String format

[Sound Steve]

Hi there,

Am trying to update a databse made by an ex emplyee of our company and am stuck!!!

I've created a new form to enable the user to select daily/monthly or weekly reports based on the underlying set of tables/queries. The problem is that i intend using the calendar control to allow the user to selc dates for the reports(sounds simple huh)....but the previous coder has built the databse using date fields as string variables rather than dates(i.e January 2004, would be two seperate string variables rather than a date field of 01/01/2004).

The databse is pretty large so i don;t want to have to redesign it all, i am looking for some code to allow me to take a date from the calendar(or Now()) function and then make it into a string which i can then work with. I need the date, year and day seperated out.

I think this must be possible?

Any help would be gratefully received!!!!

 

[RoyVidar]

The date function provides you with the current date. Hit ctrl+g and type in the following statements, hit enter, and see the results.
? Year(Date)
? Month(Date)
? Day(Date)

You seem to have inherited lot of future headaches, I'm afraid.

 

[Mile-O]

Use the Format() function:

i.e.

?Format(Date, "mmmm yyyy")

 

[Sound Steve]

Your telling me i've inherited a nightmare!!!!


Where do i place the Format function?

This is an outline of the code i'm trying to get to function at present. I want to check if a checkbox has been ticked. If it has i would like to populate a text box with a year and month. these will then be used to produce the reports. The code below will check if the current month has been selected, and will use the (NOW()) function to return the current month and year. If historical data is required the user simply selects from a set of combo boxes.

If Me.chkcurrentmonth.Value = -1 Then

monthdate = Now()
mth = Format(monthdate, "mmmm yyyy")
me.txtmth.value = "mth"
Else
MsgBox "hello from unchecked MsgBox", vbOKOnly 'test message

End If

I hope this provides more info!

I think its an issue with the way in which i am trying to deal with the variables.

 

[Mile-O]

If Me.chkcurrentmonth.Value = -1 Then
        
       monthdate = Now()
       mth = Format(monthdate, "mmmm yyyy")
       me.txtmth.value = "mth"
 Else
       MsgBox "hello from unchecked MsgBox", vbOKOnly 'test message

End If

If Me.chkCurrentMonth Then
    Me.txtMth = Format(Date, "mmmm yyyy")
Else
    MsgBox "Hello from Unchecked MessageBox"
End If

 

[Sound Steve]

format

i get a "compile error" "can;t find project or library" message when i try to execute code with the format function.

Do i need to enable it in the library menu?

 

[Mile-O]

Yes, sounds like you have a missing reference.

Open a module, goto Tools->References and see

Format() is a default function so it's strange.

 

[Sound Steve]

references

Am not sure which reference i would have to add in!

 

[Mile-O]

So there's nothing that says MISSING beside it? Hmmm

Try compacting and repairing the database.

 

[RoyVidar]

Not adding in, but check if any is marked as missing or invalid.

If none are marked as missing, I'd try to remove the Microsoft DAO 3.# Object Library, and add the reference again.

You can perhaps also try:

Me.txtMth = monthname(month(vba.date)) & " " & year(vba.date)

or explicitly referencing all the functions

Me.txtMth = vba.monthname(vba.month(vba.date)) & " " & vba.year(vba.date)

Me.txtMth = vba.Format(vba.Date, "mmmm yyyy")

Sometimes problems with the date function may also be because you have a control named "Date" (or any of the other functions)

 

[Sound Steve]

Still not working

compact and Repair didn;t work


the line of code it is failing on is....

Me.txt52 = Format(monthdate, "mmmm yyyy")

Where Me.txt52 is a textbox, and monthdate is a variable holding a date in it.

 

[Mile-O]

compact and Repair didn;t work

I gave you code above so that you wouldn't need all these unnecessary variables you had. Why change it?

Try, as Roy says, unchecking the references you have and then checking them again.

And if it still fails, create a new database, and import everything into this one.

 

[Sound Steve]

thanks

Thanks for your help and advice. Am going to recommend to the management we look at sorting out the bigger issue though (i.e the string variable dates). I think it might be as easy to get it right first time...rather than putting off the enevitable with "quick fixes"

Your comments and solutions were greatly received though.

 

[ssmithri]

Your issue might be the space:

Me.txt52 = Format(monthdate, "mmmm yyyy")

try

Me.txt52 = Format(monthdate, "mmmmyyyy")