formulae in string

[ClaraBarton]

I cannot get these two formulae strings to compile

Dim moName As String
moName = "Choose(Month([WhatDate]),'January','February','March','April', " & _
            'May','June','July','August','September','October','November','December')"
Dim daName As String
daNsme = "Choose(Weekday([WhatDate]),"Sun","Mon","Tues","Wed","Thurs","Fri","Sat")"

I've used both formulae in a query but when I try to write them in a vba string they don't work. So I broke them into string variables and they still don't work.

 

[theDBguy]

What does "don't work" mean? How exactly did you try to use it? Did you need to include an equal sign, maybe?

 

[ClaraBarton]

by don't work I mean I can't get it accepted in the vba window. Which I assume means it isn't a true string. It stays red so I can't even debug it. Where would I put an equal sign?

 

[theDBguy]

by don't work I mean I can't get it accepted in the vba window. Which I assume means it isn't a true string. It stays red so I can't even debug it. Where would I put an equal sign?

Can you post the entire code you're trying to use or show a screenshot of the area in red?

 

[plog]

Agree with theDBguy, but have a better question---Why?

What Rube Goldberg type of contraption are you building that you need a formula inside a string? Not only that but you are reinventing the wheel. There's a built in function for getting the name of the month a date is in:

MS Access: MonthName Function

This MSAccess tutorial explains how to use the Access MonthName function with syntax and examples. The Microsoft Access MonthName function returns a string representing the month given a number from 1 to 12.

www.techonthenet.com

And the name of the day of the week:

MS Access: WeekdayName Function

This MSAccess tutorial explains how to use the Access WeekdayName function with syntax and examples. The Microsoft Access WeekdayName function returns a string representing the day of the week given a number from 1 to 7.

www.techonthenet.com

What need does your function inside a string meet that can't be satisfied a better way?

 

[ClaraBarton]

Ok, Plog. I admit I lost my mind there. Fixed, thankyou very much. So here's my problem:

That red stuff... These queries all work but I wanted to put them in vba so I could use variables for the dates. I have to make a temp table because it's the easiest way I know to get sequential numbers on each record. Go ahead... tell me I'm doing it so badly.

 

[theDBguy]

Try:

"(SELECT DateAdd(""d"",[num],#" & stDate & "#) " & _

Same with the other one.

Hope that helps...

 

[Gasman]

Tip: Put the space for the next line concatenation at the start of that next line. Then you do not have to scroll to the far right all the time to ensure one is there.

 

[plog]

I don't see any crimes against humanity, but I do have thoughts.

tblDates only needs 2 fields if you need sequential numbering--DateID and WhatDate. All those other values are calculable from WhatDate so you wouldn't store them.

How often you going to do this? Less than once a month; keep doing what you are doing. More than that and I tighten up the process. You probably could get a tblDates refreshed with just 2 queries and no VBA--one to truncate it, one to populate it. Then wrap it in a macro and you can refresh it in a double click.

What's the big picture? Perhaps there's a better way to accomplish the ultimate goal.

 

[ClaraBarton]

You guys are good! Thank you so much.