PLEASE--Need help with my query coding..

[KimHuff]

Well, I just cant figure out what I am doing wrong so I hope someone here can help me.

I have a rather small database with the following fields

ID
NonPublished
StudentLastName
StudentFirstName
Address
Address2
Zip
HomePhone
Parent1LastName
Parent2FirstName
Parent2LastName
Parent2FirstName
GradYear

I am trying to create an expression for the Parents that will combine the parent fields so they will display a certain way. For example.. lets say that you have the following...

Parent1LastName: Huff
Parent2FirstName: Kim
Parent2LastName: Huff
Parent2FirstName: Larry

As long as the last names match, I would want that to display as Kim & Larry Huff

BUT, lets say you have this...

Parent1LastName: Stanley-Huff
Parent2FirstName: Kim
Parent2LastName: Huff
Parent2FirstName: Larry

I would want that to display as Kim Stanley-Huff & Larry Huff

AND, if there is only one parent listed...

Parent1LastName: Huff
Parent2FirstName: Kim
Parent2LastName
Parent2FirstName

I would want it to display as just Kim Huff.

I have tried to get this to work without much luck.

Does anyone have any suggestions on what would allow me to receive my desired output?

Thank you in advance,

Kim

 

[namliam]

A pretty simple IIF or function will do that for you, what have you got that is not working?? Then we can start from there...

 

[KimHuff]

Well, when I initially tried to do this I had not split up the parent field. When I received the Excel file the parents were separated by two fields...

Parent1
Parent2

So not unlike the examples I was giving you before you would have...

Parent1: Kim Huff
Parent2: Larry Huff

I ran a script to separate them so you have...

Parent1LastName: Huff
Parent2FirstName: Kim
Parent2LastName: Huff
Parent2FirstName: Larry

I did this so parents with the same last name would not need to have the last name printed twice.

BUT, prior to doing that this is the query I used.

Parents: IIf(IsNull([Parent2]),[Parent1],([Parent1] & " & " & [Parent2]))

Unfortunately, this statement works BUT, if there is no Parent2 I get the following...

Kim Huff &

I thought the statement was reading... If Parent2 is null, then print Parent1, otherwise print Parent1 & Parent2. Obviously, I was wrong because I always get the & after the Parent1 when there is no Parent2.

Then I was thinking, I would have more room if I only printed the last name once if both parents last name matched, so I broke up the two fields. I know if I couldnt get the other IIF statement to work (which I was sure in my head would work), there is NO WAY, I will figure out the more involved statement

Any suggestions?

Kim

PS If anyone has a clue as to why my first statement didnt work, I would love to know where I went wrong.

 

[Kryst51]

Ok, I THINK the reason could be that "IS Null" is for numbers. You have to use something that identifies zero length strings for this. I am not quite sure how to do that though.

 

[SimonB1978]

Hi,

As Kryst51 mentionned, you should check for zero length string. Changing your condition to this should cover both nulls and zero-length:

IsNull([Parent2]) OR [Parent2] = '' (single quotes here)

HTH

Simon B.

 

[Brianwarnock]

Well now I must be doing something wrong as IsNull works just fine whether
I don't enter anything in Parent2
Enter and delete from parent2
enter Blanks into parent2 , this one did surprise me.
Of course it could be different with an import from Excel ?

so I don't know why your original IIF didn't work

Parent1LastName: Huff
Parent2FirstName: Kim
Parent2LastName: Huff
Parent2FirstName: Larry

I take it that the 2 parent2Firstname fields is a typo?

Assuming that the isnull does work then this should work for your new table
IIf(IsNull([Parent2FirstName]),[Parent1FirstName] & " " & [Parent1LastName],[Parent1FirstName] & " & " & [Parent2FirstName] & " " & [Parent1LastName])


Brian

 

[KimHuff]

Dear Brian,

You are correct there was a typo in my example. (I have got to start getting some sleep before I post!) When I wrote the initial message I was going from memory. The actual field names are as follows:

Parent1FName
Parent1LName
Parent2FName
Parent2LName

SO, I took what you gave me and edited the fields to match the actual field names and although it worked, there are aspects that did not.

First, when there was NO Parent2LName it was supposed to print just the first and last name of Parent1. But instead it would print FName & LName.

Example: Kim & Huff (instead of Kim Huff)

So, I changed the code you gave me and replaced the IIF(IsNull to IIF(nz and that fixed it so the first name printed correctly if there was no Parent2LName.

The problem is when there IS a Parent2LName, it still only printed the Parent1 first and last name (Kim Huff).

PLUS, the problem gets deeper than that. I dont think the expression will check to see if the Parent1LName and the Parent2LName match.

Remember my example... The parental information held in this database will vary. For example, there may only be one parent or there could be two. In some cases both parents will have the same last name and in other cases they wont.

Example #1: (Only one parent should print as Kim Huff)
Parent1FName: Kim
Parent1LName: Huff
Parent2FName
Parent2LName

Example #2: (Two parents with the same last name should print as Kim & Larry Huff)
Parent1FName: Kim
Parent1LName: Huff
Parent2FName: Larry
Parent2LName: Huff

Example #3: (Two parents with different last names should print as Kim Stanley-Huff & Larry Huff)
Parent1FName: Kim
Parent1LName: Stanley-Huff
Parent2FName: Larry
Parent2LName: Huff

That means we have three scenarios because there will always be something in the Parent1LName and Parent1FName fields.

If anyone has any ideas, I would really appreciate the help.

Kim

 

[namliam]

When ever checking for Null, also check for an empty string "".
Us humans cannot see the difference, but the database can.

NO Null is not for numbers only, it is for any field type...

Parents: IIf(IsNull([Parent2]) or Parent2 = "",[Parent1],([Parent1] & " & " & [Parent2]))
Should do that trick...

 

[Atomic Shrimp]

It's sometimes easier just to check the length of the string, so instead of
Parents: IIf(IsNull([Parent2]) or Parent2 = "",[Parent1],([Parent1] & " & " & [Parent2]))

I think you could have:
Parents: IIf(len([Parent2])=0,[Parent1],([Parent1] & " & " & [Parent2]))

By the way... Are the people whose names you're entering in the database always 'parents'? - just something to think about - could this also include 'guardian' or foster parent, etc (in which case, might they take offence at the term 'parent'?)

Could there ever, under any circumstances be more than two of them?

 

[Brianwarnock]

I realised late last night that we hadn't covered all scenarios, at this point I would write a function.
I have changed the field names to save on my slow typing

Function fparents(p1fn, p1ln, p2fn, p2ln) As String

If p1ln = p2ln Then
fparents = p1fn & " & " & p2fn & " " & p1ln

ElseIf IsNull(p2ln) Or p2ln = "" Or p2ln = " " Then
'that should cover it
fparents = p1fn & " " & p1ln

Else
fparents = p1fn & " " & p1ln & " & " & p2fn & " " & p2ln

End If
End Function

In the query parents:fparents(field1, field2,etc )and you could just use your field names here and leave the function as is they don't have to be the same only in the correct order.

Brian

 

[Atomic Shrimp]

Another thing to chew over - how will you handle deaths and divorces?

 

[KimHuff]

You all bring up good points. Here are more details.

First, I receive an excel file from the school with all of the data in it. Everything is all in caps (I hate that) and is combined. For example, the student name is actually all in one field. So, I run a query that separates the first name from the last name so they are in two different fields. I also run a query expression that removes the caps so only the first letter of each word is capitalized.

The Parent info (and you are right there may be a guardian situation so they label the column as Parent / Guardian) is in two different fields... Parent1 and Parent2.

I think it is stupid and a waste of space to list both parents last names if they are the same hence my questions on this query. So I separated each of the Parent fields so I would have more control over that text.

With regard to death and divorces, the I get new information each year so next year when I get a new excel file, it "should" show all the updates (including divorces and deaths).

Another thing that I have to tend to that I dont know how you can program for is some of the kids are living in treatment facilities. Those kids are either deleted or have just a name entry and that is it.

Ideally, since I will be doing this for several years, would be to make it as easy as possible to run scripts allowing the data to end up in the format I need to make the directory. I know this will mean running several query expressions, but that is easier than typing over 2,500 names and addresses. ;-)

Thank you again for all of your help. Brian, I will try what you suggested when I get back from my appointment.

Kim

 

[KimHuff]

Dear Brian,

WOW! I wish I knew how to write functions like that. When I see what you wrote it makes absolute sense to me but actually writing it.. I would be lost. ;-)

Anyway, I tried putting this in the query window as an expression and I keep getting a popup wanting to know the value of fparents.

Expression in my Query

parents: ([fparents])

I had originally entered it without the square brackets and parentheses but when I tabbed it entered those for me.

Am I doing something wrong? I have entered the function under the modules and called the module fparents but I am just not seeing what I am doing wrong. It has been so long since I have done this stuff... probably 6 or more years.. and I wasnt that great to begin with. ;-)

Thank you again for all your assistance.

Kim

More Info...

I went into the query and selected Build. I browsed to the function and double clicked on it and this is what it placed in the query field.

fparents([«p1fn»],[«p1ln»],[«p2fn»],[«p2ln»])

With this it comes up saying this is an undefined function. I am sure I need to enter the "fields" you mentioned but I am not sure what you mean by that. Is this supposed to be an update or make table query? That wasnt what I thought we were doing. Sorry I am having what I am sure is a blonde moment. (no offense to the blondes out there).

 

[namliam]

You have to replace each of the function required fields enclosed by [«»], by the actual fields you want to be Merged...

 

[KimHuff]

OK, I guess I am still not getting this.

I went into the table and changed the names of all the fields to the following:

Parent1LName is now p1ln
Parent1FName is now p1fn
Parent2LName is now p2ln
Parent2FName is now p2fn

I went the query and added the following to the query:

parents: fparents([p1fn],[p1ln],[p2fn],[p2ln])

When I click on View Datasheet View I get the following error:

Undefined Function "fparents" in expression

Any suggestions,

Kim

 

[namliam]

You dont need to rename the columns in your table, you just need to put the "proper" column names in the function...
fparents([Parent1LName],[Parent1FName]...)

Did you put the function supplied into a module? If so try adding Public to it.. though redundent:

[B]Public [/B]Function fparents(p1fn as variant, p1ln as variant, p2fn as variant, p2ln as variant) As String

If p1ln = p2ln Then
    fparents = p1fn & " & " & p2fn & " " & p1ln

ElseIf IsNull(p2ln) Or trim(p2ln) = "" Then
'that should cover it
    fparents = p1fn & " " & p1ln

Else
    fparents = p1fn & " " & p1ln & " & " & p2fn & " " & p2ln

End If
End Function

 

[namliam]

No need to change your column names... the colums can stay the same in your tables... you simply have to fill in the names in the function...
fparents([Parent1LName],...,...,...)

Get it?

Did you paste the full function into a module?? If so try adding "public" in front of the function...
Public Function fparents(p1fn, p1ln, p2fn, p2ln) As String

 

[Brianwarnock]

Thanks for helping our Namliam, I made the mistake of assuming that, no worse than that I never even thought about whether the poster knew how to handle functions and VBA.

Looking at your 2 previous posts I suspect that you are suffering the same daytime(GMT) performance frustrations with the site as I am.

Brian

 

[namliam]

Yes sometimes performance seems to slow to a reverse crawl under water with a diving bell on

 

[KimHuff]

Thank you guys for all of your help. I did what Namilam suggested and made sure it said Public before function but it still didnt work. Not sure why so here is what I did... and btw.. it is working.

I copied the code and put it in another module that I knew was working and deleted the one I created. The moment I did that... it all worked great. I cant figure out what I was missing or what I did wrong in the module I created but I was able to get it to work.

If you all arent too tired and/or frustrated with me yet, can I pose another question?

I have the following code that I need to alter just slightly. I have tried to do it but I never get the output to format correctly. Here is the code.

Function getfirst(InWord As String)
On Error GoTo ErrorHandler

Dim ans As String
ans = left(InWord, InStr(InWord, " "))

getfirst = ans

ExitProcedure:
Exit Function

ErrorHandler:
Call LogError(Err.Number, Err.Description, "getfirst", "basSplitFunction")
Resume ExitProcedure

End Function

Function getsecond(InWord As String)
On Error GoTo ErrorHandler

Dim ans As String

ans = Right(InWord, Len(InWord) - InStr(InWord, " "))
getsecond = ans

ExitProcedure:
Exit Function

ErrorHandler:
Call LogError(Err.Number, Err.Description, "getsecond", "basSplitFunction")
Resume ExitProcedure

End Function

What these two functions do is split up the contents of a field. For example.

Parent1: Huff-Stanley, Kimberly S.
Parent2: Huff, Lawrence S.

I want that to split up into four fields

Parent1LName: Huff-Stanley
Parent1FName: Kimberly S.
Parent2LName: Huff
Parent2FName: Lawrence S.

** NOTICE ** That after the item is split up, the comma is gone and there are no unnecessary spaces.

The above script splits it up but the comma stays in there and some of the last names dont split correctly... like O Connor and Van Dells. So, I tried changing the script so the " " was listed as ", " so it would split on the comma instead of the space. This worked GREAT but it too leaves the comma in there and now I have a space in front of both the columns containing the first name. I know the script wont be perfect cause there will always be a chance of someone having a last name like Huff, Jr. or Huff, Sr. or Huff, III. But, these are few and far between and as long as the spaces and commas are gone, I should be able to fix a dozen or so fairly quickly. Unfortunately, it is difficult to contend with every possible scenario.

Thank you again for all your help. If any of you are in the Indianapolis area.. I sure owe you a nice dinner!!!

Kim