Problem with NULLS (I think) 1

[krisbrixon]

I usually use SQL Server, but for this application I need to use Access and I have not used Access in over four years.

I am creating a large SQL statement, but is not returning the results like I think it should. I have narrowed the problem down to a simple SQL statement and I wanted to see if I am missing something here.

SELECT ' T: '+AcctType+
' P1: '+AcctPhone1+
' P2: '+AcctPhone2+
' DL: '+AcctDLNum+
' SSN: '+AcctSSN As [MiscInfo]
FROM tblAccount;

What is happening is if any of the fields are null for a record then the entire field will be blank (null?).

i.e. If all the fields are filled in then I will receive something like:
T: Com P1: 555-555-5555 P2:555-555-5555 DL:598648 SSN:555
If any of the fields are null then I get a blank record.

Any Ideas?

Thanks,

Kris

 

[LittleSmudge]

Because, at a guess AcctDLNum is of type NUMBER and using + signs around it is bound to cause problems.

Replace ALL of the + signs with & signs and you'll be well on the way.





G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[krisbrixon]

IT WORKED!!!

I can not believe that I did not at least try that. I use the "&" all the time in VB.

They are all strings, but it solved the problem anyway.

Thanks,

Kris

 

[LittleSmudge]

They might all be strings, but using the + sign will cause JET to try to convert a numberic digit string into a number and then do 'math' on it.


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D