Replies

Here’s how I do it. I keep a table with the SQL for each query. In the Where clause of each SQL, I stick something like “AND 0=0”. For example, your query would have this:

WHERE (((t_survey.survey_date) Is Null) AND ((t_survey.report_completed_date) Is Null)) AND 1=1

Prior to calling the report, I use the Replace function to replace “1=1” with whatever I want (like “Surveyor=12345”) and then replace the SQL in the query behind the report. In your case, I’d cycle through the Surveyor table and run the report for each Surveyor. This way, you can use the Output to PDF functionality within Access.

Mark,

Can you give a little bit more detail on this, my Surveyor field is a concatenation of first_name and surname and is stored as text. In my SQL, this is the first part of the statement SELECT [t_surveyor]![first_name] & ” ” & [t_surveyor]![surname] AS Surveyor. How do I get it to loop through the Surveyor field in the query?

Thanks RG, Yes, I did include the SwitchPrinters() code in the project and PDF Creator is installed. I may need to get on to my IT department, as I cannot get into the PDF configuration file to make any changes. I will keep trying though.

Regards,
Maria.

RG, I keep getting a compile error at the following part of the code. strDfltPrt – I get ByRef Argument type mismatch. Any ideas?

Thanks, RG, I will try this out shortly and let you know how I get on.

I have just checked our system and we do have PDF Creator. I would only need to change the code to not send emails and just save each surveyor’s report as a separate PDF to a specified subdirectory, with the file name to include the Surveyor name and current date from the Surveyor field in the query.

Thanks for that RetiredGeek, how would I modify this to not use ClearPDF as a printer and to not use Outlook – there are no email addresses in the report?

Why not just use =VLOOKUP(C2,F3:J367,5,0) – this seems to much simpler that the other suggested formulae.

Regards,
Maria

Hi Mike,

Insert a new column to the right of your credit card numbers and if Your credit card numbers are in column A, then in the new column B enter the following formula =LEFT(A1,4)&”-“&MID(A1,6,4)&”-“&MID(A1,11,4)&”-“&RIGHT(A1,4) and copy down the range of your data. you can then either hide column A or convert all formulae in column B to values and delete column A.

Regards,
Maria

Thanks Steve, I just tested with the error handling and it worked, no debug mode triggered. Just what I wanted. I am not sure if your alternate method would work as the 2 pivot tables are created by the macro they do not already exist. I import data on a weekly basis from our mainframe to a new worksheet and format as needed then run the addPivotTables macro.

Regards,
Maria

I use the Wingdings characters 251 & 252 in the following macro’s.

Code:Sub XMark()
‘
‘ XMark Macro
‘ Insert X in Wingdings font
‘

‘
ActiveCell.FormulaR1C1 = “û”
With Selection.Font
.Name = “Wingdings”
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With

With Selection
.HorizontalAlignment = xlCenter
End With
ActiveCell.Offset(0, 1).Select
End Sub
Sub TickMark()
‘
‘ TickMark Macro
‘ Insert Tick in Wingdings Font
‘

‘
ActiveCell.FormulaR1C1 = “ü”
With Selection.Font
.Name = “Wingdings”
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With

With Selection
.HorizontalAlignment = xlCenter
End With
ActiveCell.Offset(0, 1).Select
End Sub

You can then modify to use the colours you want.

Regards,
Maria

Another way is when in Table design, simply set the format for the field to show >, this will force every entry into the field to uppercase.

Regards,
Maria

Just a little point, I find it better to save files with dates using Year-Month-Day – this makes files easier to sort into date order.

Regards,
Maria

Arcturus 16a,

In Excel 2007, right click on the field and go to field settings, change from Show item labels in tabular form to show item labels in outline form and make sure both boxes are checked.

Hi Trevor,

Is the report based on an Access query? If so, I would put a criteria in the query under the order number field as = not null.

Regards,
Maria

Thanks Maud,

This will be very helpful. Quite often, I need to hide empty columns after I have deleted some content and then unhide them after I have printed out the content I need. I can’t simply delete the empty columns as they would still be needed if data needs to be entered for those columns.

Regards,
Maria