Excel Querying Access Using MS Query
Jun 21, 2005
Hi folks!
I've got my application working wonderfully and now have set up some reports in Excel. People want to be able to manipulate the data and I figured this was easiest...the users don't know how to create their own report within Access but are Excel literate.
My workbook has two queries (setup using Microsoft Query) back to the database...the data is returned to the workbook and then sorted within the workbook using Excel formulas.
This works fine all data is present and correct and the results are accurate.
HERE'S THE PROBLEM
My database keeps corrupting. The LDB file isn't being deleted after the last person exits the database. I downloaded the LDBUTILS from Microsoft and found that when a person accesses the database via the Excel workbooks the session is left in a suspect state and this is causing the corruption.
I know it's not a server permissions error because I created the folder and am the owner of the file. I have full control of the files and folder on that part of the server yet my own PC frequently leaves the database in a suspect state.
Does anybody know what I can do to stop this? Is there a piece of code I can add so that when the workbook is closed the database session is also ended cleanly?
Thank you in advance
Jo
View Replies
ADVERTISEMENT
Apr 25, 2013
I am using Excel and Access 2010.
I have an excel spreadsheet with 8 tabs. They are all in the same format and column order. They are employees grouped by region. My ultimate goal is to merge all of these onto one excel tab, relatively instantly. I created a master tab and tried doing array formulas and Vlookups, it worked but my spreadsheet was way too slow.
My solution? Import and link them to an Access database, step complete. Create an XML export then import into Excel.
My problem? The only way to update the excel tab with the combined tabs is to save the excel file after changes, go back into Access, re-export to XML, then go back into excel and refresh the data.
My questions, is there any way to automate this process to the point that I can change excel, save, then hit refresh on my excel tab with the XML import to auto-update?
View 7 Replies
View Related
Nov 30, 2006
This is either very simple or very complex, I haven't figured out which yet.
I need to know the number of tables in my database and from each table I need to know how many records are in each table. Ordinarily I would just count the number of tables then open each one up to get the number of records, but I'm working with 100+ tables so that's not very practical.
If it makes a difference, the tables in question are linked tables. I don't imagine that is relevant, but thought I'd mention it.
Thanks!
View 2 Replies
View Related
May 7, 2013
I'm using Access 2003 connecting through ODBC. I'm trying to set up an automated query to grab today's data. normally it's pretty easy, all you'd have to do is enter date() in the criteria in an access query. Unfortunately, the date in my database (i can't change this) is in an odd format.
example: april 1st, 2013 = 1130401
Every single date always has that preceding "1" in front of it, then the year, then the month, and finally the day.
I want to always specify the current date whenever the query is run (without having to type it in)
View 10 Replies
View Related
May 9, 2006
OK, coming up with a title for this one was a little difficult. Almost as difficult as the problem I'm facing ;)
Here's the basic premise. The database is used to register people for a weekend convention. Part of the registration allows the person to request a specific roommate. In an ideal world, this would be relatively simple--match the roommate to the person, and you're done!
Did I mention they're teenagers?
Here's what happens. In all of the following examples, you are given only the ID number of a person to start with (because referencing any column but the bound one in a combo box from a query seems impossible, despite what Microsoft has to say (http://office.microsoft.com/en-us/assistance/HA011730581033.aspx)), and must dump all relevant names and ID's into a temp table.
Example 1:
IDFirstLastRFirstRLast
1MattBrownLukeWilson
5LukeWilsonMattBrown
9PhilHeartMattBrown
In this case, we have two people that have requested Matt Brown as their roommate. Based solely on the ID Number of any given person above I need to dump all three names into a temp table to be displayed to the user, so he can pick who will go where. Physically, the first two people would be roomed together, and the 3rd would be roomed in a single room nearby.
Example 2
IDFirstLastRFirstRLast
1MattBrownLukeWilson
5LukeWilsonMattBrown
9PhilHeartMattBrown
72CraigJohnsonPhilHeart
Similar to the last, but we've added a fourth person that has requested the 3rd person. Doing it the manul way, we'd room the first two together, and the last two together, in rooms next to each other. Unfortunately, we may not find the last two until we're two floors away, and end up having to shuffle people all over the place to get it to work. Now, we could be jerks, and just say, 'sorry, you screwed up, your problem, not ours'...except facing a crying 14-year-old girl upset because she's not with her friends isn't exactly fun. This is why I want to find all four people immediately after rooming any one of the above.
Example 3:
IDFirstLastRFirstRLast
1MattBrownLukeWilson
5LukeWilsonPhilHeart
9PhilHeartCraigJohnson
72CraigJohnsonMattBrown
Ah, the circle of life. Person 1 requests person 2, person 2 requests person 3, 3 request 4, 4 requests 1. This is surprisingly common. And annoying. Physical results would be the same as the previous example.
So to summarize, I start with person 1. Add him to the temp table. Then find the person he requested as a roommate. Add him to the table. Then look at the roommate's requested roommate, compare it to the temp table. If the person is there (ie, a direct match), then stop. If he isn't there, then query the database. Not there? Stop. Found another hit? Add him to the table. Search again. Then, display them all in a list box allowing the user to pick and choose who goes where.
I don't hope to automate the whole thing. That's not the goal. There are a variety of other factors that go into rooming that could never be automated, and require a personal touch. All I want to do is make it easier to find duplicates to streamline the existing manual procedure.
Now, how do I do it?
View 3 Replies
View Related
Jan 2, 2006
I am working on a query form, in which user can enter three parameters: date range (datefrom field, and dateto field), 1st selection of category (a drop-down menu, multi-selection enhanced), 2nd selection of category (a drop-down menu, multi-selection enhanced. By clicking command button, user can choose whether the result show in query table, or in a report. It didn't work... the error has something to do with the date criteria. If I remove the date criteria, i.e. just allowing selection of two categories, it works perfectly fine.
I've checked out many postings in this forum, but it doesn't take me far... below is the code, please please help....
Note: the cmdOK is the name of the command button for presenting results from query table.
-------------------------------------------
Private Sub cmdOK_Click()
On Error GoTo cmdOK_Click_Err
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant
Dim strDate As String
Dim str1MainCate As String
Dim str2MainCate As String
Dim str1MainCateCondition As String
Dim str2MainCateCondition As String
Dim strSQL As String
' Check for the existence of the stored query
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "QryCateDateForm" Then
blnQueryExists = True
Exit For
End If
Next qry
' Create the query if it does not already exist
If blnQueryExists = False Then
cmd.CommandText = "SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips"
cat.Views.Append "QryCateDateForm", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "QryCateDateForm") = acObjStateOpen Then
DoCmd.Close acQuery, "QryCateDateForm"
End If
' Build criteria string for Date
strDate = "(((NewsClips.IssueDate) Between #" & datefrom & "# And #" & dateTo & "#))"
' Build criteria string for 1MainCate
For Each varItem In Me.fstMainCate.ItemsSelected
str1MainCate = str1MainCate & ",'" & Me.fstMainCate.ItemData(varItem) & "'"
Next varItem
If Len(str1MainCate) = 0 Then
str1MainCate = "Like '*'"
Else
str1MainCate = Right(str1MainCate, Len(str1MainCate) - 1)
str1MainCate = "IN(" & str1MainCate & ")"
End If
' Build criteria string for 2MainCate
For Each varItem In Me.SecMainCate.ItemsSelected
str2MainCate = str2MainCate & ",'" & Me.SecMainCate.ItemData(varItem) & "'"
Next varItem
If Len(str2MainCate) = 0 Then
str2MainCate = "Like '*'"
Else
str2MainCate = Right(str2MainCate, Len(str2MainCate) - 1)
str2MainCate = "IN(" & str2MainCate & ")"
End If
' Get 1MainCate condition
If Me.optAnd1MainCate.Value = True Then
str1MainCateCondition = " AND "
Else
str1MainCateCondition = " OR "
End If
' Get 2MainCate condition
If Me.optAnd2MainCate.Value = True Then
str2MainCateCondition = " AND "
Else
str2MainCateCondition = " OR "
End If
' Build SQL statement
strSQL = " SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _
"WHERE NewsClips.[IssueDate] " & strDate & _
str1MainCateCondition & "NewsClips.[1CategoryMain] " & str1MainCate & _
str2MainCateCondition & "NewsClips.[2CategoryMain] " & str2MainCate & ";"
' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("QryCateDateForm").Command
cmd.CommandText = strSQL
Set cat.Views("QryCateDateForm").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "QryCateDateForm"
' If required the dialog can be closed at this point
' DoCmd.Close acForm, Me.Name
' Restore screen updating
cmdOK_Click_Exit:
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error has occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub
------------------------------------------------
View 4 Replies
View Related
Nov 8, 2007
I have the attached DB.
On the form I have a button which will send the results of the Access Query2 to an Excel spreadsheet-OK, this works and no problem.
But what I would like to do is push the button and it will send the results of the Access Query2 to an existing spreadsheet called caseloadmgt.xls which is located on my desktop - C:Documents and SettingsJim.BlackDesktopcaseloadmgt.xls
And the next time I push the button to run the query again I want the new data to start 2 cells below the ending of the first query results in the spreadsheet.
I have tried to follow all the examples I could find and I guess I just don’t know enough about this to make it work. What I need is someone to correct/ or change my code and put the new/corrected code in the DB where it should go
View 1 Replies
View Related
Aug 4, 2005
In Excel I have a sheet with about 3000 rows. Each row has an id. I need to retrieve a field from an Access database for each id in the Excel file.
I know how to query the Access database from Excel, but I don't know how to set the query to use the id's in the Excel sheet instead of an Access table.
Any help would be appreciated. Thanks.
View 1 Replies
View Related
Dec 31, 2005
Folks,
I have a form with combo box (cboProjForRptSeltn ) and two command buttons. The combo box is filled with name of the reports. The combo box has two columns, column 1 is the name of the report and column 2 is the bound column which also is the link field (primary key) to limit the records.
When the user selects an item from cbo box and click the "Preview Risk Table" command button it will open a report in preview mode. The report's "Record Source" property is set to a UNION query (qryRprtRskTbl) which pull the records from several tables. The report's "Filter" property is set to the following criteria:
“qryRprtRskTbl.P.intProjectId=Forms!frmReportSelec tionBlrR1!cboProjForRptSeltn”
The above criteria is nothing but filter based on the value of the cbo box.
The click event procedure is as follows:
Private Sub cmdPreviewRprt_Click()
Dim strDocName As String
strDocName = "rptRskTblProjectWise"
DoCmd.OpenReport strDocName, acPreview, "qryRprtRskTblFilter_r1"
End Sub
"qryRprtRskTblFilter_r1" is another query out of the UNION query I mentioned above (qryRprtRskTbl). qryRprtRskTblFilter_r1 is pulling all the records from qryRprtRskTbl which meets the projectID field selected in cbo box, which is also the same as the "Filter" property value of the report as indicated above.
Everything works fine with cbo box and "Preview Risk Table" command button. It just pull all the records for the project selected under cbo box and display it as report in preview mode. Looks great!!
Here's my problem. I wanted to export the same report that was previewed by the user to Excel. For this I am using, another command button called “Export to Excel”. The click event of this procedure is as shown below:
Private Sub cmdExportToExcel_Click()
On Error Resume Next
Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlWorkbook As Excel.Workbook
Dim acQuery As QueryDef
Dim objRST As Recordset
Dim strQueryName As String
Dim strSearch As String
'Dim strSQL As String
'strSearch = Me![cboProjForRptSeltn]
strQueryName = "qryRprtRskTblFilter_r1"
'strQueryName = "qryEffcyAllProjtsForRprt"
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Add
Set objRST = Application.CurrentDb.OpenRecordset(strQueryName)
'Loop through the fileds collection and make each field name a column heading in Excel
Set xlSheet = xlWorkbook.Sheets(1)
For lvlColumn = 0 To objRST.Fields.Count - 1
xlSheet.Cells(1, lvlColumn + 1).Value = objRST.Fields(lvlColumn).Name
Next
'Change the font to bold for header row
xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, objRST.Fields.Count)).Font.Bold = True
' I have some codes here for formatting Excel cells …
'Send data from Recordset out to Excel
With xlSheet
.Range("A2").CopyFromRecordset objRST
.Name = Left(strQueryName, 31)
End With
Set xlSheet = Nothing
Set xlWorkbook = Nothing
Set xlApp = Nothing
End Sub
When I click the command button, it loads an instance of Excel and adds a workbook under the query name "qryRprtRskTblFilter_r1", but no data.
If I use another query say, "qryEffcyAllProjtsForRprt", which has no connection to cbo box value then it is cool, exports all the data to Excel without any problem.
I guess, you folks understand what I am trying to achieve here. Basically, I wanted to give the user some flexibility, either they can view the data as Access report or Export to Excel with same formatting feature and add more later if they want after exporting, as they see on Access report preview.
I have attached some of the query files I have described here. May be I can clarify more down the road, if necessary.
My bottom line question is: why the “qryRprtRskTblFilter_r1” query runs perfectly on Access reports but not when I want to run to export to Excel?
I don’t want to use the TransferSpreadsheet or outputTo method of docmd object, because I wanted to do some formatting before I export to excel.
Any help is greatly appreciated.
Thanks
ShanVel
View 7 Replies
View Related
Jan 11, 2006
Hi does any body no the access syntax to create this excel formula
=POWER(SQRT(L5/10)*47.1,2)/3769.9
Into access
Thanks
John
View 2 Replies
View Related
Aug 3, 2007
Am running a query in Excel on an access table that uses lookup columns. The query is returning in excel with the right data but is returning the lookup value number rather than the data stored inside. How can I return the actual data value rather than the lookup value number.
i.e I have a list of areas
NY
CT
WA
in a lookup column.
User selects CT so is lookup value 2. But I want excel query to return CT.
How can I do this?
thanks
View 2 Replies
View Related
Apr 4, 2007
Please Help i have been trying to figure this out for some time now and I am really stuck. I need to maniuplate data that is in an excel spreadsheet basically seperating out certain data and putting that information into seperate columns an example of what i am trying to do is this.
in excel I have a numbers 117.865.65, 117.865.79, 117.865.60 associated with the Name "Cell Phone in Blue, Cell Phone in Red and Cell Phone in Green " what I am trying to get Access to do it take the part of the number that they all have in comon and put that into a column, then take each of the different numbers and put that into a column, and then put the Names of each item and put that into a seperate column. like this:
117.865 Cell Phone
117.865 .65 Cell Phone in Blue
117.865 .79 Cell Phone in Red
117.865 .60 Cell Phone in Green
anyone that know how i can get access to do this your help woudl be greatly appreciated.
View 1 Replies
View Related
Oct 23, 2007
I am using the DoCmd.TransferSpreadsheet statement that successfully exports a query result set into excel. I was wondering is there a way to get this result to populate certain fields of an existing excel spreadsheet that I have created and need the information for. Right now the query just creates a new worksheet in the specified workbook.
View 2 Replies
View Related
May 13, 2005
I am writing an Excel VBA code to connect to an Access db and execute an access query. After executing the query, I would like to display the result in the excel spreadsheet.
I was successfully able to make the connection to the access db using the following code:
Set db = OpenDatabase("file name", False, True, "MS Access;PWD=abcd")
However, I do not know what code to use to execute a query named "qrytest" on the Access db and show the result in this Excel file.
Can anyone teach me how to do this please?
P.S. I searched this forum for about 1/2 hour before posting this. Please excuse me if this was addressed anywhere before! Would appreciate a link, if it was.
View 1 Replies
View Related
May 18, 2006
I'm new to Access, but a coworker has a huge project to undertake. There are over 200 queries that produce 1 row results for each query. She's been cutting that row and pasting it into Excel 200 times. Is there an easy way to insert them 1-by-1 into Excel or prefereably Access?
BTW, the columns are the same on the query results. I'll keep investigating while I wait for the experts to answer.
View 2 Replies
View Related
Jun 4, 2007
Hey folks,
Not sure if this is the best forum but decided to put it here anyway.
I have a Access query that reads:
SELECT bleh
FROM blah
WHERE something > 10
Then in Excel I pull the data across using:
Set qdf = db.QueryDefs(qryName)
Set rs = qdf.OpenRecordset
and paste the data using
ws1.Range("IV1").End(xlToLeft).Offset(0, 2).CopyFromRecordset rs
I now want to change the query so the where statement reads
WHERE something > [amt]
So the question is how do I pass the value for the parameter value for [amt] from excel to access?
View 1 Replies
View Related
Dec 30, 2007
Hi all, I seem to be getting this error when I try to import a query from MS Access to Excel:Too Few Parameters. Expected 1.Now, it runs fine in Access and seems to be a Microsoft Query problem, I know there are some issues around the performance and capability of Microsoft Query but hopefully someone can have a look at my query and see if they can identify my "too few parameters" :)(Sorry about the largeness of the query...)SELECT YPOL_LoadTbl.[Channel Done], YPOL_LoadTbl.[Canvass Code], Sum(YPOL_LoadTbl.[Handl Nisd Amt]) AS [SumOfHandl Nisd Amt], YPOL_LoadTbl.[Issue ID]FROM YPOL_LoadTblGROUP BY YPOL_LoadTbl.[Channel Done], YPOL_LoadTbl.[Canvass Code], YPOL_LoadTbl.[Issue ID]UNIONSELECT "ALL" AS [Channel Done], "ALL" AS [Canvass Code], Sum(YPOL_LoadTbl.[Handl Nisd Amt]) AS [SumOfHandl Nisd Amt], YPOL_LoadTbl.[Issue ID]FROM YPOL_LoadTblGROUP BY YPOL_LoadTbl.[Issue ID]UNIONSELECT YPOL_LoadTbl.[Channel Done], "ALL" AS [Canvass Code], Sum(YPOL_LoadTbl.[Handl Nisd Amt]) AS [SumOfHandl Nisd Amt], YPOL_LoadTbl.[Issue ID]FROM YPOL_LoadTblGROUP BY YPOL_LoadTbl.[Channel Done], YPOL_LoadTbl.[Issue ID]UNIONSELECT YPOL_LoadTbl.[Channel Done], "ALL" AS [Canvass Code], Sum(YPOL_LoadTbl.[Handl Nisd Amt]) AS [SumOfHandl Nisd Amt], YPOL_LoadTbl.[Issue ID]FROM YPOL_LoadTblGROUP BY YPOL_LoadTbl.[Channel Done], YPOL_LoadTbl.[Issue ID]UNION SELECT "ALL" AS [Channel Done], YPOL_LoadTbl.[Canvass Code], Sum(YPOL_LoadTbl.[Handl Nisd Amt]) AS [SumOfHandl Nisd Amt], YPOL_LoadTbl.[Issue ID]FROM YPOL_LoadTblGROUP BY YPOL_LoadTbl.[Canvass Code], YPOL_LoadTbl.[Issue ID];Thanks!
View 4 Replies
View Related
Nov 20, 2014
I need to import the data from an Excel spreadsheet but only certain cells. Is there any way I can call out a range of cells in a query and tell it what fields in a table to put those records in?
View 7 Replies
View Related
Feb 27, 2015
What I am trying to do is populate the specific fields on excel sheet based on access query. so say I have 3 separate fields Name, Job, Salary on excel sheet (already designed excel sheet). and my access query has all 3 fields pulled up for all employees. Now if a person selects his "Name" on access form person should be able to see prepopulated excel sheet with his name, job and salary. I know I have to go recordset path.
View 1 Replies
View Related
May 4, 2006
I use an Excel interface to retrieve data from an Access file that has approximately 1 million records. I use an MS Query from Excel to run an Access Query to retrieve the data.
I have 16 "Criteria" fields (columns) in Excel that I change with drop-downs. Each criteria column has 2 to 9 values set; a total of 75 values. I pass these criteria fields to Access via a table link in Access.
The criteria of the fields in the query that is run in Access to pull the data based on the table link is updated by using the Values In A List (IN) option.
When I refresh this query in Excel, it pulls the data fine, but may take 10 minutes to run. (I am using MS Office 2003, 1Gig memory). I have limited VBA knowledge. Is there a better/faster way to pull this data?
View 1 Replies
View Related
Dec 13, 2004
So I'm having timing issues trying to print data from an Access (actually SQL Server) based query to a Excel Spreadsheet. Basically I'm trying to get around using MS-Query and last week I didn't seem to have any problem as far as performance. However, when i tried running today it seems I've had a slow down of at least 500%. The data I'm trying to print is quite large (few hundred records and about 140 fields) so I expect some performance issues. Like I said though i just can't undestand the decrease. Possibly machine performance?
Anyhow, I've tracked the bottle neck and no suprise it is in the loop, I'm just suprised it takes so long to loop through the recordset.
Here's my code, any help will be appreciated?
'Generate grid to dump data
If intRecordSetCount > 0 Then
rst.MoveFirst
'Loop through length and width and display results
Do Until rst.EOF
intRSTField = 0
intFieldCount = intField
For intIndex = 1 To intColumnCount
Worksheets(strWorksheet).Cells(intRowCount, intFieldCount).Value = rst(intRSTField)
intFieldCount = intFieldCount + 1
intRSTField = intRSTField + 1
Next intIndex
intRowCount = intRowCount + 1
rst.MoveNext
Loop
Else
MsgBox "Recordset has not been initialized or does not exit"
End If
'Define name range
ActiveWorkbook.Names.Add Name:=strRangeName, RefersTo:= _
"= '" & strWorksheet & "'!R" & intRow & "C" & intField & ":R" & intRowCount & "C" & intFieldCount, Visible:=True
View 5 Replies
View Related
Jun 21, 2012
How to export a MS Access table or Query to and Excel file and it works great. How to make this code Export multiple table into one Excel file/
here is the Function:
HTML Code:
Sub ExportData_Sheet_Basic()
On Error GoTo ExportData_Error
'DAO objects to get the data
Dim db As DAO.Database
Dim rs As DAO.Recordset
[code]....
View 5 Replies
View Related
May 21, 2015
I have a basic access query (MACs Report Template) that I need to export into multiple excel files based on the 1st field (Plan ID).
Example:
PLAN ID
Number
Amount
Status
AM141
12345
100
Disconnected
AM141
54321
5000
Active
AM142
11122
2000
Disconnected
AM155
22334
500
Disconnected
I need this to create a spreadsheet for each unique PLAN ID. Ideally i want it to export the following:
MACs Report AM141 20150521.xls (both records above should be in this report)
MACs Report AM142 20150521.xls
MACs Report AM155 20150521.xls
View 3 Replies
View Related
Aug 31, 2012
I have a query that includes several columns, one of those columns shows returns on investment as a percentage. I need to figure out the annualised returns on investment using the following excel formula
{=PRODUCT(1+B1:B24)-1^(12/24)-1}
This formula gathers the percentages for the last two years then provides an annualized amount.
I'm just wondering whether this is possible to apply to the query so that when I open the query it can provide me with the 2 year annualized statistics.
View 1 Replies
View Related
May 1, 2014
I'm having a rough time trying to figure out how to pass a date to an SQL statement that Excel VBA macro will run. The date is in a cell (A1) formatted as 'm/d/yyyy'. Let's say it's 2/1/2014. I want to run an SQL statement that retrieves data from a table where a field is greater than 'A1'. The table field is a date/time field and has values formatted as 'mm/dd/yyyy'.
I've tried various syntax on the Where but cannot get it to work.
sd = Range("A1")
SELECT [tn].[Date Submitted]
FROM[tn]
WHERE tn.[Date Submitted] > """ & sd & """
This results in the following where clause that does not work.
WHERE tn.[Date Submitted] > "2/1/2014"
View 4 Replies
View Related
Dec 17, 2014
I'm running a VBA routine in Excel that loops through a lot of data. As part of the process, I'd like to pass a variable from Excel to an Access database that is open and have it run a query based on that value.
View 4 Replies
View Related
