Query calling a custom function with an array

[sseto23]

Hi Folks.... this might be something that is pretty basic, but I'm confused so I thought I would ask for your help.

I have a query called qry_A and it selects 6 installment dates (Instal1, Instal2, Instal3, Instal4, Instal5, Instal6) from an installment table. I want qry_A to call a custom VBA function called PrevWeekDay which takes the 6 installment dates and confirms that they are all weekdays. For the dates which fall on a weekend, I want to change them to the last weekday prior (I guess that would be the previous Friday).

What I am wondering is:

1 - Is it possible for me to call the function PrevWeekDay and pass the argument as an array which contains the 6 installment dates?
2- If I can use an array, how and where do I declare the array and how do I populate it with the installment dates?
3- Again, if I can use an array as the argument for the function, how do I pass back the updated installment dates to qry_A and update the installment table with the updated values?

Your help would be greatly appreciated.

 

[vbaInet]

1 - Is it possible for me to call the function PrevWeekDay and pass the argument as an array which contains the 6 installment dates?
2- If I can use an array, how and where do I declare the array and how do I populate it with the installment dates?
3- Again, if I can use an array as the argument for the function, how do I pass back the updated installment dates to qry_A and update the installment table with the updated values?

1. Yes
2. In the function argument
3. Not needed.

Basically you don't need to do the above.

I have a query called qry_A and it selects 6 installment dates (Instal1, Instal2, Instal3, Instal4, Instal5, Instal6) from an installment table. I want qry_A to call a custom VBA function called PrevWeekDay which takes the 6 installment dates and confirms that they are all weekdays. For the dates which fall on a weekend, I want to change them to the last weekday prior (I guess that would be the previous Friday).

Create an update query and use an IIF() statement to update each of the 6 date fields. E.g.

UPDATE TO row:
IIF(Weekday([Instal1]) In (1, 7), [[COLOR=Red]Calculate to get previous weekday[/COLOR]], [Instal1])

I'm sure you can solve the highlighted

 

[sseto23]

Ahhh, yes I never thought of that. I used your suggestion of just updating the row and it worked perfectly.

I know the users will be adding to or changing the criteria again, so I'm not done yet but this is great!

 

[vbaInet]

Good to know

I know the users will be adding to or changing the criteria again, so I'm not done yet but this is great!

This is what keeps you at your job. Opportunities!