gleverator
asked on
Adding weekdays to a date in ms access query
I have a table with a date field and integer field
The integer field resembles the amount of weekdays
The goal is to add the integer field to the date field to get a new date
For example:
DateField: 10/26/11
IntegerField: 5
Output should be datefield plus integerfield (where we are adding weekdays only) so output should be 11/2/11
What would be the query to add the weedays to the date field?
The integer field resembles the amount of weekdays
The goal is to add the integer field to the date field to get a new date
For example:
DateField: 10/26/11
IntegerField: 5
Output should be datefield plus integerfield (where we are adding weekdays only) so output should be 11/2/11
What would be the query to add the weedays to the date field?
DateAdd("d", [integerField], [dateField])
ASKER
both solutions on this page look like they car work, however i cant get see the original table referenced so i cant determine how to fit my table structure.
https://www.experts-exchange.com/questions/26982000/Determine-the-date-of-the-business-day-from-the-business-day-number.html?anchorAnswerId=35482567#a35482567
My table is called "PurchaseOrders" and my date field is called "PODate" and the integer field is called "Integer1"
Can someone modify capricorn1 solution to work with my data?
https://www.experts-exchange.com/questions/26982000/Determine-the-date-of-the-business-day-from-the-business-day-number.html?anchorAnswerId=35482567#a35482567
My table is called "PurchaseOrders" and my date field is called "PODate" and the integer field is called "Integer1"
Can someone modify capricorn1 solution to work with my data?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the new date can also be shown in unbound field on a form. just add
=getDueDate([PODate],[Inte
to the control source property of the field you want to show it in.
=getDueDate([PODate],[Inte
to the control source property of the field you want to show it in.
Sorry forgot to add, if you do want it to be shown on a form only instead of being stored in a query, you will still need to create a module as described above.
> .. determine how to fit my table structure.
As noted, you won't do that.
Create a new module, copy and paste the function into this, compile and save the module using a name different from the function, say, basUtils.
Use this expression in a column in a query where you have pulled in your table:
DeliveryDate: ISO_WorkdayAdd([PODate], [integer1])
/gustav
As noted, you won't do that.
Create a new module, copy and paste the function into this, compile and save the module using a name different from the function, say, basUtils.
Use this expression in a column in a query where you have pulled in your table:
DeliveryDate: ISO_WorkdayAdd([PODate], [integer1])
/gustav
ASKER
jdc1944 - you have the solution and i will award you.
Just quick question.
When i put this in my query field:
Expr1: getduedate([podate],[inven
It works perfectly.
But if i try to put a criteria like this:
<date()
I get this error:
Data type mismatch in criteria expression.
Why do i get that error?
Just quick question.
When i put this in my query field:
Expr1: getduedate([podate],[inven
It works perfectly.
But if i try to put a criteria like this:
<date()
I get this error:
Data type mismatch in criteria expression.
Why do i get that error?
Convert the text to a date.
Expr1: Cdate(getduedate([podate],
Expr1: Cdate(getduedate([podate],
Hum, I think you need to check the data types in your tables as when i put that in the criteria i get no error.
I have 'PODate' as a Date/Time and the integer1 as a number field.
I have 'PODate' as a Date/Time and the integer1 as a number field.
> I get this error:
> Data type mismatch in criteria expression.
That is not possible as your function returns a date/time value only, thus wrapping the output in CDate makes no sense.
Same goes for my function and expression above.
Something else must be going on.
/gustav
> Data type mismatch in criteria expression.
That is not possible as your function returns a date/time value only, thus wrapping the output in CDate makes no sense.
Same goes for my function and expression above.
Something else must be going on.
/gustav
I used the version without the holiday table and it works for me using <now() and <date(). I don't think it should matter, but what about the default date format on your PC?
The default date format only influences the display of the value, not the value itself.
So something else must be going on.
/gustav
So something else must be going on.
/gustav
Would need to see the whole query to determin why it's failing. Could be a data type mismatch in another field.
Did you use the tbllHolidays? If so, are you sure the ObservedDate field you created was a date field?
ASKER
had to modify it for my use