MailMerge with Publisher

[swarfmaker]

I'm using Publisher to print certificates, pulling data from MSAccess
database. The date field I'm using is formatted as LongDate in Access (6
August 2006)but shows as ShortDate (06/08/2006) in Publisher. Is there any
way to show this as LongDate in Publisher? Ideally, I'd like it to be
formatted with Ordinal numbers, i.e. 6th August 2006.
TIA
Iain

 

[Arvin Meyer [MVP]]

You will need to create the ordinal as a test field in an Access query
before doing the import into Publisher. I found that I could create an
ordinal with the date first until I'd reformatted my date as text without
the slashes (/). First place the following function in a standard module so
that you can call it in a query:

Function Ordinal(ByVal X As Integer) As String
Select Case Abs(X Mod 10)
Case 1: Ordinal = X & IIf(Right(X, 2) = 11, "th", "st")
Case 2: Ordinal = X & IIf(Right(X, 2) = 12, "th", "nd")
Case 3: Ordinal = X & IIf(Right(X, 2) = 13, "th", "rd")
Case Else: Ordinal = X & "th"
End Select
End Function

Then build this query in Access, adding the fields you need:

SELECT Ordinal(Left([Expr2],2)) & " " & Format$([DateField],"mmmm") & " " &
Format$([DateField],"yyyy") AS Expr1, Format$([DateField],"ddmmyyyy") AS
Expr2, tblMyData.DateField
FROM tblMyData;

Replace the field and table names. DateField will be the name of your date
field and tblMyData is the name of your table.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[swarfmaker]

You will need to create the ordinal as a test field in an Access query
before doing the import into Publisher. I found that I could create an
ordinal with the date first until I'd reformatted my date as text without
the slashes (/). First place the following function in a standard module
so that you can call it in a query:

Function Ordinal(ByVal X As Integer) As String
Select Case Abs(X Mod 10)
Case 1: Ordinal = X & IIf(Right(X, 2) = 11, "th", "st")
Case 2: Ordinal = X & IIf(Right(X, 2) = 12, "th", "nd")
Case 3: Ordinal = X & IIf(Right(X, 2) = 13, "th", "rd")
Case Else: Ordinal = X & "th"
End Select
End Function

Then build this query in Access, adding the fields you need:

SELECT Ordinal(Left([Expr2],2)) & " " & Format$([DateField],"mmmm") & " "
& Format$([DateField],"yyyy") AS Expr1, Format$([DateField],"ddmmyyyy") AS
Expr2, tblMyData.DateField
FROM tblMyData;

Replace the field and table names. DateField will be the name of your date
field and tblMyData is the name of your table.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

I'm using Publisher to print certificates, pulling data from MSAccess
database. The date field I'm using is formatted as LongDate in Access (6
August 2006)but shows as ShortDate (06/08/2006) in Publisher. Is there
any way to show this as LongDate in Publisher? Ideally, I'd like it to be
formatted with Ordinal numbers, i.e. 6th August 2006.
TIA
Iain

Many thanks Arvin, I'll test this tomorrow

Iain

 

[swarfmaker]

You will need to create the ordinal as a test field in an Access query
before doing the import into Publisher. I found that I could create an
ordinal with the date first until I'd reformatted my date as text without
the slashes (/). First place the following function in a standard module
so that you can call it in a query:

Function Ordinal(ByVal X As Integer) As String
Select Case Abs(X Mod 10)
Case 1: Ordinal = X & IIf(Right(X, 2) = 11, "th", "st")
Case 2: Ordinal = X & IIf(Right(X, 2) = 12, "th", "nd")
Case 3: Ordinal = X & IIf(Right(X, 2) = 13, "th", "rd")
Case Else: Ordinal = X & "th"
End Select
End Function

Then build this query in Access, adding the fields you need:

SELECT Ordinal(Left([Expr2],2)) & " " & Format$([DateField],"mmmm") & " "
& Format$([DateField],"yyyy") AS Expr1, Format$([DateField],"ddmmyyyy") AS
Expr2, tblMyData.DateField
FROM tblMyData;

Replace the field and table names. DateField will be the name of your date
field and tblMyData is the name of your table.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

I'm using Publisher to print certificates, pulling data from MSAccess
database. The date field I'm using is formatted as LongDate in Access (6
August 2006)but shows as ShortDate (06/08/2006) in Publisher. Is there
any way to show this as LongDate in Publisher? Ideally, I'd like it to be
formatted with Ordinal numbers, i.e. 6th August 2006.
TIA
Iain

Arvin,
Many thanks for the code, it worked perfectly, with one caveat.
No matter how I named the Select Query the MailMege Wizard in Publisher
could not pick it up. It just would not show in the selection of tables and
queries to pick from. I had to change the Select query to a MakeTable query
then the Publisher wizard was quit happy to let me pick that up.
Any ideas as to why this should happen?

Iain

 

[Arvin Meyer [MVP]]

Arvin,
Many thanks for the code, it worked perfectly, with one caveat.
No matter how I named the Select Query the MailMege Wizard in Publisher
could not pick it up. It just would not show in the selection of tables
and queries to pick from. I had to change the Select query to a MakeTable
query then the Publisher wizard was quit happy to let me pick that up.
Any ideas as to why this should happen?

Not really. I've had that happen on rare occasions with Word merges. My
solution is the same as yours.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access