If / IndexMatch type approach to pulling data (1 Viewer)

[GFM]

Hi gang,

I’m hoping for a bit of help please, probably initially in better describing my problem in Access terms rather than solving it but here we go!

I have a table of potential meeting papers, one field is the date of the meeting they will go to.

When that date matches a userform textbox, I’d like to extract other details from the row; title, presenter and place them into a word doc with table fields.

Now for the hard (for me) part. I don’t know how many papers will go to each meeting so presumably I need to loop some code to pull the details if the date matches.

Once I’ve got these It would be great if I could allocate a letter starting at (a) to the title of each paper and thus remove the need for separate table rows in word (and thus a ton of field references) and instead just concatenate all the paper titles and use something like take a new line with vbCrLf or similar for each result within one table field that would read:

a) Title one
b) Title two
c) Title three...

then do the same for the presenters and so on. This would be easy in excel but having recently discovered the joys of access I want to learn more so I’m determined to stick with it!

Hopefully this makes sense, any ideas as to how to approach or questions that would help you translate my rambling in to a sensible access vba type question would be much appreciated.

I’ll probably not be able to share the DB given it’s on a work server but if I can give any additional detail then please ask away!

Many thanks

G

 

[Minty]

As simple where clause in a query should work.

Select * from tblMeetingpapers WHERE MeetingDate = #YourDateGoesHere#

Would return all the fields from the table for you.

 

[GFM]

As simple where clause in a query should work.

Select * from tblMeetingpapers WHERE MeetingDate = #YourDateGoesHere#

Would return all the fields from the table for you.

Thanks Minty, and once I have them how would I use them as if they were variables?

Can you please explain this to me “like” I’m an idiot, because I am in fact, an idiot.

Edit: Oh and can I use AND with WHERE so I can split the meeting into sections so where - date is x and paper type is y?

G

 

[Minty]

The above would return as many rows as there are meetings on that date.

You can restrict the fields returned by specifying them;

SELECT Title, presenter , placer, etc. etc.
FROM tblMeetingPapers
WHERE MeetingDate = #2020-11-28#

You can then simply export this to excel or do whatever you want with it.
I've never done much with word automation but am sure you could get this into a suitable format in Word as well.

 

[GFM]

Thanks,

So I just need to figure out how to get that selected data into word and repeat for each section! This is a big jump forward, many thanks for your help!!

G

 

[The_Doc_Man]

@GFM - to approach the problem, do a "divide and conquer" approach. First, gather the data, then present it as a separate question.

What Minty suggested gives you, via query, a list of potentially relevant papers. If you want to display these, you could make a report and list the detail information in the report's detail section. This would give you a list right off the bat.

The other approach is more tedious because you would have to open a Recordset based on that query and, in a VBA program loop, extract data one record at a time.

EDIT: Your post and mine "crossed on the web."

To do this with Word, look into a MAILMERGE operation based on the query as its input. Access has a facility to Export via MailMerge, which you can also look up.

 

[GFM]

@GFM - to approach the problem, do a "divide and conquer" approach. First, gather the data, then present it as a separate question.

What Minty suggested gives you, via query, a list of potentially relevant papers. If you want to display these, you could make a report and list the detail information in the report's detail section. This would give you a list right off the bat.

The other approach is more tedious because you would have to open a Recordset based on that query and, in a VBA program loop, extract data one record at a time.

EDIT: Your post and mine "crossed on the web."

To do this with Word, look into a MAILMERGE operation based on the query as its input. Access has a facility to Export via MailMerge, which you can also look up.

Thanks Doc,

So I did have a quick play with mail merge using the wizard as that was my guess at an easy way to do this but I couldn’t figure out how to get multiple pieces of info from one row into the letter and then repeat this x amount of times. I’ll have a look now know what I do from the above though and it might make a bit more sense!

It’s going to be the getting the info from access into word but that stumps me I suspect but all good fun and learning!

Thanks

G

 

[The_Doc_Man]

If your Word template has the field names from your query, you can make a mapping or correspondence between the two utilities. That is, tell Word in your MailMerge template what fields in Access's query contain your required data.

 

[GFM]

If your Word template has the field names from your query, you can make a mapping or correspondence between the two utilities. That is, tell Word in your MailMerge template what fields in Access's query contain your required data.

Now that sounds like what I want to do, let's see if my dumb ass can figure it out! Thanks