im making a datareport in vb 6 and my database is mysql. my problem is that, in the data environment i added a command in the connection and this is my sql statement:

SELECT (tblmaster.emp_lname+', '+tblmaster.emp_fname+' '+tblmaster.emp_mi+'.') as Name, tblmaster.emp_slary, tblpos.*, tblaca.*, tblpera.*,(tblaca.aca_amount+tblpera.pe_amount) AS Total FROM tblmaster inner JOIN tblpos ON tblmaster.pos_name=tblpos.pos_no INNER JOIN tblaca ON tblmaster.emp_code=tblaca.aca_empcode INNER JOIN tblpera ON tblmaster.emp_code=tblpera.pe_empcode ORDER BY tblmaster.emp_lname ASC

when i try to view the datareport, the value returned or retrieved for Name(in bold text above) are 0's but the other fields are ok. whats wrong with the query? is that how it is done when concatinating fields in querying fields using mySQL database? but with my previous projects using access as database, there was nothing wrong. hope u could respond to this problem. i really need it.. thanks!!!! God Bless!!!

I have found through SQL server that when trying to concatenate two fields, if one of them is Null the result is Null.

This may be the same type of issue you are running in to.

Look for a command such as NVL (Oracle) or IsNull (Sql Server) to set the Null field to a non Null value.

Hi,
When SET CONCAT_NULL_TIELDS_NULL is ON then, concatenating a NULL value with a string yields a NULL result.
Ex: -SELECT 'nalla' + NULL yields NULL.
When SET CONCAT_NULL_YIELDS_NULL is OFF then, concatenating a null value with a string yields the string itselt
Ex:- SELECT 'nalla' NULL yields nalla


nalaka hewage