DateAdd Update Query

[paddybloke]

Hi folks - new user, first post, so be gentle!

I am trying to create an update query which will take a field [DateReceived] and add a number of days to it - this number is stored in another field [WDays]. The final result should be updated to a [DueOut] field.

Using the expression builder, I think the correct syntax in the update section of the [DueOut] field should be

= DateAdd("w",[WDays],[DateReceived])

where "w" indicates weekdays, but the expression builder always returns an error stating there are too many arguments.

Ideally I am trying to add only working days, hence trying to use "w" instead of "d" if I understand the interval options correctly.

Any pointers?
Thanks in anticipation.

 

[dkinley]

Howdy and welcome to the forum!

I normally try and test these in an empty column in the QBE.

First, I would try:

DueOutTest: DateAdd('w',[WDays],[DateReceived])

I think your error is you have double quotes around the interval and not single. In VBA, it uses a double, but in a query, a single.

Once tested and you can verify the results, then you can use it in the Update row of your query.

-dK

 

[paddybloke]

Hi dK,

thanks for the speedy response.

I tried this and get the same error message as before -
"The expression you entered contains invalid syntax"
"You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks."

On clicking 'OK' the focus is directed to the comma after the 'w' which I guess is Microsoft's way of helping you since their 'Help' buttons are rarely that.

Any more thoughts?

Thanks again,

P

 

[SOS]

It does NOT need to be single quotes.

And this works fine as a control source for my form for me (using my test database):

=DateAdd("w",[ShipDays],[txtOrderDate])


And this works in a query:

EstRecDate: DateAdd("w",Nz([ShipDays],0),[OrderDate])

 

[SOS]

Now, just so you know -

you can use "d" instead of "w" because there is no difference.

 

[dkinley]

... which I guess is Microsoft's way of helping you since their 'Help' buttons are rarely that.

Well said.

Hmmm. I am not sure what the issue could be since this is how I have used this function in a query, hence the quick response. I went so far as to test it prior to posting it to ensure that my thoughts were correct. In fact, just retested and single and double quotes work in the QBE with '07.

Just reread your original post. Just so you are aware 'w' does not really mean week days, more like days of the week (so weekends are included).

To resolve this, you can follow the post and create a function to hande this .... http://support.microsoft.com/kb/115489

I've went ahead and compiled a sample with this in a module so you can see it work.

This method removes the need for an interval, but to be honest - I am perplexed why yours does not work. Does it work with other intervals? Is your [WDays] field an integer? You might try wrapping with the conversion function, e.x. CInt([WDays]).

Hope this helps.
-dK

 

[SOS]

And if it has nulls then you would use the NZ function like I showed in my sample. And you don't use the = sign in the query and you shouldn't be trying to STORE the data in the table as it can be calculated by a query at any time. Storing it (calculated value) in this case goes against normalization principles.

 

[c_smithwick]

Substitute the name of your table where appropriate in the below SQL

UPDATE {table name here} SET {table name here}.[DueOut] = DateAdd("d",[{table name here}]![WDays],[{table name here}]![DateReceived])

 

[SOS]

Substitute the name of your table where appropriate in the below SQL

UPDATE {table name here} SET {table name here}.[DueOut] = DateAdd("d",[{table name here}]![WDays],[{table name here}]![DateReceived])

That will work, but it is very bad practice to use it.

 

[paddybloke]

Thanks for everyone's input - Problem now solved but you may be interested to find out what it was.

On my PC the only 'non standard' setting is to use a pipe character as a list separator (I need to do this for a client's csv files - go figure!)
As soon as put this back to the standard comma, everything is working fine as it should be!

Some things are sent to try us....................

Again, thanks to all !!

 

[dkinley]

Glad you were able to take a step back and figure it out.

Thanks for posting your final solution!

-dK