Free Microsoft Excel 2013 Quick Reference

Excel Query SQL return data with date field last 30 days

I am trying to run an auto refreshing query that filters information added from the last 30 days, I am editting the SQL statement in Query using the statement belowWHERE (repmon_0."repmon-date">= dateadd(day,-30,getdate()))But I'm getting an error specifying that the column name cannot be found. Can anyone help with this please?CheersTob


Post your answer or comment

comments powered by Disqus
I hope someone can assist me?

I am looking for a solution to an excel query thats been puzzling me for a few days now. This is what im looking to do?

I have an online quote system, which my users use to create/update customer quotes. Each time they update or create a new quote, this is date and time-stamped. It has a sql/access back-end, so the updates are stored within another table.

I want to be able to create a chart/pivot table, which will display the updates for each user within the last 30 days. I can pull the data from Access, so i have two columns with username and update date/time.

I have managed to get all updates by all users displayed in a chart, but I just want to see the updates for the last 30 days.

Is it a formula that i need to create?

Any help/pointers would be very much appreciated!

XL2000: Returning Data with Data Access Objects (DAO) to a Custom ...

With Microsoft Excel, you can create custom functions using Visual Basic for Applications. You can also use Data Access Objects (DAO) to programmatically return information from an ...

XL97: Returning Data with DAO to a Custom Worksheet Function

Microsoft Excel allows you to create custom functions using Visual Basic for Applications. You can also use Data Access Objects (DAO) to programmatically return information from an ...

XL2000: Returning Data with Data Access Objects (DAO) to a Custom ...

... you can create custom functions using Visual Basic for Applications. You can also use Data Access Objects (DAO) to programmatically return information from an external database.

XL97: Returning Data with DAO to a Custom Worksheet Function

... you to create custom functions using Visual Basic for Applications. You can also use Data Access Objects (DAO) to programmatically return information from an external database.

Return Date of Last Chosen Day of Given Month Custom function that displays the date of the last chosen day in any given month

I have dates in column J and a corresponding number in column I. Rows of data get added daily. On another sheet I have the average of all the numbers in column I. How can I write a formula to average only the numbers in column I from the last 30 days?

It's more complicated and they are also sorted by other parameters, but I'm keeping the question simple.

Thanks,

Dan

I have an excel spreadsheet and am getting data from my SQL database. In MS Query I have a query to get 3 fields from a database but need it to look better.

When I try and use the 'format' next to date field, it gives me an error about 'does not recognize format command'.

Do I add the
format(tablename.fieldname,"mm/dd/yyyy")

in the 'select', 'where' and 'order by' areas of the statement?Currently I have it only in the select part of the statement.Thanks!

Using Excel VBA, I created a query (strQryName) in Access. With below code in Excel, I am trying to copy the data from this query to Excel spreadsheet. However, I am getting nothing but field names. The code returns empty rst. When I check the query in Access, there is definitely data in it. Any help on how to fix my error is greatly appreciate it.

Thanks in advance.

Public Sub CopyFromQueryForPOST()

Dim rst As ADODB.Recordset
Dim conn As ADODB.Connection
Dim fName, queArchive, queWhere, Query1, strQryName As String
Dim e, f As Integer

fName = Sheets("StartHere").Cells(3, 3)

Set conn = New ADODB.Connection

queArchive = Sheets("StartHere").Cells(10, 3)
queWhere = Sheets("StartHere").WhereCombo1.Value
Query1 = Sheets("StartHere").Cells(12, 3)

strQryName = queWhere & Query1 & queArchive

'On Error Resume Next
With conn ' Connecton properties '
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.Mode = adModeReadWrite
.ConnectionString = "Data Source=" & fName
.Open
End With

strSQL = "Select * FROM " & strQryName
Debug.Print strSQL
Set rst = New ADODB.Recordset
With rst

.Source = "Select * FROM " & strQryName
.Open strQryName, conn, adOpenKeyset, adLockOptimistic
End With

'rst.MoveLast
Debug.Print rst.RecordCount '

Hello,

I'm using Microsoft Query to return data from a SQL view to Excel. This
works fine except that when including that data into a pivot table I notice
that one field in particular is being represented both with trailing spaces
and without. The view does not (currently) trim the field and so Microsoft
Query must be interpreting the field in different ways.

I could amend my view to resolve it for this field, but if it is happening
for one field my concern is that it may also happen on other fields, so a
global fix is the ideal solution.

Has anyone else seen this before, or more importantly, does anyone know how
to resolve this issuse without 'just amending my view'?

Cheers,

Hi,

Given older versions of Excel cannot handle more than 65000 rows, my question is - can you run an SQL query from MS Query on source data in .csv format that returns an aggregated column when the source data contains more than 65000 rows but the results are less than 65000?

Thanks

Mark

Hello,

Is it possible to write an excel query to get data from a different tab. Mainly I have an access query that get > 100 records of students. I have a tab for each student with the name..and some info at the top. I would like to display their records at the bottom. What is the best way to do that? Thank you

Really stuck here. I have an excel s/s of data with dates in. These dates are formated in custom "dd-mmm-yy" format and I don't really want to change this. I want the user to input the date in an inputbox in dd-mmm-yy format and then the autofilter filter the s/s by this date. Sounds simple but I'm struggling here & the filter is not returning anything. This is my code:

Dim currentd As String
Dim currentd2 As Date

currentd = Format(Now(), "dd-mmm-yy")

currentd2 = Format(InputBox("Date of Rec?", "Valid Format - dd-Mmm-yy", currentd), "dd-mmm-yy")

Range("c2").Select

Selection.AutoFilter Field:=14, Criteria1:="=" & currentd2

End sub

Any help would be much appreciated as the only fix I've found is to convert the dates in the s/s to dd/mm/yyyy format.

I am creating a new web data source. The URL returns an html table of
data with typical Content header. The data is visible in a browser,
but it doesn't show up in the excel browser. Any ideas?

Rich

Hi All,

I have a problem when I run I execute a query using QueryTables to return data.

I would like the query to run in the background so my PC is not stuck waiting frozen for the query to complete.

My problem is, when i execute as background query true - I loop every second and check the .refreshing property which does not return.

Only when I debug does the globe stop spinning and the query data returned.

Is there anyway to "pause" my code for a split second so the query returns the data?

I have pasted a snippet of the code:

.Refresh BackgroundQuery:=True
End With

' keep looping until query completes
TimerLoop (strQueryName)

Sub TimerLoop(strQueryName As String)

Dim Timer As Double
Dim Secs As Integer
' keep looping until query completes

Again:
Application.ScreenUpdating = True
Timer = Now()
Do Until Now() - Timer > TimeValue("00:00:10")
Secs = 10 - DatePart("s", Now() - Timer)
Application.StatusBar = "Waiting for query - " & strQueryName & " (" & Secs & " seconds).."
Sleep 1000
DoEvents
Loop

If InStr(1, ActiveCell.Value, "Getting Data") > 0 Then
GoTo Again
End If
End Sub

Thank you in advance.

Sal

I have an Excel query that retrieves data from an Access database. It currently uses three parameters that each come from three separate worksheet cells. The parameters are all optional and are all partial word lookups. All are text. My SQL query is something like this:
The parameters come from cells: a3, b3 and c3.
Values are placed by users in cells: a4, b4 and c4.
Cell formulas are:
a3: =IF(A3="*","%"&"%",IF(COUNTA(A3:C3)=0,"","%"&A3&"%"))
(Note: the '*' is my technique for allowing the user to retrieve all records by placing '*' in a4 and leaving b4 and c4 blank. Is there another technique I could use?)
b3: ="%"&B3&"%"
c3: ="%"&C3&"%"

I need to add a fourth optional numeric parameter which users could place in d4. What formula/wildcard character would I use in d3 to make it an optional input and also pull all records if no value is entered by users in d4?

The '%' doesn't work as a wildcard character for numeric searches; i.e, I get an 'invalid type' error.

Thanks.

I have built a chart that shows data for three time periods: last 7 days, last 30 days and last 8 quarters. I use a list-box to allow users to select which date range. The cell value returned by the list box triggers some logic that pulls in the correct data to my source data cells.

The problem I'm having is with the date ranges that are displayed along the vertical axis. The dates are correct for the last 7 days and last 30 days but for the last 8 quarters it shows a tick mark for every month even though there are only 8 dates available in the date row of my source data.

Example, in my source data, the dates listed (in consecutive cells) are 1/1/05, 4/1/05, 7/1/05, etc. But when they get displayed in the chart, Excel is adding tick marks for all the intervening months (2/1/05, 3/1/05, 5/1/05, 6/1/05, etc).

It's not a big deal but this makes the chart area look unnecessarily cluttered and harder to read; it also makes it look like there are 24 data points (8 quarters X 3 mos. per) when in fact there are only the 8 quarterly data points.

As for Format Axis, everything is set to auto. I cannot use fixed values because sometimes I need days and sometimes I need months. Any ideas on how I can force Excel to not add the tick marks for the intervening months?

I've got a workbook that gets data via a web query and produces an export workbook, via VBA. The problem I'm having is that sometimes the table referenced in the web query doesn't exist (because the website hasn't updated); if it doesn't exist, the web-query displays an error ("This Web Query returned no data...") and the code carries on in the background.
Is there any property of the querytable I can use to check if the query is returning data, or something else I can do to make sure that the export only goes ahead if the query actually updated?

Hi all,

I have to rebuild a database that I previously built in Excel. I was originally doubtful whether to choose Access or Excel, and in the end choose Excel so the end user could more easily filter and select data accordingly to his needs within minutes. As I had to build a very big list of data, with many fields, I ended up with 25-30 fields in a single table.
The only problem I have is the insert of data: as there are so many fields it is unconfortable to put data. Here is my question:is it possible to create one or more personalized modules (like the tool "Data Module") , in order to have something like Access' masks ?

Hope I could explain myself enough, Thank you in advance

Hi all,

was having a look and cannot find the question to this answer.

1,what i have done is made a workbook with a user form to input data (customer id is the unique record)

i now want to create a form where i can put the customer id in and tell it to show me that customers details and i can edit.

i have various textboxes/combo boxes/ check boxes in the input form and i want all these to be included on the review form (for updating and changing the existing data).

2, also if possiable i will add a sheet with dates for deadline to cancel, can i get this to show on the new input form is it is to late to cancel?

thanks alot for all your help in advance peeps.

p.s. i have attached what i have in a zip file, i cannot figure out how to do these.

Tony

How do I go about querying SQL database through Excel?

Hi i've a date field in my access table i want to extract the month part out of it and display the month name so if its 23/05/2006 the month name will be May.

I want to do a update statement on the table. here is what i have so far

UPDATE query_export SET query_export.[Month] = DatePart("m",[Order Create Date]);

I want the Month field to display the name of the month from the field order Create Date column which has the date format as 23/05/2006 .

I have excel 2003, and I am trying to calculate data that I receive from a laboratory. Often the data I receive is less then the detection limits. I need to show that the data is less than the detection limits by typing, for example:

I have created a pivot table with date fields. I grouped the dates into quarters and months. Is it possible to create calculations in the month or the quarter field? Or can anyone out there enlighten me on how to use "GetPivotData"???


No luck finding an answer? You could always try Google.