X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Carriage Returns In Dlookup Return, Access 2016    
Wood Butcher
post Sep 19 2019, 12:38 PM

Posts: 59
Joined: 16-December 10

I have a report that in the header there is a DLookup referencing a table. The field it's pulling in is a multi-value field (our sales sites). So the lookup is working and returning something like "
Savannah, GA Atlanta, GA Wilmington, NC" etc etc. What i want it to do is put a carriage return between the sites so it would return something like...

Savannah, GA
Atlanta, GA
Wilmington, NC

Go to the top of the page
post Sep 19 2019, 12:40 PM

UA Admin
Posts: 36,080
Joined: 20-June 02
From: Newcastle, WA

Try using the Replace() function to replace the commas and spaces between values with Chr(13) & Chr(10) to create the equivalent of the carriage return.

My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
Wood Butcher
post Sep 19 2019, 02:50 PM

Posts: 59
Joined: 16-December 10

Good idea, but the problem is the inconsistency in the values. For example some sites have two words, some have one. Some sites have a comma in the name, others do not.
Go to the top of the page
post Sep 19 2019, 03:48 PM

UtterAccess VIP
Posts: 6,977
Joined: 30-June 11

You could create a user defined function to perform whatever manipulations you need, or better yet, clean up the data and standardize it so a Replace works.

Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc

All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    20th November 2019 - 11:08 PM