Pulling certain parts of fields into one field.

[Raynx]

Hey, I'm not incredibly new to Access, but I do need some help on something that may be simple to you guys. I'm updating/improving a database that a Post Office is using to track packages across a college campus, and they want me to build a button that they can click to automatically send an email to the student to let them know, "Hey, your package arrived. Please print this email and bring it up to the counter to pick up your package." Simple enough, just a macro button for SendObject, have the student's email be in the "To" entry, and set it to not open up for editing. Just auto-send. Right, so this is the problem:
The Post Office has to keep records of upwards of 20,000 entries, with roughly 2000 students and 1000 faculty each fiscal year. The email needs to be a field that auto-completes itself as other information is filled in for the student. Right now, this is the format:
mary.sue359@students.berry.edu, which looks like this in the field format:
[First Name] & "." & [Last Name] & "???" & "@" & "???.berry.edu"
So, I'm not sure how to fill in those question marks. I know what needs to go in there, but not how to format the field to pull the information needed.
The student's ID number is a six-digit figure. The three digits following the name of the student are derived from a combination of the student's current year and the last two digits of the student's ID number. So, for instance, freshman are a 0, sophomores are a 1, juniors are a 2, and seniors are a 3. The last two digits of the number in the email are the same as the last two digits of the student ID number. So, for Mary Sue's example in the above, her status is a senior (inferred by the 3) and the last two digits of her ID number are 59. Make sense?
So, if I have a drop-down field for selecting whether the student is a freshman, sophomore, junior, or a senior, I need the email field to automatically fill in the appropriately related number (the first digit of the three digits for the email). Then if I set up an ID number field, I need the email field to automatically take the last two digits of the ID number. Finally, the second set of question marks needs to denote whether the person is a student or faculty member.

Please, any advice would be useful. This project is huge. Thank you!

 

[MSAccessRookie]

Hey, I'm not incredibly new to Access, but I do need some help on something that may be simple to you guys. I'm updating/improving a database that a Post Office is using to track packages across a college campus, and they want me to build a button that they can click to automatically send an email to the student to let them know, "Hey, your package arrived. Please print this email and bring it up to the counter to pick up your package." Simple enough, just a macro button for SendObject, have the student's email be in the "To" entry, and set it to not open up for editing. Just auto-send. Right, so this is the problem:
The Post Office has to keep records of upwards of 20,000 entries, with roughly 2000 students and 1000 faculty each fiscal year. The email needs to be a field that auto-completes itself as other information is filled in for the student. Right now, this is the format:
mary.sue359@students.berry.edu, which looks like this in the field format:
[First Name] & "." & [Last Name] & "???" & "@" & "???.berry.edu"
So, I'm not sure how to fill in those question marks. I know what needs to go in there, but not how to format the field to pull the information needed.
The student's ID number is a six-digit figure. The three digits following the name of the student are derived from a combination of the student's current year and the last two digits of the student's ID number. So, for instance, freshman are a 0, sophomores are a 1, juniors are a 2, and seniors are a 3. The last two digits of the number in the email are the same as the last two digits of the student ID number. So, for Mary Sue's example in the above, her status is a senior (inferred by the 3) and the last two digits of her ID number are 59. Make sense?
So, if I have a drop-down field for selecting whether the student is a freshman, sophomore, junior, or a senior, I need the email field to automatically fill in the appropriately related number (the first digit of the three digits for the email). Then if I set up an ID number field, I need the email field to automatically take the last two digits of the ID number. Finally, the second set of question marks needs to denote whether the person is a student or faculty member.

Please, any advice would be useful. This project is huge. Thank you!

The answers should be relatively straightforward, and depend on information that you have not yet provided. I have provided dummy field/column names as well as test relationships in my examples, and you will need to substitute the appropriate ones to get it to work.

The "???" before the @ can be derived through the following method:

[INDENT]IIf( StudentYear = "Freshman", "0", 
    IIf( StudentYear = "Sophomore" , "1", 
        IIf( StudentYear = "Junior", "2", 
            IIf( StudentYear = "Senior", "3", "*")))) & 
Right(StudentIDNumber, 2)
[/INDENT]

The "???" before the @ will depend on how a student differs from a staff member and can be derived through the following method:

[INDENT]IIf( StudentFlag=True, "students", "faculty")
[/INDENT]

 

[georgedwilkinson]

Man, if I were a student there I'd be opposed to getting a new e-mail address every year.

 

[Raynx]

Hey, thanks so much for your input.
And, yeah, it is frustrating, especially when you're the guy that everybody points at for it. Well, I have a partner, though, so shared responsibility/blame is nice.

Anyway, I tried to set it up the way that you showed me, and here's what it looks like:

=[FirstName] & "." & [LastName] & "IIf( Student Year = "Freshman", "0", IIf ( Student Year = "Sophomore" , "1", IIf ( Student Year = "Junior", "2", IIf ( Student Year = "Senior", "3", "*")))) & Right(IDNumber, 2)" & "@" & "students.berry.edu"

But! When I click outside of the text box that I entered it into on the form, it comes up with a syntax error. Apparently I did not enter the code correctly.
Please critique and correct? Thank you?

*Specifically, this is the error:
The expression you entered contains invalid syntax.
You may have entered an operand without an operator.

 

[georgedwilkinson]

Hmmmm. What's "Student Year"? If that is indeed the name of a field within the scope of the Iif functions, it should probably be surrounded by brackets, since it breaks the rules of a good name. So something like this would be a little closer, I would think:

=[FirstName] & "." & [LastName] & "IIf( Student Year = "Freshman", "0", IIf ( [Student Year] = "Sophomore" , "1", IIf ( [Student Year] = "Junior", "2", IIf ( [Student Year] = "Senior", "3", "*")))) & Right(IDNumber, 2)" & "@" & "students.berry.edu"

There are potentially other issues. You could try simplifying the statement (just start with the inner Iif and work your way out). If this doesn't work, please let us know back along with the symptom you get for it not working.

 

[MSAccessRookie]

Hey, thanks so much for your input.
And, yeah, it is frustrating, especially when you're the guy that everybody points at for it. Well, I have a partner, though, so shared responsibility/blame is nice.

Anyway, I tried to set it up the way that you showed me, and here's what it looks like:

=[FirstName] & "." & [LastName] & "IIf( Student Year = "Freshman", "0", IIf ( Student Year = "Sophomore" , "1", IIf ( Student Year = "Junior", "2", IIf ( Student Year = "Senior", "3", "*")))) & Right(IDNumber, 2)" & "@" & "students.berry.edu"

But! When I click outside of the text box that I entered it into on the form, it comes up with a syntax error. Apparently I did not enter the code correctly.
Please critique and correct? Thank you?

*Specifically, this is the error:
The expression you entered contains invalid syntax.
You may have entered an operand without an operator.

If "Student Year" is the name of a field, then it needs to be surrounded by "[" "]". That would make your query:

    =[FirstName] & "." & [LastName] & 
        "IIf( [COLOR=red][B][[/B][/COLOR]Student Year[COLOR=red][B]][/B][/COLOR] = "Freshman", "0", 
            IIf ( [B][COLOR=red][[/COLOR][/B]Student Year[B][COLOR=#ff0000]][/COLOR][/B] = "Sophomore" , "1", 
                IIf ( [B][COLOR=red][[/COLOR][/B]Student Year[B][COLOR=red]][/COLOR][/B] = "Junior", "2", 
                    IIf ( [B][COLOR=red][[/COLOR][/B]Student Year[B][COLOR=red]][/COLOR][/B] = "Senior", "3", "*")))) & 
    Right(IDNumber, 2)" & "@" & "students.berry.edu"

I try not to use spaces or other special characters in my column/table/query/report/macro names, so that I do not need to use the "[" and the "]"

 

[Raynx]

Yeah, still not working. I tried both of your models for the code, but they did not work. I'm getting the same error message.
I'll try simplifying it, going one step at a time. Are you sure that there are not any mistakes as for the spaces or other special characters? I am rather new to this, but still, something does not feel right about it.

Thank you both for your help thus far, by the way.

 

[Raynx]

Alright, the IIf strings don't work. The last line (the one that designates the last two digits on the right of the ID Number as the last two digits for the email number) does work. So at least there's that.

 

[Raynx]

I changed up the code a little bit:

=[FirstName] & "." & [LastName] & IIf([StudentYear]="Freshman","0",IIf([StudentYear]="Sophomore","1",IIf([StudentYear]="Junior","2",IIf([StudentYear]="Senior","3","*")))) & Right([ID Number],2) & "@" & "students.berry.edu"

This works in the sense that after clicking outside of the box, no error message pops up, but on the display view, it comes up as this:

#Name?

So obviously, I'm doing something wrong again.
Oh, and doing this:

...(blahblahcodeblah)..."IIf([StudentYear]...(blahblahmorecode)...Right([IDNumber], 2)"...(blahrestofcode)

doesn't work (comes up with the same error message). Notice the quotation marks before the first IIf and after the final closing parentheses. That's the only change I made for this code versus the one shown further above.

 

[MSAccessRookie]

I just noticed how you were going to use the SQL code. Standard SQL format (which is what I gave you), will not work there. You need a single line SQL Query that is something closer to VB Format. The chr(34) that has been added, puts double quote characters into the string. Try this: (I hope I got all of the right quotes in all of the right places).

[COLOR=black][FONT=Verdana]=[FirstName] & "." & [LastName] &  "IIf( [Student Year] = " & Chr(34) & "Freshman" & Chr(34) & ", " & Chr(34) & "0" & Chr(34) & ", IIf ( [Student Year] = " & Chr(34) & "Sophomore" & Chr(34) & " , " & Chr(34) & "1" & Chr(34) & ", IIf ( [Student Year] = " & Chr(34) & "Junior" & Chr(34) & ", " & Chr(34) & "2" & Chr(34) & ", IIf ( [Student Year] = " & Chr(34) & "Senior" & Chr(34) & ", " & Chr(34) & "3" & Chr(34) & ", " & Chr(34) & "*" & Chr(34) & "))))" & Chr(34) & " & Right(IDNumber, 2) & " & Chr(34) & "@" & Chr(34) & " & " & Chr(34) & "students.berry.edu" & Chr(34)[/FONT][/COLOR]

 

[Raynx]

Wow.
That is rather long.

Well, this is what it looks like on the display view:

mary.sueIIf( [Student Year] = "Freshman", "0", IIf ( [Student Year] = "Sophomore" , "1", IIf ( [Student Year] = "Junior", "2", IIf ( [Student Year] = "Senior", "3", "*"))))" & Right(IDNumber, 2) & "@" & "students.berry.edu"

(once again, just as a reminder, the name "Mary Sue" is just a placeholder name I'm playing around with; it's not my real name)

I'm thinking there are some quotation marks out of place, but after a few minutes of trying to pinpoint exactly where, it still led me to nothing (error message, again).

Thanks, though. It looks like we'll be figuring this out very soon.

 

[MSAccessRookie]

Wow.
That is rather long.

Well, this is what it looks like on the display view:

mary.sueIIf( [Student Year] = "Freshman", "0", IIf ( [Student Year] = "Sophomore" , "1", IIf ( [Student Year] = "Junior", "2", IIf ( [Student Year] = "Senior", "3", "*"))))" & Right(IDNumber, 2) & "@" & "students.berry.edu"

(once again, just as a reminder, the name "Mary Sue" is just a placeholder name I'm playing around with; it's not my real name)

I'm thinking there are some quotation marks out of place, but after a few minutes of trying to pinpoint exactly where, it still led me to nothing (error message, again).

Thanks, though. It looks like we'll be figuring this out very soon.

You may have an extra quote. Try to see what happens if you get rid of the quote that is in RED (see below)

IIf( [Student Year] = "Freshman", "0", IIf ( [Student Year] = "Sophomore" , "1", IIf ( [Student Year] = "Junior", "2", IIf ( [Student Year] = "Senior", "3", "*"))))[SIZE=5][COLOR=red][B]"[/B][/COLOR][/SIZE] & Right(IDNumber, 2) & "@" & "students.berry.edu"

 

[Raynx]

Nope. Deleted that single quotation mark and...the same error message. Darn.

 

[MSAccessRookie]

Nope. Deleted that single quotation mark and...the same error message. Darn.

In order to proceed any further, I think we will need to see the error in action. Is there any way to get a copy of the database (all real data removed, of course) that includes the queries, forms, etc related to this problem? If we can see the problem occur, it will be easier to identify

 

[lala]

i think i know what the problem is, you're using a macro's SEND OBJECT command to do this and i don't think you can put any kind of code in the TO field of a macro's SEND OBJECT

i can be wrong, but didn't work for me


question for the pros here: what about using SEND OBJECT from VB, is it possible to do this there? i also need something like this

 

[lala]

and to answer my own question, yes, if you do it through VB, then it works, just tried it

 

[Raynx]

i think i know what the problem is, you're using a macro's SEND OBJECT command to do this and i don't think you can put any kind of code in the TO field of a macro's SEND OBJECT

i can be wrong, but didn't work for me


question for the pros here: what about using SEND OBJECT from VB, is it possible to do this there? i also need something like this

I am using a macro.
I am using SEND OBJECT.
I'm not putting the code into the TO field, but rather putting the code in an email field on a form and having the TO field pull from whatever is listed in the email field.

 

[lala]

that's why it's not working

what they're telling you is to use from VB and then it works, i tried

so instead of using a macro, chose EVENT PROCEDURE, and then say
docmd.send object
and once you do that, you get a popup that helps you on where to put all the arguments

it's all the same things as in the macro, but in a different format

let me know if you got it
if not, i will later send you a sample

 

[kjohnson]

Hey, I'm Kyle, and I am actually working with Ryan on this database project. We know that in the near future, our school will be switching to a new e-mail address algorithm. So, what would be the best way to put this algorithm into action with in access 2007?