Merging fields in the Query

[Haas]

I have used the following expression to merge two columns, Ineed to hide the
time and keep only the date in the expirydate.

Expr1: ([certificate_no]) & '---' & [expirydate]

Also I have student list of students in the table with their emails and each
student has more then 1 certficiate so i need to combine like.

Student Certifices & expiry date
1 250 - 22/12/2002 , 1240 - 02/02/2003 and so on.

The certificate no and expiry date are two different fields i combined them
using the above expression, so i need to make them a list like above.
Thank you in advance.
Any assistance.
Regards

 

[Allen Browne]

Use Format() to concatenate just the date part (without the time), e.g.:
[certificate_no] & '---' & Format([expirydate], "Short Date")

 

[Haas]

Thank you Allen,
I did it, well done, but still looking the other part.

I want to list each student against all his/her certificates as below using
this
[certificate_no] & '---' & Format([expirydate], "Short Date")

Is it possible, I have also other fields that include the list Student No.,
Name and Email.) so i want to display as below in order to send each student
only one email not as corresponding to number of his certificates
Student Certifices & expiry date
1 250 - 22/12/2002 , 1240 - 02/02/2003 and so on.

---------------

Use Format() to concatenate just the date part (without the time), e.g.:
[certificate_no] & '---' & Format([expirydate], "Short Date")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have used the following expression to merge two columns, Ineed to hide
the
time and keep only the date in the expirydate.

Expr1: ([certificate_no]) & '---' & [expirydate]

Also I have student list of students in the table with their emails and
each
student has more then 1 certficiate so i need to combine like.

Student Certifices & expiry date
1 250 - 22/12/2002 , 1240 - 02/02/2003 and so on.

The certificate no and expiry date are two different fields i combined
them
using the above expression, so i need to make them a list like above.
Thank you in advance.
Any assistance.
Regards

 

[Allen Browne]

If these are 2 fields in the same row, just use Format() again for the 2nd
one.

If the dates are in different records, you will need to write some code to
OpenRecordset() and concatenate the dates into a string. Here's an example:
http://www.mvps.org/access/modules/mdl0004.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen,
I did it, well done, but still looking the other part.

I want to list each student against all his/her certificates as below
using
this
[certificate_no] & '---' & Format([expirydate], "Short Date")

Is it possible, I have also other fields that include the list Student
No.,
Name and Email.) so i want to display as below in order to send each
student
only one email not as corresponding to number of his certificates
Student Certifices & expiry date
1 250 - 22/12/2002 , 1240 - 02/02/2003 and so on.

---------------

Use Format() to concatenate just the date part (without the time), e.g.:
[certificate_no] & '---' & Format([expirydate], "Short Date")

I have used the following expression to merge two columns, Ineed to hide
the
time and keep only the date in the expirydate.

Expr1: ([certificate_no]) & '---' & [expirydate]

Also I have student list of students in the table with their emails and
each
student has more then 1 certficiate so i need to combine like.

Student Certifices & expiry date
1 250 - 22/12/2002 , 1240 - 02/02/2003 and so on.

The certificate no and expiry date are two different fields i combined
them
using the above expression, so i need to make them a list like above.

 

[Haas]

I tried, but i couldn't, am not that good in coding, so is there easy way to
do it.
I need certificate No and expiry date two different fields to merge them i.e.
125, 12/12/2002 this one, i done - Ok.

but now i want to concatenate to list each student with his certificate
because each student has more then one certificate.
I have two tables student and certficiates - student contains - basic details.
i took Student_No, Student Name and Email.
the Other table certificates I contains student No. with duplicates becuas
each student has more then one certificate.
student No. on both tables and certificates are all text because i imported
from excel i tried to change to assign primary key to student_no in the
student file but it's not allowing since it has more then 1600 data. (no
primary keys in both tables)
so is there easy solution.
I appreciate your assitance, thanks men, you helpful.

If these are 2 fields in the same row, just use Format() again for the 2nd
one.

If the dates are in different records, you will need to write some code to
OpenRecordset() and concatenate the dates into a string. Here's an example:
http://www.mvps.org/access/modules/mdl0004.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen,
I did it, well done, but still looking the other part.

I want to list each student against all his/her certificates as below
using
this
[certificate_no] & '---' & Format([expirydate], "Short Date")

Is it possible, I have also other fields that include the list Student
No.,
Name and Email.) so i want to display as below in order to send each
student
only one email not as corresponding to number of his certificates
Student Certifices & expiry date
1 250 - 22/12/2002 , 1240 - 02/02/2003 and so on.

---------------

Use Format() to concatenate just the date part (without the time), e.g.:
[certificate_no] & '---' & Format([expirydate], "Short Date")

I have used the following expression to merge two columns, Ineed to hide
the
time and keep only the date in the expirydate.

Expr1: ([certificate_no]) & '---' & [expirydate]

Also I have student list of students in the table with their emails and
each
student has more then 1 certficiate so i need to combine like.

Student Certifices & expiry date
1 250 - 22/12/2002 , 1240 - 02/02/2003 and so on.

The certificate no and expiry date are two different fields i combined
them
using the above expression, so i need to make them a list like above.

 

[Allen Browne]

I think you have a one-to-many relationship beween students and
certificates.

If so, that's a good design, but there is not a simpler solution for
concatenating them into a single string than the approach suggested.

 

[Haas]

Thank you Allen, I appreciate your effort.
I copied the code to my modules, i want to ask you is there a place that
need me to change.
and the following statement i copied to my quiry.
SELECT certificates.*,
fConcatChild("certificate_no","expiry_date","[certificates]) AS
SubFormValuesFROM student;

I don't have subform, I don't have a primary key also in my both tables. but
the relationship is exactly 1:M but all the flields are text including
student_no and certificate_no.

Pls. advise.
Thanks for yr assistance.

 

[Haas]

Allen,

I also used Mr. Duane Hookom code, but i am getting the error in the
following line

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
thanks for yr assistance

 

[Bob Barrows [MVP]]

What error? We are not looking at your computer monitor ...

 

[Haas]

sorry Bob,
I am getting the following error, once i run the quiry
Run time error '-214721913 (80040e07);
Data type mismatch in criteria expression

then in this line.
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic


the statement i used is mr. Mr. Duane Hookom code and i put the query in
this statement.
Certificate_No: Concatenate("SELECT Certificate_No FROM
Certifices WHERE Stud_No =" & [Stud_No])

both Certificate_No and Stud_no are text flieds in both tables.
Each student has more then one certificate so i need to concatenate
certficite_no.

Thanks Allen & Bob for yr assitance.
Pls. help.

 

[Haas]

Thank you very much men,
It worked, I appreciate yr efforts, its so wonderful to have you all for
assistance.
I just added
stud_no =""" & [stud_no] & """"), because both student_nos are text.
I also thank Mr. hook

 

[John Spencer]

Is the table name Certifices or Certificates.
Don't use the name of field as the name of the calculated field
If Stud_No is a text field then you need to add quotes around the
reference to the field.

So, I would try

Certificate_Nos: Concatenate("SELECT Certificate_No FROM
CERTIFICATES WHERE Stud_No =""" & [Stud_No] & """")



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

sorry Bob,
I am getting the following error, once i run the quiry
Run time error '-214721913 (80040e07);
Data type mismatch in criteria expression

then in this line.
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic


the statement i used is mr. Mr. Duane Hookom code and i put the query in
this statement.
Certificate_No: Concatenate("SELECT Certificate_No FROM
Certifices WHERE Stud_No =" & [Stud_No])

both Certificate_No and Stud_no are text flieds in both tables.
Each student has more then one certificate so i need to concatenate
certficite_no.

Thanks Allen & Bob for yr assitance.
Pls. help.

What error? We are not looking at your computer monitor ...

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"