Empty string in query calc returns no result

  • Thread starter Thread starter Julia Boswell
  • Start date Start date
J

Julia Boswell

Hi all

I've got a query that isn't returning the result I need.

The query is:

SELECT Str$([QuoteNo])+[SupplementaryID] AS Quote, Quotes.RecordID
FROM Quotes

QuoteNo is always populated, but SupplementaryID isn't, sometimes it's an
empty string.

The data in the table looks like this

QuoteNo SupplementaryID RecordID
1000 1
1000 A 2
1000 B 3

The results I want are:

1000 1
1000A 2
1000B 3

What I get is
1
1000A 2
1000B 3

I need the query to display the Quote details even if the SupplementaryID
field is not populated.

Any ideas?

Thanks

Julia
 
Try the Ampersand operator instead of the plus operator:
SELECT Str([QuoteNo]) & [SupplementaryID] AS Quote, ...

There is s slight difference between the 2 concatenation operators in
Access:
"A" & Null yields "A"
"A" + Null yields Null

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

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

Julia Boswell said:
Hi all

I've got a query that isn't returning the result I need.

The query is:

SELECT Str$([QuoteNo])+[SupplementaryID] AS Quote, Quotes.RecordID
FROM Quotes

QuoteNo is always populated, but SupplementaryID isn't, sometimes it's an
empty string.

The data in the table looks like this

QuoteNo SupplementaryID RecordID
1000 1
1000 A 2
1000 B 3

The results I want are:

1000 1
1000A 2
1000B 3

What I get is
1
1000A 2
1000B 3

I need the query to display the Quote details even if the SupplementaryID
field is not populated.

Any ideas?

Thanks

Julia
Click to expand...
 
Thanks Allen, that fixed it. I thought it was probably something really
simple!
Allen Browne said:
Try the Ampersand operator instead of the plus operator:
SELECT Str([QuoteNo]) & [SupplementaryID] AS Quote, ...

There is s slight difference between the 2 concatenation operators in
Access:
"A" & Null yields "A"
"A" + Null yields Null

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

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

Julia Boswell said:
Hi all

I've got a query that isn't returning the result I need.

The query is:

SELECT Str$([QuoteNo])+[SupplementaryID] AS Quote, Quotes.RecordID
FROM Quotes

QuoteNo is always populated, but SupplementaryID isn't, sometimes it's an
empty string.

The data in the table looks like this

QuoteNo SupplementaryID RecordID
1000 1
1000 A 2
1000 B 3

The results I want are:

1000 1
1000A 2
1000B 3

What I get is
1
1000A 2
1000B 3

I need the query to display the Quote details even if the SupplementaryID
field is not populated.

Any ideas?

Thanks

Julia
Click to expand...
Click to expand...
 
Back
Top