Today statement in MSQuery

[jeremyb]

I have a macro that refreshes data based upon an MSQuery.
It works fine but I have to manually enter the paramaters for the date field, stating:
>=#01/09/2005# And <=#01/09/2006#

What I would like to have is:
>=Today()-30 And <=Today()+30

but when I enter this, MSQuery does not like the statement.

What is the syntax for this sort of query where you want data returned based upon todays date + or - a number days.

Help much appreciated.


Jeremy

 

[Norie]

Jeremy

Instead of Today() try Date.

>=Date-30 And <=Date+30

 

[jeremyb]

Thanks but this has not worked. Keeps giving error: non-numeric character when numeric character expected

Jeremy

 

[Oaktree]

Not quite the answer to your question, but you could set it up as a parameter query with the criterion between [start] and [end]. The first time you run the query, you'll have to manually enter values for start and end; but, after data is returned the first time, you can right click on the query, select "parameters", and have the query automatically pull the parameters from cells. The, just set the cells up with whatever formula you want to calculate the dates (such as your =today()-30)

 

[jeremyb]

Unfotunately this wont work for me, as I need to run the query without any interaction.

J

 

[Norie]

Jeremy

My post was just really a guess, based on what I would use if I was using Access.

Where is your data coming from?

 

[jag108]

You may record a macro using your ODBC connection if that is what you are using, and add the bitss from this to it.

You may need to adjust the code to suit your ned but I hope this points you in the right direction.

I am constantly SQLing DB's from Excel. This will run as soon as you open the Workbook.

Sub auto_open()
sql_test
End Sub
Sub sql_test()
datevar_plus = Date
datevar_minus = Date
Cells.Select
With Selection
.ClearContents
End With
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=your db;UID=BSS/bss;;DBQ=yourdb;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;" _
), Array("MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;")), Destination:=Range("A1"))
Sql = " select * from table_line where date_col between '" & datevar & "' And '" & datevarplus & "'"
Sql = Sql & " order by 4 asc"
.Sql = Sql
.FieldNames = True
.Refresh BackgroundQuery:=False
End With

lastcol = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
For I = 1 To lastcol - 1 Step 1
Columns(I).EntireColumn.AutoFit
Next
Cells(1, 1).Select

End Sub

 

[jag108]

Forgot to adjus tthe variables, sorry

datevar_plus = Date + 30
datevar_minus = Date - 30