Replies

If FORMNAME is a text field you need to use
“[FORNAME] = ” & chr(34) & Me!FORENAME & chr(34)))

It seems to me you have 3 ways of tackling this
(1) Use Geek’s suggestion to use a query with a join on the common PK and have 100 or conditions a.Field(n) b.Field(n) to locate records with different field contents (if a query can handle that many otherwise a series of queries). And an outer join to show new and deleted records.
(2) Maintain a log of changes as they are made. (This does not help with historical data.)
(3) Use VBA to loop through recordsets comaring field values, the option I’d personally use.

I haven’t tried it but how about setting the height of a hidden rectangle.

Why don’t you use the same query for the Word mail merge as is used to print from Access? If there was a filter being applied to the Access report, set up the same criteria in your Word Mail Merge filter. Alternatively, change the query in Access to give you just the records you want.

One way might be to position the data sequentially. For example, if you had say up to 21 data items to display, put 21 unbound text boxes in the report called txt01 to txt21, in say 3 columns, 7 rows

Open a recordset with the 21 field values and then loop through the values with the following (air) code in the OnFormat event of the report

intNextPosition = 1
For n = 1 to 21
if len(rst(n-1) & “”)> 0 then
me(“txt” & format(intNextPosition ,”0#”))= rst(n-1)
intNextPosition =intNextPosition +1
endif
next n

I expect you’d also want to have labels describing the data being displayed. One way would be to store the label captions for the 21 fields in a string array and add
me(lbl” & format(intNextPosition ,”0#”)).caption= astrCaption(n)

or if the field name were descriptive enough
me(lbl” & format(intNextPosition ,”0#”)).caption=rst(n-1).name

Is the form bound to the table where the data being printed is sourced from? If so then the easiest is to add the field where you want to store your input value and then add to your code after the data has been input:
me.txtAngleBendCert= strMessage

Otherwise you can use an update query to add the data to the table.

I’m sure it will work.
datMaximum = DMax(“ExDay”,”qryA”)
After executing this, datMax will contain the maximum value in the field ExDay in the data table or query qryA. Doesn’t matter if there is one field or many.

I can think of a few situations where your database is opened read only
1. Your default setting is such.
2. The database is in a folder which is read only (or a CD) or the folder cannot create the lock file *.ldb or *.laccdb
3. Someone else has opened it exclusively.

This list may not be exhaustive.

Use Date() in the criteria of the recordsource for the report. Then you don’t need to worry about macros or BA.

As to running the report in the mornings, if you open the database every morning, you could add the report to the Autoexec macro. If not, then you’ll need to set up some scheduler to open your Access db.

Seasons greetings Geek

Your intended solution is better because it aims to pick up not only the “X” situation, but any other where there is a Null value.

I think the issue is in the first part of the IIf which should be Nz([Option])=0 Without the test for zero, the result is zero which is False.

Cronk

There is a problem though catering for leap years with 366 days.

Your a better man than me with complex IIf statements, but the first part would be
IIf(DateSerial(Year(pDOB) + 1, Month(pDOB), Day(pDOB)) <= Date,…..

But there seems to be issues with the rest because I get the results below for a birth date of 16 November 2011 (NB UK date format). I'd be using a function.

Cronk

17/11/2011 1
18/11/2011 12 Months
19/11/2011 12 Months
….
29/11/2011 12 Months
30/11/2011 12 Months
1/12/2011 10 Months
2/12/2011 10 Months
…
15/12/2011 10 Months
16/12/2011 11 Months
17/12/2011 11 Months
18/12/2011 11 Months
….
30/12/2011 11 Months
31/12/2011 11 Months
1/01/2012 9 Months
2/01/2012 9 Months
3/01/2012 9 Months

CStr(CLng(Rnd * (2 ^ 31))) is generating a random, and hopefully unique, number which is used as the Key value to the particular instance of the specific report object that you are creating in the collection of reports.

What defines an “invalid” receipt number? One that does not exist? You could check the existence first (check out DLookup) and if it does not exist, advise the user accordingly.

Thanks for the steer. I’ll try it and let you know the outcome. But it won’t be for a couple of weeks because I’ve just arrived in Surfers Paradise to thaw out.

Look up Access help on DAvg and use the criteria “FieldName Not Is Null”