Access question

[Scott]

I know there is an immense amount of knowledge in this group, so I shall ask the question.

I have a membership database on Access. The address is configured in three fields: (1) home address (up to three lines), (2) city, (3) postal code. There is then a query to produce labels for members receiving the newsletter by post (ie, excluding those receiving by email). The labels are okay, showing (1) in full. However, the Access query only displays the first line of (1) although it must contain all the data or the address labels would be incomplete. Is there a way to force the query to display the home address (1) in full?

I know the problem could be resolved by dividing the home address field into line 1, line 2, and line 3 but my concern is this would create blank lines in some of the labels.

Any assistance appreciated as very few people know anything about Access.

[Pancho]

Increase the height of rows in the query display grid, so the row is high enough to show more than one line.

On the right-hand side of the grid, right click approximately between two lines, and it should display an expand icon, pull this to make rows bigger.

[Scott]

Thanks. I take it this is in the Datasheet view not the Design view? Unfortunately, I cannot find the option you describe wherever I have tried right clicking. This is the '365' version of Access.

[Pancho]

I don't know Access, so don't know the names, but it is the same as the way you change row height in Excel.

[Joe]

This is the right way to do it, multiple lines in one field can be difficult to deal with, especially if you ever encounter 'first line and postcode' software in the future.

I don't have anything to hand to open Access files at the moment, but I remember dealing with the display using brute force and ignorance. Something like:

If AddressLine2 is null or AddressLine2 = ""

else $string += AddressLine2 . <cr>

endif

I don't remember the actual report syntax, as it's probably at least ten years since I did any Access work. I have a feeling that the whole thing, testing all address lines after the first, was one giant nesting of 'if's resulting in a single long string.

For labels, an additional tweak would be to count the lines in use first, and add one or two blank lines at the top to centre the address on the label vertically. Not necessary on letters, invoices etc.

[Scott]

Okay. Gotcha. It's on the left side of the grid with no right clicking involved. Thanks for your assistance.

[Scott]

Thanks. This is useful if we are to develop the system further. I want to move online (probably membermojo) but the rest of the committee won't wear it.

[Joe]

OK, I got less lazy and fired up my wife's Windows machine. Here is the code from an address text box:

=[CustomerName] & " " & IIf(IsNull([Address1]),"",[Address1] & " ") & IIf(IsNull([Address2]),"",[Address2] & " ") & IIf(IsNull([Address3]),"",[Address3] & " ") & IIf(IsNull([Address4]),"",[Address4] & " ") & IIf(IsNull([Address5]),"",[Address5] & " ") & [CityOrCounty] & " " & [PostCode] & " " & [Country]

The returns are just carriage returns within the text, in the positions they appear.

Brings back memories...

[Alan J. Wylie]

<snip>

Not relevant to the question about Access databases, but some of you might find these amusing and even informative

"Falsehoods programmers believe about addresses"

And "UK Address Oddities"

[Jethro_uk]

My goto would be ChatGPT

[Adrian Caspersz]

I wish I had other memories....

Was an Access VBA developer for many years.

I gladly use the word "Was".

It was a tool commonly abused that managers could present or have presented data dumbed down to simple forms that users could view and edit like a windows application.

I'd style the whole thing, so users were not even aware they were in Access! - until Memo fields corrupted, network users got row locked, and their table links vanished. Then nightmare downtime compacting bloated .mdb files and coping with frustrated users. :-(

It's a trap. Access is really not a serious environment for production, more a demonstration plaything so that users could design the properly transactional replacement app written in a more expensive Microsoft development environment pinned on expensive SQL servers and (at that time - 20 years ago, and denied to me) costly hardware.

Today, it can be replaced with an open source Webserver, MySQL and a choice of scripting language.

Or if you just interested in mangling your own data - Excel.

[Jeff Gaines]

SQLite is good for smaller projects where there is no server and it works well with .NET.

[SH]

think there is MariaDB as an alternative to SQL?

[Tim Streater]

No. It's an SQL server-based database.

SQLite is very good if it's just you using it. But you'll have to write some code.

[Scott]

On Thu, 7 Sep 2023 12:42:49 +0100, Joe [e-mail] wrote: [snip]

Thanks for this. I am wondering: given that the labels are produced by Word, with the Access Query as the source, could this type of code be used in Word?

[Theo]

SQL is a database manipulation language. MariaDB is an implementation of a database that supports SQL. MariaDB is derived (forked) from MySQL, an earlier open source implementation (still developed). Other databases supporting SQL include SQLite, Microsoft SQL Server, Postgres, Oracle Database and more. There is also NoSQL which is more a philosophy of how to build a non-relational database rather than an actual language.

Theo

[Joe]

I would think so. It's Visual Basic for Applications code, used throughout Office. Access uses it heavily, though you can make quite an impressive database user interface with no code at all.

I've also used VBA in Excel, but not in Word. I've generated Word documents and Excel spreadsheets from within Access, though nowadays you would probably go straight to PDF most of the time. Creation of documents normally needs VBA code, though very simple tricks like mail merge are actually built into Word. Both Access and Excel can use Word bookmarks in a blank template document to poke in any kind of data, not just names and addresses.

I should stress that my work was many years ago, I've been pretty much out of the Windows world for most of twenty years, and now do most things as web apps in PHP with a MariaDb database backend. As has been pointed out, SQLite can be used like an Access .mdb file in place of a real SQL relational database manager if you don't need networking. I wouldn't be too surprised if a modern Access version could use SQLite, at the very least through the dreadful ODBC protocol.