DateAdd in unbound control source

[murph]

Hi guys,

I'm fairly new to access but thought what I was trying to do was fairly simple. I could really do with some help.

I'm trying to calculate and display a date 6 months in the future from "DateOne". DateOne is a manually entered field which is stored in a table.

FutureDate is an unbound text box in a form. Can anyone tell me help me with this? I've tried using the DateAdd function by giving the FutureDate Control Source a value of:

DateAdd("m",6,[DateOne]) this just gives me a #NAME? value when viewing the form.

=DateAdd("m",6,[DateOne]) which just returns the error "There was an error compiling this function" whilst still in design mode

Can anyone tell me where I'm going wrong?

Cheers,
Murph

 

[KenHigg]

=DateAdd("m",6,[DateOne]) should work provided [DateOne] is either the name of a text box or the name of a field in the forms underlying query.

 

[murph]

Cheers Ken. I thought this was correct but it's definitely not working. The full pop-up error message I'm getting is "There was an error compiling this function. The Visual Basic module contains a syntax error. Check the code, and then recompile it."

Could it be an underlying problem with the was VB is installed... or something?

-Murph

 

[KenHigg]

Hum... I would try doing it in a new form and see what happens.

 

[KenHigg]

I think you may have an error else where - ?

Open the form code module and compile it and see what happens.

 

[murph]

TRied compiling and the module window highlights the "=" at the front of the =DateAdd...

and a Microsoft Visual Basic pop up appears with "Compile error: Expected: line number or label or statement or end of statement"

 

[KenHigg]

Did you try creating a new form and trying to get it to work?

 

[murph]

Yeah I've tried the same formula in many new forms. I've even tried replacing DateOne with Date() and other values. I'm getting the same errors with completely different, but related code. For example:
=DateSerial(year([StartDate]), month([StartDate])+6, day([StartDate]))which I pasted in directly from the ms example at: http://office.microsoft.com/en-us/access/HA010550721033.aspx

 

[KenHigg]

a. Not sure but you may need to check references... ?
b. Have you tried a fresh brand new .mdb?

 

[murph]

All references appear to be correct but I'm obviously missing something. It works perfectly in a brand new database (with only those two fields present). I hope it doesn't come to it but if it works I'll just have to start it all over again!!

Ken you're a star. cheers for your time and help.

 

[KenHigg]

Cool - Glad you have it working. At this point you can try compact & repair or copy all of your objects over into the new .mdb - ?