DateAdd Function in VBA Code

[ceesql]

I am having difficulty adding the dateadd function to a sql script "where" clause within an application. I want to include records that have neg qty within a 20day period.
The original code is:
sql = "Delete * From tblSales where date_shipped >= #" & LastUpdate & "#" & _

I added the following:
&quot;or tblSales.qty_shipped < 0 and tblSales.date_shipped >= dateadd(dd,-20,# LastUpdate #)

DoCmd.RunSQL (sql)

Though I could do this easily in T-SQL I'm running into a fence trying to do this within Access.

 

[cmmrfrds]

Is the database being used Access or SQL Server?

 

[ceesql]

I'm pulling data from a SQL DB table to a local table in the application.

 

[ceesql]

Following is more of the code:

sql = &quot;INSERT INTO tblSales &quot; & _
&quot;SELECT tblSales.* &quot; & _
&quot;FROM &quot; & _

*******ODBC CLAUSE HERE******

&quot;INNER JOIN tblSalesOrganization &quot; & _
&quot;ON (tblSales.ship_to_code = tblSalesOrganization.ship_to_code) &quot; & _
&quot;AND (tblSales.customer_code = tblSalesOrganization.customer_code) &quot; & _
&quot;WHERE tblSales.date_shipped > #&quot; & LastUpdate & &quot;#&quot; & _
&quot;OR tbl.Sales.qty_shipped < 0 and tblSales.date_shipped >= dateadd(d,-20,#&quot; & LastUpdate & &quot;#&quot;

DoCmd.RunSQL (sql)

 

[cmmrfrds]

What error are you getting?
Does the code run without the additional criteria?

Are the sql server tables linked to the access mdb? If so, why the ODBC clause and what does it contain.

 

[ceesql]

Thank you for your help.

The error I get is &quot;Data type mismatch in criterior&quot;

The code worked fine, before I added this statement:

&quot;or tblSales.qty_shipped < 0 and tblSales.date_shipped >= dateadd(d,-20,#&quot; & LastUpdate & &quot;#)&quot;

 

[braindead2]

drop the pound signs or relocate them

= wrong dateadd(d,-20,#&quot; & LastUpdate & &quot;#)&quot;

correct dateadd(d,-20,[LastUpdate])&quot;

may need to do
tblSales.qty_shipped < 0 and tblSales.date_shipped >= # & dateadd(d,-20,LastUpdate ) & &quot;#&quot;

 

[ceesql]

Thanks for your help! I'll try this now and let you know.

 

[apkohn]

The problem is that the d part of the DateAdd part needs to be a string. Your code is attempting to pass a parameter dd to the DateAdd function, not the &quot;d&quot; string.

 

[ceesql]

Thank you all for your expert advice.

I ended up with this:
&quot;Delete * From tblSales where date_shipped >= #&quot; & LastUpdate & &quot;#&quot; & _
&quot;OR (date_shipped >= #&quot; & DateAdd(&quot;d&quot;, -20, LastUpdate) & &quot;# And qty_shipped < 0)&quot;