Setting ControlSource in VBA

[MarcVS]

I'm trying to set the ControlSource of a TextBox in VBA.
Normally this is simple:
Me.TextBox.ControlSource = "=Date()"

But...

I need to use the DateAdd function:

Me.TextBox.ControlSource = "=DateAdd('w';1;Date())"

This gives an error: 'the syntax of the expression you gave is invalid'
the problem is the '=' sign. Without that I have no error, but of course, the ControlSource is not right then... (it gives #name?")

 

[theDBguy]

Hi. Welcome to AWF! Just a guess but try it this way:

Me.TextBox.ControlSource = "=DateAdd(""w"";1;Date())"

 

[arnelgp]

maybe change the semi colon to comma.

 

[MarcVS]

>Me.TextBox.ControlSource = "=DateAdd(""w"";1;Date())"
This gave the same error

>maybe change the semi colon to comma.
That worked.

But...

The formula is more complex, in fact it is:

Me.TextBox.ControlSource = "=DateAdd('d',-1,DateAdd('m',9,[Formulieren]![frmFaseringCBMI]![datEffectieveStartdatumIn]))"

It gives no error but says #Name#

Now the problem is [Formulieren]![frmFaseringCBMI]![datEffectieveStartdatumIn]

It works when I put in in the controlsource property of the textbox, but not when I set it in VBA...

 

[arnelgp]

remove the variable from the quote:

Me.TextBox.ControlSource = "=DateAdd('d',-1,DateAdd('m',9," & [Formulieren]![frmFaseringCBMI]![datEffectieveStartdatumIn] & "))"

 

[MarcVS]

I had to change it to

"=DateAdd('d',-1,DateAdd('m',9," & Me.datEffectieveStartdatumIn & "))"

but the result is not what was expected: (01/01/1900)
So I changed it to

"=DateAdd('d',-1,DateAdd('m',9,#" & Me.datEffectieveStartdatumIn & "#))"

And now it works.

Thank you for your help.

 

[theDBguy]

I had to change it to

"=DateAdd('d',-1,DateAdd('m',9," & Me.datEffectieveStartdatumIn & "))"

but the result is not what was expected: (01/01/1900)
So I changed it to

"=DateAdd('d',-1,DateAdd('m',9,#" & Me.datEffectieveStartdatumIn & "#))"

And now it works.

Thank you for your help.

Hi. Glad to hear you got it sorted out. Good luck with your project.