Show all ID's as a string

[fat controller]

I have a query that will return a small amount of records (maybe five or six each time it is used), which is essentially to check on the contents of one particular field before a report is produced. This report is sent by email to an external body.

So, I was thinking of having a sub that will run the query, and if there are records returned, I would like it to display all of the ID numbers in a message box. Worthy of note, the ID numbers returned will not necessarily be sequential as some of the records will already have the required field completed.

Is there something similar to a DLookup that can be used to show a series of different ID's?

 

[CJ_London]

think you need the concatrelated function which can be run in VBA code like a dlookup

http://allenbrowne.com/func-concat.html

 

[fat controller]

Blimey, how embarrassing - - I actually use that elsewhere in the database and completely forgot

 

[fat controller]

I am struggling this. I already have a variable declared (Today) which returns today's date, so have set the line to look up the values as:

References = ConcatRelated("ID", "EndTimeCheck", "Date=#" & Today & "#")

The error I am getting is 3061 'Too Few Parameters, Expected 1'

All three of the fields named are within the query, and the date is being correctly picked up (checked by stepping into the code);

Confused......

 

[arnelgp]

use Date() instead of Today:

"[Datefield]=Date()"

 

[CJ_London]

yes - but the OP has used Date as a field name - Date is a reserved word

best solution is to change the name of your Date field to something more meaningful such as CheckDate

Otherwise, try putting square brackets round your field name, might solve the problem

see this link for a list of and problems with using reserved words

https://support.office.com/en-us/ar...da237c63eabe?ui=en-US&rs=en-US&ad=US&fromAR=1

in particular, read the second paragraph

 

[fat controller]

I think I have worked it out to a point - it appears that the Concatrelated function doesn't want to work when referred to a query?

I have a potential workaround, which is to refer it to the table instead, and filter on a Where condition - however, I would need three parameters - - is this possible?

Those would parameters be:

[Date] = Date()
[EndTime] Is Null
[Include] Is True

 

[CJ_London]

no reason for concatrelated not to work with queries - except perhaps treatment of memo fields

go into the code and see what sql string it is building as a result of the parameters you provide - and copy to a new query and see how it runs

 

[CJ_London]

just a thought - does your query require parameters?

 

[arnelgp]

then dont use the query instead a select statement:

ConcatRelated("ID", "(SELECT [ID],[Date],[EndTime],[Include] FROM Table)", "[Date]= Date() AND IsNull([EndTime]) AND [Include]=True")