Need help constructing SQL with DateAdd function in it

[SiberBob]

I need to set a SQL statement to retrieve all records for the last 10 days only.

I have tried this:

Dim RS As New ADODB.Recordset
    
    TenDaysAgo = Format(DateAdd("d", -10, Now), "Short Date")

    strSQL = "SELECT * FROM [Wreckers] WHERE [WreckerDate] > #" & TenDaysAgo & "#"

RS.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockBatchOptimistic

But I keep getting an error: "No value given for one or more required parameters".

The code works if I make the following change:

    strSQL = "SELECT * FROM [Wreckers]"         '    WHERE [WreckerDate] > #" & TenDaysAgo & "#"

So I know it's in the date calculation part of my SQL. But I can't figure out how to incorporate a DateAdd into my sql. Any ideas?

Thanks!

 

[RoyVidar]

What datatype is TenDaysAgo?

You'll need a different formatting. If TenDaysAgo is a date, try something like this:

[tt]strSQL = "SELECT * FROM [Wreckers] WHERE [WreckerDate] > #" & _
format(TenDaysAgo,"yyyy-mm-dd") & "#"[/tt]

Roy-Vidar

 

[SiberBob]

Thanks for the input! I tried:

Dim TenDaysAgo As Date

Dim RS As New ADODB.Recordset
    
    TenDaysAgo = Format(DateAdd("d", -10, Now), "yyyy-mm-dd")

    strSQL = "SELECT * FROM [Wreckers] WHERE [WreckerDate] > #" & TenDaysAgo & "#"

RS.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockBatchOptimistic

Can't get that to work... Is that what you were suggesting or did I misread it...

 

[RoyVidar]

Nope, I meant what I posted. Format the date into the sql string. Formatting a date into a date variable, doesn't help, formatting a date into an SQL string in the specified format does.

For the assigning of the TenDaysAgo, just use:

[tt]TenDaysAgo = DateAdd("d", -10, Date)[/tt]

For more info, you might look at for instance International Dates in Access

Roy-Vidar

 

[SiberBob]

Now getting an error:

Can't find the the field 'Date' referenced in your expression

with the following line highlighted...

TenDaysAgo = DateAdd("d", -10, Date)

I can see where you are going with actually formatting the date in the SQL statement instead of prior to the SQL statement, but I don't understand they why of it. The way I originally had it had the value of strSQL showed

"SELECT * FROM [Wreckers] WHERE [WreckerDate] > #11/4/2004#"

in the immediate window in break mode.

 

[PHV]

Another way:
strSQL = "SELECT * FROM Wreckers WHERE [name of date field]>Date()-10"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

You may have a control or a field on the form named date, which is not recommended (should be renamed). Or perhaps a missing/invalid reference (check that). If none of those, use VBA.Date or Int(Now) - or PHV's suggestion should work.

Then you're probably on US regional setting, so I must return the question, why did you format the dateadd when assigning to the date variable?

If you feel that there's a possibility of this system being used with other settings than US, then you should format the date. The only place where formatting to US date is necessary, is when interacting with the jet engine - so it must be concatenated into the string. Formatting a date variable has noe function at all, since it's really nothing more than a number (number of days since 1900)

Roy-Vidar

 

[SiberBob]

Ok,

Originally I (I know this is stupid) used 'Date' for a field name. I realized the error of my ways and fixed that prior starting this thread. When I fixed that I used 'WreckerDate' for the field name as well as the control name. I have since fixed that and the control name is now 'txtWreckerDate'. I know I should be using universally recognized tags to name things like 'txt', 'bol', 'cmb', and so on - but I'm still working on making that a habit.

Back to the thread...

I have tried:
(int(now)-10)
(vba.date -10) and
(Date()-10)

and they all give me the same problem.

I don't forsee this being used anywhere outside Missouri and expect the US Regional setting to be the only setting ever used.

When you suggest to check references - you are talking about 'vba editor / tools menu / references' right? Any guesses what reference?

, so I must return the question, why did you format the dateadd when assigning to the date variable?

I originally had the DateAdd in the RS.Open command, but thought the quote marks in the DateAdd("d", -10, Now) might be playing havoc with my SQL so I thought if I assigned it to a variable ahead of time that would take the hassle of figuring out the weird quote marks within my SQL. Does that make sense?

I will have to get back to this tomorrow... Thanks for your help so far!

Bob

 

[SiberBob]

Think I may have found the culprit - will update this Thursday or Friday after I get done testing and confirming what I suspect the problem was...