Query to returns rows where field contents <= today + 2 business days

[Broker666]

Hi all,

This is my first post so be gentle. I have tried searching for what I am looking for but to no avail (if the code is floating around somewhere on here I apologise but I did have a good look prior to posting). Basically what I want to do is something along the lines of the following.

I have a select query where I want to return any row that has a value (field contains dates in the format yy-mm-dd) less than or equal to the current day + 2 business days.

i.e.

SELECT Field1, Field 2 etc
FROM Pending_Trades
WHERE several other conditions are met for which I already have working code
AND Value_date <= (today + 2 business days)

Crucially this has to be business days, not just calendar days.

Any help would be appreciated.

Regards,

Ryan.

 

[Isskint]

Your best approach here would probably be a custom function.

Public Function NextBusinessDate(dteSeedDate As Date, intNoBusinessDays As Integer) As Date
Dim intDtLp As Integer
NextBusinessDate = dteSeedDate
For intDtLp = 1 To intNoBusinessDays
    NextBusinessDate = DateAdd("d", 1, NextBusinessDate)
    While Weekday(NextBusinessDate) = 1 Or Weekday(NextBusinessDate) = 7
        NextBusinessDate = DateAdd("d", 1, NextBusinessDate)
    Wend
Next intDtLp
End Function

so in your query use it like;

AND Value_date <= (NextBusinessDate(today,2))

 

[Broker666]

Hi IssKint, thanks for getting back to me so quickly. Sounds like a good solution, my only question is how and where do I embed / save the function ready to call it with the query? Sorry if this is pretty basic, my knowledge up to this point is writing SQL queries / sub-queries. Fairly new to fuctions in an Access environment.

 

[Isskint]

We learn from asking questions (and mistakes).

You will need to save the function in a module. Open the Visual Basic Editor (ALT + F11). In the navigation pane on the left, right click your databse name and select Insert>Module. This will open a new module pane on the right, this is where you paste the function. Save it (you will be asked to name the module, either leave it as the default Module1, Module2 etc or rename it yourself BUT DO NOT name it the same as the function. All done

You can now call this function from any where: on a form, in a report or query or from VBA

 

[Broker666]

I am quickly learning that... These forums are a lifesaver. Thanks again for your help. I will let you know how it goes.

Ryan.

 

[Broker666]

Hi,

I have saved the function in a module but when I try to run the query I am prompted to 'Enter parameter value' for 'today' so I'm guessing Access is thinking 'today' is a field in my table, any ideas how to resolve this?

Regards,

Ryan.

 

[Broker666]

I have sorted it through trial and error, I replaced today with now and it appears to work fine. Thanks again for your help.

 

[Isskint]

The other option would be to use Date()

 

[Broker666]

Right next piece of the puzzle. I have created a text box on my form that updates to the current date (and preferably time but I haven't worked out how to do that yet) each time a button is clicked. I have set the macro ran on the button click to run the following SQL code to update a table with the current date that the text box then looks at to get the latest timestamp (i.e the time the button was last clicked).

SQL code:

UPDATE tblTimeStamp SET tblTimeStamp.TimeStamp=Date()

However I am told their is a syntax error 'missing operator in query expression 'Today()'. Can anyone see any errors here? The table it should be updating is called 'TimeStamp' and the field name is also called 'TimeStamp'.

Regards,

Ryan.