expression problem

[cyraxote]

Hi all.

I have a database in which each record has a corresponding MS Word file. I have the code to open Word, but I'm having trouble creating a pathname from the separate bits of data that have to be strung together.

What I have so far is:

Path: Left([Dockets].[Month],3) & "\" & [Dockets].[Journal] & (Date([Dockets].[Month])) & [qry_MOL_Feb_2003].[LogicID] & ".doc"

where [Dockets].[Month] contains the full text month name, [Dockets].[Journal] contains a 3-letter text entry, and [qry_MOL_Feb_2003].[LogicID] contains a 4-digit numerical ID. Each word file is in a directory with a 3-letter month nam named JJJMLLLL.doc; JJJ is the the journal code, M is the month number (Oct-Jan are all = "1"), and LLLL is the LogicID.

What I want is text (such as):

Feb\MOL21234.doc

The path before the month name is static.

I can't seem to master the syntax necessary for this expression to function---keep getting error messages. Does anyone see anything glaringly obvious that I've overlooked? Am I using Date() incorrectly? Is there a month(date) function?

I'm trying to add this as a field in the qry_MOL_Feb_2003 query. Perhaps a separate query containing just this expression?

Thanks.

Rodney

 

[Pat Hartman]

You are using Date() incorrectly. Date() is a function that takes NO parameters. It simply returns the current date. There are functions that will return the various parts of a date. The one you need is DatePart().

 

[jfgambit]

Also, stop double posting

 

[cyraxote]

Well, the posts aren't exactly the same. I thought I was getting no answers because I (a) hadn't given enough information and (b) might have put the question in the wrong forum.

But thanks anyway for policing my innocent mistake...

 

[jfgambit]

No one is policing you, but getting multiple answers for the same post can confuse you as well as the person trying to help you. If you have additional questions feel free to add them to the original post. The forums are constantly read by individuals who are trying to provide assistance to new users as well as seasoned Access gurus. Sometimes, it takes a couple of hours to get a question answered based on the complicated nature of the question.

Good luck with your database and please continue to post if you need more assistance.

 

[cyraxote]

Ok, My [month] field is text, so I can't use DatePart() on it, and i can't change the data type because that will break other things.

That means, I think, that I'm going to have to

a - reinvent the wheel with a bunch of code saying, in essence, Feb = 2, March = 3, etc.

b - break the other things and figure out how to fix them.

Any suggestions?

Thanks.

 

[cyraxote]

Solved!

I got around the different data type problem by creating a fake date expression:

FakeDate: "7-" & Left([Dockets].[Month],3) & "03"

and feeding it to DatePart():

Path: Left([Dockets].[Month],3) & "\" & [Dockets].[Journal] & DatePart("m",FakeDate) & [LogicID] & ".doc"

All is working as expected.

Thanks all.