How to Build expression in a query with multiple lines. (1 Viewer)

[dealwi8me]

Hello,

I'm trying to build a string as expression into a query. What i want to do is to get the field [Address],[PostalCode],[Village],[District] from table tblApplicant and build an address block as the following but i don't know which character to use for changing line

Address Block
-------------------
[Address]
[PostalCode] [Village]
[District]

Any suggestions?

Thank you in advance!

 

[dealwi8me]

How to build an expression in a query with multiple lines.

Hello,

I'm trying to build a string as expression into a query. What i want to do is to get the field [Address],[PostalCode],[Village],[District] from table tblApplicant and build an address block as the following but i don't know which character to use for changing line

Address Block
-------------------
[Address]
[PostalCode] [Village]
[District]

Any suggestions?

Thank you in advance!

 

[geekay]

'>>>>>Copy the below code into a module

'>>>>>Type Call AddressDisplay and press enter key in the immediate window to see the result.

'>>>>>Change the code as per your needs

Public Sub AddressDisplay()
On Error GoTo Err_AddressDisplay
Dim strSQL As String, strAddressBlock As String
Dim rs As ADODB.RecordSet, Cn As ADODB.Connection
Set rs = New ADODB.RecordSet: Set Cn = New ADODB.Connection
Set Cn = CurrentProject.Connection
strSQL = "Select tblApplicant.* From tblApplicant
rs.Open strSQL, Cn, adOpenStatic, adLockReadOnly
Do While Not rs.EOF
strAddressBlock = rs!Address & "," & vbCrLf & rs!PostalCode & "," & vbCrLf & rs!Village & "," & vbCrLf & rs!District & "."

'>>>Try vbLf instead of vbCrLf to see that it also works<<<<<

MsgBox strAddressBlock '>>>>>>>>>>> This MsgBox is only to show you the result.

'>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Use strAddressBlock in the way you need

rs.MoveNext

Loop

rs.Close
Set rs = Nothing

Exit_AddressDisplay:
Exit Sub
Err_AddressDisplay:
MsgBox Err.Number & " - " & Err.Description, , "Error Handler "
Resume Exit_AddressDisplay
End Sub

 

[geekay]

Never do multiple postings. Say sorry for it before you read my reply in your second posting. If I have seen the multiple postings, I would not have replied.

 

[dealwi8me]

that works thank you!

is it possible to use the module to a report or do i have to store the strAddressBlock into a table field and then use it to the report?

 

[boblarson]

And the simpler way to use in a report (you don't need a function for this):
Make sure all of the fields are in your report's recordsource and then
for the control source of the text box you want to display the address just use:

=[Address] & Chr(13) & Chr(10) & [PostalCode] & Chr(13) & Chr(10) & [Village] & Chr(13) & Chr(10) & [District]

Nothing more required.

 

[dealwi8me]

And the simpler way to use in a report (you don't need a function for this):
Make sure all of the fields are in your report's recordsource and then
for the control source of the text box you want to display the address just use:

=[Address] & Chr(13) & Chr(10) & [PostalCode] & Chr(13) & Chr(10) & [Village] & Chr(13) & Chr(10) & [District]

Nothing more required.

Thanks that's more suitable for me because it's not necessary to save the value

Never do multiple postings. Say sorry for it before you read my reply in your second posting. If I have seen the multiple postings, I would not have replied.

I never posted twice the same post and when i posted the second post you hadn't answer yet (i saw your answer today). If you take a look to my previous posts you will see that i am always trying to respect the rules and the other users.

Anyway thank you both for your answers they were useful and geekay sorry for the inconvenience

 

[boblarson]

I never posted twice the same post

Actually, that's not quite accurate because I merged both posts into this one.