Concatenate 1

[Vaudousi]

Dear Friends

I downloaded Concat2k from Mr Hookom. The example works perfectly. Now I am stick with a difficulty and need help for going one step further. If I have a date of birth and an IsActive field in tblFamMem, how do I write the select clause for limiting the output to people born before a chosen date and who are Active.

I have tried many things but each time Access lockups with an error and I am obliged to Ctrl+Add+Del.

Many thanks for your help

 

[dhookom]

What do you mean by "chosen date"? Is a user entering something into a form? Is there a date in a field?
What is your table structure and significant fields?

Duane
Hook'D on Access
MS Access MVP

 

[Vaudousi]

Many thanks for your prompt reply. This is exactly what you sensed.

I use your 2 tables : tblFamily and tblFamMem. tblFamily has 2 more fields : DateofBirth (Date) and isActive (Yes/No).

I want to invite people to a meeting if :
1 - isActive is true
2 - their DateOfBirth is before a date entered into a form.

 

[dhookom]

It would have helped if you provided the table structure with significant fields. We don't know what field value to concatenate or the name of the form and control. However:

Concatenate("SELECT [FirstName] FROM tblFamMem WHERE IsActive AND [DateOfBirth]<=#" & Forms!frmYrFrm!txtDate & "#")

Duane
Hook'D on Access
MS Access MVP

 

[Vaudousi]

Sorry Duane

Before creating my own database, I wanted to experiment with what is in Concat2k just by adding 2 fields for limiting the number of guests.

Only persons who are marked as Active and are born before a date entered on a form will be invited.

I will use the query for a MailMerge. Like in your example, I want to concatenate FirstNames of people members of the same Family.

I tried to complete the last example you posted but was unable to reintroduce in the right place the « WHERE FamID =" &[FamID]»

Again many thanks for taking time to help me.

 

[dhookom]

Concatenate("SELECT [FirstName] FROM tblFamMem WHERE IsActive AND [DateOfBirth]<=#" & Forms!frmYrFrm!txtDate & "# AND FamID =" & FamID)

Duane
Hook'D on Access
MS Access MVP

 

[Vaudousi]

Hi Duane,

I executed your Query as is, but did not get exactly what I expected, close tough. All families are listed even there is no member born before the given date. I confim that, for preventing problems with date format, when asking for the date, I entered 01/01/1945 for my limit (I live in France).

When a family has one or more persons born before this date, the Firstname column displays correctly the Firstnames concatenated.

I spent the last hours trying to figure out how to not display these offending lines, but to no avail. Please come to my rescue.

 

[dhookom]

If you don't want any family records where no members are active and old, you will need to add something to your main query criteria outside the concatenate. Try:

WHERE FamID IN (SELECT FamID FROM tblFamMem WHERE IsActive AND [DateOfBirth]<=#" & Forms!frmYrFrm!txtDate & "#)

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Noticed I missed the last " as I submitted

WHERE FamID IN (SELECT FamID FROM tblFamMem WHERE IsActive AND [DateOfBirth]<=#" & Forms!frmYrFrm!txtDate & "#[COLOR=red yellow][b]"[/b][/color])

Duane
Hook'D on Access
MS Access MVP

 

[Vaudousi]

After 4 days (and nights) trying all combinations, in spite of your kind help, I must admit, to my great shame, that I cannot do it.

I am completely at large. I surrender.

Many thanks for your patience.

 

[dhookom]

I don't like giving up this quick ;-) If you want to provide your most recent SQL view of your query, someone can trouble-shoot it for you.

All dates in queries should be in the format m/d/y or 11-Jun-2009 or similar. You might want to read Allen Browne's - International Dates in Access.

Duane
Hook'D on Access
MS Access MVP

 

[Vaudousi]

Duane, after receiving the post about subquery, I visited Allen's site and downloaded « Subquery basics ». I also searched other sites for problem similar to mine. Nothing found.

Just a confirmation : As your tables were close to mine : a family table and a person table, I added 2 fields to your tblFamMem ; DateofBirth and IsActive. Checking IsActive or modifying BirthDate is easy with few records.

SELECT tblFamily.FamID, tblFamily.famLastName, Concatenate("SELECT [FirstName] FROM tblFamMem WHERE IsActive AND [DateOfBirth]<=#" & [Forms]![frmYrFrm]![txtDate] & "# AND FamID =" & [FamID]) AS FirstNames
FROM tblFamily;

sends back all families even if there is no ember filling the condition: acive or born before the fateful date.

I tried to include your last subquery without success.

 

[dhookom]

Try:

SELECT tblFamily.FamID, tblFamily.famLastName, Concatenate("SELECT [FirstName] FROM tblFamMem WHERE IsActive AND [DateOfBirth]<=#" & Format([Forms]![frmYrFrm]![txtDate]"mm\/dd\/yyyy") & "# AND FamID =" & [FamID]) AS FirstNames
FROM tblFamily
WHERE FamID IN (SELECT FamID FROM tblFamMem WHERE IsActive AND DateOfBirth <=Format(Forms!frmYrFrm!txtDate, "mm\/dd\/yyyy"));

Or

SELECT tblFamily.FamID, tblFamily.famLastName, Concatenate("SELECT [FirstName] FROM tblFamMem WHERE IsActive AND [DateOfBirth]<=#" & Format([Forms]![frmYrFrm]![txtDate]"mm\/dd\/yyyy") & "# AND FamID =" & [FamID]) AS FirstNames
FROM tblFamily
WHERE Len(Concatenate("SELECT [FirstName] FROM tblFamMem WHERE IsActive AND [DateOfBirth]<=#" & Format([Forms]![frmYrFrm]![txtDate]"mm\/dd\/yyyy") & "# AND FamID =" & [FamID]))>1;

Duane
Hook'D on Access
MS Access MVP

 

[Vaudousi]

No joy. Both abort with « Syntax error (missing operator in expression... »

"mm\/dd\/yyyy" is highlighted.

 

[dhookom]

I think I was missing a comma.

SELECT tblFamily.FamID, tblFamily.famLastName, Concatenate("SELECT [FirstName] FROM tblFamMem WHERE IsActive AND [DateOfBirth]<=#" & Format([Forms]![frmYrFrm]![txtDate][COLOR=red yellow],[/color]"mm\/dd\/yyyy") & "# AND FamID =" & [FamID]) AS FirstNames
FROM tblFamily
WHERE FamID IN (SELECT FamID FROM tblFamMem WHERE IsActive AND DateOfBirth <=Format(Forms!frmYrFrm!txtDate, "mm\/dd\/yyyy"));

Or

SELECT tblFamily.FamID, tblFamily.famLastName, Concatenate("SELECT [FirstName] FROM tblFamMem WHERE IsActive AND [DateOfBirth]<=#" & Format([Forms]![frmYrFrm]![txtDate][COLOR=red yellow],[/color]"mm\/dd\/yyyy") & "# AND FamID =" & [FamID]) AS FirstNames
FROM tblFamily
WHERE Len(Concatenate("SELECT [FirstName] FROM tblFamMem WHERE IsActive AND [DateOfBirth]<=#" & Format([Forms]![frmYrFrm]![txtDate][COLOR=red yellow],[/color]"mm\/dd\/yyyy") & "# AND FamID =" & [FamID]))>1;

Duane
Hook'D on Access
MS Access MVP

 

[Vaudousi]

It looks like it is OK now. Let me investigate a little more and I'll come back.

Bravo !

 

[Vaudousi]

Duane, I confirm that the first of the last 2 queries works perfectly. I have not tested the second yet.

I don't want to impose upon your kindness, but if you have a minute left, I have a very last question : in some families, the members have different names. Is it easy to have another column with the name of the person ?

Anyway, mille fois merci pour votre aide. Regards.

 

[dhookom]

Assuming you add a field to the child table [MemLastName] you can return multiple fields in concatenate with an expression like:

Concatenate("SELECT [FirstName] & ' ' & [MemLastName] FROM tblFamMem WHERE IsActive AND [DateOfBirth]<=#" & Format([Forms]![frmYrFrm]![txtDate],"mm\/dd\/yyyy") & "# AND FamID =" & [FamID]) AS FullNames

Duane
Hook'D on Access
MS Access MVP

 

[Vaudousi]

I have it. I will experiment with the variations of Concat. Thanks for the lesson. I realize that Subqueries are very powerful in the hands of experts.